RE: Performance analysis (enqueue and buffer busy waits)

From: <Riyaj_Shamsudeen_at_i2.com>
Date: Wed, 15 Aug 2001 12:34:57 -0700
Message-ID: <F001.0036BE83.20010815122837@fatcity.com>

Ethan
You are committing 50 times / second which is quite a bit high. So, LGWR has to write out the buffers at least 50 times in a second (excluding the group commits). I didn't quite follow the initial threads, but are you using file system based log files ? For this type of high activity, I would recommend using async IO and raw / Quick IO log files. You may need to read the tip by Steve Adams.. http://www.ixora.com.au/tips/creation/raw_log_files.htm

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com

"Post, Ethan" <epost_at_kcc.com>
Sent by: root_at_fatcity.com
08/15/01 01:53 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc:        
        Subject:        RE: Performance analysis (enqueue and buffer busy waits)

12 CPU machine redo logs on separate disks and controller, nothing else on them. Application puts PK's on data tablespace so might be some contention there, other indexes are on index tablespace which is separated from data disks.

The job causes:

Between 37 and 48 MB of rollback per minute
600-700 allocation retries per minute
250,000 log buffer entries per minute
600-700 log buffer spaces waits per minute
Almost no disk sorts
3000 commits per minute, this will have to go much higher to meet requirements...ouch.
2-3 redo log space requests per minute
300-400 buffer busy waits per minute, v$waitstat shows they are for data blocks
Average log write size shows around 22K at the moment but was not tracking during test. I will set up a metric to track this next time.

Thanks for all the help. I will be setting event 10046 for the next test and setting up a few more metrics. I guess the real question is how fast can this job ultimately go if the above stats represent a paltry 230 sales order lines per minutes. I think redo is going to be the big bottleneck as I don't see a lot of ways to reduce it since we are talking about a packaged ERP app unless the client wants to pay to have the job rewritten which is going to be big $.

>-----Original Message-----
>From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
>Sent: Wednesday, August 15, 2001 4:36 AM
>To: Multiple recipients of list ORACLE-L
>Subject: Re: Performance analysis (enqueue and buffer busy waits)
>
>
>Ignore (or at least treat very lightly) the comments about DBWm.
>
>Excessive database writes can cause log file sync waits, as
>dbwr calls lgwr to write the log protecting the blocks it is
>about to write. In this case, you will see v$session_event
>for the db writers showing log file waits. Unfortunately I
>have a mental block that makes me explain this phenomenon
>100% the wrong way round every 6 months or so. And that
>is what I did last night.
>
>I suspect you are also going to tell me that the anomalous
>difference between CPU and elapsed time on the one big
>update is because everything else is done by triggers,
>and the update is waiting for triggers to complete ;(
>
>4GB of redo log in 50 minutes is quite a lot.
>Unless I've done the arithmetic wrong, that's
>close to 1.4MB per second (or 2.8 since you are
>using Oracle duplexing). It seems a little odd that
>you are getting 'log file sync' as a problem
>without getting (in your case) 'log buffer space'
>and 'log file ... write'.
>
>What is your average log file write size ?
>(redo blocks written / redo writes). and
>what do you other 'redo%' stats look like
>over the period ?
>
>How many CPUs ?
>
>
>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
>
>
>--
>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).
>

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 Wed Aug 15 2001 - 14:34:57 CDT