RE: missed Anjo's web cast..

I manage one db that has an obscene amount of physical IO but most of it has to be coming from EMC disk cache (2 GB). I am not so concerned with whether they are real reads or not ( I don't think you can know from within Oracle can you?), my real concern is establishing a baseline then getting notified when I have reached a point in the system that is above the baseline far enough that I know there might be problems. Wait time is of course a bigee but if I have 100 sessions and 90% of the wait time is from 2 sessions I really don't care. What I care about is when all wait times are starting to go above my baseline. So one thing I want to monitor is something like this...

"tell me when more than 20% of the sessions are contributing to more than %80 of the wait time and the total wait time is currently more than %150 of normal for this time of weekday/weekend"

or

"tell me when total wait time is more than %300 of normal"

I started hacking out a very simple PL/SQL package with one or two tables (hopefully) that will do all some of this. It will write out essentially the Oracle Server load out to the alert log, will work much like the UNIX load. When I get it done I will share it with the list.

Personally I have learned to stop caring about actual numbers unless I am trouble shooting and just get my baseline. Find out what the system looks like when performance is bad and then tell it to tell me just before it is bad so I can dig in and make a phone call to the creep performing multiple full table scans against a 5 GB table.

By the way, Anjo, we use Precise and it is a super product but I personally don't really use it that much. I find myself staring at multiple inefficient SQL statements that no one has the time to fix and besides nobody is complaining (usually). I think they may be getting accustomed to the slow response times however :)

Ethan Post
perotdba (AIM), epost1 (Yahoo)

-----Original Message-----
Sent: Thursday, August 08, 2002 2:26 PM
To: Multiple recipients of list ORACLE-L

I haven't checked this lately but 'physical reads' in v$sysstat is the number of blocks read. To get the real physical I/O operations do check the waits column in v$system_event for 'db file sequentail read' and 'db file scattered read'. And keep an eye on the TIME_WAITED column.

Anjo.

Post, Ethan wrote:

>What's wrong with V$SYSSTAT, db block gets + consistent gets = logical io
>(there may even be a logical IO stat, can't recall and too lazy to look)
and
>physical reads. Don't worry about the cache rate but track the rate of
>logical IO and physical IO.
>
>Ethan Post
>perotdba (AIM), epost1 (Yahoo)
>--------------------------------------------------------------------
>
>-----Original Message-----
>Sent: Thursday, August 08, 2002 12:40 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Having missed the web cast ... is there as simple way to identify LIOs and
>PIOs in the database 'as of now'? I know Craig_at_orapub does it by setting
>trace and then calculating values off the trace file.
>
>But is there a simple way to calculate without generating traces?
>
>Thanks in advance
>Raj
>______________________________________________________
>Rajendra Jamadagni MIS, ESPN Inc.
>Rajendra dot Jamadagni at ESPN dot com
>Any opinion expressed here is personal and doesn't reflect that of ESPN
Inc.
>
>QOTD: Any clod can have facts, but having an opinion is an art!
>
>-----Original Message-----
>Sent: Thursday, August 08, 2002 1:05 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Moi wrong ;-) Jeeh, human after all
>
>To summarize the webcast:
>db-block-buffers do mattter. Too many LIO do matter. Too many PIO do
matter.
>But Buffer Cache Hit ratio doesn't matter ....... End user satisfaction
does
>matter.
>
>I am always willing to clarify any points that I made, you just have to ask
>me l ....
>
>Anjo.
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Anjo Kolk
  INET: anjo_at_oraperf.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Post, Ethan
  INET: Ethan.Post_at_ps.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).