Space Management Secondary Process Wnnn highly uses the physical memory

郑全

Space Management Secondary Process Wnnn highly uses the physical memory (Doc ID 2467681.1)       

In this Document
Symptoms
Cause
Solution
References

APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 18.3.0.0.0 [Release 11.2 to 18]
Information in this document applies to any platform.
SYMPTOMS
The Space Management Secondary Process Wnnn highly uses the physical memory.


> smem -P ora_w00 -t -k -s command
PID User     Command                         Swap      USS      PSS      RSS
5949 oracle   ora_w000_CRM92CMP                  0   175.4M   175.6M   207.7M
6933 oracle   ora_w000_CRM92DEV                  0   145.4M   145.6M   177.9M
26027 oracle   ora_w000_CRM92TST                  0   159.8M   159.9M   191.7M
5954 oracle   ora_w001_CRM92CMP                  0   129.1M   129.2M   160.5M
6937 oracle   ora_w001_CRM92DEV                  0   128.6M   128.8M   159.6M
26031 oracle   ora_w001_CRM92TST                  0   192.1M   192.3M   224.6M
6394 oracle   ora_w002_CRM92CMP                  0   114.0M   114.1M   145.3M
7134 oracle   ora_w002_CRM92DEV                  0   173.9M   174.0M   205.4M
26116 oracle   ora_w002_CRM92TST                  0   235.8M   236.0M   267.7M
6687 oracle   ora_w003_CRM92CMP                  0   206.3M   206.4M   237.4M
7492 oracle   ora_w003_CRM92DEV                  0   128.3M   128.4M   160.0M
26165 oracle   ora_w003_CRM92TST                  0   128.6M   128.7M   160.3M
6806 oracle   ora_w004_CRM92CMP                  0   190.5M   190.6M   222.4M
7548 oracle   ora_w004_CRM92DEV                  0   220.6M   220.7M   251.2M
26341 oracle   ora_w004_CRM92TST                  0   159.4M   159.6M   191.3M
9840 oracle   ora_w005_CRM92CMP                  0   220.4M   220.6M   251.7M
10357 oracle   ora_w005_CRM92DEV                  0   191.3M   191.5M   223.0M
26999 oracle   ora_w005_CRM92TST                  0    81.7M    81.8M   113.5M
10339 oracle   ora_w006_CRM92CMP                  0   175.6M   175.9M   211.2M
10360 oracle   ora_w006_CRM92DEV                  0   160.3M   160.4M   191.8M
27235 oracle   ora_w006_CRM92TST                  0   238.5M   238.7M   270.5M
10341 oracle   ora_w007_CRM92CMP                  0   128.6M   128.7M   159.6M
10362 oracle   ora_w007_CRM92DEV                  0   191.0M   191.1M   223.2M
28473 oracle   ora_w007_CRM92TST                  0   144.0M   144.2M   175.9M
14680 oracle   python /dbashare/scripts/bi        0     4.2M     4.6M     6.0M
-------------------------------------------------------------------------------
25 1                                           0     3.9G     3.9G     4.7G
CAUSE
The used 4.7 GB physical memory was the total of three databases usages, not just a single one.

As per the OS command "smem", it is obvious that there are several databases running on the same server box. Each instance has multiple space secondary. The usage of a single secondary is around 270 MB which should not be considered to be high. This does not qualify to be considered as a memory leak as this space advisor feature is normally memory expensive one.


SOLUTION
First of all, the Space Management Secondary Process is designed to perform various background space management tasks, including proactive space allocation and space reclamation. These tasks could be inadequate if performed on several databases on the same server when this server box does not have considerably big physical memory.

Database Reference
Appendix (F) Background Processes

https://docs.oracle.com/en/datab ... F-AA05-BB935F57B76D


Wnnn secondary processes perform work on behalf of Space Management and on behalf of the Oracle In-Memory Option.

   - Wnnn processes are secondary processes dynamically spawned by SMCO to perform space management tasks in the background. These tasks include preallocating space into locally managed tablespace and SecureFiles segments based on space usage growth analysis, and reclaiming space from dropped segments. After being started, the secondary acts as an autonomous agent. After it finishes task execution, it automatically picks up another task from the queue. The process terminates itself after being idle for a long time.

   - Wnnn processes execute in-memory populate and in-memory repopulate tasks for population or repopulation of in-memory enabled objects. For in-memory, both the IMCO background process and foreground processes will utilize Wnnn secondary for population and repopulation. Wnnn processes are utilized by the IMCO background process for prepopulation of in memory enabled objects with priority LOW/MEDIUM/HIGH/CRITICAL, and for repopulation of in-memory objects. In-memory populate and repopulate tasks running on Wnnn secondary are also initiated from foreground processes in response to queries and DMLs that reference in-memory enabled objects.


If that is the case, then it may be good going for one of the following approaches:

  - Disable the space preallocation (default value is 3):

  $ sqlplus / as sysdba
  alter system set "_enable_space_preallocation"=0;


  - or limit the number of the space background process secondary (default value is 1024):

  $ sqlplus / as sysdba
  alter system set "_max_spacebg_secondary"=&new_value;


  - or disable the space management tasks completely (default state is enabled):

  $ sqlplus / as sysdba
  alter system set "_enable_spacebg"=false;

All the above hidden parameters can be set dynamically.

REFERENCES
https://docs.oracle.com/en/datab ... F-AA05-BB935F57B76D