Re: Performance analysis (enqueue and buffer busy waits)

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 14 Aug 2001 14:42:55 -0700
Message-ID: <F001.0036A9F1.20010814145144@fatcity.com>

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

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 14 August 2001 22:36

|I am currently attempting to tune an ERP batch job for processing EDI
sales
|orders. We have a very powerful machine and the assumption is if one
|process can handle 200 lines per minute then 20 processes all running
a
|different data selection can process 4000 lines per minute. The
bottlenecks
|at the moment are log file sync and enqueue. I have trace files for
3 of
|the processes that all do pretty much the same thing. One thing I
know is I
|need to reduce contention on the objects by increasing freelist and
|initrans. How much I don't know yet. I think Gaja's book said 2*CPU
is
|about as good as your get but there can be drawbacks to having to
many
|freelist.
|
|Logs are on separate devices, no mirroring just Oracle multiplexing.
|
|I can see from the trace file the objects F42UI11 and F42UI130 have
some
|problems.
|
|
|Charts tracking various metrics during testing requires free MS
Snapshot
|Viewer (http://www.microsoft.com/accessdev/prodinfo/snapshot.htm)
|
|http://www.freetechnicaltraining.com/docs/2001-08-14_TEST01.snp
|
|Trace file from one of the sessions:
|http://www.freetechnicaltraining.com/docs/52750.txt
|
|Log buffer size is 256K at the moment, we have tried smaller and
larger.
|Don't know what else I can really do to reduce log file sync. The
job
|create about 4 GB of redo in 50 minutes. Disk sub system is suppose
to be
|fast (SHARK) I think, so the admin tells me. The system is remote so
I
|havn't really dug down into it yet.
|
|As far as the enqueues I ran on of Steve's scripts and they were TX
related
|so I am assuming the are coming from all the DML contention which
hopefully
|will be reduced significantly when change table parameters. Feel
feel to
|give the files a look and let me know what you think.
|
|Thanks,
|Ethan
|http://www.geocities.com/epost1
|
|>-----Original Message-----
|>From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
|>Sent: Tuesday, August 14, 2001 1:27 PM
|>To: Multiple recipients of list ORACLE-L
|>Subject: Re: Performance analysis (enqueue and buffer busy waits)
|>
|>
|>
|>Enqueue waits cannot cause buffer busy waits,
|>but the absence of indexes (and you point out
|>missing FK indexes) can result in excessive
|>tablescanning, and tablescanning can result
|>in buffer busy waits.
|>
|>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
|
|---------------------------------------------------------------------

|This e-mail is intended for the use of the addressee(s) only and may
contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you.
|
|=====================================================================

|--
|Please see the official ORACLE-L FAQ: http://www.orafaq.com
|--
|Author: Post, Ethan
| INET: epost_at_kcc.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: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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 Tue Aug 14 2001 - 16:42:55 CDT