Get the Execution Plan from/using DBA_HIST_SQL_PLAN (AWR)

The Automatic Workload Repository (AWR) infrastructure and Statspack store execution plans of top SQL statements. Plans are recorded into DBA_HIST_SQL_PLAN. This view captures information from V$SQL_PLAN and is used with the DBA_HIST_SQLSTAT view.

Sample Script :

col operation for a75
SELECT
      LPAD(' ',depth)||
      OPERATION||'_'||
      OPTIONS||' '||
      OBJECT_NAME  operation
FROM
       DBA_HIST_SQL_PLAN
WHERE
        sql_id='&SQL_ID'
    and DBID=&DBID
order by  ID,PLAN_HASH_VALUE;




DISPLAY_AWR :

This table function displays the contents of an execution plan stored in the AWR. This is give you old execution plans.

Syntax :

DBMS_XPLAN.DISPLAY_AWR( 

   sql_id                  IN      VARCHAR2,

   plan_hash_value   IN      NUMBER DEFAULT NULL,

   db_id                  IN      NUMBER DEFAULT NULL,

   format                IN      VARCHAR2 DEFAULT TYPICAL);

Examples :

To display the different execution plans associated with the SQL ID 'atfwcg8anrykp':

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('atfwcg8anrykp'));