Reports2017-11-18T12:27:27+00:00

DBA AWR SQL Explain Plan History

Description
Categories: DBA, Enginatics
Execution plan history for a particular SQL id from the automatic workload repository

select
dhsp.sql_id,
dhsp.plan_hash_value,
xxen_util.client_time(dhsp.timestamp) timestamp,
lpad(' ',dhsp.depth*2)||dhsp.operation||
case when dhsp.options is not null then ' '||dhsp.options end||
case when dhsp.object_name is not null then ' '||dhsp.object_type||' '||dhsp.object_owner||'.'||dhsp.object_name end operation,
dhsp.qblock_name query_block,
lpad(' ',dhsp.depth)||dhsp.depth depth,
dhsp.cardinality,
dhsp.bytes,
dhsp.time,
dhsp.cost,
dhsp.cpu_cost,
dhsp.io_cost,
dhsp.id,
dhsp.parent_id
from
dba_hist_sql_plan dhsp
where
1=1 and
dhsp.dbid=(select vd.dbid from v$database vd)
order by
dhsp.timestamp desc,
dhsp.plan_hash_value,
dhsp.id,
dhsp.position

Parameter Name SQL text Validation
Using Index
(dhsp.sql_id,dhsp.plan_hash_value) in (select dhsp0.sql_id, dhsp0.plan_hash_value from dba_hist_sql_plan dhsp0 where dhsp0.object_name=:index_name and dhsp0.operation='INDEX')
LOV
Plan Hash Value
dhsp.plan_hash_value=:plan_hash_value
LOV
SQL Id
dhsp.sql_id=:sql_id
LOV

By continuing to use the site, you agree to the use of cookies. Accept