重庆思庄Oracle、Redhat认证学习论坛

标题: 12C开始oracle实现了SCALABLE LGWR多进程并行写redo log [打印本页]

作者: jiawang 时间: 2020-12-25 18:34
标题: 12C开始oracle实现了SCALABLE LGWR多进程并行写redo log
在oracle 12.1版本之前,oracle日志写进程LGWR为单进程,oracle 12.1开始实现了SCALABLE LGWR多进程，采用多个LGWR协同对redo log日志进行并行写入,这是Oracle数据库内部最重要的改进，解决了最后的并发瓶颈点

SCALABLE LGWR主要受到隐藏参数_use_single_log_writer和_max_outstanding_log_writes的控制

_use_single_log_writer参数主要有三个可选值 true,false,adaptive,默认值为adaptive
false,adaptive:如果CPU个数大于一个则会有多个lg0n进程
true:不生成多个lg0n进程，如同12.1之前那样仅有单个LGWR

_max_outstanding_log_writes参数默认值为2

[oracle@12C ~]$ ps -ef |grep ora_lg |grep -v grep
oracle    6305    1  0 14:53 ?       00:00:00 ora_lgwr_OCP
oracle    6311    1  0 14:53 ?       00:00:00 ora_lg00_OCP
oracle    6315    1  0 14:53 ?       00:00:00 ora_lg01_OCP
SQL> select VERSION from v$instance;
VERSION
-----------------
12.1.0.2.0
SQL>select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a,
x$ksppcv b where a.indx = b.indx and a.ksppinm in
('_max_outstanding_log_writes','_use_single_log_writer');
NAME                         VALUE    DESCRIPTION
------------------------------ ---------- --------------------------------------------------
_use_single_log_writer       ADAPTIVE Use a single process for redo log writing
_max_outstanding_log_writes 2       Maximum number of outstanding redo log writes

修改_max_outstanding_log_writes=3

SQL> alter system set "_max_outstanding_log_writes"=3 scope=spfile;
System altered.
SQL> startup force
SQL>select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a,
x$ksppcv b where a.indx = b.indx and a.ksppinm in
('_max_outstanding_log_writes','_use_single_log_writer');
NAME                         VALUE    DESCRIPTION
------------------------------ ---------- --------------------------------------------------
_use_single_log_writer       ADAPTIVE Use a single process for redo log writing
_max_outstanding_log_writes 3       Maximum number of outstanding redo log writes
[oracle@12C ~]$ ps -ef|grep lg|grep -v grep
oracle    8589    1  0 15:13 ?       00:00:00 ora_lgwr_OCP
oracle    8593    1  0 15:13 ?       00:00:00 ora_lg00_OCP
oracle    8597    1  0 15:13 ?       00:00:00 ora_lg01_OCP
oracle    8601    1  0 15:13 ?       00:00:00 ora_lg02_OCP

修改_use_single_log_writer='true'

SQL>  alter system set "_use_single_log_writer"='true' scope=spfile;
System altered.
SQL> startup force
SQL>select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a,
x$ksppcv b where a.indx = b.indx and a.ksppinm in
('_max_outstanding_log_writes','_use_single_log_writer');
NAME                         VALUE    DESCRIPTION
------------------------------ ---------- --------------------------------------------------
_use_single_log_writer       true    Use a single process for redo log writing
_max_outstanding_log_writes 1       Maximum number of outstanding redo log writes

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)