|
|
看看也許有用
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.
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
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.
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;
If the count returned is dropping while SMON is working, it is
likely that SMON is coalescing free space.
What are the effects on the database?
o. Because SMON acquires the Space Transaction (ST) enqueue in
exclusive mode, other processes requiring the enqueue will be
blocked. This is typically manifested by multiple <oerr:ORA-1575>
errors.
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.
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 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.
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. 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.
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.
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.
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.
o. Offlining the tablespace/datafiles in which the extents reside has NO
effect.
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].
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;
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.
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 |
|
楼主: szbestway
IP卡
狗仔卡
楼主
显身卡
发表于 2001-11-23 17:38
发表于 2001-11-26 05:47
