RE: RE: wait events and v$session

From: Mohan, Ross <RMohan_at_thexchange.com>
Date: Thu, 13 May 2004 15:00:07 -0400
Message-ID: <CC74E7E10A8A054798B6611BD1FEF4D304CE7BB4@vamail01.thexchange.com>

Man, I love precise language. Queueing theory is kinda cool too, never got a chance to look into it, though. Something about M/M/1 being really challenging, but realistic?
*sigh* I guess I am going to have to put another book on the "Do Not Ignore" pile I've been ignoring.

-----Original Message-----

From: Cary Millsap [mailto:cary.millsap_at_hotsos.com] Sent: Tuesday, May 11, 2004 12:56 PM
To: oracle-l_at_freelists.org
Subject: RE: RE: wait events and v$session_event

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


-----------------------------------------------------------------





----------------------------------------------------------------

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 Thu May 13 2004 - 14:58:32 CDT