Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: I/O EVENTS

Re: I/O EVENTS

From: Greg Moore <sqlgreg_at_pacbell.net>
Date: Thu, 16 May 2002 15:18:20 -0800
Message-ID: <F001.00462FBC.20020516151820@fatcity.com>


John,

> Hmm.... I wouldn't think so. If there were just _one_ overall view that I
> could check to determine an Oracle bottleneck, it would be this view.

Maybe not one, but what about two? At the same time v$system_event is checked a couple of times, so you can see a time slice, v$sysstat can be checked, focusing on CPU used by this session, parse time cpu and recursive cpu usage. One view gives wait time, one gives CPU time.

The absolute values from v$system_event are meaningful as relative values when compared with the three from v$sysstat. If most time is spent on CPU (say 95%) then the waits, while they are there, might not deserve to be called a bottleneck. If you manage to reduce them by 50%, you've only improved performance by two or three percent. Waits may have been reduced by 50%, but users probably won't even notice. Of course, if waits are 95%....

The issue today seems to be how to treat the three v$sysstat values. Some say CPU used by this session is the true measure of total CPU, yet I have a database where recursive cpu usage is 4x bigger than CPU used by this session. That apparently happens because plain SQL time is recorded in CPU used by this session, but SQL inside PL/SQL is recorded in recursive cpu usage.

Sending a StatsPack through oraperf and figuring out how the first section (Response Breakdown) uses the three values from v$sysstat reveals Anjo Kolk's thinking on this....

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Greg Moore
  INET: sqlgreg_at_pacbell.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).
Received on Thu May 16 2002 - 18:18:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US