It is not possible to increase the number of lgwr processes in a single instance until 9.2, and the manuals suggest that (a) you don't do it, and (b) you only do it if you have a very large number of CPU and massive stress on redo generation.
Let me repeat - v$system_event can be very misleading, and you need to check v$session_event for patterns. HOWEVER - assuming your redo generation is uniform across all active processes, and you don't have a handful of processes that are doing massive updates by running pl/sql loops with single row commits them a) Your average redo per transaction is about 2K, which is SLIGHTLY suspect for an OLTP system given that the average number of redo entries per transaction is only 5. This HINTS at the possibility that your code is updating every column in the row when doing a simple update. This is possibly the issue to target to make any significant dent. (I've just realised, of course, that I haven't mentioned the possibility that lots of people are doing things that simply don't need to be done. It isn't unknown for applications to update a row in a table every 10 seconds as a way of sending a heartbeat to the database). b) You sync time per transaction is 1.9 centiseconds. If your usage is really only OLTP, then no-one is going to notice that INDIVIDUALLY they lose that amount of time on every commit. (Of course in a 1M row update running single row commits, that 1.9 cs per commit becomes significant). Bottom line - if no-one is complaining, you don't seem to have a problem. If someone is complaining, you know which bit of v$session_event (and V$sesstat) to look at to pinpoint the problem. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____England______January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -----Original Message----- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 02 January 2003 16:41 > >REPLIES TO YOUR QUESTIONS ARE IN CAPITALS BELOW :- > >THANKS > >-----Original Message----- >Sent: Thursday, January 02, 2003 3:59 PM >To: Multiple recipients of list ORACLE-L > > > >Usual caveat: > looking a v$system_event can be very misleading, > you need to examine v$session_event to determine > if anyone is actually noticing a problem. > >Usual caveat 2: > A statspack report without a time interval > is almost meaningless. However, in this case, > log file sync at the top is sufficiently unusual to > warrant a little hypothesis. > >SOME STATSPACK OUTPUTS :- > Snap Id Snap Time Sessions > ------- ------------------ -------- >Begin Snap: 124 31-Dec-02 12:53:00 1,237 > End Snap: 133 31-Dec-02 19:12:19 1,237 > Elapsed: 379.32 (mins) > > >Cache Sizes >~~~~~~~~~~~ > db_block_buffers: 200000 log_buffer: 2097152 > db_block_size: 8192 shared_pool_size: 157286400 > >Load Profile >~~~~~~~~~~~~ Per Second Per Transaction > --------------- ------------ --- > Redo size: 118,042.27 2,007.50 > >Instance Efficiency Percentages (Target 100%) >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > Buffer Nowait %: 99.99 Redo NoWait %: 100.00 > >Wait Events for DB: NCB Instance: ncb Snaps: 124 -133 >-> cs - centisecond - 100th of a second >-> ms - millisecond - 1000th of a second >-> ordered by wait time desc, waits desc (idle events last) > >Event Waits Timeouts Time (cs) (ms) /txn >---------------------------- ------------ ---------- ----------- ---- -- ------ >log file sync 970,563 537 2,597,831 27 0.7 >log file parallel write 831,141 23 484,948 6 0.6 >db file sequential read 8,310,890 0 416,355 1 6.2 >log file switch completion 98 0 1,712 175 0.0 >log buffer space 124 0 1,551 125 0.0 >LGWR wait for redo copy 4,785 22 299 1 0.0 >log file single write 123 0 49 4 0.0 > > >Background Wait Events for DB: NCB Instance: ncb Snaps: 124 -133 >-> ordered by wait time desc, waits desc (idle events last) > Avg > Total Wait wait Waits >Event Waits Timeouts Time (cs) (ms) /txn >---------------------------- ------------ ---------- ----------- ---- -- ------ >log file parallel write 831,123 23 484,945 6 0.6 >log file sequential read 89,714 0 3,418 0 0.1 > > >Instance Activity Stats for DB: NCB Instance: ncb Snaps: 124 -133 > >Statistic Total per Second per Trans >--------------------------------- ---------------- ------------ ----- ------- >redo blocks written 5,875,674 258.2 4.4 >redo buffer allocation retries 218 0.0 0.0 >redo entries 6,531,597 287.0 4.9 >redo log space requests 98 0.0 0.0 >redo log space wait time 1,712 0.1 0.0 >redo ordering marks 0 0.0 0.0 >redo size 2,686,523,912 118,042.3 2,007.5 >redo synch time 2,602,866 114.4 1.9 >redo synch writes 957,313 42.1 0.7 >redo wastage 227,405,300 9,991.9 169.9 >redo write time 1,185,661 52.1 0.9 >redo writer latching time 304 0.0 0.0 >redo writes 831,047 36.5 0.6 > > >Latch Activity for DB: NCB Instance: ncb Snaps: 124 -133 > Pct Avg Pct > Get Get Slps NoWait NoWait >Latch Name Requests Miss /Miss Requests Miss >----------------------------- -------------- ------ ------ ---------- -- ------ >redo allocation 8,204,689 0.1 0.1 0 >redo writing 4,932,177 0.8 0.0 0 > > >Latch Miss Sources for DB: NCB Instance: ncb Snaps: 124 -133 >-> only latches with sleeps are shown >-> ordered by name, sleeps desc > > NoWait Waiter >Latch Name Where Misses Sleeps Sleeps >------------------------ -------------------------- ------- --------- - ------- >redo allocation kcrfwr: redo allocation 0 469 510 >redo allocation kcrfwi: before write 0 78 31 >redo allocation kcrfwi: more space 0 20 26 >redo writing kcrfsr 0 970 72 >redo writing kcrfss 0 209 1,242 >redo writing kcrfwi: after write 0 170 19 >redo writing kcrfwcr 0 16 32 > > >redo writing Waiter Sleeps Value = 1,242 IS LARGER THAN THAT OF OTHER EVENTS > >Question: Was log file write really number two, >or have you knocked out one or two lines between >the two log-related waits ? > >"log file parallel write" IS INDEED THE SECOND WAIT AFTER "Log file sync" IN STATSPACK > >Log file syncs are from the sessions, >log file writes are from LGWR > >A log file sync is a call from a session to lgwr >to write some log buffer to disc. As such, you >could get multiple sessions calling at about the >same time - and only the first one in gets lgwr >to write, the rest have to wait until lgwr returns >and notices that there is now a queue and does >a piggyback write. > >Consequently, it is possible on a highly concurrent >system for log file sync to have far more WAITS >then log file write, and therefore look a much bigger >problem than it really is. > >However, in your case, the number of log file sync >WAITS is about the same as the number of log file write >WAITS - so the fact that the TIME is five times as long >suggests that concurrency of waits is not the issue, and >you may have a proper problem. > >I suspect that the problem is the number of processes >running on your system. Session A issues a log file sync, >and goes off the run queue; some time later, lgwr gets the >message and writes and posts session A to allow it to go >back on the run queue. Session A sits on the run queue >for ages, and finally becomes runnable. Solution - >look at MTS, or get more CPUs on the box. > >IS THERE ANY WAY TO INCREASE THE NUMBER OF LGWR PROCESSES >IN A SINGLE INSTANCE DATABASE ? > >But having said that - do check if any sessions are >actually noticing a significant loss of time due to >log file sync before worry about it. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
