Re: log file sync , log file parallel write

From: db_man <hopehope_123_at_yahoo.com>
Date: 18 Dec 2006 22:51:13 -0800
Message-ID: <1166511073.306617.57200@j72g2000cwa.googlegroups.com>

Thanks for your answers. Everything is in raid5 .

Os is sun solaris , 16gb.ram , 8 cpus.

Statistic                                      Total     per Second

per Trans

--------------------------------- ------------------ --------------

---

user commits                                 446,365          124.1
     1.0

in one hour , 446365 commits occurred.

Kind Regards,

sybrandb wrote:
> 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'
Received on Tue Dec 19 2006 - 00:51:13 CST