Re: log_buffer 10gr2

Regarding "DB Time > Elapsed", see the section "Infinite capacity for waiting" on pp215–216 of the book *Optimizing Oracle Performance*.

By trying to manage to the system-wide statistics you're looking at, you run the risk of working really hard, possibly even succeeding at reducing the value of a statistic, and still creating a result that nobody notices (or worse).

From the report below, it looks like the average latency per *log file sync*call is .701 seconds. However, it's not possible to tell, from what is shown
here, whether that's because a few tasks with lots of redo per commit are influencing the average upward, or because all *log file sync* calls really are taking 3/4 of a second each.

It's often (it may be correct to say "always" here) illuminating to focus in on one or a few runs of one individual task at a time (10046 level 12). Doing that will at least enable you to learn how relevant the work you're trying to do will be if you succeed.

For example, imagine that you trace one really important task—the one that the business most needs for you to speed up—and you find out that its average *log file sync* latency for that type of task is 0.005 seconds. Learning that would fundamentally change what you should decide to go work on next.

It's possible that this is what you're going to find out. Maybe, maybe not. You can't know from the data you've shown here.

Cary Millsap
Method R Corporation

http://method-r.com
http://carymillsap.blogspot.com
http://twitter.com/cary_millsap

On Thu, May 28, 2009 at 11:15 AM, Ujang Jaenudin <ujang.jaenudin_at_gmail.com>wrote:

