Re: Slow performance on index creation

tkyte_at_us.oracle.com (Thomas J Kyte) wrote:
>
>Anyway, if you want a 'rule of thumb' (and like all rules of thumb, your mileage
>may vary and experiences be different)...
>
>1) Lots (10's) of small log files will give you flat throughput, predicable
>response time, slower performance.
>
>2) A few (1's) of large (25-100meg) log files will give you burstier
>throughput, sometimes unpredicable repsonse times, faster performace.
>
>1 is true since
>- checkpoints will take a small amount of time since they are happening
>frequently and affect a small amout of data (flat throughput & pred. resp)
>- checkpoints are ALWAYS happening due to the small logs (slower performance)
>
>2 is true since
>- a checkpoint not complete, if hit, could take a long....... time to
>recover from since a lot of blocks may need to be flushed since checkpoints
>don't happen often (unpred. response times)
>- faster performance due to less work (eg: a block is 'hot', it gets updated
>500 times in an hour, if we checkpoint lots we write it out often. If
>we checkpoint 1 time an hour we write it out once).
>
>
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Government
>
>
>--------------------------------------------------------
>opinions and statements are mine and do not necessarily
>reflect the opinions of Oracle Corporation.
>
When we say, 'If we have larger redo logs, it will take longer to recover.' But there are two types of recovery: media recovery and instance recovery. If a background process fails to write to disk and causes an instance to die, Oracle performs instance recovery during startup, and it's automatic. During instance recovery, Oracle applies redo logs. In this case, if we have larger redo logs, it will take longer. Under worst case, Oracle may need to apply the whole redo log file if online redo logfile was nearly full. But if an instance crashed soon after we got the message, 'check point not complete', then Oracle may need to apply more than one redo logs, and go back to previous log file. So the recovery time duriong instance failure will depend on the size of redo logs.

This is not the case during media recovery. Whatever be the size of redo logs file, Oracle needs to apply the same total size of redo logs. There may be some overhead, but the size of redo generated since your last online or offline backup doesn't depend on the size of the redo log file. So size of the redo log file shouldn't change your media recovery time.

You can determine how much time a checkpoint takes by setting log_checkpoints_to_alert to true in alert file. Oracle records when checkpoint was started and when it was completed. If you want to trigger checkpoints by log switch event only, you can tune the size of the redo logs by these information. For example, if log switch happens on average every 4 minutes due to smaller redo logs, and checkpoint takes 2 minutes on average, then you might consider increasing the size of the redo logs as your system is busy doing check point and your system performance is bound to degrade due to heavy activities caused by checkpoint event. But if checkpoint takes only 10 seconds on average, then you checkpoint overhead is minimal, and you have nearly optimal redo log size. During 10 seconds that checkpoint event took, you want to watch your system performance. There might be heavy disk i/o. If your system performace degrages significantly during checkpoint, then you maynot want to increase the size of redo logs. If you have larger redo logs, it would take longer to complete, and your system would be busy longer doing checkpoint. It might be better to have a small burst of heavy activities.

So the size of the redo log file should be determined carefully based on these factors. Find out how much redo is being generated in, let's say 24 hours, determince the frequency of log switch, and redo log file size, and keep watching, and tune the size of redo logs.

Jay Mehta
DBA
Mobil Oil Received on Wed Mar 27 1996 - 00:00:00 CET