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));

SQL計画ベースラインに登録する

DECLARE
  ret PLS_INTEGER;
BEGIN 
  ret := dbms_spm.load_plans_from_cursor_cache(sql_id => 'cbnkw688amax5');
END;
/

SQL計画ベースラインを確認する

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

共有プール内実行計画をパージする。
（'00000000AA0FF3B0, 279554981'は前でv$sqlから取得したaddressとhash_value）

BEGIN 
  dbms_shared_pool.purge('00000000AA0FF3B0, 279554981','C');
END;
/

再度SQL文を実行する

select * from SH.CUSTOMERS where cust_id < 13894;

実行計画を確認し、SQL計画ベースラインが利用された。

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

やりたいのはSQL文を変えずに、「TABLE ACCESS FULL」ではなく、indexを利用する。
hint句でindexを利用する実行計画を作成する。

select /*+ index(CUSTOMERS CUSTOMERS_PK) */ * 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('78u6gacgk780f',0));

下記パラメータを使ってindexを利用する実行計画をhint句なしのSQL文のSQL計画ベースラインに登録する。
・SQL計画ベースラインに登録済のhint句なしのsql_handle
・hint句ありのsql_idおよびplan_hash_value

DECLARE
  ret PLS_INTEGER;
BEGIN 
  ret := dbms_spm.load_plans_from_cursor_cache(
           sql_handle      => 'SQL_45773f7cb700d0bd',
           sql_id          => '78u6gacgk780f',
           plan_hash_value => '116944297'
         );
END;
/

SQL計画ベースラインを確認する

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

hint句なしのSQL文の計画ベースラインに2つの実行計画が登録された。
先に登録した実行計画を削除する。

DECLARE
  ret PLS_INTEGER;
BEGIN 
  ret := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_45773f7cb700d0bd',plan_name => 'SQL_PLAN_4axtzgkvh1n5x64541f84');
END;
/

再度SQL計画ベースラインを確認する

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

SQL計画ベースラインから実行計画を確認し、indexが利用される。

select * from table(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle => 'SQL_45773f7cb700d0bd',plan_name => 'SQL_PLAN_4axtzgkvh1n5x2ee72f5f'));

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

alter system flush shared_pool;--本番環境での利用は要注意
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));

以上