Shulou(Shulou.com)05/31 Report--

This article will explain in detail what to do when Oracle 11g encounters a serious waiting event of log file sync. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Database version: 11.2.0.3.0

RAC two nodes, DG one node.

RAC Node 1 is normal, RAC Node 2 has a serious log file sync wait event, and database performance is seriously affected.

According to the AWR report:

The DB Time is very high, and the log file sync wait is serious.

Normally, the Avg wait for log file sync should be 1.

The problem is that log buffer writes slowly to log file.

The IO problem is ruled out.

There is an article about the log file sync wait event for 11.2.0.3.

Http://www.askmaclean.com/archives/bug-13551402-high-log-file-syncs-after-upgrading-from-10-2-0-5-to-11-2.html

If you are experiencing a performance problem with a significant increase in LOG FILE SYNCS wait events from 10.2.0.5 to 11.2, it is worth reading this article.

In previous experience, if you encounter such a scenario, priority is given to setting "_ use_adaptive_log_file_sync" = false. Adaptive log file sync is a new feature that optimizes redo log writing proposed in 11.2, and defaults to TRUE after 11.2.0.3.

There are cases in which the average wait time for log file sync wait events decreased from 10ms to 1~2ms after the customer changed "_ use_adaptive_log_file_sync" = false.

The reason for the performance degradation of _ use_adaptive_log_file_sync may be that it causes LGWR to use polling instead of post/wait, and the interval of polling is 10ms, which is written dead in the code.

Also pay special attention if you use Veritas/symantec 's ODM: you may have encountered Bug 13551402 High "log file parallel write" and "log file sync" after upgrading 11.2 with Veritas/Symantec ODM, which has been confirmed to exist on 11.2.0.3 and 11.2.0.2.

The internal discussion of the bug finally confirmed that the IO of lgwr in 11.2 uses a batch synchronization I _ ODM O interface, which results in performance degradation when used with Veritas/symantec 's ODM.

Patches are currently available on several Unix/Linux platforms for the BUG:

Here I directly modify "_ use_adaptive_log_file_sync" = false

ALTER SYSTEM SET "_ use_adaptive_log_file_sync" = FALSE

SQL > SELECT ksppinm, ksppstvl, ksppdesc

2 FROM x$ksppi x, x$ksppcv y

3 WHERE x.indx = y.indx AND ksppinm like'_ use_adaptive_log_file_sync'

KSPPINM

KSPPSTVL

KSPPDESC

_ use_adaptive_log_file_sync

FALSE

Adaptively switch between post/wait and polling

After the correction, run AWR again.

By comparing AWR reports at the same time two days before and after, log file sync waits for the event to disappear. Log file sync becomes 1.

DB Time also dropped sharply.

Problem solved.

About Oracle 11g encounter log file sync serious waiting event what to do to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.