RE: Performance analysis (enqueue and buffer busy waits)

From: Post, Ethan <epost_at_kcc.com>
Date: Tue, 14 Aug 2001 14:08:27 -0700
Message-ID: <F001.0036A8F6.20010814141659@fatcity.com>

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

Received on Tue Aug 14 2001 - 16:08:27 CDT