Re: Redo logs - need a guru

On Mon, 22 Jul 1996 13:35:02 GMT, steve_at_aethos.demon.co.uk (Steve Holdoway) wrote:

>Dear All,
>
>Can anyone out there spread some light on how or if we can improve the
>performance of our system. We are still benchmarking Oracle 7.2.3 on
>HP-UX 10.10, using a 5 processor T520, and 14Gb of Mirrored disc ( 14
>X 2 Gb discs over 4 controllers ). Nice toys, huh!
>
>The bottleneck to the whole system seems to be the Redo logs. We have
>tried up to eight redo logs of up to 1Mb in size, scattered out of the
>way of the database. When benchmarking, the system sprints along until
>it has filled up all of these, and then waits for the first one ( or
>is it all ) to drain out before starting up. Or, in effect, the whole
>system stops for about 30 seconds whilst it untwists its knickers.
>

8 meg of log for a 14gig system is not big enough. Some would suggest as much as 10% of the database size should be allocated to logs. In your system, 100, 200, or even 300 megs would not be an unreasonable request.

>We would prefer to have a degredation in absolute performance and a
>constant throughput than this situation. Is there any way that we can
>get the redo logs draining down and doing their bit whilst the main
>application performs most of its work on the parts of the table that
>are resident in the SGA?
>

set the log_checkpoint_interval to a small value. The problem is not that the redo logs need 'draining' down, but that a checkpoint must complete before we can reuse the first log file.

>If so, how do we do it? We found that bigger redo logs stopped the
>system less often but for longer. Should we try using hundreds of
>small redo logs? Or just run the Log Writer as a realtime process?
>

Sounds like your checkpoint is taking a long time, look at:

• are you using async-io
• are you using raw disks
• do you have the ckpt process going
• if not using async-io do you have multiple dbwrs configured
• have you looked at the disks where the datafiles are during the 'pause'. Is there an obvious hot spot (it will not be where the redo logs are, they will actually be quite inactive during this time)

To verify that the checkpoint is the problem, look in your Alert$ORACLE_SID.log file in $ORACLE_HOME/rdbms/log. It will contain messages to the effect 'checkpoint not complete, cannot allocate new log' during these periods of no activity....

>Any ideas will be gratefully received, the more off the wall they are,
>the more fun we'll have in testing them!
>
>The weirdest thing is that we're getting approximately the performance
>that we expected out of this system, it just doesn't seem to be trying
>at all!
>
>TIA
>
>Steve.
>
>
>

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Tue Jul 23 1996 - 00:00:00 CEST