RE: what do I tune?

-----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 <g_u_r_e_v_i_c_h_at_yahoo.com>
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>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: g_u_r_e_v_i_c_h_at_yahoo.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: Jon Walthour
  INET: jonw_at_fuse.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Horne, Joe
  INET: Joe.Horne_at_storagenetworks.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).