Child pages
  • Executions plans for SQL from AWR
Skip to end of metadata
Go to start of metadata

How to obtain execution plans from the Oracle Database optimizer for SQL statements mentioned in an AWR report

SQL> col parsed format a6
col sql_text format a40
set lines 200
set pages 300
select     
 sql_text,
 parsing_schema_name as parsed,
 elapsed_time_delta/1000/1000 as elapsed_sec,
 stat.snap_id,
 to_char(snap.end_interval_time,'dd.mm hh24:mi:ss') as snaptime,
 txt.sql_id
from     
 dba_hist_sqlstat stat,
 dba_hist_sqltext txt,
 dba_hist_snapshot snap
where     
 stat.sql_id=txt.sql_id and
 stat.snap_id=snap.snap_id and
 snap.begin_interval_time>=sysdate-1 and
 lower(sql_text) like '%&t%' and
 parsing_schema_name not in ('SYS','SYSMAN','MDSYS','WKSYS')
order by elapsed_time_delta asc;

sysdate-1 returns SQL up to yesterday. With the sql_id, you can also retrieve the execution plan from the snapshots:

SQL> select plan_table_output from table (dbms_xplan.display_awr('&sqlid'));

 

Source: Uwe Hesse