Question: I am running Oracle 10gr2 with a
standby database on Data Guard using ASYNC redo log transport.
I am getting these LNS event in my AWR report, LNS Wait events.
Avg
%Time
Total Wait wait Waits
Event Waits -outs
Time (s) (ms) /txn
----------------------- -------------- ------
----------- ------- ---
LNS wait on SENDREQ 312
.0 197 632 0.0
LNS ASYNC end of log 143,963 100.0
1,545 11 5.8
I have a slow network with an average packet time of 25
milliseconds with busy updates at a rate of 250 block changes per second.
During the period of high LNS wait on SENDREQ, my database
"hangs", not allowing anybody to sign-in. What is a wait on
the Log Network Server (LNS) process, and how do I fix this issue?
Answer: The LNS wait on SENDREQ can happen
with either the SYNC or ASYNC LGWR attributes.
When using ASYNC transport mode in Oracle 10g r2 and beyond,
Oracle recommends allowing for sufficient I/O bandwidth for LNS read
I/Os to the online redo logs of the production database.
You can choose the LGWR attributes
for synchronous (LogXptMode
= 'SYNC') or
asynchronous mode (LogXptMode = 'ASYNC'). See these
important notes on Data
Guard synchronous (SYNC) vs asynchronous (ASYNC) modes.
You can dynamically change the LGWR attributes with the "edit
database" command:
edit database 'stdbydb' set
property LogXptMode=SYNC
edit database 'primdb' set property LogXptMode=ASYNC
The LNS wait on SENDREQ occurs more frequently on
a busy network when the LNS process is stuck, waiting for the RFS to
send an ack from the standby server. The root cause of a LNS
wait on SENDREQ is a busy network, and this error is most likely to
appear when the system is during high-volume updates (heavy DML).
Log transport steps
Here are the log transport steps and the associated waits for
each log transport step. Step 1 is manifested as a log file parallel
write wait, while all subsequent steps are the "LNS wait on
SENDREQ':
0 - The LGWR process writes from the online redo
log filesystem to the archived redo log directory. This can be
seen as a "log file parallel write" wait event.
1 - Using an
FTP-type mechanism, the LNS process detects the completion of a redo
log being written to the local archived redo log directory.
2 - Once the redo has completed archiving, the LNS process
communicates with the remote file service (RFS) process) to manage
the transport of the flat file to the remote standby server, waiting
for a system acknowledgement (an "ack") from the remote file service
(RFS) process.
3 - Once the ack is received from
standby, the LNS process notifies the log writer process (LGWR) and
the commit occurs.
4 - Upon arrival at the standby server,
the redo updates are applied to the standby database, thereby
reproducing the updates from the originating server.
For more details on the LNS wait for SENDREQ, see MOSC note
233491.1.
Possible solutions for LNS wait on SENDREQ
Possible solutions for a hung database on LNS wait on SENDREQ
include:
0 - Change net_timeout: If using
SYNC mode in 10gr2 and beyond, consider reducing the value of the
net_timeout attribute. The value of net_timeout
indicates number of seconds that LGWR on the production
database waits for Oracle*Net to respond to a LGWR request.
1 - Try setting
COMMIT WRITE NOWAIT. This will allow transactions to
continue processing without waiting for LGWR to post a message back
stating that all redo changes are on disk.
2 - Spread-out the
DML load over a longer period of time, by adjusting the schedule of
any batch jobs
3 - Get a faster network transport such as
dark fibre.
4 -
Change the size of the online redo logs to make the archived
redo log smaller, thereby creating smaller archived redo logs.
This will create more frequent, smaller redo log transports that
will complete faster.
5 - Tune the TNS
layer, perhaps trying the
tcp.nodelay parameter in the protocol.ora file
How the LNS wait on SENDREQ occurs
The Data Guard redo log transport may be synchronous mode (LogXptMode
= 'SYNC') or
asynchronous mode (LogXptMode = 'ASYNC').
The Async LNS process is a background
daemon process that periodically checks for new redo entries from
the log writer (LGWR) process. The ASYNC wait is a normal idle
time event when you are using Data Guard asynchronous redo log
transport.
The Log Network Server (LNS) is a background process that manages
all archived redo log flat file transports to a standby server.
This query display data from the
select
process,
status
from
v$managed_standby;
The "LNS wait on SENDREQ" is the sum of network
time and RFS I/O time. To see the network time, take the value
for LNS wait on SENDREQ and subtract the "RFS write" wait event time
from the standby server. You can also use the UNIX/Linux
netstat utility to measure packet shipment latency.
The
Oracle documentation notes these wait events for destinations
configured with the LGWR SYNC and ASYNC Attributes
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
