LGWR any worker group – LGWR all worker groups – LGWR worker group ordering in oracle

LGWR any worker group – LGWR all worker groups – LGWR worker group ordering in oracle

The “LGWR any worker group” is an idle event which seems to be connected to database that have flash memory (flash, SSD) RAM installed. It doesn’t seem like this is a worrying event.

W_CHAINS                                                TIMER CHART_WCLASS         SQL_ID         TOTAVGACT     PER%AS
-------------------------------------------------- ---------- -------------------- ------------- ---------- ----------
|LGWR any worker group [|]<~~~ LGWR any worker gro          1                                           .36         .1
up|

|LGWR all worker groups [|]<~~~ buffer busy waits|          1                      du1vfy7c7zvf2        .36         .1
| [|]<~~~ resmgr:plan change|                               1                      1h50ks4ncswfn        .36         .1
|resmgr:plan change [|]<~~~ LGWR all worker groups          1                                           .36         .1
|

|buffer busy waits [|]<~~~ LGWR any worker group|           1                                           .36         .1

Since there are multiple LGWR slaves processes working on redo, if any of the LG00 – LGnn process sit idle without any log activity, then event LGWR any or all worker group occur. By default in 19c, log writer functionality is defined as ADAPTIVE which allow the lgwr to switch between single or multiple lgwr processes.

kIsH@Xhydra<>set lines 200 pages 1000
col "Hidden_parameter" for a20
col "It's Value" for a20
select x.ksppinm as Hidden_parameter,
            y.ksppstvl "It's Value"
     from
  x$ksppi x
  inner join x$ksppcv y on (x.indx = y.indx)
and
  x.ksppinm like '%_use_single_log_writ%'
order by x.ksppinm;kIsH@Xhydra<>kIsH@Xhydra<>kIsH@Xhydra<>  2    3    4    5    6    7    8

HIDDEN_PARAMETER     It's Value
-------------------- --------------------
_use_single_log_writ ADAPTIVE
er

kIsH@Xhydra<>!ps -ef|grep ora_lg|grep -v 'grep'
oracle      4080       1  0 07:06 ?        00:00:00 ora_lgwr_db9zx
oracle      4084       1  1 07:06 ?        00:00:05 ora_lg00_db9zx
oracle      4088       1  0 07:06 ?        00:00:00 ora_lg01_db9zx 

LGWR trace shows 2 lgwr slaves created

Created 2 redo writer workers (2 groups of 1 each)
*** 2022-09-08 09:54:54.123124 [krsv.c:2729]

*** 2022-09-08T09:54:54.132649+05:30 (CDB$ROOT(1))
krso_proc_stop: Clearing start ARCH error state

*** 2022-09-08T09:54:55.139669+05:30 (CDB$ROOT(1))
Waking up the heartbeat redo informer process

Multiple lgwr process is a benefit during heavy DML load on the system. This will decrease the load on a single log writer process and coordinate the work in parallel. But every good feature has its benefit and trade off’s. Managing multiple lgwr processes is sometimes tedious due to deadlock issues, database hang, resource starvation like CPU and IO issues. If there is no sufficient IO bandwidth for lgwr processes, then aggressive commit’s will lead to log file sync wait’s. Hence set the number of lgwr process to single with the help of hidden parameter “_use_single_log_writer”

kIsH@Xhydra<>alter system set "_use_single_log_writer"=true scope=spfile;

System altered.

kIsH@Xhydra<>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
kIsH@Xhydra<>startup;
ORACLE instance started.

Total System Global Area 1962932632 bytes
Fixed Size                  9136536 bytes
Variable Size            1476395008 bytes
Database Buffers          469762048 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.


kIsH@Xhydra<>set lines 200 pages 1000
col "Hidden_parameter" for a20
col "It's Value" for a20
select x.ksppinm as Hidden_parameter,
            y.ksppstvl "It's Value"
     from
  x$ksppi x
  inner join x$ksppcv y on (x.indx = y.indx)
and
  x.ksppinm like '%_use_single_log_writ%'
order by x.ksppinm;kIsH@Xhydra<>kIsH@Xhydra<>kIsH@Xhydra<>  2    3    4    5    6    7    8

HIDDEN_PARAMETER     It's Value
-------------------- --------------------
_use_single_log_writ TRUE
er

Post setting the parameter, all the idle wait event’s LGWR worker group vanished