Oracle 8i slow shutdown
Submitted by madvip on Wed, 02/06/2008 - 11:31.
In the past weeks I was working on databases with a slow 'shutdown immediate' process while one in particular just hung in the process. After some research I concluded that for a database that takes more than 30 minutes to 'shutdown immediate', you have to check the CPU and divide the case in two scenarios.
Scenario 1 - No CPU activity:
If there is no CPU activity from the Oracle processes, chances are that the PMON process is not cleaning any processes and probably no logs are generated in the udump directory. This might happen because of a bug in Oracle or in the kernel. In such case it is safe to 'shutdown abort', then 'startup restrict' and then 'shutdown immediate' to be on the safe side.
Scenario 2 - High CPU activity:
This is a very nasty scenario and Metalink has written an article on this matter.
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.
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
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 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.
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 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 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.
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;
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).
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
In other words, prior to shutdown a database, keep in mind the following tips:
1) Make sure the temporary tablespace is not large since SMON tries to coalesce it
2) Make sure all tablespaces have a PCTINCREASE of 0 since SMON tries to coalesce tablespaces with PCTINCREASE != 0. If necessary perform the following command:
ALTER TABLESPACE temporary DEFAULT STORAGE ( PCTINCREASE 0 );
