some queryes to measure disk io
select 100*(round ( count (*)/sum(count(*)) over(),2 )) as pct ,nvl(wait_class,'CPU') wait_class from v$active_session_history group by wait_class order by 1 desc; select 100*(round ( count (*)/sum(count(*)) over(),2 )) as pct ,nvl(event,'CPU') event from v$active_session_history where wait_class like '%I/O%' group by event order by 1 desc;
generate pivot list
select listagg( ''''||event||'''',',') within group (order by event )from v$active_session_history where wait_class like '%I/O%' group by event ;
or top wait pivot list:
with disk_events as ( select 100*(round ( count (*)/sum(count(*)) over(),2 )) as pct ,nvl(event,'CPU') event from v$active_session_history where wait_class like '%I/O%' group by event ) select listagg( ''''||event||'''',',') within group (order by event )from disk_events where pct >5 ;
query and result
new:select * from (select event,case when est_waits >0 then round (est_dbtime_ms / est_waits,1) else null end as est_avg_latency_ms, time# from ( select event, round( sum( case when time_waited >0 then greatest(1,1000000/time_waited) else 0 end ) ) as est_waits, sum(1000) as est_dbtime_ms , TRUNC( ash.SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM ash.SAMPLE_TIME), 5) /(24 * 60) time# from v$active_session_history ash where ash.wait_class ='User I/O' group by TRUNC(SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM SAMPLE_TIME), 5) /(24 * 60) ,event ) ) pivot ( sum(est_avg_latency_ms) for event in ( 'db file scattered read','db file sequential read','log file parallel write') ) order by time# TIME# | 'db file scattered read' | 'db file sequential read' | 'log file parallel write' ------------------- | --------------------------------------- | --------------------------------------- | --------------------------------------- 10.08.2015 14.10.00 | 2,2 | ,3 | <NULL> 10.08.2015 14.15.00 | 2,4 | ,2 | <NULL> 10.08.2015 14.20.00 | 1,7 | ,3 | <NULL> 10.08.2015 14.25.00 | 1,3 | ,3 | <NULL> 10.08.2015 14.30.00 | 3,5 | ,2 | <NULL> 10.08.2015 14.35.00 | 1,8 | ,3 | <NULL> 10.08.2015 14.40.00 | 1,1 | ,3 | <NULL> 10.08.2015 14.45.00 | 2,6 | ,2 | <NULL> 10.08.2015 14.50.00 | 1,5 | ,3 | <NULL> 10.08.2015 14.55.00 | 1,2 | ,3 | <NULL> 10.08.2015 15.00.00 | 2,6 | ,2 | <NULL> 10.08.2015 15.05.00 | 1,3 | ,4 | <NULL> 10.08.2015 15.10.00 | 1,6 | ,3 | <NULL> 10.08.2015 15.15.00 | 3 | ,2 | <NULL> 10.08.2015 15.20.00 | 1,4 | ,3 | <NULL> 10.08.2015 15.25.00 | 1,6 | ,3 | <NULL> 10.08.2015 15.30.00 | 3,1 | ,2 | <NULL> 10.08.2015 15.35.00 | 1,8 | ,4 | <NULL> 10.08.2015 15.40.00 | 1,2 | ,3 | <NULL> 10.08.2015 15.45.00 | 2,5 | ,2 | <NULL> 10.08.2015 15.50.00 | 1,6 | ,3 | <NULL> 10.08.2015 15.55.00 | 1,4 | ,3 | <NULL> 10.08.2015 16.00.00 | 4,8 | ,2 | <NULL> 10.08.2015 16.05.00 | 1,8 | ,4 | <NULL> 10.08.2015 16.10.00 | 1,2 | ,4 | <NULL> 10.08.2015 16.15.00 | 3 | ,2 | <NULL> 10.08.2015 16.20.00 | 1,3 | ,3 | <NULL> 10.08.2015 16.25.00 | 1,1 | ,3 | <NULL> 10.08.2015 16.30.00 | 3,1 | ,2 | <NULL> 10.08.2015 16.35.00 | 1,6 | ,3 | <NULL> 10.08.2015 16.40.00 | 1,6 | ,3 | <NULL> 10.08.2015 16.45.00 | 2,9 | ,2 | <NULL> 10.08.2015 16.50.00 | 1,6 | ,3 | <NULL> 10.08.2015 16.55.00 | 1,3 | ,2 | <NULL> 10.08.2015 17.00.00 | 2,2 | ,2 | <NULL> 10.08.2015 17.05.00 | 1,5 | ,4 | <NULL> 10.08.2015 17.10.00 | 1,7 | ,3 | <NULL> 10.08.2015 17.15.00 | 3 | ,2 | <NULL> 10.08.2015 17.20.00 | 1,8 | ,3 | <NULL> 10.08.2015 17.25.00 | 1,3 | ,3 | <NULL> 10.08.2015 17.30.00 | 3,3 | ,3 | <NULL> 10.08.2015 17.35.00 | 1,6 | ,3 | <NULL> 10.08.2015 17.40.00 | ,7 | ,3 | <NULL>
this output can be used to build graphs:
1 комментарий
select 100*(round ( count (*)/sum(count(*)) over(partition by dbid ),2 )) as pct ,nvl(wait_class,’CPU’) wait_class,dbid from dba_hist_active_sess_history group by wait_class,dbid order by dbid,1 desc;
select 100*(round ( count (*)/sum(count(*)) over( partition by dbid ),2 )) as pct ,nvl(event,’CPU’) event,dbid from dba_hist_active_sess_history where wait_class like ‘%I/O%’ group by event,dbid order by dbid, 1 desc;