【MOS】EVENT: DROP_SEGMENTS - cleanup of TEMPORARY segments (文档 ID 47400.1)
The DROP_SEGMENTS event ~~~~~~~~~~~~~~~~~~~~~~~ Available from 8.0 onwards. DESCRIPTION Finds all the temporary segments in a tablespace which are not currently locked and drops them. For the purpose of this event a "temp" segment is defined as a segment (seg$ entry) with TYPE#=3. Sort space in a TEMPORARY tablespace does not qualify under this definition as such space is managed independently of SEG$ entries. PARAMETERS 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. NOTES 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. We do not know whether missed post is a real possibility or more theoretical situation, so we provide this event as an insurance against SMON misbehaviour. Under normal operation there is no need to use this event. It may be a good idea to alter tablespace <tablespace> coalesce; after dropping lots of extents to tidy things up. *SQL Session (if you can connect to the database): alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1'; The TS# can be obtained from v$tablespace view: select ts# from v$tablespace where name = '<Tablespace name>'; Or from SYS.TS$: select ts# from sys.ts$ where name = '<Tablespace name>' and online$ != 3; 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';
Oracle Database often requires temporary workspace for intermediate stages of SQL statement execution. Typical operations that may require a temporary segment include sorting, hashing, and merging bitmaps. While creating an index, Oracle Database also places index segments into temporary segments and then converts them into permanent segments when the index is complete.
Oracle Database does not create a temporary segment if an operation can be performed in memory. However, if memory use is not possible, then the database automatically allocates a temporary segment on disk.
Temporary segments will also be created for the following operations a well.
During normal operations, user processes that create temporary segments are responsible for cleanup. If the user process dies before cleaning them up, or the user process receives an error causing the statement to fail, SMON is posted to do the cleanup.
The background process System Monitor (SMON) frees the temporary segments when the statement has been completed.If a large number of sort segments has been created, then SMON may take some time to drop them; this process automatically implies a loss of overall database performance.After SMON has freed up the temporary segment, the space is released for use by other objects.Temporary Segments in a Temporary Tablespace
The background process SMON actually de-allocates the sort segment after the instance has been started and the database has been opened. Thus, after the database has been opened, SMON may be seen to consume large amounts of CPU as it first de-allocates the (extents from the) temporary segment, and after that performs free space coalescing of the free extents created by the temporary segment cleanup. This behavior will be exaggerated if the temporary tablespace, in which the sort segment resides, has inappropriate (small) default NEXT storage parameters.