1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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 >0order by 1 asc,2 desc; |
replace dba_hist_active_sess_history with v$active_session_history to get ash info