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

From: Charles Hooper <hooperc2001_at_gmail.com>
Date: Mon, 31 Oct 2011 13:39:20 -0700 (PDT)
Message-ID: <cf1cdca4-b0fd-498c-b826-c4a0b41d9bf4_at_m5g2000prg.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

Chris,

I am curious where you found the SQL statement that unions V $SYSTEM_EVENT and V$SYS_TIME_MODEL. There is a very similar SQL statement in the book "Oracle Database 11g Performance Tuning Recipes". I took issue with that SQL statement for a couple of reasons when I reviewed the book. You can see the SQL statement in context here:
http://books.google.com/books?id=1ryN1kK8PMoC&pg=PA175#v=onepage&q&f=false

You are correct that you need to calculate the change in the values for the statistics. The time model statistics are helpful to gain an understanding of what is happening in the system, but it is important to realize that there are parent-child relationships between the statistics. See the documentation for the session-level view (V $SESS_TIME_MODEL) to understand the parent-child relationships: http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/dynviews_3015.htm

If you want to build something that monitors the time model statistics, I have two article series that might help, assuming that you have a spare computer that is running Windows: Three part series that shows how to work with the Time Model Viewer, with a sample VBS script to calculate the delta values: http://hoopercharles.wordpress.com/2010/01/13/working-with-oracle-time-model-data/

Six part series that shows how to build a Time Model Viewer using Microsoft Excel:
http://hoopercharles.wordpress.com/2011/02/28/oracle-database-time-model-viewer-in-excel-1/

Charles Hooper
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Oct 31 2011 - 15:39:20 CDT

Original text of this message