Skip to content

script to slice awr\ash report by time

script to slice awr\ash report by time published on Комментариев к записи script to slice awr\ash report by time нет
select tt.*,s.sql_text from (
select t.time#,sum(t.pct_time) pct_db_time,t.tot_act_sess,t.pl_sql_obj,t.sql_id,t.event,t.username from 
(
select TRUNC( ash.SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM  ash.SAMPLE_TIME), &&sample_interval_in_minutes) /(24 * 60) time# ,
            100* round (sum( ash.TM_DELTA_DB_TIME) / (sum(sum( ash.TM_DELTA_DB_TIME)) over ( partition by TRUNC(SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM  ash.SAMPLE_TIME), &&sample_interval_in_minutes) /(24 * 60) )) ,2) pct_time,
            count (distinct  ash.session_id) over ( partition by TRUNC( ash.SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM  ash.SAMPLE_TIME), &&sample_interval_in_minutes) /(24 * 60) )  as tot_act_sess,
            ash.session_id, ash.session_serial#,
            dp.owner||nvl2(dp.object_name,'.'||dp.object_name,null) ||nvl2(dp.procedure_name,'.'||dp.procedure_name,null) as pl_sql_obj,           
            ash.sql_id ,ash.event,du.username
            from dba_hist_active_sess_history ash
  left join dba_procedures dp on dp.object_id=ash.PLSQL_ENTRY_OBJECT_ID and dp.subprogram_id=ash.PLSQL_ENTRY_SUBPROGRAM_ID
  join dba_users du on du.user_id=ash.user_id
  where  ash.sample_time between trunc (sysdate -2) +14/24 +30/(24*60) and trunc(sysdate-2) +16/24  +30/(24*60)
group by dp.owner||nvl2(dp.object_name,'.'||dp.object_name,null) ||nvl2(dp.procedure_name,'.'||dp.procedure_name,null),
sql_id ,ash.event,
TRUNC(SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM SAMPLE_TIME), &&sample_interval_in_minutes) /(24 * 60) ,
session_id,session_serial#,du.username
) t
  group by t.time#,t.tot_act_sess,t.pl_sql_obj,t.sql_id,t.username,t.event ) tt
  left join dba_hist_sqltext s on tt.sql_id=s.sql_id
  where tt.pct_db_time >0
order by 1 asc,2 desc;

replace dba_hist_active_sess_history with v$active_session_history to get ash info

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

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

Primary Sidebar

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