SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE( sql_handle⇒'SYS_SQL_209d10fabbedc741',format⇒'basic'));

select * from dbms_xplan.display_sql_plan_baseline('SQL_40fcb6653c2a4dfe','SQL_PLAN_41z5qcny2nmgyde0711fe');

1 - Retrieve Current SQL_ID + BAD Execution Plan from OEM or Perform query on :

e.g.:

- select * from gv$sql_plan where sql_id = '5wyb7qcb01mxb'; - select * from gv$open_cursor where sql_id = '5wyb7qcb01mxb'; - select * from gv$open_cursor where sql_text like '%V_FBNK_CUSTOMER%'; - select * from gv$sql where sql_text like '%V_FBNK_CUSTOMER%';

Query:

SELECT RECID FROM T24.V_FBNK_CUSTOMER WHERE BIL_UP_SHRT_NME LIKE :1 ORDER BY MNEMONIC ASC NULLS FIRST, RECID;

SQL_ID + BAD Execution Plan:

SQL_ID: 5wyb7qcb01mxb sql plan 2008034826 =⇒ Bad

2 - Load Bad Execution Plan into the Baseline:

var res number; exec :res := dbms_spm.load_plans_from_cursor_cache(sql_id ⇒ '5wyb7qcb01mxb', plan_hash_value ⇒ 2008034826);

Check Loaded Baseline for the Bad execution plan:

select * from DBA_SQL_PLAN_BASELINES order by created;

select * from DBA_SQL_PLAN_BASELINES where to_char(created,'dd/mm/yyyy') = to_char(sysdate,'dd/mm/yyyy') order by created;

SQL_HANDLE retrieved from above query:

sql_handle ⇒ 'SQL_677239092ca3b096'

3 - Generate New Execution Plan using Hint (/*+ INDEX (TABLE_NAME INDEX_NAME)*/):

SELECT /*+ INDEX(V_FBNK_CUSTOMER FBNK_CUSTOMER_USN_IDX)*/ RECID FROM T24.V_FBNK_CUSTOMER WHERE BIL_UP_SHRT_NME LIKE :1 ORDER BY MNEMONIC ASC NULLS FIRST, RECID;

select * from table(dbms_xplan.display_cursor);

SQL_ID: 8y3pyv1axd3dv sql plan 4238396409 =⇒ Good

4 - Load Good Execution Plan (SQL_ID: 8y3pyv1axd3dv) into the Baseline for the SQL_HANDLE retrieved from the Bad Execution Plan (SQL_ID: 5wyb7qcb01mxb  SQL_HANDLE: SQL_677239092ca3b096)

var res number exec :res := dbms_spm.load_plans_from_cursor_cache(sql_id ⇒ '8y3pyv1axd3dv', plan_hash_value ⇒ 4238396409, sql_handle ⇒ 'SQL_677239092ca3b096', fixed ⇒ 'YES');

select * from DBA_SQL_PLAN_BASELINES order by created;

select * from DBA_SQL_PLAN_BASELINES where to_char(created,'dd/mm/yyyy') = to_char(sysdate,'dd/mm/yyyy') order by created;

5 - Drop Bad Execution Plan from the SQL Baseline:

SQL_HANDLE (SQL_677239092ca3b096) + PLAN_NAME (SQL_PLAN_6fwjt14qa7c4q330d7e98)

var res number exec :res :=DBMS_SPM.DROP_SQL_PLAN_BASELINE ('SQL_677239092ca3b096','SQL_PLAN_6fwjt14qa7c4q330d7e98');

select * from DBA_SQL_PLAN_BASELINES order by created;

PS: Those steps are based on the following doc: https://wiki-it/pages/viewpage.action?spaceKey=DBA&title=Load+hinted+plan+in+baseline

select snap_id,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id ='3cx8dxr6g8bzf';

var res number exec :res := dbms_spm.load_plans_from_awr( begin_snap⇒1,end_snap⇒43, basic_filter⇒q'# sql_id='3cx8dxr6g8bzf' and plan_hash_value='2373235350' #' );

–AWR SPECIFIC SQL_ID: @?/rdbms/admin/awrsqrpt.sql;

