ASH Mining Slow Queries

"Database is slow." It was fast three days ago. We know the problem table but no queries are informed to the dba. Luckily we have diagnostics pack purchased. Time to start finding the slowest queries touching the table.

with sqid as (
  select /*+materialize*/
         distinct sql_id 
    from dba_hist_sql_plan p, dba_tables t 
   where t.owner = :table_owner
     and t.table_name = :table_name
     and (p.object_owner,p.object_name) in (
            select t.owner,t.table_name 
              from dual
            union all
            select owner,index_name
              from dba_indexes i
             where i.table_owner = t.owner 
               and i.table_name = t.table_name
            )
)
select /*+leading(s)*/ 
       trunc(h.sample_time) dt
     , h.sql_id
     , max(h.sample_time-h.sql_exec_start) dur
     , min(h.sample_time) mins
     , max(h.sample_time) maxs
     , count(distinct h.sql_plan_hash_value) cntpln
     , collect(distinct h.sql_plan_hash_value) plns
     , count(distinct h.sql_exec_id) cntexec
     , count(distinct h.session_id) cntsess
     , collect(distinct h.event) events
     , (select dbms_lob.substr(t.sql_text,2000) 
          from dba_hist_sqltext t 
         where t.sql_id = h.sql_id) txt
  from dba_hist_active_sess_history h, sqid s
 where sample_time > trunc(sysdate)-7 
   and h.sql_id = s.sql_id
group by trunc(h.sample_time),h.sql_id
order by max(dur)over(partition by h.sql_id) desc, dt desc
;