From epost@kcc.com Wed, 15 Aug 2001 13:44:35 -0700 From: "Post, Ethan" Date: Wed, 15 Aug 2001 13:44:35 -0700 Subject: RE: Performance analysis (enqueue and buffer busy waits) Message-ID: MIME-Version: 1.0 Content-Type: text/plain Yeah, raw logs are an option but not yet. We are leaving this system for the client to maintain so the simpler the better. Of course they are yet another company that thinks thier local MSCE network admin can also handle all the DBA duties on top of a jillion other things. I mean, it's just a database afterall, how hard can it be, right? :) Glad to see your still with I2, my brother got let go two weeks ago, I was wondering if I would see you post again. Times are getting tougher. Well we got an employee outing today at the horse races, got to go! Thanks for all the help from everyone today. Thanks, Ethan -----Original Message----- Sent: Wednesday, August 15, 2001 1:29 PM To: Multiple recipients of list ORACLE-L 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" Sent by: root@fatcity.com 08/15/01 01:53 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L 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: 1. Between 37 and 48 MB of rollback per minute 2. 600-700 allocation retries per minute 3. 250,000 log buffer entries per minute 4. 600-700 log buffer spaces waits per minute 5. Almost no disk sorts 6. 3000 commits per minute, this will have to go much higher to meet requirements...ouch. 7. 2-3 redo log space requests per minute 8. 300-400 buffer busy waits per minute, v$waitstat shows they are for data blocks 9. 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@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@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@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@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@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@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@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).