首页 » ORACLE 9i-23ai » Troubleshooting Oracle 19c ora_wNNN process memory usage high

Troubleshooting Oracle 19c ora_wNNN process memory usage high

  • 2024/03/01
  • ORACLE 9i-23ai
  • 202 views
  • Troubleshooting Oracle 19c ora_wNNN process memory usage high已关闭评论

近日一银行客户巡检发现一内存使用率高的oracle 19c(19.4) RAC数据库环境，因为是linuxone拆分的多机器，内存分配不是很充足，在当前动辄1T内存可能并不易发现，分析内存使用主要是因为Oracle的多个后台进程ora_wNNN_xxx进程，每个进程PGA使用近600MB。WNNN后台进程是SMCO(Space Management Coordinator Process)的slave进程，从oracle 11g引入的智能空间预分配space preallocation的新特性，在oracle 12c后SMCO也负责生命周期管理ILM(Information Lifecycle Management).由于SMCO 或W00n在完成space preallocation过程中遇到的问题时，可以考虑禁用该特性.

现象：


SMCO空间预分配space preallocation的特性涉及3个方面：

    • 1. 表空间的预分配和扩展
    • 2. 数据段segment的预分配和扩展
    • 3. LOB chunk的预分配和扩展

对应的数据库参数是 _enable_space_preallocation

Parameter Name: _enable_space_preallocation
Parameter Type: integer
Allowable Values:
0x00: disable all levels of space preallocation
0x01: enable tablespace extension ahead of time
0x02: enable segment growth ahead of time
0x04: enable Lob chunk allocation ahead of time

Description: This parameter allows the user to enable different levels of space preallocation.
Dynamic: The parameter is dynamic and the scope is ALTER SYSTEM.

19c Default: 3

SQL> @pd space_pre
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX    NAME                                                     VALUE                          DESCRIPTION
---------- -------- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
      2720   AA0    _enable_space_preallocation                              3                              enable space pre-allocation
      2739   AB3    _kttext_warning                                          5                              tablespace pre-extension warning threshold in percentage

预分配和扩展任务主要由SMCO(Space Management Coordinator Process)和其小工进程W00n(slave )一起完成。其中SMCO充当调度进程以便管理任务队列和slave进程池，基于任务需要来动态分配新的slave(W00n)进程，并监控slave进程。W00n等一组Slave Process，Slave进程总是实际干活的人。Salve process由SMCO这个调度器动态分配。一旦启动后，slave 进程将自主工作，最大个数受_max_spacebg_slaves参数限制，默认为1024. 都不是致命进程，可以KILL并不会影响实例可用性。

SQL> @pd spacebg
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX    NAME                                                     VALUE                          DESCRIPTION
---------- -------- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
      2752   AC0    _enable_spacebg                                          TRUE                           enable space management background task
      2753   AC1    _max_spacebg_slaves                                      1024                           maximum space management background slaves
      2754   AC2    _minmax_spacebg_slaves                                   8                              min-max space management background slaves
      2755   AC3    _min_spacebg_slaves                                      2                              minimum space management background slaves
      2756   AC4    _max_spacebg_tasks                                       8192                           maximum space management background tasks
      2757   AC5    _max_spacebg_msgs_percentage                             50                             maximum space management interrupt message throttling
      2768   AD0    _spacebg_sync_segblocks                                  TRUE                           to enable/disable segmon driver

7 rows selected.

已知bug
Bug 30098251 – WNNN PROCCESSES CREATE AN EXCESSIVE NUMBER OF OPEN CURSORS investigated the issue where Wnnn process has high number of open cursors and consuming more memory.

修复安装One-off patch,或升级
19.6.0.0.200114 (Jan 2020) Database Release Update (DB RU)
18.9.0.0.200114 (Jan 2020) Database Release Update (DB RU)
12.2.0.1.200114 (Jan 2020) Database Release Update (DB RU)
12.1.0.2.200114 (Jan 2020) Database Proactive Bundle Patch
12.2.0.1.200114 (Jan 2020) Bundle Patch for Windows Platforms

或临时禁用WNNN进程。

禁用方法
可在线禁用/启用，重启进程， 释放内存.

Disable the space management tasks completely (default state is enabled):
  $ sqlplus / as sysdba
SQL> alter system set "_enable_spacebg"=false;
System altered.
-- or --
Disable the space preallocation (default value is 3):
SQL> alter system set "_enable_space_preallocation"=0;
System altered.


Alert 日志输出.
2024-03-01 10:30:03.951000 +08:00
Starting background process SMCO
SMCO started with pid=43, OS id=15502
ALTER SYSTEM SET _enable_spacebg=TRUE SCOPE=BOTH;
2024-03-01 10:48:59.967000 +08:00
Stopping background process SMCO
2024-03-01 10:49:02.049000 +08:00
ALTER SYSTEM SET _enable_spacebg=FALSE SCOPE=BOTH;

— over —

打赏

, ,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：