Reports 2017-11-18T12:27:27+00:00

DBA SGA SQL Explain Plan History

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

select
gsp.sql_id,
gsp.plan_hash_value,
gsp.timestamp,
lpad(' ',gsp.depth*2)||gsp.operation||
case when gsp.options is not null then ' '||gsp.options end||
case when gsp.object_name is not null then ' '||gsp.object_type||' '||gsp.object_owner||'.'||gsp.object_name end operation,
lpad(' ',gsp.depth)||gsp.depth depth,
gsp.cardinality,
gsp.bytes,
gsp.time,
gsp.cost,
gsp.cpu_cost,
gsp.io_cost,
gsp.id,
gsp.parent_id
from
gv$sql_plan gsp
where
1=1
order by
gsp.timestamp desc,
gsp.plan_hash_value,
gsp.id,
gsp.position

Parameter Name SQL text Validation
SQL Id
gsp.sql_id=:sql_id
LOV
Plan Hash Value
gsp.plan_hash_value=:plan_hash_value
Number
Using Index
(gsp.inst_id,gsp.sql_id,gsp.plan_hash_value) in (select gsp0.inst_id, gsp0.sql_id, gsp0.plan_hash_value from gv$sql_plan gsp0 where gsp0.object_name=:index_name and gsp0.operation='INDEX')
LOV