CNDBA社区
在上篇博客我们讲可以通过修改LGWR进程的优先级来缓解log file sync的等待事件。
Oracle log file sync 等待事件 与 COMMIT_WAIT，COMMIT_LOGGING 参数说明
https://www.cndba.cn/dave/article/3144
参数是： _high_priority_processes=’VKTM|LMS|LGWR”
从11g开始，LGWR进程默认就在优先级的参数中：
[dave@www.cndba.cn ~]$ orz _param _high_priority_processes
Session altered.
NAME VALUE
---------------------------------------- ----------------------------------------
_high_priority_processes LMS*|LM1*|LM2*|LM3*|LM4*|LM5*|LM6*|LM7*|
LM8*|LM9*|LM*|LCK0|CKPT|DBRM|RMS0|LGWR|CR*|RS0*|RS1*|RS2*
这里是RAC 缓解，所以包含了LMS进程，这个参数是个静态参数，修改必须重启：
[dave@www.cndba.cn dbs]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Nov 16 21:48:08 2018
Version 18.3.1.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.1.0.0
SQL> alter system set "_high_priority_processes"="VKTM|LMS|LGWR" scope=both;
alter system set "_high_priority_processes"="VKTM|LMS|LGWR" scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set "_high_priority_processes"="VKTM|LMS|LGWR" scope=spfile;
System altered.
SQL> shutdown immedaite
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2768239832 bytes
Fixed Size 8899800 bytes
Variable Size 754974720 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
In-Memory Area 117440512 bytes
Database mounted.
Database opened.
SQL> !orz _param _high_priority_processes
Session altered.
NAME VALUE
---------------------------------------- ----------------------------------------
_high_priority_processes VKTM|LMS|LGWR|LM*|LCK0|CKPT|DBRM|RMS0|LGWR|CR*|RS0*|RS1*|RS2*
但注意这里修改之后的值，还是加了原来默认的选项，如果我们查看pfile，缺是我们修改的值：
SQL> create pfile from spfile;
File created.
[dave@www.cndba.cn dbs]$ cat initcndba1.ora |grep _high_priority_processes
*._high_priority_processes='VKTM|LMS|LGWR'
[dave@www.cndba.cn dbs]$
再修改一次：
[dave@www.cndba.cn dbs]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Nov 16 21:48:08 2018
Version 18.3.1.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.1.0.0
SQL> alter system set "_high_priority_processes"="LWGR" scope=spfile;
System altered.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2768239832 bytes
Fixed Size 8899800 bytes
Variable Size 754974720 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
In-Memory Area 117440512 bytes
Database mounted.
Database opened.
[dave@www.cndba.cn dbs]$ orz _param _high_priority_processes
Session altered.
NAME VALUE
---------------------------------------- ----------------------------------------
_high_priority_processes LWGR|LM*|LCK0|CKPT|DBRM|RMS0|LGWR|CR*|RS0*|RS1*|RS2*
经过对比，我们发现了规律，就是_high_priority_processes是用户指定的进程，实际上还会加上默认的参数值，就是这里的LM|LCK0|CKPT|DBRM|RMS0|LGWR|CR|RS0|RS1|RS2*。 所以如果进程已经存在的话，就没必要在进行修改了。
版权声明：本文为博主原创文章，未经博主允许不得转载。
