From Oramosswiki
--
-- File: get_awr_plan.sql
-- Purpose: generates an explain plan definition from AWR repository
--
--
-- Change History
--
-- Date Author Description
-- =========== ================= ================================================
-- 22-DEC-2009 Jeff Moss Created
--
set scan on
set define on
set linesize 300
set pagesize 10000
set wrap off
set echo off
set termout off
-- Columns to set SQL Prompt...
COLUMN dbid NEW_VALUE dbid
SELECT dbid
FROM v$database
/
set termout on
UNDEFINE sql_id
UNDEFINE plan_hash_value
UNDEFINE db_id
UNDEFINE format
PROMPT
PROMPT NOTE - Before running, ensure you have the SQL_ID and PLAN_HASH_VALUE from V$SQL for the SQL you want a plan for
PROMPT
ACCEPT sql_id PROMPT 'Please enter SQL_ID: '
ACCEPT plan_hash_value PROMPT 'Please enter PLAN_HASH_VALUE: '
ACCEPT db_id PROMPT 'Please enter DB_ID[&dbid]: '
ACCEPT format PROMPT 'Please enter FORMAT [BASIC|TYPICAL|SERIAL|ALL]: '
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id',&plan_hash_value,'&db_id','&format'))
/
