This is my blog on all things Oracle. I was an Enterprise Architect for a unnamed company, doing unamed things.
Please note that the views expressed here are my own.

Twitter Updates 2.2.1: FeedWitter

Friday, July 6, 2012

What happened to my sql (sql_id) ?

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;