Re: Measuring db time / waiit events over a given period
Date: Thu, 3 Nov 2011 03:58:50 -0700 (PDT)
Message-ID: <5e6fca93-7cb7-4556-8599-0fde4f5cbdc4_at_g7g2000vbv.googlegroups.com>
On Oct 31, 6:38 pm, John Hurley <hurleyjo..._at_yahoo.com> wrote:
> On Oct 31, 10:08 am, dba cjb <chris.br..._at_providentinsurance.co.uk>
> wrote:
>
>
> Thanks to all for your helpfull & comprehensive feedback
Sometimes you ask a question
& you are not sure if it is valid & or easy to answer
I guess you've confirmed I've more work to do
regards
Chris B
PS AWR is usefull but the relationship between time model & wait
events is a little less than clear
..as Charles has alluded to
>
>
> > 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.- Hide quoted text -
>
> - Show quoted text -
Received on Thu Nov 03 2011 - 05:58:50 CDT