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: Performance analysis (enqueue and buffer busy waits)

RE: Performance analysis (enqueue and buffer busy waits)

From: Babette Turner-Underwood <babette+oraclelist_at_pythian.com>
Date: Mon, 20 Aug 2001 05:32:17 -0700
Message-ID: <F001.00370A18.20010820052207@fatcity.com>

I am curious.
What are you trying to accomplish by
decreasing rollback segment size??

-----Original Message-----
Lewis
Sent: Tuesday, August 14, 2001 6:52 PM
To: Multiple recipients of list ORACLE-L

Looking at your trace file, most of your lost time appears to be in an update of F4211.

  UPDATE CRPDTA.F4211

    SET SDRSDJ=:BND1,SDPPDJ=:BND2,
        SDCARS=:BND3,SDMOT=:BND4,
        SDFRTH=:BND5,SDUSER=:BND6,SDPID=:BND7,
        SDJOBN=:BND8,SDUPMJ=:BND9,SDTDAY=  :BND10
    WHERE ( SDCDCD = :KEY1 ) 9 seconds CPU, 283 seconds elapsed.

I doubt if freelists are your problem, but possibly the lost time is initrans. NB from 8.1.6 you can change initrans without rebuilding the table - which is good if you don't have to go back to the old data and keep doing the same concurrent updates.

There is no point in going above initrans = 20, but you might push it up to 10. This may improve the enqueue (TX mode 4?) problem, but if it does it will probably make the log sync problem worse.

Try and swing more resources into DBWn - increase LRU latches to start with, and
check whether async I/O, I/O slaves, or
multiple db_writers works best for you.

You may also want to review the size of
your log files (upwards), and your rollback segments (downwards).

Jonathan Lewis

Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html

[snipped]
--

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

Author: Babette Turner-Underwood
  INET: babette+oraclelist_at_pythian.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 Mon Aug 20 2001 - 07:32:17 CDT

Original text of this message

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