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より確認できました。