August 17, 2007
Stoping MMON
If you have to stop MMON but don´t what to change you SGA to manual adjust, you can use this trick: execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 0); doing this you change the collecting to each 110 years
To check you can query select SNAP_INTERVAL from DBA_HIST_WR_CONTROL ;
Thanks to Ales Kavsek that gave me the direction.
Making SGA as big as possible always a good idea ?
“In general, for versions from 8i onwards, the size of the buffer cache
shouldn’t make any difference to the time it takes to determine the
blocks that need to be written as Oracle introduced the “checkpoint
queue” in 8i. When a block first becomes dirty it is appended to the
queue, so the database writer only has to walk the queue in order to
find blocks that need to be written.
One of the threats comes from consistent read copies – if you have an
excessively large buffer you may find extreme numbers of copies of the
same block building up in the cache (despite the notional limit of 6
set by the _db_block_max_cr_dba parameter). Since all the copies will
be covered by the same cache buffers chains latch, processes may start
to hold the latch for long periods of time as they search the chain
for the correct copy.
Another threat comes from dropping or truncating objects – Oracle
needs to search the buffer for CLEAN blocks from the object and, prior
to 10g (possibly 10.2), there was no really quick way to perform this
search. 10g maintains an “object queue” for each object in the cache,
and that is supposed to make this task more efficient.
In the case of the original poster – a 1GB database with 8GB of RAM -
it’s almost guaranteed that a fully cached database will be beneficial
i.e. 1GB of db_cache_size – but keeping an eye open for long waits on
the cache buffers chains latches.
Regards
Jonathan Lewis”
This post had been extracted from http://forums.oracle.com/forums/thread.jspa?threadID=543877&tstart=135
