Get Historical Plans from the AWR Using SQLID 
--
-- Get Historical Plans from the AWR Using SQLID
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
SELECT * FROM TABLE(dbms_xplan.display_awr('&SQL_ID'))
/

OR

--
-- Get Historical Plans from the Cursor Cache Using SQLID
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(
  SQL_ID=>'&SQLID', 
  -- CHILD_NUMBER=>the_child_number, 
  FORMAT=>'ALLSTATS LAST ALL +OUTLINE'))
/

Related Scripts

List Distinct SQL ID's for a Given Session
Displays SQL Statement for the Specified Address or Hash
List SQL Run Dates for a Given Plan Hash Value

awrdbms_xplanplansplsqlsql
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License