In oracle, _ use_single_log_writer and _ max_outstanding_log_writes

Scalable lgwr is an exciting feature introduced in 12cR1, because LGWR writes logs in the OLTP environment often become the main performance bottleneck of the system. If the LGWR process can be like DBWR (DBW0 ~ As long as multiple processes (LGNN) write redo to LOGFILE, The OLTP concurrency capability may be greatly released, increasing the transaction processing capability of the Transcation system per unit time. Here we will test the scalable lgwr feature in version 12.2 and determine the influence of the _ use_single_log_writer and _ max_outstanding_log_writes parameters on the scalable lgwr feature.

Database version

SQL> select * from v $ version;
 
BANNER CON_ID
Certificate ----------------------------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3-64bit Production 0
PL/SQL Release 12.2.0.0.3-Production 0
CORE 12.2.0.0.3 Production 0
TNS for Linux: Version 12.2.0.0.3-Production 0
NLSRTL Version 12.2.0.0.3-Production 0
_ Use_single_log_writer and _ max_outstanding_log_writes default values
SQL>/
Enter value for param: _ use_single_log_writer
Old 6: and upper (a. ksppinm) LIKE upper ('% & param % ')
New 6: and upper (a. ksppinm) LIKE upper ('% _ use_single_log_writer % ')
 
NAME VALUE DESCRIPTION
-----------------------------------------------------------------------------------------------------------------------
_ Use_single_log_writer ADAPTIVE Use a single process for redo log writing
 
SQL>/
Enter value for param: _ max_outstanding_log_writes
Old 6: and upper (a. ksppinm) LIKE upper ('% & param % ')
New 6: and upper (a. ksppinm) LIKE upper ('% _ max_outstanding_log_writes % ')
 
NAME VALUE DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------
_ Max_outstanding_log_writes 2 Maximum number of outstanding redo log writes

Lg process count

Here we can see that there is an lgwr process, two lg processes and the _ max_outstanding_log_writes parameter configuration match
[Oracle @ ora1221 ~] $ Ps-ef | grep ora_lg
Oracle 49790 1 0? 00:00:00 ora_lgwr_orcl12c2
Oracle 49794 1 0? 00:00:00 ora_lg00_orcl12c2
Oracle 49798 1 0? 00:00:00 ora_lg01_orcl12c2

Modify the _ max_outstanding_log_writes parameter

By modifying the _ max_outstanding_log_writes parameter to 4, we can find that the number of lg processes has also changed to 4. It turns out that the _ max_outstanding_log_writes process determines the number of lg processes.
SQL> alter system set "_ max_outstanding_log_writes" = 4;
Alter system set "_ max_outstanding_log_writes" = 4
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
 
 
SQL> alter system set "_ max_outstanding_log_writes" = 4 scope = spfile;
 
System altered.
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area 2516582400 bytes
Fixed Size 8260048 bytes
Variable Size 671090224 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8515584 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.0.3-64bit Production
[Oracle @ ora1221 ~] $ Ps-ef | grep lg
Oracle 72339 1 0? 00:00:00 ora_lgwr_orcl12c2
Oracle 72343 1 0? 00:00:00 ora_lg00_orcl12c2
Oracle 72347 1 0? 00:00:00 ora_lg01_orcl12c2
Oracle 72351 1 0? 00:00:00 ora_lg02_orcl12c2
Oracle 72359 1 0? 00:00:00 ora_lg03_orcl12c2

Modify the _ use_single_log_writer parameter

By modifying the _ use_single_log_writer parameter, we can determine that After _ use_single_log_writer is changed to true, the lgwr management method before the database is restored to 12c.

[Oracle @ ora1221 ~] $ Ss
 
SQL * Plus: Release 12.2.0.0.3 Production on Thu Aug 6 13:45:33 2015
 
Copyright (c) 1982,201 6, Oracle. All rights reserved.
 
 
Connected:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3-64bit Production
 
SQL> alter system set "_ use_single_log_writer" = 1;
Alter system set "_ use_single_log_writer" = 1
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
 
 
SQL> alter system set "_ use_single_log_writer" = 1 scope = spfile;
Alter system set "_ use_single_log_writer" = 1 scope = spfile
*
ERROR at line 1:
ORA-00096: invalid value 1 for parameter _ use_single_log_writer, must be from
Among ADAPTIVE, FALSE, TRUE
 
 
SQL> alter system set "_ use_single_log_writer" = TRUE scope = spfile;
 
System altered.
 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.0.3-64bit Production
[Oracle @ ora1221 ~] $ Ps-ef | grep lg
Oracle 72702 71510 0 00:00:00 pts/0 grep lg
[Oracle @ ora1221 ~] $ Ss
 
SQL * Plus: Release 12.2.0.0.3 Production on Thu Aug 6 13:46:50 2015
 
Copyright (c) 1982,201 6, Oracle. All rights reserved.
 
Connected to an idle instance.
 
SQL> startup
ORACLE instance started.
 
Total System Global Area 2516582400 bytes
Fixed Size 8260048 bytes
Variable Size 671090224 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8515584 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.0.3-64bit Production
[Oracle @ ora1221 ~] $ Ps-ef | grep lg
Oracle 72754 1 0? 00:00:00 ora_lgwr_orcl12c2
Oracle 73008 71510 0 00:00:00 pts/0 grep lg

From here, you can determine whether to enable scalable lgwr (multiple lg sub-processes) and _ max_outstanding_log_writes to determine the number of lg processes.

Original article from: Workshop