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

On Oct 31, 10:08*am, dba cjb <chris.br... (AT) providentinsurance (DOT) co.uk>
wrote:
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.

On Oct 31, 10:08*am, dba cjb <chris.br... (AT) providentinsurance (DOT) co.uk>
wrote:
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=1ry...page&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/E...views_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/2...me-model-data/

Six part series that shows how to build a Time Model Viewer using
Microsoft Excel:
http://hoopercharles.wordpress.com/2...er-in-excel-1/

Charles Hooper
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Oct 31, 10:08*am, dba cjb <chris.br... (AT) providentinsurance (DOT) co.uk>
wrote:
What you are looking for has been invented previously. If you happen
to have a Diagnostic Pack License on top of your Enterprise Edition
you can simply use Active Session History (ASH) to understand the DB
Time and Wait Events sampled over time.

If you don't have a Diagnostic License there are several free ASH
implementations that you can use even with Standard Edition or pre-10g
releases.

Start here for the free implementations: http://ashmasters.com/

It's also available via Sourceforge.net: http://sourceforge.net/projects/orasash/

There is also the free ASH Viewer that supports both, Oracle's ASH as
well as its own implementation: http://sourceforge.net/projects/ashv/

As well as the old ASHMON also available via above ASHMASTERS link.

The free implementation was originally done by Kyle Hailey (http://
dboptimizer.com), the latest version of OraSASH has been maintained by
Marcin Przepiorowski (http://oracleprof.blogspot.com/)

If you want to understand how to interpret / analyze ASH data, see
this presentation by Graham Wood (one of the fathers of ASH / ADDM):

http://www.oracle.com/technetwork/da...ory-129612.pdf

You can even watch him presenting about ASH:

http://www.oaktable.net/media/mow201...hes-time-part1
http://www.oaktable.net/media/mow201...es-time-part-2

Hope this helps,
Randolf

What's wrong with the awrrpt ?

On Oct 31, 6:38*pm, John Hurley <hurleyjo... (AT) yahoo (DOT) com> wrote:
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