- データベース管理者のための主要20c新機能の学習
- データベース管理者のための主要20c新機能の学習
- パフォーマンスおよび高可用性のオプション
- 自動操作
- 自動索引最適化
- 演習: 索引のストレージ階層化ADOポリシーの実装
演習: 索引のストレージ階層化ADOポリシーの実装
この演習では、自動データ最適化ポリシーで定義された特定の条件に応じて、別の表領域への索引の移動を自動化する方法を示します。
- 新しい演習を開始する前に、演習の環境の推奨事項を参照してください。
- 索引にストレージ層化ADOポリシーを作成する前に、
/home/oracle/labs/M104783GC10/ADO_setup.shを実行します。このシェル・スクリプトでは、既存のADOポリシーをクリーン・アップし、ADOTBSINDX表領域からLOW_COST_STORE_INDX表領域に索引を移動するために2つの表領域を作成し、索引がADOTBSINDXに格納されている主キーPK_EMPLOYEE_IDを持つHR.EMP表を作成します。また、ヒート・マップ統計の収集も開始します。$ cd /home/oracle/labs/M104783GC10 $ /home/oracle/labs/M104783GC10/ADO_setup.sh SQL*Plus: Release 20.0.0.0.0 - Production on Tue Jan 7 03:31:27 2020 Version 20.2.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production Version 20.2.0.0.0 SQL> set feedback off SQL> delete ilm_results$; SQL> delete ilm_execution$; SQL> delete ilm_executiondetails$; SQL> DROP TABLESPACE adotbsindx INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE adotbsindx INCLUDING CONTENTS AND DATAFILES * ERROR at line 1: ORA-00959: tablespace 'ADOTBSINDX' does not exist SQL> DROP TABLESPACE low_cost_store_indx INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE low_cost_store_indx INCLUDING CONTENTS AND DATAFILES * ERROR at line 1: ORA-00959: tablespace 'LOW_COST_STORE_INDX' does not exist SQL> SQL> declare 2 begin 3 dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.TBS_PERCENT_USED,85); 4 exception 5 when others then 6 raise; 7 end; 8 / SQL> SQL> declare 2 begin 3 dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.TBS_PERCENT_FREE,25); 4 exception 5 when others then 6 raise; 7 end; 8 / SQL> SQL> exit Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production Version 20.2.0.0.0 SQL*Plus: Release 20.0.0.0.0 - Production on Tue Jan 7 03:31:28 2020 Version 20.2.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production Version 20.2.0.0.0 SQL> set feedback off SQL> delete ilm_results$; SQL> delete ilm_execution$; SQL> delete ilm_executiondetails$; SQL> DROP TABLESPACE adotbsindx INCLUDING CONTENTS AND DATAFILES; SQL> DROP TABLESPACE low_cost_store_indx INCLUDING CONTENTS AND DATAFILES; SQL> SQL> declare 2 begin 3 dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.TBS_PERCENT_USED,85); 4 exception 5 when others then 6 raise; 7 end; 8 / SQL> SQL> declare 2 begin 3 dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.TBS_PERCENT_FREE,25); 4 exception 5 when others then 6 raise; 7 end; 8 / SQL> SQL> exit Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production Version 20.2.0.0.0 SQL*Plus: Release 20.0.0.0.0 - Production on Tue Jan 7 03:31:34 2020 Version 20.2.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production Version 20.2.0.0.0 SQL> ALTER SYSTEM SET heat_map=on SCOPE=BOTH; System altered. SQL> exit Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production Version 20.2.0.0.0 SQL*Plus: Release 20.0.0.0.0 - Production on Mon Jan 6 03:29:05 2020 Version 20.2.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production Version 20.2.0.0.0 specify password for HR as parameter 1: specify default tablespeace for HR as parameter 2: specify temporary tablespace for HR as parameter 3: specify log path as parameter 4: PL/SQL procedure successfully completed. User created. User altered. User altered. Grant succeeded. Grant succeeded. Session altered. Session altered. Session altered. ****** Creating REGIONS table .... ... ****** Creating EMPLOYEES table .... ... ****** Populating EMPLOYEES table .... ... 1 row created. ... Index created. ... Trigger altered. ... PL/SQL procedure successfully completed. SQL> Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production Version 20.2.0.0.0 SQL*Plus: Release 20.0.0.0.0 - Production on Mon Jan 6 03:29:13 2020 Version 20.2.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production Version 20.2.0.0.0 SQL> SQL> GRANT select any dictionary TO hr; Grant succeeded. SQL> CREATE TABLESPACE adotbsindx 2 DATAFILE '/home/oracle/labs/adotbs1.dbf' 3 size 2m reuse autoextend off extent management local uniform size 64K; Tablespace created. SQL> CREATE TABLESPACE low_cost_store_indx 2 DATAFILE '/home/oracle/labs/lcs.dbf' 3 size 100M; Tablespace created. SQL> SQL> CREATE TABLE hr.emp TABLESPACE users AS SELECT * FROM hr.employees ; Table created. SQL> ALTER TABLE hr.emp MODIFY employee_id NUMBER(38) ; Table created. SQL> ALTER TABLE hr.emp ADD CONSTRAINT pk_employee_id primary key (employee_id) using index tablespace adotbsindx; Table altered. SQL> INSERT INTO hr.emp 2 SELECT employee_id*3, first_name,last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id 3 FROM hr.emp; 107 rows created. SQL> INSERT INTO hr.emp 2 SELECT employee_id*7, first_name,last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id 3 FROM hr.emp; 214 rows created. SQL> COMMIT; Commit complete. SQL> exit Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production Version 20.2.0.0.0 $ HR.EMP表の主キーの索引が格納されている表領域と、セグメントが使用している領域の量を表示します。$ sqlplus system@PDB20 SQL*Plus: Release 20.0.0.0.0 - Production on Mon Jan 6 03:36:57 2020 Version 20.2.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: password Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production Version 20.2.0.0.0 SQL> COL tablespace_name FORMAT A20 SQL> COL index_name FORMAT A20 SQL> COL owner FORMAT A10 SQL> SELECT tablespace_name, index_name, owner FROM dba_indexes WHERE table_name='EMP'; TABLESPACE_NAME INDEX_NAME OWNER -------------------- -------------------- ---------- ADOTBSINDX PK_EMPLOYEE_ID HR SQL> SQL> SELECT bytes FROM dba_segments WHERE segment_name='PK_EMPLOYEE_ID'; BYTES ---------- 65536 SQL>HR.EMP表の主キーの索引が格納されている表領域の使用済領域および空き領域を表示します。SQL> SELECT /* + RULE */ df.tablespace_name "Tablespace", df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)", Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used" FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name,df.bytes ORDER BY 4; Tablespace Size (MB) Free (MB) % Free % Used ------------------------------ ---------- ---------- ---------- ---------- SYSTEM 270 6.125 2 98 SYSAUX 340 17.1875 5 95 USERS 5 2.3125 46 54 ADOTBSINDX 2 .9375 47 53 UNDOTBS1 100 66.125 66 34 LOW_COST_STORE_INDX 100 99 99 1 6 rows selected. SQL>ADOTBSINDX表領域の空き領域の割合が90%未満の場合に、評価されるILMポリシーによってADOアクションがトリガーされて索引がLOW_COST_STORE_INDX表領域に移動されるように、ストレージ階層化ADOポリシーを索引に作成します。SQL> ALTER INDEX hr.pk_employee_id ILM ADD POLICY TIER TO low_cost_store_indx; Index altered. SQL>- データ・ディクショナリ・ビューにポリシーを表示します。
SQL> CONNECT hr@PDB20 Enter password: password Connected. SQL> SELECT policy_name, action_type, scope, tier_tablespace "TIER_TBS" FROM user_ilmdatamovementpolicies ORDER BY policy_name; POLI ACTION_TYPE SCOPE TIER_TBS ---- ----------- ------- -------------------- P61 STORAGE SEGMENT LOW_COST_STORE_INDX SQL> - 挿入された索引エントリによって
ADOTBSINDX表領域の空の領域の割合が90%未満になるまで、HR.EMPに行を挿入します。SQL> INSERT INTO hr.emp SELECT employee_id*101, first_name,last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id FROM hr.emp; 428 rows created. SQL> INSERT INTO hr.emp SELECT employee_id+436926 , first_name,last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id FROM hr.emp; 856 rows created. SQL> COMMIT; Commit complete. SQL> SELECT /* + RULE */ df.tablespace_name "Tablespace", df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)", Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used" FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name,df.bytes ORDER BY 4; Tablespace Size (MB) Free (MB) % Free % Used ------------------------------ ---------- ---------- ---------- ---------- SYSTEM 270 6.125 2 98 SYSAUX 340 16.625 5 95 ADOTBSINDX 2 .875 44 56 USERS 5 2.25 45 55 UNDOTBS1 100 66.6875 67 33 LOW_COST_STORE_INDX 100 99 99 1 6 rows selected. SQL>挿入された索引エントリによって、
ADOTBSINDX表領域の空き領域の割合が90%未満になります。 HR.EMP表の主キーの索引が現在格納されている表領域を表示します。索引はLOW_COST_STORE_INDX表領域に移動されていますか。SQL> SELECT tablespace_name, index_name, owner FROM dba_indexes WHERE table_name='EMP'; TABLESPACE_NAME INDEX_NAME OWNER -------------------- -------------------- ---------- ADOTBSINDX PK_EMPLOYEE_ID HR SQL>ADOTBSINDX表領域の空き領域の割合は90%未満ですが、索引は他の表領域に移動されていません。- セグメントを移動するADOの決定は、すべてのユーザー定義表領域に対してデータベース・レベルで定義されているデフォルトのしきい値にも依存します。
TBS_PERCENT_FREEしきい値を90%に、TBS_PERCENT_USEDしきい値を30%に設定します。SQL> CONNECT sys@PDB20 AS SYSDBA Enter password: password Connected. SQL> COL name FORMAT A40 SQL> SELECT * FROM dba_ilmparameters; NAME VALUE ---------------------------------------- ---------- ENABLED 1 RETENTION TIME 30 JOB LIMIT 2 EXECUTION MODE 2 EXECUTION INTERVAL 15 TBS PERCENT USED 85 TBS PERCENT FREE 25 POLICY TIME 0 8 rows selected. SQL> EXEC dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.TBS_PERCENT_FREE,90) PL/SQL procedure successfully completed. SQL> EXEC dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.TBS_PERCENT_USED,30) PL/SQL procedure successfully completed. SQL> SELECT * FROM dba_ilmparameters; NAME VALUE ---------------------------------------- ---------- ENABLED 1 RETENTION TIME 30 JOB LIMIT 2 EXECUTION MODE 2 EXECUTION INTERVAL 15 TBS PERCENT USED 30 TBS PERCENT FREE 90 POLICY TIME 0 8 rows selected. SQL>- デモの目的上、ADOポリシー・ジョブをトリガーするためにメンテナンス・ウィンドウが開くまで待ちません。かわりに、ADOポリシー所有者
HRとして接続された次のPL/SQLブロックを使用する次のコマンドを実行します。SQL> CONNECT hr@PDB20 Enter password: password Connected. SQL> ALTER SESSION SET nls_date_format='dd-mon-yy hh:mi:ss'; Session altered. SQL> DECLARE v_executionid number; BEGIN dbms_ilm.execute_ILM (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA, execution_mode => dbms_ilm.ilm_execution_offline, task_id => v_executionid); END; / PL/SQL procedure successfully completed. SQL> - 索引が
LOW_COST_STORE_INDX表領域に移動されているかどうかを再度確認します。SQL> COL object_type FORMAT A10 SQL> COL object_name FORMAT A14 SQL> COL selected_for_execution FORMAT A28 SQL> COL job_name FORMAT A9 SQL> SELECT OBJECT_TYPE, OBJECT_NAME, SELECTED_FOR_EXECUTION, JOB_NAME FROM user_ilmevaluationdetails; OBJECT_TYP OBJECT_NAME SELECTED_FOR_EXECUTION JOB_NAME ---------- -------------- ---------------------------- --------- INDEX PK_EMPLOYEE_ID SELECTED FOR EXECUTION ILMJOB124 SQL> SELECT task_id, job_name, job_state FROM user_ilmresults; TASK_ID JOB_NAME JOB_STATE ------- ---------- ----------------------------------- 41 ILMJOB124 COMPLETED SUCCESSFULLY SQL>
HR.EMP表の主キーの索引が現在格納されている表領域を表示します。索引はLOW_COST_STORE_INDX表領域に移動されていますか。SQL> SELECT tablespace_name, index_name, owner FROM dba_indexes WHERE table_name='EMP'; TABLESPACE_NAME INDEX_NAME OWNER -------------------- -------------------- ---------- LOW_COST_STORE_INDX PK_EMPLOYEE_ID HR SQL>索引は他の表領域に移動されています。
- 索引のADOポリシーを削除します。
SQL> ALTER INDEX pk_employee_id ILM DELETE POLICY p61; Index altered. SQL> - ヒート・マップ統計収集を停止し、すべてのヒート・マップ統計をクリーン・アップします。
SQL> CONNECT / AS SYSDBA Connected. SQL> ALTER SYSTEM SET heat_map=off SCOPE=BOTH; System altered. SQL> EXEC dbms_ilm_admin.clear_heat_map_all PL/SQL procedure successfully completed. SQL> EXIT $
親トピック: 自動索引最適化