Re: Redo latch contention

Or with Cary Millsap's hprof/Sparky stuff (hotsos.com). I'm deligthed that guys like you and Cary are writing tools/products that harvest the enormous amount of useful stuff available in 10046 level 8/12 trace files.

You know what would be really cool? The same stuff is available via x$trace. x$trace is in itself much more non-intrusive than 10046 traces (it uses memory buffers), but has some features and possibilities that sets it apart:

• The memory buffers are round-robin, ie. they get re-used. You would need to either get data out of them all the time or use the wonderful feature of flushing the data to disk and harvest the information from those files
• It's all or nothing. You either trace all sessions or none at all (in other words: instance-level)
• With the absolutely incredible amount of data available through the x$trace interface you can - as I see it - map the interactions between processes (foreground/background).

So it would be really cool if some tool some day could get some of this data out :).

"Danisment Gazi Unal (Unal Bilisim)" wrote:

> Mogens,
>
> You are right.
>
> nothing is a performance problem unless there is a time contention.
>
> In general, it's very hard to see time spent in each latch. itrprof SQL Analyzer
> with waitgroup=(name,P1,P2) can report time spent in each latch. So, you can see
> time spent in A latch, time spent in B latch, etc. I'm not a very old dba, but I
> don't think many latches can be tuned by init.ora parameters.
>
> with waitgroup=(name,P1), you can see time spent in each enqueue such as 5 sec
> in (TX), 3 sec in (TM) etc.
>
> 'latch free' and 'enqueue' waits do not make sense themselves. You can
> drill-down these each waits by itrprof.
>
> a little marketing...
>
> Mogens Nørgaard wrote:
>
> > First of all, if you don't see cumulated waits for the 'latch free' event in
> > either v$system_event or v$session_event (for a specific session/job) there
> > is absolutely no need to do anything about these ratios. It's about the only
> > two latches mentioned in the reference books and it's about the only two
> > latches that never really have a problem :).
> >
> > My guess is that this effort (increasing log_simultaneous_copies and
> > log_small_entry_max_size) hasn't increased performance on your system
> > whatsoever.
> >
> > If - only if - you have latch contention/waits in the system (high
> > percentage of time_waited in the above-mentioned
> > v$system_event/v$session_event, you should ignore all those calculations
> > with gets and misses and insted just look in v$latch like this:
> >
> > select name, sleeps from v$latch order by 2;
> >
> > to find the latches with highest contention. But again: If your system or
> > session is not really waiting for latches, why bother?
> >
> > Rajesh Dayal wrote:
> >
> > > Hi All,
> > > I had some situation of Redo Allocation and copy latch
> > > contention as stated in following output.....
> > >
> > > SQL> SELECT substr(NAME,1,18) NAME, GETS,MISSES, IMMEDIATE_GETS,
> > > IMMEDIATE_MISSES
> > > FROM V$LATCH WHERE NAME LIKE '%redo%'
> > > /
> > >
> > > NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
> > >
> > > ------------------ ---------- ---------- -------------- ----------------
> > >
> > > redo allocation 74878 16 0 0
> > >
> > > redo copy 114 100 53756 232
> > >
> > > redo writing 30219 1 0 0
> > >
> > > 3 rows selected.
> > >
> > > Realizing small contention on redo allocation latch, I increased
> > > the value of "log_small_entry_max_size" from 80 to 90.
> > > But this would definitely overload (already suffering) redo copy
> > > latches, so I increased the value of log_simultaneous_copies from 2 to
> > > 6.
> > > This sorted out redo latch contention, but somewhere in FM it's
> > > mentioned that value of log_simultaneous_copies shouldn't be more than
> > > (2 * #_of_CPUs). Again I know that the CPU is "not" heavily used so far.
> > > So...
> > >
> > > 1. Is it OK to set log_simultaneous_copies higher than 2*CPU. What are
> > > the golden rules. I have seen some authers not mentioning this.
> > >
> > > 2. Why this parameter is missing from Oracle 8i?? Has Oracle changed the
> > > algorithm?? What is the new strategy to handle redo latch contention??
> > >
> > > Interestingly, in Oracle 8i (Oracle 8.1.6) Tuning Manuals they
> > > still talk of these parameters(which are made obsolete)...
> > >
> > > Appreciate your inputs ;-)
> > >
> > > Cheers,
> > > Rajesh
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Rajesh Dayal
> > > INET: Rajesh_at_ohitelecom.com
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > > San Diego, California -- Public Internet access / Mailing Lists
> > > --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> >
> > --
> > Venlig hilsen
> >
> > Mogens Nørgaard
> >
> > Technical Director
> > Miracle A/S, Denmark
> > Web: http://MiracleAS.dk
> > Mobile: +45 2527 7100
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Mogens =?iso-8859-1?Q?N=F8rgaard?=
> > INET: mno_at_MiracleAS.dk
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Danisment Gazi Unal (Unal Bilisim)
> INET: danisment.unal_at_unal-bilisim.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

--
Venlig hilsen

Mogens Nørgaard

Technical Director
Miracle A/S, Denmark
Web: http://MiracleAS.dk
Mobile: +45 2527 7100


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mogens =?iso-8859-1?Q?N=F8rgaard?=
  INET: mno_at_MiracleAS.dk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).