降低lms进程数量oracle,Oracle 修改进程的优先级

在上篇博客我们讲可以通过修改LGWR进程的优先级来缓解log file sync的等待事件。

Oracle log file sync 等待事件 与 COMMIT_WAIT，COMMIT_LOGGING 参数说明

https://www.cndba.cn/dave/article/3144

https://www.cndba.cn/dave/article/3145

参数是： _high_priority_processes=’VKTM|LMS|LGWR”

https://www.cndba.cn/dave/article/3145

从11g开始，LGWR进程默认就在优先级的参数中：

https://www.cndba.cn/dave/article/3145https://www.cndba.cn/dave/article/3145https://www.cndba.cn/dave/article/3145https://www.cndba.cn/dave/article/3145

[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，缺是我们修改的值：

https://www.cndba.cn/dave/article/3145https://www.cndba.cn/dave/article/3145

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]$

再修改一次：

https://www.cndba.cn/dave/article/3145

[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*。 所以如果进程已经存在的话，就没必要在进行修改了。

https://www.cndba.cn/dave/article/3145

版权声明：本文为博主原创文章，未经博主允许不得转载。