Space may not be the final frontier, but it is one DBAs need to navigate. Oracle does make the task somewhat easier with locally managed tablespaces and one aspect of that management is the Space Management Coordinator process (SMCO}. This process, appearing in version 11.2 and all versions afterwards, does much to help manage space in tablespaces as it performs several tasks DBAs should be aware of. Let’s look at that process and what it covers.
The SMCO process coordinates the following space management tasks:
- Performs proactive space allocation and space reclamation
- Dynamically spawns secondary processes (Wnnn) space reclamation requires
- Tablespace-level space (Extent) pre-allocation.
Pre-allocation refers to datafile extension, which occurs when a space request (extent allocation) operation is triggered by inserts or loading to a segment and contiguous space is not currently available in the tablespace. A file extension
request is initiated and the file is then incremented by the next defined allocation set for the datafile.
This, of course, requires that datafiles are set to autoextend for the tablespace of interest. SMCO expands the tablespace based on the history of extension activity. Such extensions are split evenly across all datafiles in the tablespace which have not reached their maxsize setting. These extensions are limited to 10% of the full tablespace size per hourly SMCO activity. As a reminder the full tablespace size is the total space across all datafiles in the tablespace at the time the action is taken.
In addition to the above mentioned operations the SMCO process is also responsible for the following:
- Updating block and extent counts in SEG$ view 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.
Although the SMCO process starts at database startup the actual work is done via a related module. KTSJ is the space coordinator module, managing the Wnnn processes that actually perform the work. Querying V$SESSION from a typical running database we see:
SQL > select program, module from v$session
2 where module = ‘KTSJ’
3 order by 1;
PROGRAM MODULE PROGRAM MODULE ------------------------------------------------ ---------------------------------------------------------------- oracle@ip-1-2-3-4 (SMCO) KTSJ oracle@ip-1-2-3-4 (W000) KTSJ oracle@ip-1-2-3-4 (W001) KTSJ oracle@ip-1-2-3-4 (W002) KTSJ oracle@ip-1-2-3-4 (W003) KTSJ oracle@ip-1-2-3-4 (W004) KTSJ oracle@ip-1-2-3-4 (W005) KTSJ oracle@ip-1-2-3-4 (W006) KTSJ oracle@ip-1-2-3-4 (W007) KTSJ oracle@ip-1-2-3-4 (W008) KTSJ oracle@ip-1-2-3-4 (W009) KTSJ oracle@ip-1-2-3-4 (W00A) KTSJ oracle@ip-1-2-3-4 (W00B) KTSJ oracle@ip-1-2-3-4 (W00C) KTSJ oracle@ip-1-2-3-4 (W00D) KTSJ oracle@ip-1-2-3-4 (W00E) KTSJ oracle@ip-1-2-3-4 (W00F) KTSJ 17 rows selected.
16 Wnnn processes are spawned from SMCO via KTSJ, and it’s the KTSJ process that is reported in AWR and ASH reports. This can be reported in the following sections of an AWR:
Latch Miss Sources DB/Inst:BOB/BOB Snaps: 2691-2692
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
ksuosstats global area ksugetosstat 0 14 14
space background task la ktsj_grab_task 0 2,455 4,723
space background task la ktsj_detach_task 0 2,270 6
space background task la ktsj_smco_purgeexpired 0 4 0
unknown latch kghalo 0 6 3
unknown latch kghfrunp: alloc: wait 0 1 0
unknown latch kghupr1 0 1 3
------------------------------------------------------
<edited>
Enqueue Type (Request Reason)
------------------------------------------------------------------------------
Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms)
------------ ------------ ----------- ----------- ------------ --------------
CR-Reuse Block Range (block range reuse ckpt)
40 40 0 4 0 .00
JG-Job Scheduler1
189,060 189,060 0 0 0 N/A
JG-Job Scheduler1 (queue lock)
189,060 189,060 0 0 0 N/A
JG-Job Scheduler1 (q mem clnup lck)
18,004 18,004 0 0 0 N/A
CF-Controlfile Transaction
4,462 4,462 0 0 0 N/A
SJ-KTSJ Slave Task Cancel (Slave Task Cancel)
2,518 2,520 0 0 0 N/A
<edited>
The SMCO process polls for work every hour, so the sleeps are expected. It hands off the work to KTSJ, which then coordinates work between the 16 Wnnn slaves so tablespaces can have space managed efficiently. [Other databases may show more Wnnn slaves, some less, depending upon the configuration. 16 is what the database used for this example configured.] As noted earlier these management tasks include pre-allocation of extents and data cleanup of temporary extents. These ‘temporary’ extents could be due to table/index creation (each table or index extent is considered temporary until the creation task has completed). Such ‘temporary’ extents reveal themselves when querying the DBA_EXTENTS view when tables or indexes are being created. An interesting value, numeric in nature, presents itself while the object creation is in process. Once the operation successfully completes the segment_name is replaced with the actual segment name the object has been given. Should the operation fail the KTSJ/Wnnn processes perform the necessary cleanup, recovering the allocated extents for the object that no longer exists. This cleanup occurs rapidly, returning the blocks to the freelists so other sessions/processes can use them if necessary.
Notice the “grab_task” and “detach_task” latches; these, as would be expected, allow KTSJ and the Wnnn slaves to take on and complete storage management operations required for pre-allocation of extents. As this process is designed to be proactive, rather than reactive, as Oracle detects the need for additional space in a given tablespace the KTSJ/Wnnn “team” begins work on allocating the anticipated additional extents based upon the NEXT setting for the datafile or datafiles in question. Being proactive makes the space additions more fluid, reducing any wait time by creating these extents before Oracle has need for them. Remember that Oracle must allocate and format new storage extents before they are available for use; the SMCO/KTSJ/Wnnn architecture streamlines that process by allowing Oracle to allocate and format such storage before it’s actually needed. It may be that the only indication the DBA sees for autoextend activities is the presence of KTSJ entries in an AWR or ASH report.
It is possible to turn off this process, or change its behavior, by setting an initialization parameter, “_enable_space_preallocation”, to one of the following values:
- 0 to turn off the tbs pre-extension feature.
- 1 To enable tablespace extension.
- 2 To enable segment growth.
- 3 To enable both 1 and 2
- 4 To enable chunk allocation.
As it is a dynamic parameter it can be set using an alter system call and no database restart is required. To turn the process off:
ALTER SYSTEM SET “_ENABLE_SPACE_PREALLOCATION” = 0;
By setting “_enable_space_preallocation”=3 (the default value) the expected behavior is restored. It goes without saying that available space in autoextend datafiles must still be managed by the DBA, and when that space is running low additional datafiles will need to be added manually. The SMCO mechanism can do many things, but expanding tablespace storage isn’t one of them.
Tablespace space management for autoextend datafiles has been given a helping hand with the SMCO process by introducing more efficiency in the allocation mechanism. Pre-allocation of extents keeps data flowing smoothly during times of heavy insert and update activity. It’s like having a crystal ball for database storage.
Without the spirits.
Oracle Tips and Tricks -- David Fitzjarrell 