Re: Runaway SMON process on 7.2.2.3?

In article <4ckfm9$8ij_at_atlas.tus.ssi1.com>

           zitelli_at_tus.ssi1.com "Andrew Zitelli" writes:

: After heavy querying, SMON takes
: up >98% of available CPU for 5 minutes or more. This in turn has two
: noticeable results. First, it blocks other Oracle processes with an
: ST lock, until SMON has completed its work. Second, it dramatically
: slows down all system users, even those not using Oracle. Eventually
: everything completes normally, just much more slowly than expected.
: Adjustments in many initialization parameters have not yielded any
: improvement. The SMON process also becomes hyperactive if I flush the
: shared pool.
:

Just a guess, but is it possible that your fet$ and uet$ tables and the cluster they are in have become very large. Do you have a large number of tablespaces and files.

Possibly, and I repeat this is just guessing, the heavy query activity is flushing the fet$/uet$ blocks out of the SGA, and the flush shared pool affectsw the dc_cache associated with free extents with some odd side-effect that causes thrashing as the data is reloaded.

If this is anything to do with it, a data base rebuild seems to be the only possible cure.

One possible option: the SMON data query is something like:

    select free extent info where default pctfree is not zero. If you set your default pctfree to zero across the board, maybe smon could minimise its work load. If you then need to worry about coalescing space, there is an event you could kick off (or in 7.3 an official COALESCE command) to coalesce a specific tablespace.

-- 
Jonathan Lewis