12cよりSQL計画管理は自動展開タスクにより検証が自動化されました。
自動展開タスクはメンテナンスウィンドウを使用したアドバイザタスクとして実行されるか、DBMS_SPMパッケージのプロシージャによって、手動でタスクを作成し、実行します。
SQL> COLUMN parameter_name FORMAT a25
SQL> COLUMN parameter_value FORMAT a15
SQL> SELECT parameter_name, parameter_value
2 FROM dba_advisor_parameters
3 WHERE task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
4 AND parameter_value != 'UNUSED'
5 ORDER BY parameter_name;
PARAMETER_NAME PARAMETER_VALUE
------------------------- ---------------
ACCEPT_PLANS FALSE
DAYS_TO_EXPIRE UNLIMITED
DEFAULT_EXECUTION_TYPE SPM EVOLVE
EXECUTION_DAYS_TO_EXPIRE 30
JOURNALING INFORMATION
MODE COMPREHENSIVE
TARGET_OBJECTS 1
TIME_LIMIT 3600
_SPM_VERIFY TRUE
9行が選択されました。
自動展開タスクが有効化されているかは、dba_advisor_parametersのACCEPT_PLANSがTRUEになっていることを確認します。
SQL> BEGIN
2 DBMS_SPM.set_evolve_task_parameter(
3 task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
4 parameter => 'ACCEPT_PLANS',
5 value => 'TRUE');
6 END;
7 /
PL/SQLプロシージャが正常に完了しました。
無効（FASLSE）になっている場合はset_evolve_task_parameterにより有効化します。
SQL> CREATE TABLE spm_test_tab (
2 id NUMBER,
3 description VARCHAR2(50)
4 );
表が作成されました。
SQL> INSERT /*+ APPEND */ INTO spm_test_tab
2 SELECT level,
3 'Description for ' || level
4 FROM dual
5 CONNECT BY level <= 10000;
10000行が作成されました。
SQL> COMMIT;
コミットが完了しました。
自動展開タスクを検証するためテストテーブルを作成し、10000件のデータを挿入します。
SQL> SET AUTOTRACE TRACE
SQL> SELECT description
2 FROM spm_test_tab
3 WHERE id = 99;
実行計画
----------------------------------------------------------
Plan hash value: 1107868462
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SPM_TEST_TAB | 1 | 25 | 14 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=99)
統計
----------------------------------------------------------
1 recursive calls
3 db block gets
53 consistent gets
42 physical reads
124 redo size
584 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
10000件のデータから1件のデータを検索します。
インデックスが存在しないのでテーブルスキャンになります。
SQL> CONN / as sysdba
接続されました。
SQL> SELECT sql_id
2 FROM v$sql
3 WHERE plan_hash_value = 1107868462
4 AND sql_text NOT LIKE 'EXPLAIN%';
SQL_ID
-------------
gat6z1bc6nc2d
v$sqlビューより実行したSQLのSQL_IDをPlanのhash値より検索します。
SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => 'gat6z1bc6nc2d');
DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/
該当のSQLの実行計画（Plan）をbaselineにロードします。
COLUMN sql_handle FORMAT a20
COLUMN plan_name FORMAT a30
SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%spm_test_tab%'
AND sql_text NOT LIKE '%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ENA ACC
-------------------- ------------------------------ --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES
実行計画がベースラインに格納されたことが、dba_sql_plan_baselinesより確認できました。
自動展開タスクはメンテナンスウィンドウを使用したアドバイザタスクとして実行されるか、DBMS_SPMパッケージのプロシージャによって、手動でタスクを作成し、実行します。
SQL> COLUMN parameter_name FORMAT a25
SQL> COLUMN parameter_value FORMAT a15
SQL> SELECT parameter_name, parameter_value
2 FROM dba_advisor_parameters
3 WHERE task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
4 AND parameter_value != 'UNUSED'
5 ORDER BY parameter_name;
PARAMETER_NAME PARAMETER_VALUE
------------------------- ---------------
ACCEPT_PLANS FALSE
DAYS_TO_EXPIRE UNLIMITED
DEFAULT_EXECUTION_TYPE SPM EVOLVE
EXECUTION_DAYS_TO_EXPIRE 30
JOURNALING INFORMATION
MODE COMPREHENSIVE
TARGET_OBJECTS 1
TIME_LIMIT 3600
_SPM_VERIFY TRUE
9行が選択されました。
自動展開タスクが有効化されているかは、dba_advisor_parametersのACCEPT_PLANSがTRUEになっていることを確認します。
SQL> BEGIN
2 DBMS_SPM.set_evolve_task_parameter(
3 task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
4 parameter => 'ACCEPT_PLANS',
5 value => 'TRUE');
6 END;
7 /
PL/SQLプロシージャが正常に完了しました。
無効（FASLSE）になっている場合はset_evolve_task_parameterにより有効化します。
SQL> CREATE TABLE spm_test_tab (
2 id NUMBER,
3 description VARCHAR2(50)
4 );
表が作成されました。
SQL> INSERT /*+ APPEND */ INTO spm_test_tab
2 SELECT level,
3 'Description for ' || level
4 FROM dual
5 CONNECT BY level <= 10000;
10000行が作成されました。
SQL> COMMIT;
コミットが完了しました。
自動展開タスクを検証するためテストテーブルを作成し、10000件のデータを挿入します。
SQL> SET AUTOTRACE TRACE
SQL> SELECT description
2 FROM spm_test_tab
3 WHERE id = 99;
実行計画
----------------------------------------------------------
Plan hash value: 1107868462
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SPM_TEST_TAB | 1 | 25 | 14 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=99)
統計
----------------------------------------------------------
1 recursive calls
3 db block gets
53 consistent gets
42 physical reads
124 redo size
584 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
10000件のデータから1件のデータを検索します。
インデックスが存在しないのでテーブルスキャンになります。
SQL> CONN / as sysdba
接続されました。
SQL> SELECT sql_id
2 FROM v$sql
3 WHERE plan_hash_value = 1107868462
4 AND sql_text NOT LIKE 'EXPLAIN%';
SQL_ID
-------------
gat6z1bc6nc2d
v$sqlビューより実行したSQLのSQL_IDをPlanのhash値より検索します。
SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => 'gat6z1bc6nc2d');
DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/
該当のSQLの実行計画（Plan）をbaselineにロードします。
COLUMN sql_handle FORMAT a20
COLUMN plan_name FORMAT a30
SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%spm_test_tab%'
AND sql_text NOT LIKE '%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ENA ACC
-------------------- ------------------------------ --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES
実行計画がベースラインに格納されたことが、dba_sql_plan_baselinesより確認できました。
コメント