【MOS】EVENT: DROP_SEGMENTS - cleanup of TEMPORARY segments (文档 ID 47400.1)

最新推荐文章于 2022-08-23 09:32:19 发布

转载最新推荐文章于 2022-08-23 09:32:19 发布 · 220 阅读

文章标签：

【MOS】EVENT: DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments (文档 ID 47400.1)

***Checked for relevance on 14-Jun-2012***

 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';

Master Note: Database System Monitor Process (SMON) (文档 ID 1495163.1)

Temporary Segment Cleanup

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.

CREATE TABLE AS SELECT
ALTER INDEX REBUILD
DROP TABLE
CREATE SNAPSHOT
CREATE PARTITION TABLE

When does SMON cleanup temporary segments?

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.

Sort segments residing in PERMANENT tablespace are cleaned up by SMON after the sort is completed.
For performance issues, extents in TEMPORARY tablespaces are not released or deallocated once the operation is complete. Instead, the extent is simply marked as available for the next sort operation. SMON cleans up the segments at startup.

SMON also might get tied up cleaning uncommitted transactions though, and be too busy to process requests to grow an existing sort segment. Starting with Oracle 8i, playing around with fast_start_parallel_rollback might workaround that.In addition, actions like CREATE INDEX create a temporary segment for the index, and only convert it to permanent once the index has been created. Also, DROP <object> converts the segment to temporary and then cleans up the temporary segment.

Temporary Segments in a Permanent Tablespace

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.

How to identify whether SMON is cleaning up temporary extents ?

Check whether there are a large number of temporary extents that might be being cleaned up by running the following query a few times

SELECT COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_TYPE='TEMPORARY';

If the count returned by the above query is dropping while SMON is working, it is likely that SMON is performing temp segment cleanup.

Effects on Database

SMON will continually acquire and then release the ST enqueue in exclusive mode. This can cause contention with other processes and lead to <oerr:ORA-1575> errors.
It should be noted that a normal/immediate shutdown will not complete until all temporary segments have been cleaned up. Shutdown will 'kick' SMON to complete cleanup.
If you are using TEMPORARY type temporary tablespaces then SMONs cleanup of the segment can be a problem as it will not service sort segment requests while performing cleanup.
If SMON is busy cleaning up a TEMP segment containing a lot of extents it cannot service 'sort segment requests' from other sessions. Pointing the users at a PERMANENT tablespace as their temporary tablespace can help keep the system running until SMON is free again.
Starting with Oracle8i, rather than reverting back to a PERMANENT tablespace if SMON is cleaning up an old sort segment at startup, you can potentially drop and recreate the tempfiles of the existing TEMPORARY tablespace. The cleanup should be faster anyway since by rule a TEMPORARY tablespace made of tempfiles need to be LOCALLY MANAGED.You can remove tempfiles from TEMPORARY tablespaces and keep the logical structure empty.

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

About Me

...............................................................................................................................

● 本文转载自MOS

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群：230161599 微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-04-28 09:00 ~ 2017-04-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。