dbms_xplan.display_awr

dbms_xplan.display_awr..

from !!

SQL> desc dbms_xplan

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 PLAN_HASH_VALUE                NUMBER(38)              IN     DEFAULT
 DB_ID                          NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 CON_ID                         NUMBER(38)              IN     DEFAULT
 AWR_LOCATION                   VARCHAR2                IN     DEFAULT




 
select distinct sql_id,plan_hash_value from dba_hist_sql_plan 

-- where 1=1

;


SQL_ID        PLAN_HASH_VALUE
------------- ---------------
afcswub17n34t      1774461949
62yyzw3309d6a      2667441017
6y55dxn24t86q      2904063496
4m5tr50dg6xmc      2772656747



set linesize 120
 col PLAN_TABLE_OUTPUT for a120
select * from table(dbms_xplan.display_awr(SQL_ID=>'afcswub17n34t',format=>'ALLSTATS LAST +cost +bytes'));
 
 

set linesize 120
col PLAN_TABLE_OUTPUT for a120
select * from table(dbms_xplan.display_awr(SQL_ID=>'afcswub17n34t',PLAN_HASH_VALUE=>1774461949,format=>'ALLSTATS LAST +cost +bytes'));
 
 

set linesize 120
col PLAN_TABLE_OUTPUT for a120
select * from table(dbms_xplan.display_awr(SQL_ID=>'afcswub17n34t',PLAN_HASH_VALUE=>1774461949,format=>'ALLSTATS LAST +outline'));
 

 set linesize 120
 col PLAN_TABLE_OUTPUT for a120
select * from table(dbms_xplan.display_awr(SQL_ID=>'afcswub17n34t',PLAN_HASH_VALUE=>1774461949,format=>'ALLSTATS LAST +cost +bytes +PARALLEL +PARTITION +IOSTATS +outline +PEEKED_BINDS'));