RE: RE: wait events and v$session

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Tue, 11 May 2004 11:55:49 -0500
Message-ID: <005e01c43778$d335d420$6601a8c0@CVMLAP02>

Here's an example of what I meant: RAC node B begins a query at = 12:00n, which will end at 12:03p. At 12:01p, RAC node A updates a block that B = won't read until 12:02p. At 12:02p, B discovers that the current incarnation = of the block is newer than 12:00n, so it has to reconstruct the 12:00n = image of the block using undo. This undo (one or more undo blocks) was at least = at some point in node A's buffer cache. If it still is, then the block has = to travel the interconnect and endure the expensive cache coherency lock management code path. (If it's not still in A's cache, then it's just a = PIO on node B, but then you have different problems.)
The confusion comes from calling timed events "waits." The duration = of an Oracle timed event like 'db file sequential read' is the duration of the = OS read() call that the Oracle kernel called to fetch the block. In the queueing theory sense, it's not a "wait" at all; it's a service time. = It's a "wait" only from the perspective of how long the Oracle kernel has to = "wait" from the issuance of a gettimeofday call occurring right before the = read() to the issuance of another gettimeofday call occurring right after the read().

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 5/18 Edison NJ, 6/22 Pittsburgh, 7/20 =
Boston
- SQL Optimization 101: 5/24 San Diego, 6/14 Chicago, 6/28 Denver

Hotsos Symposium 2005: March 6-10 Dallas
Visit www.hotsos.com for schedule details...

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of ryan.gaffuri_at_cox.net
Sent: Tuesday, May 11, 2004 11:23 AM
To: oracle-l_at_freelists.org
Subject: Re: RE: wait events and v$session_event

Cary,

Could you be a little more specific, I don't quite understand this? I am using one node right now for everything in my ETL process. However, we = have
load_balancing turned on.=20

Also, why would I get file read waits if I just have one session = running?=20

One note worth
mentioning here is that even the famous "I'll do TP on one RAC node and query-only on the other" strategy is prone to lots of "global cache *" activity.

Why? Because of the undo blocks that are used by both nodes.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue May 11 2004 - 11:55:06 CDT

RE: RE: wait events and v$session_event