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
> -------------------------------------------------------------

log file sync = commit.
Consequently, if you state you can't change the app, you can't change the *number* of commits, but you can influence their *duration*.

-- 
Sybrand Bakker
Senior Oracke DBA'