如何进一步提高Oracle lgwr的写性能？

今天在我们的一个项目中，客户非常关系Oracle的log file sync、db file parallel write性能。

由于我们的分布式存储zdatax已经是nvme了，因此db file parallel write的性能足够好了，平均等待时间也就0.13ms。

然后log file sync 确高达0.6ms，实际上大家都知道0.6ms也是非常非常的低了。无奈，客户要求很高，那么怎么办？

通过awr报告我发现很大一部分的等待消耗在了log file parallel write上，如何能降低这个等待，那么log file sync是不是也降低了呢？

这里想到一个测试优化的方法，以及我的测试过程和结论，供大家参考！

+++test 1

alter system set "_redo_write_coalesce_all_threshold"=1048576   scope=spfile sid='*';
alter system set "_redo_write_coalesce_slave_threshold"=1048576 scope=spfile sid='*';
alter system set "_redo_write_sync_single_io"=true scope=spfile sid='*';


--session 1
SQL> startup force
ORACLE instance started.

Total System Global Area 4294965376 bytes
Fixed Size                  8947840 bytes
Variable Size            1811939328 bytes
Database Buffers         2147483648 bytes
Redo Buffers              326594560 bytes
Database mounted.
Database opened.
SQL> !host ps -ef|grep lgwr
/bin/bash: host: command not found

SQL> host ps -ef|grep lgwr
oracle   30829     1  0 20:32 ?        00:00:00 ora_lgwr_ora19c
oracle   31428 16274  0 20:32 pts/2    00:00:00 /bin/bash -c ps -ef|grep lgwr
oracle   31430 31428  0 20:32 pts/2    00:00:00 grep lgwr

SQL> @test_lgwr.sql

Table dropped.
。。。。。。

--session 2
oracle@oradb1:/home/oracle $sqlplus "/as sysdba" 

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 19 20:33:05 2024
Version 19.23.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL> oradebug setospid 30829
Oracle pid: 21, Unix process pid: 30829, image: oracle@oradb1 (LGWR)
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_lgwr_30829.trc
SQL> oradebug event 10046 trace name context off
Statement processed.
SQL> 



oracle@oradb1:/tmp $cat /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_lgwr_30829.trc|grep "log file single write" | awk '{sum+=$8} END {print sum}'
25441
oracle@oradb1:/tmp $cat /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_lgwr_30829.trc|grep "log file parallel write" | awk '{sum+=$8} END {print sum}'
2075230
oracle@oradb1:/tmp $


+++test 2

alter system set "_redo_write_coalesce_all_threshold"=1048576   scope=spfile sid='*';
alter system set "_redo_write_coalesce_slave_threshold"=1048576 scope=spfile sid='*';
alter system set "_redo_write_sync_single_io"=true scope=spfile sid='*';


oracle@oradb1:/tmp $cat /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_lgwr_32733.trc|grep "log file single write" | awk '{sum+=$8} END {print sum}'
2726
oracle@oradb1:/tmp $cat /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_lgwr_32733.trc|grep "log file parallel write" | awk '{sum+=$8} END {print sum}'
1544500
oracle@oradb1:/tmp $


+++test 3

alter system set "_redo_write_coalesce_all_threshold"=10485760   scope=spfile sid='*';
alter system set "_redo_write_coalesce_slave_threshold"=10485760 scope=spfile sid='*';
alter system set "_redo_write_sync_single_io"=true scope=spfile sid='*';

oracle@oradb1:/tmp $cat /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_lgwr_2429.trc|grep "log file single write" | awk '{sum+=$8} END {print sum}'
7907
oracle@oradb1:/tmp $cat /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_lgwr_2429.trc|grep "log file parallel write" | awk '{sum+=$8} END {print sum}'
1403895
oracle@oradb1:/tmp $

+++test 4

alter system set "_redo_write_coalesce_all_threshold"=10485760   scope=spfile sid='*';
alter system set "_redo_write_coalesce_slave_threshold"=10485760 scope=spfile sid='*';
alter system set "_redo_write_sync_single_io"=false scope=spfile sid='*';


oracle@oradb1:/tmp $ cat /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_lgwr_5665.trc|grep "log file single write" | awk '{sum+=$8} END {print sum}'
25678
oracle@oradb1:/tmp $cat /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_lgwr_5665.trc|grep "log file parallel write" | awk '{sum+=$8} END {print sum}'
888966
oracle@oradb1:/tmp $




++++test 5

alter system reset "_redo_write_coalesce_all_threshold"   scope=spfile sid='*';
alter system reset "_redo_write_coalesce_slave_threshold" scope=spfile sid='*';
alter system set "_redo_write_sync_single_io"=true scope=spfile sid='*';



oracle@oradb1:/tmp $cat /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_lgwr_7680.trc|grep "log file single write" | awk '{sum+=$8} END {print sum}'
47659
oracle@oradb1:/tmp $cat /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_lgwr_7680.trc|grep  "log file parallel write" | awk '{sum+=$8} END {print sum}'
2105721
oracle@oradb1:/tmp $

其中sql脚本如下:
oracle@oradb1:/home/oracle $                                 cat test_lgwr.sql 
drop table test1119;
alter system switch logfile;
create table test1119 as select * from dba_objects;
insert into test1119 select * from test1119;
commit;
insert into test1119  select * from dba_objects where rownum <100;
commit;
insert into test1119  select * from dba_objects where rownum <100;
commit;
insert into test1119  select * from dba_objects where rownum <100;
commit;
insert into test1119  select * from dba_objects where rownum <100;
commit;
insert into test1119  select * from dba_objects where rownum <100;
commit;
insert into test1119  select * from dba_objects where rownum <100;
commit;
insert into test1119  select * from dba_objects where rownum <100;
commit;
insert into test1119  select * from dba_objects where rownum <100;
commit;
insert into test1119  select * from dba_objects where rownum <100;
commit;
insert into test1119  select * from dba_objects where rownum <100;
commit;
insert into test1119  select * from dba_objects where rownum <100;
commit;
insert into test1119 select * from test1119;
commit;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

针对上述4个场景的测试数据进行一下汇总：

场景1: 单块写 25441 多块写 2075230 合计时间 2100671

场景2: 单块写 2726 多块写 1544500 合计时间 1547226

场景3: 单块写 7907 多块写 1403895 合计时间 1411802

场景4: 单块写 25678 多块写 888966 合计时间 914644

场景5: 单块写 47659 多块写 2105721 合计时间 2153380

从测试来看，_redo_write_sync_single_io参数的影响微乎其微，可忽略不计。

而_redo_write_coalesce_all_threshold和_redo_write_coalesce_slave_threshold的参数如果设置为10m和保持默认值的情况进行对比。 我们可以发现lgwr写消耗时间差了几乎1倍。由此可见，这是一个提升log file parlalel write的重要手段。

通过降低log file parallel write的等待，进而降低log file sync的等待。

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

加入DataBase Fans付费群，您能有哪些收获？ 

1、大家可与顶级数据库专家互动，问题范围不限于Oracle，MySQL,openGauss等。
      -群内有全国Oracle顶级恢复专家，sql优化专家，MySQL源码专家，都是实战派
2、 入群可以获得顶级专家的收藏脚本。
3、 可提供原厂资料文档代查【包括xxxx账号,你们懂的】
4、 不定期组织直播案例分析【包括但不限于Oracle、MySQL、国产xxx数据库】
5、 付费群：365人/年 【2025/1/1 - 2025/12/31】

想加入的朋友， 加v咨询 Roger_database