| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: log file sync Wait
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
(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 <ORACLE-L_at_fatcity.com>
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
> --------------- ------------
---Received on Fri Jan 03 2003 - 04:28:47 CST
> 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: jonathan_at_jlcomp.demon.co.uk 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: 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).
 |
 |