等待事件 LGWR all worker groups

郑全

今天在生产系统上看到这个 LGWR all worker groups 等待事件，这个是干什么的？
看看MOS上怎么说的：



APPLIES TO:Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.
GOAL

This document explains LGWR worker group.

SOLUTION

LGWR worker group is a new feature of 12c and it is enabled by default in 12c and later.
It is often referred as "LGWR slave group" or "multiple Log Writers."


When it is enabled, at least two more background processes lg00, lg01 are spawned.

% ps -ef | grep ORA1 | grep lg

oracle  8073     1  0  2022 ?        00:02:44 ora_lgwr_ORA1
oracle  8077     1  0  2022 ?        00:01:22 ora_lg00_ORA1
oracle  8079     1  0  2022 ?        00:00:47 ora_lg01_ORA1

In AWR reports, you can see the following wait events related to LGWR worker group.

LGWR intra group sync   
LGWR worker group ordering   
LGWR any worker group   
LGWR intra group IO completion   
LGWR all worker groups

This feature may degrade LGWR performance in some environments.

You can turn off this feature by setting a hidden parameter.

SQL> alter system set "_use_single_log_writer" = true scope=spfile;  

It is set to 'ADPTIVE' by default.
This is a static parameter. You need to restart the instance for the change.

我这里正好是19.3的环境。