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: Gogala, Mladen <MGogala_at_oxhp.com>
Date: Mon, 20 Aug 2001 13:16:11 -0700
Message-ID: <F001.003718DA.20010820133903@fatcity.com>

Increase the "snapshot too old" query mortality rate?

> -----Original Message-----
> From: Babette Turner-Underwood [mailto:babette+oraclelist_at_pythian.com]
> Sent: Monday, August 20, 2001 9:22 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Performance analysis (enqueue and buffer busy waits)
>
>
> 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: Gogala, Mladen
  INET: MGogala_at_oxhp.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 - 15:16:11 CDT

Original text of this message

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