edba: Explain Plan

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

-- execution plan of the last executed statement for that session
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

select * from TABLE(dbms_xplan.display_awr('&sql_id'));

select * from TABLE(dbms_xplan.display_cursor(null, null, 'ALL ALLSTATS LAST'));
set pagesize 32000
set linesize 200
column sid format a10
column username format a10
column terminal format a10
column sql_text format a90 wrap
column OPERATION format a30
column OBJECT format a30
break on SID

Select ''''||s.sid||','||s.serial#||'''' SID,
CASE
WHEN (E.OPERATION like '%STATEMENT') then Q.SQL_TEXT
else LPAD(' ',E.DEPTH*2,' ')||RTRIM(REPLACE(E.OPERATION||'/'||E.OPTIONS||'/'||E.OBJECT_OWNER||'.'||E.OBJECT_NAME,'//','/')||'/'||to_char(E.PARTITION_START)||'-'||to_char(E.PARTITION_STOP),'/-.')
END SQL_TEXT
--,E.OBJECT_NODE
--,E.OPTIMIZER
--,E.ID
--,E.PARENT_ID
--,E.DEPTH
--,E.COST
--,E.CARDINALITY
--,E.BYTES
--,E.OTHER_TAG
--,E.PARTITION_START
--,E.PARTITION_STOP
--,E.PARTITION_ID
--,E.OTHER
--,E.DISTRIBUTION
,E.CPU_COST
,E.IO_COST
From v$sql_plan E, v$sql Q, V$SESSION S
Where E.address=Q.address(+)
and E.hash_value=Q.hash_value(+)
and E.child_number=Q.child_number(+)
and E.address=S.sql_address
order by s.sid,e.ID
/

Check changes in Explain Plan
========================

select * from ( select sql_id, sum(execs), min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev
from ( select sql_id, plan_hash_value, execs, avg_etime, stddev(avg_etime) over (partition by sql_id) stddev_etime
from ( select sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
(sum(elapsed_time_delta)/decode(sum(nvl(executions_delta,0)),0,1,sum(executions_delta))/1000000) avg_etime
-- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
group by sql_id, plan_hash_value
)
)
group by sql_id, stddev_etime
)
where norm_stddev > nvl(to_number('&min_stddev'),0) and
max_etime > nvl(to_number('&min_etime'),0)
order by norm_stddev;


1. Select the query id : select DBID, SQL_ID, SQL_TEXT from dba_hist_sqltext where sql_text like ‘%your query%’;


Get the plan : select * from table(dbms_xplan.display_awr(‘&sql_id’))


Or following steps


1. Get the hash values for the same query at two different times :


select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs, S.optimizer_cost,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3

or

col begin_interval_time for a40

col end_interval_time for a40

select q.snap_id, q.sql_id , s.begin_interval_time, s.end_interval_time , q.plan_hash_value, q.optimizer_cost, q.optimizer_mode
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.sql_id = '&sqlid'
and q.snap_id = s.snap_id
-- and s.begin_interval_time between sysdate-2 and sysdate
order by s.snap_id desc;

2. Run for each hash value

select id, operation, options, object_name, cost
from dba_hist_sql_plan
where sql_id = '&sqlid'
and plan_hash_value = '&plan_hash_value';

select cost from dba_hist_sql_plan where sql_id = '&sqlid' and id =0;

Or following steps

1. In a single query

break on sql_id

select p.SQL_ID, p.PLAN_HASH_VALUE , p.ID , p.COST --,count(1)
from
(select * from dba_hist_sql_plan where ID = 0 )p ,
( SELECT distinct SQL_ID, PLAN_HASH_VALUE --,PARSING_SCHEMA_NAME --,count(1) ,OPTIMIZER_COST,ELAPSED_TIME_TOTAL
FROM dba_hist_sqlstat q,
(
SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
FROM dba_hist_snapshot ss
WHERE ss.begin_interval_time BETWEEN (SYSDATE - 2) AND SYSDATE
) s
WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap
and PARSING_SCHEMA_NAME not in ('SYS')
--group by SQL_ID , PLAN_HASH_VALUE ,PARSING_SCHEMA_NAME
--having count(1) > 1
) q
where p.SQL_ID = q.SQL_ID
group by p.SQL_ID, p.PLAN_HASH_VALUE , p.ID ,p.COST
having count(1) > 1
order by p.SQL_ID, p.PLAN_HASH_VALUE

Pages

Explain Plan