@@header

PROMPT 
PROMPT #################################################################
PROMPT #       SCRIPT NOT COMPLETED YET
PROMPT #################################################################
PROMPT 


/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display different in execution plan from AWR
*  Parameters : 1 - SQL_ID
*             : 1 - PLAN_HASH_VALUE1 
*             : 1 - PLAN_HASH_VALUE2 
*               
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  18-Mar-12  Vishal Gupta  Intial version
*
*
*/

SET verify OFF

VARIABLE sql_id VARCHAR2(13)
VARIABLE plan_hash_value1 VARCHAR2(20)
VARIABLE plan_hash_value2 VARCHAR2(20)

BEGIN
   :sql_id := '&&1';
   :plan_hash_value1 := '&&2';
   :plan_hash_value2 := '&&3';
END;
/

PROMPT 
PROMPT #################################################################
PROMPT #       A L L    S Q L     P L A N   H A S H   V A L U E 
PROMPT #################################################################

SELECT p.sql_id
     , p.plan_hash_value
     , p.cost
     , to_char(p.timestamp ,'DD-MON-YY HH24:MI:SS') "Date First Used"
  FROM dba_hist_sql_plan p
     , v$database d
 WHERE d.dbid = p.dbid
   AND p.sql_id = :sql_id 
   AND p.id = 0 -- Top row which has cost as well
ORDER BY timestamp desc
/


PROMPT 
PROMPT 
PROMPT #################################################################
PROMPT #         A L L    E X E C U T I O N     P L A N S
PROMPT #################################################################
PROMPT 

SET long 4000 
SET longchunksize 400000 
set pages 0

COLUMN plan_table_output             HEADING "SQLText"  FORMAT a175 WRAP

print :sql_id
print :plan_hash_value1
print :plan_hash_value2
/*
VARIABLE plan_diff VARCHAR2(4000)
BEGIN
:plan_diff := DBMS_XPLAN.diff_plan_awr( sql_id   => :sql_id  , plan_hash_value1 => :plan_hash_value1 ,  plan_hash_value2 => :plan_hash_value2) ;
END;
/

print :plan_diff

*/

DECLARE
  plan_diff VARCHAR2(4000);
BEGIN
plan_diff := DBMS_XPLAN.diff_plan_awr( sql_id   => :sql_id  , plan_hash_value1 => :plan_hash_value1 ,  plan_hash_value2 => :plan_hash_value2) ;
DBMS_OUTPUT.put_line(plan_diff);
END;
/


BEGIN
   :sql_id := NULL;
   :plan_hash_value1 := NULL;
   :plan_hash_value2 := NULL;
END;
/


@@footer