Oracle Database - Enterprise Edition - Version 7.3.0.0 and later
Information in this document applies to any platform.
Purpose
SMON - Temporary Segment Cleanup and Free Space Coalescing in Oracle 7.3 and Higher
PURPOSE
~~~~~~~
Since the introduction of the unlimited extents feature in Oracle 7.3, it is
possible for SMON to have to either clean up a large number of temporary
extents, or to coalesce a large number of free extents. This can manifest
itself by SMON appearing to spin, consuming a high percentage of CPU for
long periods. This article explains what is happening, and what (if
anything) can be done.
The discussion concentrates mainly on non-TEMPORARY type tablespaces. There
is however a section at the end of the article which discusses possible
issues with tablespaces of type TEMPORARY.
NOTE:
=====
Coalescing free space is not necessary for locally managed tablespaces because bitmaps automatically track adjacent free space.
Please refer to:
Oracle9i Database Administrator's Guide
Release 2 (9.2)
Part Number A96521-01
Chapter 11 - Managing Tablespaces
Questions and Answers
SCOPE & APPLICATION
~~~~~~~~~~~~~~~~~~~
This article is intended to assist DBAs encountering SMON appearing to spin
and consume high percentages of CPU by providing an understanding of the
issues.
RELATED DOCUMENTS
~~~~~~~~~~~~~~~~~
Note:35513.1 Removing `stray` TEMPORARY Segments
Note:50592.1 Extent Sizes for Sort, Direct Load and Parallel Operations
Note:65973.1 Temporary Tablespaces and the Sort Extent Pool
Note:68836.1 How to efficiently drop a table with many extents
Note:47400.1 EVENT: DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments
| Test environment ~~~~~~~~~~~~~~~~ All tests were performed on 8.0.4.1 on Sun Solaris 2.5. A large disk-based sort created a temporary segment. Setting small initial and next default temporary tablespace parameters, along with maxextents unlimited and pctincrease 0, ensured that a large number of extents were allocated to the sort segment.[This section is not visible to customers.] |
What to look for
~~~~~~~~~~~~~~~~
The most common indicator is the SMON process consuming large amounts
of CPU for a long period. UNIX O/S utilities sar or vmstat will show how busy
CPU(s) are; ps will show which process is using the CPU.
What is SMON doing
~~~~~~~~~~~~~~~~~~
Once you have identified that SMON is using lots of CPU, you need to
identify whether it is performing temporary segment (extent) cleanup, or
free space coalescing.
Free space coalescing
~~~~~~~~~~~~~~~~~~~~~
When does SMON coalesce?
o. SMON wakes itself every 5 minutes and checks for tablespaces with
default pctincrease != 0.
| o. ktmmon() has a default timeout of five minutes. Each timeout, it makes multiple calls to ktsclsb()(kts coalesce space in background) scanning across all tablespaces, until there are no extents which need coalesced. When called from ktmmon(), ktsclsb() coalesces 5 extents at a time, relinquishing the ST enqueue each time, allowing other processes a window to perform space management. [This section is not visible to customers.] |
How to identify whether SMON is coalescing
o. Check whether there are a large number of free extents that might
be being coalesced by running the following query a few times:
SELECT COUNT(*) FROM DBA_FREE_SPACE;---local manage之前需要回收才可以，之后直接修改bitmap表就可以
If the count returned is dropping while SMON is working, it is
likely that SMON is coalescing free space.
| o. Dump an errorstack from the SMON process (using oradebug). This will show the following routines: ksbrdp()->ktmmon()->ktsclsb()[This section is not visible to customers.] |
What are the effects on the database?
o. Because SMON acquires the Space Transaction (ST) enqueue （not TS ！！！）in
exclusive mode, other processes requiring the enqueue will be
blocked. This is typically manifested by multiple <oerr:ORA-1575>
errors.
| Multiple systemstates may also highlight this.[This section is not visible to customers.] |
o. SMON sits in a very tight loop while coalescing, and consumes close
to 100% CPU. If the system is CPU-bound, the run queue will increase
as other processes try to get onto CPU.
Can anything be done to stop SMON grabbing CPU?
o. If there is no CPU contention, and no processes being blocked because
of failure to acquire the ST enqueue, DO NOT DO ANYTHING. Leave SMON
to complete the coalescing.
| o. Event:10269 (I used level 10) can be set to stop SMON from coalescing. It can be set either in the init.ora or by posting SMON using oradebug. However, the event must be set before SMON wakes up and starts to coalesce; there is no checking for 10269 in the loop in which SMON sits. If SMON has started to coalesce, and you wish to disable it, you will have to set 10269 in the init.ora and restart the database. I was unable to turn off 10269 using oradebug, and had to restart the database.[This section is not visible to customers.] |
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.
o. 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. （全部回滚）
o. It is possible to force a user session to coalesce free extents. See
Note:35513.1 "Removing `stray` TEMPORARY Segments" for details. Again,
quicker than SMON. HOWEVER, IF THIS OPERATION IS INTERRUPTED, ALL IT'S
COALESCING WORK WILL BE LOST.
o. Offlining the tablespace/datafiles containing the extents to be
coalesced has NO effect.
| Can the rate of coalescing be monitored? o. It is possible to approximate the rate at which SMON is coalescing. This involves tracing SMON with event:10046 (level 4) and setting parameter:timed_statistics=true. The resultant raw trace file will show the SQL being executed by SMON. The binds, along with the timing information, will allow you to calculate the coalescing rate.[This section is not visible to customers.] |
Temporary segment cleanup
~~~~~~~~~~~~~~~~~~~~~~~~~
When does SMON cleanup temporary segments?
o. Typically a user process allocates a temporary segment (multiple
extents) and then dies before cleaning them up, or the user process
receives an error causing the statement to fail. SMON is posted to do
the cleanup. 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.
| SMON cleans up 8 temporary extents per space transaction.[This section is not visible to customers.] |
o. During normal operations, user processes that create temporary segments
are responsible for cleanup.
How to identify whether SMON is cleaning up temporary extents
o. 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.
See section 'Tablespaces of type TEMPORARY' for more details on
this.
| o. Dump an errorstack from the SMON process (using oradebug). This will show the following routines: ksbrdp()->ktmmon()->ktssdt_segs()-> ktssdro_segment()[This section is not visible to customers.] |
What are the effects on the database?
o. Again, 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.
o. CPU utilization is not exceptionally high. During tests, SMON
consumed between 10% and 20% CPU during cleanup, and so this operation
has less impact than coalescing, as far as SMON is concerned.
Furthermore, SMON performed the cleanup in 'chunks', cleaning up a
subset of the extents at a time.
Can anything be done to stop SMON grabbing CPU?
o. Not a great deal. As with coalescing, if there is no CPU contention,
and no processes being blocked because of failure to acquire the ST
enqueue, DO NOT DO ANYTHING. However because SMON does not work as hard
cleaning up temporary extents, it should not be a big issue.
Note: 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.
See below (TEMPORARY tablespaces) for more information.
| o. If you really want to stop cleanup, you can set event:10061 (I used level 10). This disables SMON from cleaning up temporary extents. This will leave used (unavailable) space in the tablespace in which the extents reside. You can then point users to another temporary tablespace if necessary. The database can then be restarted off-peak without 10061 and SMON will clean up the temporary extents.[This section is not visible to customers.] |
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.----abort才能阻止，这就是解释了大量insert cancel后停库耗费很长时间
| This is true whether 10061 has been set or not. A shutdown abort will succeed, but as soon as the database has been started again, SMON will carry on where it left off. o. Drop the tablespace in which the extents reside. This can be done after disabling SMON with 10061. The user process performing the drop will do the cleanup. However, this appears to be no quicker than SMON.[This section is not visible to customers.] |
o. Offlining the tablespace/datafiles in which the extents reside has NO
effect.
o. DROP_SEGMENTS event could be set set to force the cleanup of
temporary segments, see Note:47400.1 "EVENT: DROP_SEGMENTS - Forcing
cleanup of TEMPORARY segments".
Avoidance
~~~~~~~~~
With a little forethought and care, the above situations can be avoided:
o. Do not create temporary tablespaces with small initial and next default
storage parameters. Also beware of unlimited maxextents on temporary
tablespaces.
Note, TEMPORARY type tablespaces set maxextents unlimited automatically.
Furthermore, the NEXT AND INITIAL extent sizes are determined from
the default NEXT size (default INITIAL is ignored). For more details on
temporary extent sizes, see Note:50592.1 "Extent Sizes for Sort, Direct
Load and Parallel Operations (PCTAS & PDML)".
o. Use tablespaces of type TEMPORARY. Sort segments in these tablespaces
are not cleaned up. This reduces contention on the ST enqueue and also
reduces CPU usage by SMON **UNLESS** the database is shutdown and
restarted. If TEMPORARY type tablespaces are in use then SMON will
clean up its segments after startup following a shutdown. In this case
large numbers of extents can be a severe problem as SMON will not
service user "sort segment requests" until the cleanup is complete.
If the cleanup is to take a long time users will not be able to perform
sort operations.
In this scenario you can point users at a PERMANENT temporary tablespace
while SMON cleans up the TEMPORARY temporary tablespace. This is likely to
cause ST enqueue contention but will allow users sessions to sort on disk
when necessary rather then them just blocking.
Eg:
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:
CREATE TABLESPACE NEWTEMP .... (your own specification here)
(DO NOT CREATE IT AS TYPE TEMPORARY)
Move the users over to this:
select username from dba_users
where temporary_tablespace='TEMP';
For each user in this list:
alter user XXXXX temporary tablespace NEWTEMP;---后期不可以用非temp 表空间存放吧
Once SMON has cleaned up the extents reset the storage clause
on each tablespace to sensible values and you can then point
users back at a TEMPORARY temp tablespace.
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.
o. 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.
Oracle8 ONLY. Make use of the tablespace MINIMUM EXTENT size to help
minimise the risk of mistakes in scripts causing small extent sizes.
This parameter ensures that every used and/or free extent size in a
tablespace is at least as large as, and is a multiple of, this value.
Oracle8i ONLY: It is worth considering the use of a locally managed
temporary tablespace. This has the benefit of faster temporary segment
cleanup after the instance has been aborted.
Note, locally managed temporary tablespaces must be created using
tempfile(s). Any attempt to create a locally managed temporary
tablespace using a datafile will result in the error:
ORA-25144: invalid option for CREATE TABLESPACE with TEMPORARY contents
Tablespaces of type TEMPORARY
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TEMPORARY-type tablespaces were introduced in Oracle 7.3 (see Note:65973.1
"Temporary Tablespace, the Sort Extent Pool, and OPS"). In summary:
o. The first disk sort (after instance startup) creates a sort segment in
the TEMPORARY tablespace.
o. Free extents in the sort segment are re-used as required by sessions.
o. The sort segment grows to a steady-state.
o. Sort extents are not de-allocated whilst the instance is running.
o. Permanent objects cannot be created in TEMPORARY tablespaces.
o. There is a maximum of one sort segment per TEMPORARY tablespace.
Thus, contention on the ST enqueue is reduced as user sessions are allocating
and de-allocating fewer extents. Even if a user session dies, SMON will not
de-allocate extents that the session was using for sorting.
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 then when it is
requested to perform free space coalescing of the free extents created by
the temporary segment cleanup. Again, this behaviour will be exaggerated if
the TEMPORARY tablespace in which the sort segment resides has inappropriate
(small) default NEXT storage parameters (see 'Avoidance' above).
| IMPORTANT. If you perform a shutdown before allowing SMON to clean up all temporary extents in a temporary tablespace, then after the subsequent startup SMON may appear to spin without cleaning up any further extents. The reason for this is that SMON is effectively retracing work it had previously completed during cleanup of this segment. It has to re-scan UET$ for all previously 'scanned' extents before it can continue again with the process of 'exchanging' extents in UET$ and FET$. Whilst it is scanning UET$ (again), there is no reduction in the number of extents until it reaches the point at which it previously left off (the shutdown). At this point the number of temporary extents will start to drop again. Because of the CPU activity and no apparent work being performed, it appears that SMON is spinning. If you think that SMON may be doing this, put a 10046 level 4 trace on it's process. The resulting output will show the following cursor being executed over and over: select file#,block#,length from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 and ext#=:4 There will be no other activity in the trace file (no other cursors being executed), and you should see the value for the bind variable :4 (associated with ext#) decrementing with every execution. It is advisable to let SMON complete cleanup in one go. Repeatedly bouncing the instance may cause the above problem, and the total time to clean up will be increased.[This section is not visible to customers.] |
Permanent object cleanup
~~~~~~~~~~~~~~~~~~~~~~~~
If a permanent object is made up of many extents, and the object is to be
dropped, the user process dropping the object will consume large amounts
of CPU in the same way as SMON does when cleaning up a temporary segment.
Please see Note:68836.1 "How To Efficiently Drop A Table With Many Extents"
for a discussion of this.