I don't see any reason it should be completely out of the question. Not
sure if you follow Usenet but I do think Howard has demonstrated himself to
be pretty knowledgeable as you certainly have also. My personal "clock"
time is to size my redo logs to switch no more than once per hour. Most
databases are completely capable of this. I think in general checkpoints
are costly. I have manually issued a checkpoint after large batch jobs and
it definitely had a noticeable impact on cpu/disk and db performance. I have
put some posts from Usenet from people who I think can be trusted for the
most part. There is plenty more on this there. I say don't throw out clock
time all together.
CUT & PASTE from USENET BELOW FROM VARIOUS ***
I wonder why many small log switches degrade performance? When there
are more checkpoints, let's say every 15 minutes, the disk load will be
distributed more evenly over the day in my opinion, or am I missing
Bear in mind that when you switch out of a log group, DBWR only flushes
dirty buffers covered by the log file being switched away from. Yet CKPT
has to update the headers of *all* datafiles, and all controlfiles.
Lots of small checkpoints are therefore grossly inefficient from the point
of view of CKPT.
Of course, your point has a degree of validity: the whole idea of
fast_start_io_target is to get DBWR constantly dribbling dirtied blocks back
down to disk -practically doing constant checkpointing.
But it remains true that lots of small (or constant) checkpoints will result
in even, but lower, performance. Better that, I suppose, than relatively
high performance forever interrupted by mammoth checkpoints. But better
still (in my opinion, anyway) is high performance uninterrupted by any
checkpoints (at least until you manually force one at a time when no-one
will care about the massive hiatus).
Hence, I suppose, Oracle's hint that you'd only use fast_start_io_target
when you absolutely, positively, have to bound your Instance Recovery time
for the sake of an SLA.
Connor McDonald wrote:
I prefer the best of both worlds. I have "massive" redo logs (where
"massive" means they virtually "never" switch by themselves) and then
use dbms_job to manually switch them each 'n' minutes (where 'n' is
typically 30 or 60).
Then I know exactly when redo switches will be occurring; I know exactly
how out of date my standby database will be; I can tweak the dbms_jobs
to do it less frequently over night when large stuff is going on etc etc
The smooth operation of the buffer cache will be disrupted for quite a
while after a checkpoint.
You should switch at a rate that you're happy with.
Sorry -that sounds like a bit of a non-answer, but it isn't. Log switches
are good because they induce checkpoints. Once a checkpoint has been
issued, you can pretty much guarantee that if you suddenly lost power on
your machine and therefore suffered an Instance failure, transactions raised
prior to the checkpoint will not need to be recovered. Therefore, frequent
log switches bound Instance recovery time.
On the other hand, log switches are also bad because they induce
checkpoints. Checkpoints cause DBWR to flush buffers left, right, and
centre, and also cause CKPT to have to do its thing to every data file and
control file in the database. Masses of I/O mean bad performance. So
frequent log switches cause woeful performance.
Somewhere between trivially small Instance recovery times/dreadful
performance (frequent switches)and huge Instance recovery times/good
performance (few switches) is a point of equilibrium and compromise that
you'll be able to live with. But it varies for each database, installation,
and dba. You have to find your own happy compromise.
On the other hand, lots of sites seem to think (as a sort-of rule of thumb)
that a switch every half-hour or hour or so is acceptable.
All you need then do is add log groups of a size that brings the rate of
switching down to your chosen number, and drop the logs that are of a
different size (because you can't resize existing logs, unfortunately).
Please see the official ORACLE-L FAQ: http://www.orafaq.com
Author: Post, Ethan
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).