While finishing up a few things, I ran across a query that wasn't playing nicely. It had 4 different plans over the course of the last couple of days, and I wanted to see what happend.. I came up with the nifty query below. If you plug in a sql_id, it will go through the AWR history, and return (ordered by date last executed), the plans grouped by plan_hash_value. Within each plan_hash_value it will give you the objects in the plan, and when they were last analyzed. By using this you should see what plans are good, when they were last executed, and if anything was analyzed to change the plan.
set linesize 160
set pagesize 1000
break on plan_hash_value skip 1 nodup on last_executed skip 1 nodup on avg_exec_time skip 1
select object_owner ||'.'|| object_name object_name,
object_type,
a.plan_hash_value,
case object_type
when 'INDEX' then (select last_analyzed from dba_indexes b where owner=object_owner and index_name=object_name)
when 'TABLE' then (select last_analyzed from dba_tables b where owner=object_owner and table_name=object_name)
else null
end last_analyzed,
to_char((select max(end_interval_time) from dba_hist_snapshot b,
dba_hist_sqlstat c
where c.sql_id=a.sql_id and
c.plan_hash_value=a.plan_hash_value and
b.snap_id=c.snap_id),'mm/dd/yy hh24:mi') last_Executed,
to_char((select sum(elapsed_time_delta)/sum(executions_delta) from dba_hist_sqlstat d where d.sql_id=a.sql_id and d.plan_hash_value=a.plan_hash_value)/1024/1024,'999.99') avg_exec_time
from DBA_HIST_SQL_PLAN a
where a.SQL_ID='gbug7dg8adhgh'
and object_type in ('INDEX','TABLE')
order by last_executed desc ,a.plan_hash_value , last_analyzed desc;
Here is an example of the output
OBJECT_NAME OBJECT_TYPE PLAN_HASH_VALUE LAST_ANALYZED LAST_EXECUTED AVG_EXE
-------------------------------------------------------------- -------------------- --------------- ------------------- -------------- -------
MY_SCHEMA.SNP_CDC_SUBS TABLE 2518369181 2012-07-06 09:25:15 07/06/12 10:00 791.37
MY_SCHEMA.SNP_CDC_SUBS TABLE 2012-07-06 09:25:15
MY_SCHEMA.D$TAB_REG TABLE 2012-07-06 09:25:06
MY_SCHEMA.J$TAB_REG TABLE 2012-07-06 09:24:50
MY_SCHEMA.J$TAB_REG TABLE 2012-07-06 09:24:50
MY_SCHEMA.J$TAB_REG TABLE 2012-07-06 09:24:50
MY_SCHEMA.J$TAB_REG TABLE 2012-07-06 09:24:50
ERD.DIM_TABS_COMP_PLCY_AGMT TABLE 2012-07-06 00:39:33
MY_SCHEMA.TAB_COMP_PLCY_AGMT TABLE 2012-05-25 09:20:39
MY_SCHEMA.IDX_WCPA_AGMT_ID INDEX 2012-05-25 09:20:39
MY_SCHEMA.TAB_COMP_PLCY_ST_CLSF_VT TABLE 2012-05-15 18:49:43
MY_SCHEMA.TAB_COMP_PLCY_ST_CLSF TABLE 2012-05-15 18:49:43
MY_SCHEMA.WPTD_COMP_PER_TAX TABLE 2012-05-15 18:39:30
MY_SCHEMA.TAB_REG TABLE 2012-05-15 18:31:18
MY_SCHEMA.CO_TAB TABLE 2012-05-15 18:27:50
MY_SCHEMA.TAB_PAYR TABLE 2012-05-15 18:26:47
MY_SCHEMA.AGMT_REG TABLE 2012-05-15 18:21:09
MY_SCHEMA.SNP_CDC_SUBS TABLE 1903861587 2012-07-06 09:25:15 07/06/12 09:00 882.94
MY_SCHEMA.SNP_CDC_SUBS TABLE 2012-07-06 09:25:15
MY_SCHEMA.D$TAB_REG TABLE 2012-07-06 09:25:06
MY_SCHEMA.J$TAB_REG TABLE 2012-07-06 09:24:50
MY_SCHEMA.J$TAB_REG TABLE 2012-07-06 09:24:50
MY_SCHEMA.J$TAB_REG TABLE 2012-07-06 09:24:50
MY_SCHEMA.J$TAB_REG TABLE 2012-07-06 09:24:50
ERD.DIM_TABS_COMP_PLCY_AGMT TABLE 2012-07-06 00:39:33
MY_SCHEMA.TAB_COMP_PLCY_AGMT TABLE 2012-05-25 09:20:39
MY_SCHEMA.IDX_WCPA_AGMT_ID INDEX 2012-05-25 09:20:39
MY_SCHEMA.TAB_COMP_PLCY_ST_CLSF_VT TABLE 2012-05-15 18:49:43
MY_SCHEMA.TAB_COMP_PLCY_ST_CLSF TABLE 2012-05-15 18:49:43
MY_SCHEMA.WPTD_COMP_PER_TAX TABLE 2012-05-15 18:39:30
MY_SCHEMA.TAB_REG TABLE 2012-05-15 18:31:18
MY_SCHEMA.CO_TAB TABLE 2012-05-15 18:27:50
MY_SCHEMA.TAB_PAYR TABLE 2012-05-15 18:26:47
MY_SCHEMA.AGMT_REG TABLE 2012-05-15 18:21:09
No comments:
Post a Comment