– Check query using sql plan baseline SELECT

  s.inst_id,
  s.sql_id,
  s.sql_text,
  s.first_load_time,
  s.last_load_time,
  to_char(s.last_active_time, 'dd/mm/yyyy hh24:mi:ss') AS last_active_time,
  b.sql_handle,
  b.plan_name,
  b.creator,
  b.origin,
  b.created,
  b.enabled,
  b.accepted,
  b.fixed,
  b.reproduced,
  b.optimizer_cost,
  b.module

FROM

  dba_sql_plan_baselines  b,
  gv$sql                  s

WHERE

      s.exact_matching_signature = b.signature
  AND s.sql_plan_baseline = b.plan_name;

var res number exec :res := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle ⇒ 'SQL_40fcb6653c2a4dfe', plan_name ⇒ 'SQL_PLAN_41z5qcny2nmgy330d7e98', attribute_name ⇒ 'ENABLED', attribute_value ⇒ 'NO');

var res number exec :res := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle ⇒ 'SQL_40fcb6653c2a4dfe', plan_name ⇒ 'SQL_PLAN_41z5qcny2nmgyde0711fe', attribute_name ⇒ 'AUTOPURGE', attribute_value ⇒ 'NO');

— Exec time + info + dba_sql_plan_baselines SELECT s.inst_id,

       s.EXECUTIONS "EXEC",
       round(DECODE(s.executions,0,0,(s.ELAPSED_TIME/s.EXECUTIONS))/1000000) "ELAPSED_PER_EXEC_IN_SEC", 
       s.sql_id,
       s.plan_hash_value,
       s.SQL_PROFILE,
       s.first_load_time,
       s.last_load_time,
       to_char(s.last_active_time, 'dd/mm/yyyy hh24:mi:ss') AS last_active_time,
       b.sql_handle,
       b.plan_name,
       s.hash_value,
       s.rows_processed "ROWS",
       s.sorts "SORTS",
       round(DECODE(s.executions,0,0,s.BUFFER_GETS/s.EXECUTIONS)) "BLOCKS_PER_EXEC",
       round(DECODE(s.executions,0,0,s.DISK_READS/s.EXECUTIONS)) "DISK_READS_PER_EXEC",
       round(DECODE(s.executions,0,0,(s.CPU_TIME/s.EXECUTIONS))/1000000) "CPU_TIME_PER_EXEC_IN_SEC", 
       round(DECODE(s.executions,0,0,((s.IO_CELL_OFFLOAD_RETURNED_BYTES/s.EXECUTIONS)/1024/1024/1024)))||' Gb' "IO_CELL_OFFLOAD_RETURNED_BYTES",
       round(DECODE(s.executions,0,0,((s.IO_CELL_OFFLOAD_ELIGIBLE_BYTES/s.EXECUTIONS)/1024/1024/1024)))||' Gb' "IO_CELL_OFFLOAD_ELIGIBLE_BYTES",
       round(DECODE(s.executions,0,0,((s.IO_INTERCONNECT_BYTES/s.EXECUTIONS)/1024/1024/1024)))||' Gb' "IO_INTERCONNECT_BYTES",         
       decode(s.IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,0,100*(s.IO_CELL_OFFLOAD_ELIGIBLE_BYTES-s.IO_INTERCONNECT_BYTES)
       /decode(s.IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,1,s.IO_CELL_OFFLOAD_ELIGIBLE_BYTES)) "IO_SAVED_%",
       s.buffer_gets "BUF_GETS",
       s.disk_reads "DSK_READS",
       s.parse_calls "PRS_CALLS",
       ROUND(s.cpu_time/1000000) "TOTAL_CPU_TIME_IN_SEC",
       s.sql_text,
       s.sql_fulltext,         
       s.module,
       s.action,
       s.child_number

FROM GV$SQL s, dba_sql_plan_baselines b WHERE s.exact_matching_signature = b.signature and s.sql_plan_baseline = b.plan_name and nvl(s.MODULE,'NULL') NOT LIKE '%TOAD%' AND nvl(s.MODULE,'NULL') NOT LIKE '%T.O.A.D%' AND nvl(s.MODULE,'NULL') NOT IN ('DBMS_SCHEDULER','MMON_SLAVE') and (round(DECODE(s.executions,0,0,(s.ELAPSED_TIME/s.EXECUTIONS))/1000000) > 5) and regexp_like(s.sql_fulltext,'FBNK_CUSTOMER','i') and s.last_active_time >= sysdate - 20 /(24*60) order by s.last_active_time desc;