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: Waits on cache buffers chains latch

Re: Waits on cache buffers chains latch

From: Gaja Krishna Vaidyanatha <oraperfman_at_yahoo.com>
Date: Thu, 21 Jun 2001 10:40:23 -0700
Message-ID: <F001.00332501.20010621103531@fatcity.com>

Hi Bruce,

Not sure whether you got a response on this, so here is one. First of all, I am hoping that you have some kind of performance problem on your hand, that you are trying to solve and that led you to checking out the "wait events" in your database. If so, great. If not our discussion is purely theoritical, and I do sincerely hope that you don't look at percentages once and conclude that you have a problem. I am assuming that the "latch free" wait event for the cache buffers chains latch occurs frequently and shows up in V$SESSION_WAIT. Contention or waits for the "cache buffers chains latch" usually indicates that there is "too much logical I/O" that is being performed in your environment. Contrary to common knowledge, logical I/O is not 3 orders of magnitude faster than physical I/O in an Oracle environment, as there is a lot more that goes on when Oracle performs a logical I/O, than just "reading blocks from memory". So reducing logical I/O should also be one of the primary efforts one takes in tuning efforts.

The cache buffers chains latch is a scarce resource that needs to be acquired for performing logical I/O and can cause serious contention (which you are probably experiencing). This could be caused because of the use of GTT with tablespace type of "Permanent", as the blocks for temporary segment need to be processed via the database buffer cache, when the size of the data processed by the GTT (at the transaction or the session level) exceeds the size of SORT_AREA_SIZE. Your system may also be experiencing severe contention for the "ST enqueue" as a result of you changing your temporary tablespace to type "permanent", as there could be constant allocation and deallocation of temp segments, which requires the ST enqueue.

One solution to your problem is to upgrade to 8.1.7 (if possible and hoping that the bug is fixed) and flip your temporary tablespace back to type "temporary". Another option is to increase the size of SORT_AREA_SIZE, so that you reduce the frequency with which your sessions generate temporary segments. You also should look into the option of creating a "locally managed temporary tablespace" with the CREATE TEMPORARY TABLESPACE command which again will alleviate the contention for blocks in the database buffer cache.

Hope that helps,

Gaja


Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101

Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gaja Krishna Vaidyanatha
  INET: oraperfman_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).
Received on Thu Jun 21 2001 - 12:40:23 CDT

Original text of this message

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