Oracle Database Standard Edition SQLプランベースラインによる実行計画の固定化

Oracle Database Standard Edition(SE2)はSQL文ごとに許可されるSQLプランベースラインは1つのみであり、以下の記事の通り、実行計画の固定化はできない。
SQLプランベースラインによる実行計画の固定化
本記事はストアドアウトライン経由で実行計画の固定化を行う。

下記SQL文を実行する。

select * from SH.CUSTOMERS where cust_id < 13894;

実行計画を確認する。

select sql_id,child_number,address,hash_value,plan_hash_value, sql_text from v$sql where sql_text like '%cust_id < 13894%' and sql_text not like '%v$sql%';

select * from table(DBMS_XPLAN.DISPLAY_CURSOR('cbnkw688amax5',0));

PRIVATE OUTLINEを作成する。

CREATE PRIVATE OUTLINE CUSTOMERS_SELECT
ON select * from SH.CUSTOMERS where cust_id < 13894;

indexを利用するPRIVATE OUTLINEを作成する。

CREATE PRIVATE OUTLINE CUSTOMERS_SELECT_USE_INDEX
ON select /*+ index(CUSTOMERS CUSTOMERS_PK) */ * from SH.CUSTOMERS where cust_id < 13894;

作成したPRIVATE OUTLINEを確認する。

--OL$はグローバル一時テーブルSYSTEM.OL$のパブリックシノニム
select OL_NAME,SQL_TEXT,CATEGORY,HINTCOUNT from OL$;

--OL$HINTSはグローバル一時テーブルSYSTEM.OL$HINTSのパブリックシノニム
select OL_NAME,HINT#,CATEGORY,HINT_TYPE,HINT_TEXT from OL$HINTS;

hint句なしのSQL文のOUTLINEをhint句ありのSQL文のOUTLINEを入れ替える。

UPDATE ol$
SET
    hintcount = (
        SELECT
            hintcount
        FROM
            ol$
        WHERE
            ol_name = 'CUSTOMERS_SELECT_USE_INDEX'
    )
WHERE
    ol_name = 'CUSTOMERS_SELECT';
    
DELETE ol$hints
WHERE
    ol_name = 'CUSTOMERS_SELECT';

UPDATE ol$hints
SET
    ol_name = 'CUSTOMERS_SELECT'
WHERE
    ol_name = 'CUSTOMERS_SELECT_USE_INDEX';

DELETE FROM ol$
WHERE
    ol_name = 'CUSTOMERS_SELECT_USE_INDEX';

編集結果を確認する。

--OL$はグローバル一時テーブルSYSTEM.OL$のパブリックシノニム
select OL_NAME,SQL_TEXT,CATEGORY,HINTCOUNT from OL$;

--OL$HINTSはグローバル一時テーブルSYSTEM.OL$HINTSのパブリックシノニム
select OL_NAME,HINT#,CATEGORY,HINT_TYPE,HINT_TEXT from OL$HINTS;

PUBLIC OUTLINEを作成する。

CREATE PUBLIC OUTLINE PUBLIC_CUSTOMERS_SELECT FROM PRIVATE CUSTOMERS_SELECT;

PUBLIC OUTLINEが作成された。

select * from dba_outlines;

select * from dba_outline_hints;

SQL文を実行し、ストアドアウトラインによる実行計画が固定化され、indexが利用されることを確認する。

ALTER SESSION SET use_stored_outlines = true;
select * from SH.CUSTOMERS where cust_id < 13894;
select sql_id,child_number,address,hash_value,plan_hash_value, sql_text from v$sql where sql_text like '%cust_id < 13894%' and sql_text not like '%v$sql%';

最新の実行計画を確認する。

select * from table(DBMS_XPLAN.DISPLAY_CURSOR('cbnkw688amax5',1));

ストアドアウトラインをSQLプランベースラインに移行する。

DECLARE
    ret CLOB;
BEGIN
    ret := dbms_spm.migrate_stored_outline(attribute_name => 'outline_name', attribute_value => 'PUBLIC_CUSTOMERS_SELECT');
    dbms_output.put_line(ret);
END;
/

PUBLIC OUTLINEが「MIGRATED」になっている。

select * from dba_outlines;

移行されたSQLプランベースラインを確認する

select signature,sql_handle, sql_text,plan_name,created,last_modified,enabled,accepted,fixed from dba_sql_plan_baselines;

移行済のPUBLIC OUTLINEを削除する。

DECLARE
    ret PLS_INTEGER;
BEGIN
    ret := DBMS_SPM.DROP_MIGRATED_STORED_OUTLINE;
END;
/

最後に、SQL文を実行し、SQL計画ベースラインによる実行計画が固定化され、indexが利用されることを確認する。

select * from SH.CUSTOMERS where cust_id < 13894;
select sql_id,child_number,hash_value,plan_hash_value, sql_text from v$sql where sql_text like '%cust_id < 13894%' and sql_text not like '%v$sql%';

最新の実行計画を確認する。

select * from table(DBMS_XPLAN.DISPLAY_CURSOR('cbnkw688amax5',2));

以上