RE: Performance analysis (enqueue and buffer busy waits)

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Wed, 22 Aug 2001 07:12:52 -0700
Message-ID: <F001.00373C5E.20010822061628@fatcity.com>

To keep the rollback segments in buffer, so that rolling around the rollback segments happens before blocks are written to disk. Which will increase snapshot to old, but increase performance.

"Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863

-----Original Message-----
Sent: Monday, August 20, 2001 9:22 AM
To: Multiple recipients of list ORACLE-L

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

Babette

-----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).
--

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

Author: Christopher Spence
INET: cspence_at_FuelSpot.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------