> cary,
>
> I'm concerning on this number actually,
> Elapsed: 39.95 (mins) <== wal clock
> DB Time: 5,235.95 (mins) <== dbtime is longer than
> wall clock??
>
> Top 5 Timed Events
>
> Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
> log file sync 388,862 272,488 701 86.7 Commit
> buffer busy waits 62,470 16,109 258 5.1 Concurrency
> enq: TX - index contention 30,076 7,451 248 2.4 Concurrency
> enq: HW - contention 12,758 5,430 426 1.7 Configuration
> latch free 13,043 4,400 337 1.4 Other
>
> --
> thanks and regards
> ujang | oracle dba | mysql dba
> http://ora62.wordpress.com
>
>
> On Thu, May 28, 2009 at 10:23 PM, Cary Millsap
> <cary.millsap_at_method-r.com> wrote:
> > Are log file sync events eating appreciable time for each transaction?
> Have
> > you measured that?
> >
> > ...Or is it that you're concerned about the call count or the total
> "system
> > time" being consumed by the calls?
> >
> >
> > Cary Millsap
> > Method R Corporation
> > http://method-r.com
> > http://carymillsap.blogspot.com
> > http://twitter.com/cary_millsap
> >
> >
> > On Thu, May 28, 2009 at 9:49 AM, Ujang Jaenudin <
> ujang.jaenudin_at_gmail.com>
> > wrote:
> >>
> >> I configure it from 4 different LUN + ASM. (2 mirrors of log member).
> >> this is 2 node RAC and for the connection, a service is set for all
> >> bea JDBC connection pool to one node only (failover mode).
> >>
> >>
> >> On Thu, May 28, 2009 at 9:43 PM, Johnson, William L (TEIS)
> >> <WLJohnson_at_tycoelectronics.com> wrote:
> >> > Where are your online redo logs located? Are they using the same disk
> >> > that
> >> > supports your data files for the database? Many Admins will tell you
> >> > that
> >> > hardware capabilities will allow you to collocate data files with redo
> >> > logs
> >> > – but that is not the real world I have seen – even using HP
> enterprise
> >> > class disk…
> >> >
> >> >
> >> >
> >> > ________________________________
> >> >
> >> > From: oracle-l-bounce_at_freelists.org
> >> > [mailto:oracle-l-bounce_at_freelists.org]
> >> > On Behalf Of Bobak, Mark
> >> > Sent: Thursday, May 28, 2009 10:12 AM
> >> > To: Cary Millsap; ujang.jaenudin_at_gmail.com
> >> > Cc: Oracle Discussion List
> >> > Subject: RE: log_buffer 10gr2
> >> >
> >> >
> >> >
> >> > Cary read my mind. The problem is almost certainly related to commit
> >> > frequency. Excessive commits will *kill* performance and scalability.
> >> >
> >> >
> >> >
> >> > From: Cary Millsap [mailto:cary.millsap_at_method-r.com]
> >> > Sent: Thursday, May 28, 2009 10:09 AM
> >> > To: ujang.jaenudin_at_gmail.com
> >> > Cc: Bobak, Mark; Oracle Discussion List
> >> > Subject: Re: log_buffer 10gr2
> >> >
> >> >
> >> >
> >> > Does the application need to be committing as often as it does?
> >> >
> >> > Cary Millsap
> >> > Method R Corporation
> >> > http://method-r.com
> >> > http://carymillsap.blogspot.com
> >> > http://twitter.com/cary_millsap
> >> >
> >> > On Thu, May 28, 2009 at 8:55 AM, Ujang Jaenudin
> >> > <ujang.jaenudin_at_gmail.com>
> >> > wrote:
> >> >
> >> > bobak,
> >> >
> >> > log file sync is the main reason.
> >> > _log_io_size is 0 (default??)
> >> >
> >> > strength on my db when user session only 200 but log file sync can
> >> > reach 800 session waitings (from dbconsole). when checking the io log
> >> > file parallel write max is 4ms, but log file sync can reach 22ms.
> >> >
> >> > average redo blocks per write is 13 which means average buffer to
> >> > write to redolog buffer is only 6656 nytes.
> >> >
> >> > yes the application is heavily commit activites (now in the "load
> >> > testing" stage)
> >> >
> >> > any direction why too many spikes (strange) on log file sync?
> >> >
> >> > --
> >> > thanks and regards
> >> > ujang | oracle dba | mysql dba
> >> > http://ora62.wordpress.com
> >> >
> >> > On Thu, May 28, 2009 at 8:49 PM, Bobak, Mark <Mark.Bobak_at_proquest.com
> >
> >> > wrote:
> >> >> Ok, first, take a step back.
> >> >>
> >> >> What is motivating you to look at log_buffer in the first place? Why
> >> >> do
> >> >> you think it needs to be adjusted? Starting w/ 10g, the log_buffer
> is
> >> >> maintained by Oracle, and should not need to be adjusted. (See
> >> >> MetaLink Doc
> >> >> ID 351857.1.) What is the current value of _log_io_size? Why do you
> >> >> think
> >> >> you need to adjust it?
> >> >>
> >> >> Main question:
> >> >> What is it that is leading you to believe that log_buffer and
> >> >> _log_io_size
> >> >> need to be adjusted?
> >> >>
> >> >> -Mark
> >> >>
> >> >> -----Original Message-----
> >> >> From: oracle-l-bounce_at_freelists.org
> >> >> [mailto:oracle-l-bounce_at_freelists.org]
> >> >> On Behalf Of Ujang Jaenudin
> >> >> Sent: Thursday, May 28, 2009 6:14 AM
> >> >> To: Oracle Discussion List
> >> >> Subject: log_buffer 10gr2
> >> >>
> >> >> dear all,
> >> >>
> >> >> I have 10.2.0.3 on solaris sparc,
> >> >> when trying to decrease log_buffer, it could not be done.
> >> >>
> >> >> always keep 14MB, sga is 3.5GB
> >> >> I want to decrease it to 1MB
> >> >>
> >> >> according to metalink note. 351857.1
> >> >> it is expected behavior.
> >> >>
> >> >> so, is it save to change _log_io_size to 1MB ?
> >> >>
> >> >>
> >> >> --
> >> >> thanks and regards
> >> >> ujang | oracle dba | mysql dba
> >> >> http://ora62.wordpress.com
> >> >> --
> >> >> http://www.freelists.org/webpage/oracle-l
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> > --
> >> > http://www.freelists.org/webpage/oracle-l
> >> >
> >> >
> >>
> >>
> >>
> >> --
> >> thanks and regards
> >> ujang | oracle dba | mysql dba
> >> http://ora62.wordpress.com
> >
> >
>

--
http://www.freelists.org/webpage/oracle-l