DBA AWR SQL Execution Plan History
Description
Categories: Diagnostic Pack, Enginatics, Toolkit - DBA
Repository: Github Columns: Sql Id, Plan Hash Value, Timestamp, Operation, Query Block, Depth, Cardinality, Mb, Time, Cost ...
Repository: Github Columns: Sql Id, Plan Hash Value, Timestamp, Operation, Query Block, Depth, Cardinality, Mb, Time, Cost ...
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/1000000 mb, dhsp.time, dhsp.cost, dhsp.cpu_cost, dhsp.io_cost, dhsp.id, dhsp.parent_id, ds.object_size from dba_hist_sql_plan dhsp, ( select ds.owner, ds.segment_type, ds.segment_name, sum(ds.blocks)*(select vp.value from v$parameter vp where vp.name like 'db_block_size')/1000000 object_size from dba_segments ds group by ds.owner, ds.segment_type, ds.segment_name ) ds where 1=1 and dhsp.dbid=(select vd.dbid from v$database vd) and dhsp.object_owner=ds.owner(+) and case when dhsp.object_type like '% %' then substr(dhsp.object_type,1,instr(dhsp.object_type,' ')) else dhsp.object_type end=ds.segment_type(+) and dhsp.object_name=ds.segment_name(+) order by dhsp.sql_id, dhsp.timestamp desc, dhsp.plan_hash_value, dhsp.id, dhsp.position |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Using Index |
| LOV | |
Diagnostic Pack enabled |
| LOV | |
SQL Id |
| LOV | |
Plan Hash Value |
| LOV | |
Object Name |
| LOV | |
Options |
| LOV | |
Objects larger than x GB |
| Number |