Re: log file sync , log file parallel write

db_man wrote:
> Hi ,
>
> my top wait list shows 2 events , log file sync and log file parallel
> write in 1 hour.
>
> During this time , the db server created 370,594 bytes redo per second.
>
> the docs say that high value of both log file sync and log file
> parallel write indicates io problem .
> How can we decide whether below numbers are close to each other or not?
>
>
> If it is not possible to change the application and eliminate the
> unnecessary commits (such as sap) , how can lg file sync be decreased?
>
> Kind Regards
>
>
> Load Profile
> ~~~~~~~~~~~~ Per Second Per
> Transaction
> ---------------
> ---------------
> Redo size: 370,594.95
> 2,966.21
> Logical reads: 39,925.48
> 319.56
> Block changes: 2,447.17
> 19.59
> Physical reads: 67.83
> 0.54
> Physical writes: 433.47
> 3.47
> User calls: 880.52
> 7.05
> Parses: 275.49
> 2.21
> Hard parses: 1.92
> 0.02
> Sorts: 553.98
> 4.43
> Logons: 0.29
> 0.00
> Executes: 5,303.67
> 42.45
> Transactions: 124.94
>
> % Blocks changed per Read: 6.13 Recursive Call %: 89.20
> Rollback per transaction %: 0.68 Rows per Sort: 11.52
>
> Instance Efficiency Percentages (Target 100%)
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Buffer Nowait %: 99.99 Redo NoWait %: 99.95
> Buffer Hit %: 99.83 In-memory Sort %: 100.00
> Library Hit %: 100.25 Soft Parse %: 99.30
> Execute to Parse %: 94.81 Latch Hit %: 99.03
> Parse CPU to Parse Elapsd %: 15.58 % Non-Parse CPU: 98.01
>
> Shared Pool Statistics Begin End
> ------ ------
> Memory Usage %: 94.91 95.30
> % SQL with executions>1: 87.78 87.31
> % Memory for SQL w/exec>1: 70.98 71.07
>
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~
> % Total
> Event Waits Time (s)
> Ela Time
> -------------------------------------------- ------------ -----------
> --------
> log file sync 483,596 194,940
> 64.17
> latch free 656,366 46,963
> 15.46
> db file sequential read 232,327 31,051
> 10.22
> CPU time 20,846
> 6.86
> log file parallel write 201,538 2,810
> .93
> -------------------------------------------------------------

A lot depends on things like which hardware you are on, are you using/does the OS support async i/o, are you dedicating your redo logs to a device that doesn't have other things writing to it, are you using raid, and if so, what kind/what kind of cache, Oracle version/patch level, how big is your redo buffer, how big are redo files and how many groups, is this all the time or just during certain times, etc.

If you are doing something putting everything on a RAID-5 device, that might be the problem right there.

jg

--
@home.com is bogus.
http://htmldb.oracle.com/pls/otn/f?p=21195:1:2525094322570244