The post dives into the basics of SMCO (Space Management Coordinator) Background Process in Oracle 11g and how to enable or disable it.
What is the function of SMCO background process
SMCO coordinates the following space management tasks. It performs proactive space allocation and space reclamation. It dynamically spawns slave processes (Wnnn) to implement the task.
Tablespace-level space (Extent) pre-allocation
Pre-allocation here refers to datafile extention. Datafile extension happens when a space request (extent allocation) operation generally triggered via Insert/loading to a segment does not find contiguous space in the tablespace, the session will extend the file by the next increment set for the datafile and will continue with the space request or Extent allocation.
For SMCO to autoextend a datafile, the AUTOEXTEND should be set to ON for the datafile. SMCO decides to expand the tablespace based on history, extension is split evenly across all datafiles in the tablespace which has not reached their maxsize and is still limited to 10% of the full tablespace size in one hourly SMCO wakeup.
(Full tablespace size = Sum of datafile sizes at any given instance of time.)
Apart from the above mentioned task, the SMCO process is also responsible for performing the following tasks.
- updating block and extents counts in SEG$ for locally managed tablespaces after adding an extent
- Securefile lob segment pre-extension.
- Securefile lob segment in-memory dispenser space pre-allocation.
- Securefile lob segment space reclamation (moving free chunks from uncommitted free space area to committed free space area).
- Temporary segment space reclamation.
The main advantage is that sessions need not wait for reactive space allocation/deallocation operations as this is proactively done, and hence a gain in performance.
How to disable and enable the SMCO background process
The feature provided by the SMCO process is called “Tablespace-level space (Extent) pre-allocation“.
It can be turned off by setting “_enable_space_preallocation=0” as below:
SQL> ALTER SYSTEM SET "_ENABLE_SPACE_PREALLOCATION" = 0;
The feature can be turned on again any time by setting “_enable_space_preallocation=3” which is the default value:
SQL> ALTER SYSTEM SET "_ENABLE_SPACE_PREALLOCATION" = 3;
The parameter is dynamic and the scope is ALTER SYSTEM.
Following is other possible settings:
- 0 to turn off the tbs pre-extension feature.
- 1 To enable tablespace extension.
- 2 To enable segment growth.
- 4 To enable chunk allocation.