- データベース管理者のための主要20c新機能の学習
- データベース管理者のための主要20c新機能の学習
- パフォーマンスおよび高可用性のオプション
- 自動操作
- 自動索引最適化
- 演習: 索引の最適化ADOポリシーの実装
演習: 索引の最適化ADOポリシーの実装
この演習では、自動データ最適化ポリシーで定義された特定の条件に応じて、既存の自動データ最適化(ADO)フレームワークを使用して、索引の圧縮および最適化を自動化する方法を示します。
- 新しい演習を開始する前に、演習の環境の推奨事項を参照してください。
- 索引に最適化ADOポリシーを作成する前に、
/home/oracle/labs/M104783GC10/ADO_setup2.shを実行します。このシェル・スクリプトでは、既存のADOポリシーをクリーン・アップし、HR.EMP表を作成してヒート・マップ統計の収集を開始します。$ cd /home/oracle/labs/M104783GC10 $ /home/oracle/labs/M104783GC10/ADO_setup2.sh SQL*Plus: Release 20.0.0.0.0 - Production on Tue Jan 7 03:35:49 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> 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:35:50 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> 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:35:51 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> DROP TABLESPACE low_cost_store_indx INCLUDING CONTENTS AND DATAFILES; Tablespace dropped. 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 $ - 索引に最適化ADOポリシーを作成する前に、
HR.EMP表の2つの列FIRST_NAMEおよびLAST_NAMEにコンポジット索引I_NAMEを作成します。$ sqlplus hr@PDB20 Enter password: password Connected. SQL> CREATE INDEX hr.i_name ON hr.emp (first_name, last_name) TABLESPACE low_cost_store_indx; Index created. SQL> - 索引の圧縮属性を確認します。
SQL> COL index_name FORMAT A26 SQL> SELECT compression, index_name FROM dba_indexes WHERE table_name='EMP'; COMPRESSION INDEX_NAME ------------- -------------------- DISABLED PK_EMPLOYEE_ID DISABLED I_NAME SQL> - OPTIMIZE ADOポリシーを
I_NAME索引に追加します。OPTIMIZE句を指定すると、索引に最適なアクションが自動的に判別され、最適化プロセスの一部としてそのアクションが実装されます。実行するアクションを指定する必要はありません。最適化プロセスには、索引の圧縮、縮小または再構築などの処理が含まれます。OPTIMIZE句により、ポリシー条件が満たされるたびにADOで索引を最適化する機会が与えられます。ADOによって起動される正確なアクションは、Oracle Databaseによって行われる決定に基づきます。たとえば、30%を超えるリーフ・ブロックがCOALESCEに適している場合、REBUILD ONLINEの経過時間が短くなり、確実にUNDOの生成が少なくなる可能性があります。SQL> ALTER INDEX hr.i_name ILM ADD POLICY OPTIMIZE AFTER 10 DAYS OF NO MODIFICATION; Index altered. SQL> - ポリシーが追加されていることを確認します。
SQL> SELECT policy_name, action_type, scope, compression_level, condition_type, condition_days FROM user_ilmdatamovementpolicies ORDER BY policy_name; 2 3 4 POLI ACTION_TYPE SCOPE COMPRESSION_LEVEL CONDITION_TYPE ---- ----------- ------- ----------------- ---------------------- CONDITION_DAYS -------------- P62 OPTIMIZE SEGMENT LAST MODIFICATION TIME 10 SQL> - ポリシーが日数ではなく秒数で指定されることを示すには、
POLICY TIMEをデフォルト値の0 (日)ではなく、1 (秒)に設定してポリシーの期間を待たずにすぐにADOポリシーの評価をテストします。SQL> CONNECT sys@PDB20 AS SYSDBA Enter password: password Connected. SQL> EXEC dbms_ilm_admin.customize_ilm(dbms_ilm_admin.POLICY_TIME,dbms_ilm_admin.ILM_POLICY_IN_SECONDS) 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 85 TBS PERCENT FREE 25 POLICY TIME 1 8 rows selected. SQL> HR.EMP表、ひいてはHR.I_NAME索引を変更せずに、少なくとも1分(10日ではなく)経過するまで待ちます。デモの目的上、MMONがADOポリシーを評価するまで待ちません。ADOポリシー評価およびADOタスク実行は、次の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>- 実行したタスクの結果を表示します。
SQL> COL SELECTED_FOR_EXECUTION FORMAT A28 SQL> COL job_name FORMAT A9 SQL> SELECT task_id, task_owner, state FROM dba_ilmtasks WHERE task_owner='HR'; TASK_ID TASK_OWN STATE ------- -------- --------- 42 HR COMPLETED SQL> SELECT task_id, policy_name, object_name, selected_for_execution, job_name FROM dba_ilmevaluationdetails WHERE object_name='I_NAME'; TASK_ID POLI OBJECT_N SELECTED_FOR_EXECUTION JOB_NAME ------- ---- -------- ------------------------------ --------- 42 P62 I_NAME STATISTICS NOT AVAILABLE - 索引統計を収集します。
SQL> ANALYZE INDEX hr.i_name COMPUTE STATISTICS; Index analyzed. SQL> - ADOポリシー評価およびADOタスク実行をすぐに再起動する前に、少なくとも1分待ちます。
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> SELECT task_id, task_owner, state FROM dba_ilmtasks WHERE task_owner='HR'; TASK_ID TASK_OWN STATE ------- -------- --------- 42 HR COMPLETED 43 HR COMPLETED SQL> SELECT task_id, policy_name, object_name, selected_for_execution, job_name FROM dba_ilmevaluationdetails WHERE object_name='I_NAME'; TASK_ID POLI OBJECT_N SELECTED_FOR_EXECUTION JOB_NAME ------- ---- -------- ---------------------------- --------- 43 P62 I_NAME PRECONDITION NOT SATISFIED 42 P62 I_NAME STATISTICS NOT AVAILABLE SQL>PRECONDITION NOT SATISFIEDが表示されない場合は、索引にさらに多くのエントリを生成します。ステップ12に進みます。いずれの場合も、ステップ12に進みます。 - 表にさらに行を挿入して、索引にさらに多くのエントリを生成します。
/home/oracle/labs/M104783GC10/ADO_loop_insert.sqlSQLスクリプトを使用します。SQL> @/home/oracle/labs/M104783GC10/ADO_loop_insert.sql SQL> SET ECHO ON SQL> CONNECT hr/password@PDB20 Connected. SQL> INSERT INTO hr.emp 2 SELECT employee_id + (select max(employee_id) from hr.emp), first_name,last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id 3 FROM hr.emp; 428 rows created. ... SQL> INSERT INTO hr.emp 2 SELECT employee_id + (select max(employee_id) from hr.emp), first_name,last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id 3 FROM hr.emp; 109568 rows created. SQL> COMMIT; Commit complete. SQL> ANALYZEコマンドを使用して、索引統計を収集します。SQL> ANALYZE INDEX hr.i_name COMPUTE STATISTICS; Index analyzed. SQL>- ADOポリシー評価およびADOタスク実行をすぐに再起動する前に、少なくとも1分待ちます。
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> SELECT task_id, task_owner, state FROM dba_ilmtasks WHERE task_owner='HR'; TASK_ID TASK_OWN STATE ------- -------- --------- 42 HR COMPLETED 43 HR COMPLETED 44 HR COMPLETED SQL> SELECT task_id, policy_name, object_name, selected_for_execution, job_name FROM dba_ilmevaluationdetails WHERE object_name='I_NAME'; TASK_ID POLI OBJECT_N SELECTED_FOR_EXECUTION JOB_NAME ------- ---- -------- ---------------------------- --------- 44 P62 I_NAME SELECTED FOR EXECUTION ILMJOB164 43 P62 I_NAME PRECONDITION NOT SATISFIED 42 P62 I_NAME STATISTICS NOT AVAILABLE SQL>実行の事前条件がまだ満たされていない場合は、表にさらに行を挿入して、索引にさらに多くのエントリを生成します。
/home/oracle/labs/M104783GC10/ADO_loop_insert2.sqlSQLスクリプトを使用します。次に、ステップ13および14を再実行します。 - 索引の圧縮属性を表示します。
SQL> SELECT compression, index_name FROM dba_indexes WHERE table_name='EMP'; COMPRESSION INDEX_NAME ------------- -------------------- DISABLED PK_EMPLOYEE_ID ADVANCED LOW I_NAME SQL> - 索引のADOポリシーを削除します。
SQL> ALTER INDEX hr.i_name ILM DELETE POLICY p62; 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 $
親トピック: 自動索引最適化