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" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
08/15/01 01:53 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
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:[EMAIL PROTECTED]]
>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: [EMAIL PROTECTED]
>
>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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
