Re: Latch free waits..

manojtom007_at_yahoo.com (Thomas) wrote in message news:<65182aad.0407261102.1746c19e_at_posting.google.com>...
> High amount of latch free waits is slowing down sessions on db.. any
> ideas on how to debug further?
>
>
> LATCH# NAME
> ---------- ----------------------------------------------------------------
> 156 shared pool
> 157 library cache
> 159 library cache pin allocation
> 98 cache buffers chains
>
> latch sleep /impacts stats..
>
> LATCH TYPE IMPACT SLEEP RATE WAITS
> HOLDING LEVEL
> ------------------------------------- ----------- ----------
> ------------- -----
> library cache 3589515 0.08%
> 3500279 5
> shared pool 800304 0.03%
> 772812 7
> library cache pin allocation 625205 0.05%
> 613239 4
> row cache objects 25799 0.00%
> 24582 4
> row cache enqueue latch 12366 0.00%
> 12171 5
> library cache pin 3985 0.00%
> 3983 4

3 things:

1. please include oracle database server version.
2. col "latch type" format a30
3. Bjorn Ensig's paper on efficient use of cursors: http://otn.oracle.com/deploy/performance/pdf/cursor.pdf

you might find an "Ag" bullet of setting cursor_sharing=FORCE might help to reduce the pressure on library cache. check your hard and soft parses. you're far better off to identify any sql that uses literals for column values like primary key ids for single row operations (non-shared SQL) than go down the path of cursor_sharing=force.

culprits?
start with:

select * from (
select substr(sql_text,1.40), min(hash_value), count(1) cnt, sum(executions)
  from v$sql
 group by substr(sql_text,1.40)
 having count(1)>20
 order by count(1))
where rownum<=10

you'll find lots of articles covering this in detail at dbazine.com.

Pd Received on Tue Jul 27 2004 - 00:43:17 CDT