Re: Redo Log File Sizes

On Jan 23, 7:56 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> Oracle 10.2.0.3.0,
> Windows 2003 Server
> 8GB Memory
> 8 CPU
> Shared Server
>
> When we have two or more processing performing updates, deletes and/or
> inserts at the same time our system slows to a crawl.
>
> I was under the impression that Oracle suggests that Redo Log Files be sized
> so they switch only every 20 minutes or so.  Is the frequency of the log
> file switching in the excerpt below normal?
>
> Thread 1 advanced to log sequence 168511
> Tue Jan 22 12:00:06 2008
> Thread 1 advanced to log sequence 168518
>   Current log# 2 seq# 168518 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG2.ORA
> Tue Jan 22 12:01:52 2008
> Thread 1 advanced to log sequence 168519
>   Current log# 1 seq# 168519 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG1.ORA
> Tue Jan 22 12:03:57 2008
> Thread 1 advanced to log sequence 168520
>   Current log# 3 seq# 168520 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG3.ORA
> Tue Jan 22 12:05:14 2008
> Thread 1 advanced to log sequence 168521
>   Current log# 4 seq# 168521 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG4.ORA
> Tue Jan 22 12:06:37 2008
> Thread 1 advanced to log sequence 168522
>   Current log# 2 seq# 168522 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG2.ORA
> Tue Jan 22 12:07:57 2008
> Thread 1 advanced to log sequence 168523
>   Current log# 1 seq# 168523 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG1.ORA
> Tue Jan 22 12:09:27 2008
> Thread 1 advanced to log sequence 168524
>   Current log# 3 seq# 168524 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG3.ORA
> Tue Jan 22 12:15:36 2008
> Thread 1 advanced to log sequence 168525
>   Current log# 4 seq# 168525 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG4.ORA

> Tue Jan 22 13:57:03 2008
> Thread 1 advanced to log sequence 168528
>   Current log# 3 seq# 168528 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG3.ORA
> Tue Jan 22 14:00:33 2008
> Thread 1 advanced to log sequence 168529
>   Current log# 4 seq# 168529 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG4.ORA
> Tue Jan 22 14:02:38 2008
> Thread 1 advanced to log sequence 168530
>   Current log# 2 seq# 168530 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG2.ORA

> Tue Jan 22 14:50:26 2008
> Thread 1 advanced to log sequence 168535
>   Current log# 1 seq# 168535 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG1.ORA
> Tue Jan 22 14:51:47 2008
> Thread 1 advanced to log sequence 168536
>   Current log# 3 seq# 168536 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG3.ORA
> Tue Jan 22 14:53:14 2008
> Thread 1 advanced to log sequence 168537
>   Current log# 4 seq# 168537 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG4.ORA
> Tue Jan 22 14:54:16 2008
> Thread 1 advanced to log sequence 168538
>   Current log# 2 seq# 168538 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG2.ORA
> Tue Jan 22 14:55:43 2008
> Thread 1 advanced to log sequence 168539
>   Current log# 1 seq# 168539 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG1.ORA
> Tue Jan 22 14:57:03 2008
> Thread 1 advanced to log sequence 168540
>   Current log# 3 seq# 168540 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG3.ORA
> Tue Jan 22 14:58:23 2008
> Thread 1 advanced to log sequence 168541
>   Current log# 4 seq# 168541 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG4.ORA
> Tue Jan 22 14:59:31 2008
> Thread 1 advanced to log sequence 168542
>   Current log# 2 seq# 168542 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG2.ORA
> Tue Jan 22 15:04:44 2008
> Thread 1 advanced to log sequence 168543
>   Current log# 1 seq# 168543 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG1.ORA
> Tue Jan 22 15:06:05 2008
> Thread 1 advanced to log sequence 168544
>   Current log# 3 seq# 168544 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG3.ORA
> Tue Jan 22 15:07:30 2008
> Thread 1 advanced to log sequence 168545
>   Current log# 4 seq# 168545 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG4.ORA
> Tue Jan 22 15:08:42 2008
> Thread 1 advanced to log sequence 168546
>   Current log# 2 seq# 168546 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG2.ORA
> Tue Jan 22 15:10:01 2008
> Thread 1 advanced to log sequence 168547
>   Current log# 1 seq# 168547 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG1.ORA
> Tue Jan 22 15:11:21 2008
> Thread 1 advanced to log sequence 168548
>   Current log# 3 seq# 168548 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG3.ORA
> Tue Jan 22 15:13:46 2008
> Thread 1 advanced to log sequence 168549
>   Current log# 4 seq# 168549 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG4.ORA
> Tue Jan 22 15:17:08 2008
> Thread 1 advanced to log sequence 168550
>   Current log# 2 seq# 168550 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG2.ORA
> Tue Jan 22 15:22:31 2008
> Thread 1 advanced to log sequence 168551
>   Current log# 1 seq# 168551 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG1.ORA
> Tue Jan 22 15:25:26 2008
> Thread 1 advanced to log sequence 168552
>   Current log# 3 seq# 168552 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG3.ORA

