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.
- Ethan *** 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 > something? 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. Regards HJR **************************************************************************** ********** 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 > etc > > hth > connor > **************************************************************************** ********** The smooth operation of the buffer cache will be disrupted for quite a while after a checkpoint. Cheers Nuno Souto [EMAIL PROTECTED] **************************************************************************** ********** 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). Regards HJR -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
