Skip to content

how to vizualize table usage by queries with awr and exel

how to vizualize table usage by queries with awr and exel published on Комментариев к записи how to vizualize table usage by queries with awr and exel нет

generate pivot script:

select listagg( ''''||sql_id||'''',',') within group (order by 1 )from (select distinct (sql_id) from  dba_hist_sql_plan where object_name='OPN_HIS' ) ;

use info in awr to build a graph

select  * from (
select s.sql_id,
sum( nvl(s.executions_delta,0)) execs,TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24') date#
-- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS, dba_hist_sqltext st
where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and elapsed_time_delta > 0
   and st.sql_id=s.sql_id
   and st.sql_text not like '/* SQL Analyze%'
   and s.sql_id in  ( select p.sql_id from dba_hist_sql_plan p where p.object_name='OPN_HIS') 
and ss.begin_interval_time > sysdate-7
group by TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24'),s.sql_id )
pivot ( sum(execs)  for sql_id in (
'04chua8g507qc','0ppqkga09s858','13nb5fgqggy5y','2y61btbkgbt99','3akp3rn03dw0m','4hwvc5b6kg9s9','5btym214ffk95','5fjnq328z8cvm','5haf8w0xm1qrz','5yrbchx0n2ww9','70qkbhpxdyhmd',
'7459pp1x54x2g','7hap4j3ar5fu8','9ym4u1trvzcq8','aabhxfrznx8ty','b4ynhjjwwdjf9','bp0sfgzv774u4','btm5kxhrc34qy','bxfhw0z4cnmxp','by4vbkw71nrax','dpnr7csjy3657','f3n5pm0ck9yuw','f452wsrxch6js' )
  );

result :

Screen Shot 2016-05-31 at 12.05.45

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Primary Sidebar

Яндекс.Метрика