> Tue Jan 22 16:25:31 2008
> Thread 1 advanced to log sequence 168556
>   Current log# 3 seq# 168556 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG3.ORA
> Tue Jan 22 16:27:17 2008
> Thread 1 advanced to log sequence 168557
>   Current log# 4 seq# 168557 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG4.ORA
> Tue Jan 22 16:29:18 2008
> Thread 1 advanced to log sequence 168558
>   Current log# 2 seq# 168558 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG2.ORA
> Tue Jan 22 16:31:18 2008
> Thread 1 advanced to log sequence 168559
>   Current log# 1 seq# 168559 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG1.ORA
> Tue Jan 22 16:33:20 2008
> Thread 1 advanced to log sequence 168560
>   Current log# 3 seq# 168560 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG3.ORA
> Tue Jan 22 16:35:27 2008
> Thread 1 advanced to log sequence 168561
>   Current log# 4 seq# 168561 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG4.ORA
> Tue Jan 22 16:37:12 2008
> Thread 1 advanced to log sequence 168562
>   Current log# 2 seq# 168562 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG2.ORA
> Tue Jan 22 16:38:35 2008
> Thread 1 advanced to log sequence 168563
>   Current log# 1 seq# 168563 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG1.ORA
> Tue Jan 22 16:39:57 2008
> Thread 1 advanced to log sequence 168564
>   Current log# 3 seq# 168564 mem# 0: D:\ORACLE\ORADATA\TIERMED\REDO_LOG3.ORA

It seems like I recall reading a couple years ago in "Oracle Performance Tuning 101" by Gaja Krishna Vaidyanatha that redo log files should switch roughly every 20 minutes under normal processing loads. Your post above indicates that there are periods in the day when your log files are switching once every 1 to 2 minutes.

Additional references:
Paraphrased from "Oracle Database Performance Tuning Guide for 10g R2":
"Generally, larger redo log files provide better performance. Undersizing log files increases checkpoint activity. Redo log files in the range of a hundred megabytes to a few gigabytes are considered reasonable. Switch log files at most every 20 minutes."

Paraphrased from "Expert Oracle Database 10g Administration": "Redo log files should switch roughly every 20 minutes during a busy work load and once an hour during normal work loads."

http://jonathanlewis.wordpress.com/2007/04/12/log-file-switch/ "If the intervals are too short (which could cause excessive activity from the database writer(s) as the log file checkpoint occurs) you can add new, larger, log files and drop the old ones."

http://jonathanlewis.wordpress.com/2007/10/23/ "log file switch (checkpoint not complete) happens to be a wait time that can (usually) be eliminated very easily - having more, or bigger, log files is often all that it takes to avoid the checkpoint completion issue."

How large are your redo log files, and how many do you have? Take a close look at the two links above to help determine if the redo log files need to be resized.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Jan 23 2008 - 21:19:50 CST