Re: Measuring db time / waiit events over a given period
Date: Mon, 31 Oct 2011 11:38:32 -0700 (PDT)
Message-ID: <3543b77b-482d-40b3-9e8f-0e89468d3f92_at_n18g2000vbv.googlegroups.com>
On Oct 31, 10:08 am, dba cjb <chris.br..._at_providentinsurance.co.uk> wrote:
> db 10.2.0.4 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
> 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
Are you licensed for diagnostic and tuning pack? OEM even on 10.2.0.4 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 ...read 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