104 lines
2.4 KiB
SQL
104 lines
2.4 KiB
SQL
@@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
|