|
This can occur if the SMON process is having to clean up a large number
of temporary extents or to coalesce a large number of free extents in
your tablespaces.
To find the number of temporary extents, run the following query.
select tablespace_name, segment_name, segment_type, sum(bytes),
count(extent_id)
from dba_extents
where segment_type = 'TEMPORARY'
group by tablespace_name, segment_name, segment_type;
To check to see if SMON is spending time coalescing free space in your
tablespaces run the following query several times.
Select count(*) from DBA_FREE_SPACE
If the count is going down, then that will indicate that SMON is
definitely cleaning up free space.
How long has this been occurring and when does it occur? Does it occur
when trying to start the database? Did something change? If this just
started, you will need to identify if something recently changed in the
application or database. Are your tablespaces dictionary managed or
locally managed tablespaces? Is the application creating temporary work
tables and then dropping them, or are tables being truncated? If you
are using dictionary managed tablespaces, this will cause SMON to spend
extra time cleaning up the free extends from dropping or truncating
tables. To prevent this switch to locally managed tablespaces.
For more information refer to Metalink document 61997.1.
|
