-----Original Message-----
Sent: Tuesday, July 24, 2001 1:22 PM
To: Multiple recipients of list ORACLE-L
Gene:
It all depends on a couple of factors: (1) what are the average
wait times for each of the wait events, (2) which one(s) have
the highest wait times. I would ignore any waits less than 1
centisecond for the most part.
Buffer Busy Waits occur when a session is waiting for a buffer
to become available. This is because a buffer is either being
read into the buffer cache by another session (and the session
is waiting for that read to complete) or the buffer is in the
buffer cache, but in an incompatible mode(that is, some other
session is changing the buffer). There are several courses of
action here, depending on what type of block it is:
-- If it is a data block, change the pctfree and pctused or,
in the case of an index, check for "right-hand indexing" or increase
initrans. The key is to reduce the number of rows/leaves per
block to reduce contention.
-- If it is a segment header, increase the number of freelists
or use freelist groups.
-- If it is a freelist block, increase the number of freelists.
-- If it is an undo header block, add more rollback segments
when in exclusive mode and consider setting transactions per
rbs = 1.
-- If it is an undo block, add more rollback segments when in
exclusive mode or make the segments you have larger.
DB file sequential read waits indicate that either (a) an index
lookup is being performed or (b) a controlfile is being rebuilt
or (c) datafile headers are being dumped or retrieved. In your
case, it's probably the first one.
Enqueue waits are waits for locks to be released. Taking care
of those can be quite complex depending on the types of locks
being held and those being requested and on what structures,
etc.
Latch free waits are waits for another to release a latch on
a given resource. The presence of latch free waits of any significant
magnitude may indicate a bottleneck within the SGA.
It seems obvious that when you turned up the degree of parallelism
on the query, the database as you currently have it set up could
not handle the load. All these wait events would be consistent
with that.
Hope this helps.
Jon Walthour
>--- Original Message ---
>From: Gene Gurevich <[EMAIL PROTECTED]>
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Date: 7/24/01 11:56:29 AM
>
>Hi all:
>
>I am trying to tune some queries. I ran them all in
>one stream (in sequence) and then ran them in several
>(up to 64) parallel streams. I got a snapshot of
>the system for each run. I see some of the waits went
>up significantly when I switched from 2 parallel
>streams to 64: buffer busy wait, db file seq read,
>enqueue waits, latch free waits and many more. Now
>some of this increases may be OK, some may be not. My
>question is how do I decide which of these waits are a
>problem and should be looked into and which are normal
>and can be safely ignored. Are there any quantative
>rules that I could use?
>
>thank you for any insight
>
>Gene
>
>=====
>
>
>__________________________________________________
>Do You Yahoo!?
>Make international calls for as low as $.04/minute with Yahoo!
Messenger
>http://phonecard.yahoo.com/
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Gene Gurevich
> INET: [EMAIL PROTECTED]
>
>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: [EMAIL PROTECTED] (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: Jon Walthour
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Horne, Joe
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
