Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: log file sync , log file parallel write

Re: log file sync , log file parallel write

From: sybrandb <sybrandb_at_gmail.com>
Date: 19 Dec 2006 01:07:00 -0800
Message-ID: <1166519220.800941.85280@a3g2000cwd.googlegroups.com>

On Dec 19, 7:51 am, "db_man" <hopehope_..._at_yahoo.com> wrote:
> 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'- Hide quoted text -- Show quoted text -

RAID5 usually is just plain evil.

Also: please stop top posting. You can change your nick, but you have been here long enough to know we don't appreciate top-posting.

-- 
Sybrand Bakker
Senior Oracle DBA
Received on Tue Dec 19 2006 - 03:07:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US