RE: Redo latch contention

Looks like your itrprof id doing the same thing as Millsap's www.hotsos.com (available already or soon)for which they are going to charge $50-100 per upload.

Alex Hillman

-----Original Message-----
[mailto:danisment.unal_at_unal-bilisim.com] Sent: Saturday, June 16, 2001 12:26 PM
To: Multiple recipients of list ORACLE-L

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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Hillman, Alex
  INET: Alex.Hillman_at_usmint.treas.gov

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