Re: Measuring db time / waiit events over a given period

From: John Hurley <>
Date: Mon, 31 Oct 2011 11:38:32 -0700 (PDT)
Message-ID: <>

On Oct 31, 10:08 am, dba cjb <> wrote:
> db enterprise on windows 2003
> 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
> 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')
> 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

Are you licensed for diagnostic and tuning pack? OEM even on is pretty good.

Lots of sessions are typically stuck in sqlnet message from client ( waiting for end user to click on something / do something ) so that gets rolled up and is "typically" ignored.

Have you read Cary Millsap's Optimizing Oracle Performance book? If not I would recommend that you do a full stop ... read that book ... read it again ... think for a while it a 3rd time ... before you go any further.

It sounds a little like you are thinking about doing brain surgery and right now you are not sure if you are going to use a scalpel or a chainsaw. Cary's book gives a repeatable performance solving methodology ... aka a method to the madness.

If not licensed for diagnostics and tuning pack you may want to look at the ash masters website. Received on Mon Oct 31 2011 - 13:38:32 CDT

Original text of this message