Script to get explain plan for the baselines created with SQL_ID

Please run the below query and get the explain plan for which baselines created for an SQL ID.

col sql_text for a60 wrap
set verify off
set pagesize 999
set lines 155
col username format a13
col prog format a22
col sid format 999
col child_number format 99999 heading CHILD
col ocategory format a10
col avg_etime format 9,999,999.99
col avg_pio format 9,999,999.99
col avg_lio format 999,999,999
col etime format 9,999,999.99
SELECT sql_handle
FROM
  (SELECT t.SQL_HANDLE,
    t.SIGNATURE,
    t.SQL_TEXT,
    s.SQL_ID
  FROM SYS.SQL$TEXT t,
    DBA_HIST_SQLSTAT s
  WHERE s.SQL_ID = nvl(‘&sql_id’,sql_id)
  AND t.SIGNATURE    = s.FORCE_MATCHING_SIGNATURE
  ) WHERE rownum = 1 ;

spool baseline.out
select * from table(dbms_xplan.display_sql_plan_baseline(‘&sql_handle’,format => ‘all’));
spool off
quit

  • March 12, 2019 | 16 views
  • Comments