Measuring db time / waiit events over a given period

I would like to measure db time over a sample period so that I can get a feel both for activity & bottlenecks on a given database.

My source query is
select wait_class,event,time_waited/100 tw from v$system_event e
where e.wait_class <> 'Idle' and time_waited>0 UNION
select 'Time Model',stat_name NAME,
ROUND((VALUE/1000000),2) time_secs
from v$sys_time_model
where stat_name not in ('background elapsed time','background cpu time')
ORDER BY 3 DESC; Sample output for 10 second period

User I/O	direct path read	36.91
Time Model	sql execute elapsed time	30.02
Time Model	DB time	28.68
Time Model	DB CPU	3.74
User I/O	db file sequential read	1.43

My aim is to understand load so that I could be make a statement/ assumption of the following nature

for given period db time was eg 500 secs /wait activity contributed 10% of this ie 50 secs

I ultimately want to understand how different loads at different times suffer from bottlenecks
/ wait events so that I can understand possible options for moving loads between time periods

eg if morning period db time 1000 secs 5% waits

          afternoon period db time 3000 secs 50% ..we may want to investigate moving afternoon load to the morning

I was wondering if

1. My aim is achievable
2. If so can I use my query ( don't understand example where wait event is greater than db time )

or
3) will different views / methods give the answer

thanks for any pointers

Chris B Received on Mon Oct 31 2011 - 09:08:02 CDT