Avoidance
- Do not create temporary tablespaces with small initial and next default storage parameters.
- Use tablespaces of type TEMPORARY. Sort segments in these tablespaces are not cleaned up. This reduces contention on ST enqueue and also reduces CPU usage by SMON **UNLESS** the database is shutdown and restarted.
- Beware of creating large objects with inappropriate (small) extents. If the creation of the object fails, SMON cleans up. Also, dropping such an object will create a lot of cleanup work for the user process.
Force Temp Segment cleanup
DROP_SEGMENTS event could be set set to force the cleanup of temporary segments.
This routine does what SMON does in the background, i.e. drops temporary segments. It is provided as a manual intervention tool which the user may invoke if SMON misses the post and does not get to clean the temp segments for another 2 hours.
level - tablespace number+1. If the value is 2147483647 then temp segments in ALL tablespaces are dropped, otherwise, only segments in a tablespace whose number is equal to the LEVEL specification are dropped.
alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1';ion are dropped.
If ts# is 5, an example of dropping the temporary segments in that tablespace would be:
alter session set events 'immediate trace name DROP_SEGMENTS level 6';
Note 177334.1 Overview of Temporary Segments Note 35513.1 Removing 'Stray' TEMPORARY Segments Note 61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing NOTE:160426.1 - TEMPORARY Tablespaces : Tempfiles or Datafiles ? Note 102339.1 Temporary Segments: What Happens When a Sort Occurs Note 1039341.6 Temporary Segments Are Not Being De-Allocated After a Sort Note 68836.1 How To Efficiently Drop (or Truncate) A Table With Many Extents Note 47400.1 EVENT: DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments Note 132913.1 How To Free Temporary Segment in Temporary Tablespace Dynamically
Free Space COALESCE
A free extent in a dictionary-managed tablespace is made up of a collection of contiguous free blocks. When new extents are allocated to a tablespace segment, the database uses the free extent which is closest in size to the required extent.When the segments are dropped,their extents are deallocated and marked a free. It is not combined with adjacent free extents to form a larger free extents.The result is fragmentation that makesallocation of larger extents more difficult.
Fragmentation is addressed such that
- When attempting to allocate a new extent for a segment, the database first tries to find a free extent large enough for the new extent.Whenever the database cannot find a free extent that is large enough for the new extent, it coalesces adjacent free extents in the tablespace and looks again.
The SMON background process periodically coalesces neighboring free extents when the PCTINCREASE value for a tablespace is not zero.
Oracle Database automatically and transparently coalesces the free space of a data block only when the following conditions are true:
- An INSERT or UPDATE statement attempts to use a block that contains sufficient free space to contain a new row piece.
- The free space is fragmented so that the row piece cannot be inserted in a contiguous section of the block.
After coalescing, the amount of free space is identical to the amount before the operation, but the space is now contiguous.
Fig 2: Datablock before and after the Coalesce
If you set PCTINCREASE=0, no coalescing of free extents occurs. If you are concerned about the overhead of ongoing coalesce operations of SMON, an alternative is to set PCTINCREASE=0, and periodically coalesce free space manually.
Manually Coalesce any adjacent free extens user this command:
ALTER TABLESPACE TABLESPACENAME COALESCE
SMON wakes itself every 5 minutes and checks for tablespaces with default pctincrease != 0.
SELECT COUNT(*) FROM DBA_FREE_SPACE;
If the count returned is dropping while SMON is working, it is likely that SMON is coalescing free space.
Effects:
- Because SMON acquires the Space Transaction (ST) enqueue in exclusive mode, other processes requiring the enqueue will be blocked.
- THE DATABASE CAN BE SHUTDOWN CLEANLY WITH UN-COALESCED EXTENTS. If SMON is performing the coalesce, a shutdown will NOT undo the work completed so far.
- Use the 'alter tablespace <tbs name> coalesce' command. This is quicker than SMON, and the work is performed in in fewer space transactions, and therefore makes fewer enqeueue acquisitions. HOWEVER, IF THE COMMAND IS INTERRUPTED, ALL ITS COALESCING WORK WILL BE LOST.
- Offlining the tablespace/datafiles containing the extents to be coalesced has NO effect.
Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
Note 61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing (Doc ID 61997.1) Note 105120.1 Advantages of Using Locally Managed vs Dictionary Managed Tablespaces
· Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition > RDBMS > Background processes
COALESCE; FAST_START_PARALLEL_ROLLBACK; SMON; SPACE TRANSACTION; TEMP SEGMENTS; TEMPORARY SEGMENT
OVERVIEW OF ORACLE DATABASE PRODUCTS > ORACLE DATABASE > ORACLE DATABASE RELEASE 9.2
ORA-1575
|