oracle 12c scalable lgwr 进程提高性能_max_outstanding_log_writes

57 阅读 0 评论 38 点赞

我是靠谱客的博主舒心铃铛，最近开发中收集的这篇文章主要介绍oracle 12c scalable lgwr 进程提高性能_max_outstanding_log_writes，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

os: centos 7.6
db: oracle 19.3

在 oracle11g 版本及之前，oracle 日志写进程即 lgwr 为单进程，众所周知，单进程写入效率并不高.
oracle12c 版本新增特性：lgwr 多进程。即采用多个 lgwr 协同对 redo log 日志进行写入。

# ps -fu oracle |grep -i ora_lg |grep -v grep
oracle   11316     1  0 10:30 ?        00:00:14 ora_lgwr_orclp1
oracle   11320     1  0 10:30 ?        00:00:35 ora_lg00_orclp1
oracle   11324     1  0 10:30 ?        00:00:02 ora_lg01_orclp1

默认有两个 lgwr slave 进程.

_max_outstanding_log_writes

该参数是隐藏参数，默认值为 2

$ sqlplus / as sysdba

SQL> set lines 200;
set pages 200;
col inst_id format 99;
col con_id format 99;
col name for a35;
col description for a30;
col value for a10;

select
     x.inst_id,
     y.con_id,
     x.ksppinm  name,
     x.ksppdesc description,
     y.ksppstvl  value,
     y.ksppstdf  isdefault,
     decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')  ismod,
     decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE')  isadj
from sys.x$ksppi x,
     sys.x$ksppcv y
where 1=1
  and x.inst_id = y.inst_id
  and x.indx = y.indx
  and x.ksppinm in ( '_use_single_log_writer', '_max_outstanding_log_writes')
order by translate(x.ksppinm, ' _', ' '),x.inst_id,y.con_id
;

INST_ID CON_ID NAME				   DESCRIPTION			  VALUE      ISDEFAULT ISMOD				ISADJ
------- ------ ----------------------------------- ------------------------------ ---------- --------- -------------------------------- --------------------------------
      1      1 _max_outstanding_log_writes	   Maximum number of outstanding  2	     TRUE     FALSE				FALSE
						   redo log writes

      1      1 _use_single_log_writer		   Use a single process for redo  ADAPTIVE   TRUE      FALSE				FALSE
						   log writing

_use_single_log_writer 默认值为 ADAPTIVE,不需要修改.
现在修改 _max_outstanding_log_writes

SQL> alter system set "_max_outstanding_log_writes"=8 scope=spfile;

需要重启实例

$ srvctl stop database -db orclp
$ srvctl start database -db orclp

查看 lgwr 进程

# ps -fu oracle |grep -i ora_lg |grep -v grep
oracle   12114     1  0 11:11 ?        00:00:14 ora_lgwr_orclp1
oracle   12118     1  0 11:11 ?        00:00:35 ora_lg00_orclp1
oracle   12122     1  0 11:11 ?        00:00:02 ora_lg01_orclp1
oracle   12126     1  0 11:11 ?        00:00:00 ora_lg02_orclp1
oracle   12137     1  0 11:11 ?        00:00:00 ora_lg03_orclp1
oracle   12148     1  0 11:11 ?        00:00:00 ora_lg04_orclp1
oracle   12153     1  0 11:11 ?        00:00:00 ora_lg05_orclp1
oracle   12157     1  0 11:11 ?        00:00:00 ora_lg06_orclp1
oracle   12163     1  0 11:11 ?        00:00:00 ora_lg07_orclp1

已经是 8 个 lgwr slave 了.

参考:
<< ALERT: Bug 21915719 Database hang or may fail to OPEN in 12c IBM AIX or HPUX Itanium - ORA-742, DEADLOCK or ORA-600 [kcrfrgv_nextlwn_scn] ORA-600 [krr_process_read_error_2] (文档 ID 1957710.1) >>