This was first published on https://blog.dbi-services.com/dataguard-wait-events-have-changed-in-12c (2015-04-08)
Republishing here for new followers. The content is related to the the versions available at the publication date
There are several new features in 12c about Data Guard: cascaded standby, far sync instance. But there are also some architecture changes: new processes and new wait events. Here is an example of an AWR report of a LogXptMode=’SYNC’ DataGuard configuration in 11g – which means that the log_archive_dest is defined with: ‘LGWR SYNC AFFIRM’ That report comes from a period of time where the primary database was stuck because the standby server had a problem. It’s 11.2.0.2 and the foreground events show that the user sessions are waiting on LGWR:
Event | Waits | Time(s) | Avg wait (ms) | % DB time | Wait Class |
---|---|---|---|---|---|
log file sync | 17,002 | 21,113 | 1242 | 46.08 | Commit |
log buffer space | 1,322 | 8,583 | 6493 | 18.73 | Configuration |
buffer busy waits | 1,869 | 4,376 | 2342 | 9.55 | Concurrency |
enq: TX – row lock contention | 997 | 2,105 | 2111 | 4.59 | Application |
DB CPU | 1,773 | 3.87 |
then we have to go to the background event section in order to see what the LGWR was waiting on:
Event | Waits | %Time -outs | Total Wait Time (s) | Avg wait (ms) | Waits /txn | % bg time |
---|---|---|---|---|---|---|
LNS wait on SENDREQ | 17,674 | 0 | 2,103 | 119 | 1.04 | 37.50 |
LGWR-LNS wait on channel | 153,447 | 88 | 2,094 | 14 | 9.03 | 37.34 |
log buffer space | 17 | 0 | 114 | 6723 | 0.00 | 2.04 |
db file parallel write | 52,931 | 0 | 105 | 2 | 3.12 | 1.87 |
enq: CF – contention | 16 | 0 | 78 | 4865 | 0.00 | 1.39 |
log file switch completion | 4 | 0 | 63 | 15640 | 0.00 | 1.12 |
log file sync | 6 | 0 | 55 | 9102 | 0.00 | 0.9 |
In 11g the LNS processes (Log Network Server) is responsible to send redo to the standby. Because we are in SYNC we wait for remote acknowlegement. Nothing new here. But let’s see what has changed in 12c.
In 12c the name of the processes have changed. It’s not LNS anymore, but:
and you will see new wait events for them:
So what is the 12c equivalent of the following waits that are symptomatic of SYNC latency:
Here they are in picture (using Orachrome Lighty):
If you were monitoring for ‘LNS’ wait events, you have to change it. Here is the new pattern in an AWR report:
Event | Waits | %Time -outs | Total Wait Time (s) | Avg wait (ms) | Waits /txn | % bg time |
---|---|---|---|---|---|---|
log buffer space | 7 | 0 | 896 | 128023.85 | 0.00 | 58.96 |
SYNC Remote Write | 129 | 0 | 300 | 2322.96 | 0.00 | 19.72 |
buffer busy waits | 2 | 0 | 287 | 143596.94 | 0.00 | 18.89 |
log file parallel write | 116 | 0 | 6 | 48.02 | 0.00 | 0.37 |
Data Guard Broker Wait | 5 | 100 | 5 | 1000.26 | 0.00 | 0.33 |
Redo Transport MISC | 110 | 0 | 2 | 22.70 | 0.00 | 0.16 |
The documentation is not yet up-to-date, but all the old and new wait events are documented here.
Hi Franck, Thank you for the article. I have below query: How can we reduce the time for background wait events – Redo transport MISC and SYNC remote Write? I have 1 Primary Database and 3 Physical Standby Databases in the DR setup. In ADG, The Protection mode is MAX Availability. Oracle 12c – 12.1.0.2 Recovery is configured with Redo log real apply mode.
Please suggest.
Hi Gaurav, So you have 3 standbys in SYNC? This means that each commit has to wait that redo is written in 4 places, two of them having network latency in addition. I don’t know if they are all done in parallel in 12.1.0.2 Do you really need that? If you have Active Data Guard, you may prefer real-time cascading. Regards, Franck.
Hi Franck,
Nice Blog with great examples.
One question, is each destination(LOG_ARCHIVE_DEST_n) has its own NSSn ? i.e. NSSx is named as LOG_ARCHIVE_DEST_y, where x and y are same number ? For example, NSS2 is for LOG_ARCHIVE_DEST_2.
Regards,
Hi Ksun, Yes when you have two destinations you have two NSS processes and they seem to be numbered as the log_archive_dest. Regards, Franck.