In a 8.1.7 database , running batch PL/SQL programs (typical : packaged procedure doing for r in select loop cursor joining some very BIG tables, making some calcs, updating readed line where PK=r.PK , UPDATE/INSERT some others relationed tables), commit after the loop only, with 3 ou 4 sessions executing the same packaged procedure (only with different parameters, hence different record), the sessions are executing very slowly, and checking the waits is showing latch free almost all the times :


system@INTERP:SQL>get x

column sid format 999
column seq# format 99999
column event format a29 heading "Wait Event" trunc
column state format a15 heading "Wait State" trunc
column secs format 9999999 heading "Waited so|far (sec)"
column wt format 9999999 heading "Waited|Seconds"
SELECT sid, seq#, wait_time wt , event, seconds_in_wait secs, state
FROM v$session_wait
where sid in (34, 36, 74, 81)
ORDER BY sid, wait_time, event;

system@INTERP:SQL>@x

Waited Waited so
SID SEQ# Seconds Wait Event far (sec) Wait State
---- ------ -------- ----------------------------- --------- ---------------
34 44154 1 latch free 0 WAITED KNOWN TI
36 39092 1 latch free 0 WAITED KNOWN TI
74 24718 1 latch free 0 WAITED KNOWN TI
system@INTERP:SQL>@x

Waited Waited so
SID SEQ# Seconds Wait Event far (sec) Wait State
---- ------ -------- ----------------------------- --------- ---------------
34 44159 0 latch free 0 WAITING
36 39099 1 latch free 0 WAITED KNOWN TI
74 24722 1 latch free 3 WAITED KNOWN TI
system@INTERP:SQL>@x

Waited Waited so
SID SEQ# Seconds Wait Event far (sec) Wait State
---- ------ -------- ----------------------------- --------- ---------------
34 44162 1 latch free 0 WAITED KNOWN TI
36 39102 1 latch free 0 WAITED KNOWN TI
74 24728 1 db file sequential read 3 WAITED KNOWN TI

tracing with level 12 (to show waits) , withou surprises, results in :

grep -i 'WAIT' ora_161234_interp.trc
.....
WAIT #22: nam='latch free' ela= 1 p1=504403161435086792 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161444265320 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161440525000 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161433478968 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161443231688 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161444041832 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161442263240 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161432808168 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161439494472 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161432758248 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161443150984 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161437908328 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161439336168 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161434528072 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161441347560 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161436160776 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161440705032 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161437399272 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161442185640 p2=66 p3=0
WAIT #22: nam='db file sequential read' ela= 2 p1=12 p2=755620 p3=1
WAIT #22: nam='latch free' ela= 1 p1=504403161439311336 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161438733992 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161438128712 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161432830008 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161443389992 p2=66 p3=0
WAIT #22: nam='db file sequential read' ela= 2 p1=206 p2=724385 p3=1
WAIT #22: nam='latch free' ela= 1 p1=504403161437725192 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161434112136 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161440344968 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161441319624 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161439696232 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161440776424 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161433457128 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161439559656 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161441869032 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161438898504 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161432945448 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161434158696 p2=66 p3=0
WAIT #22: nam='latch free' ela= 1 p1=504403161442163912 p2=66 p3=0
.....


my questions are :

a) being latch 66=cache buffer chains, I want to know who (what SIDs, obviosly one of the ones running the package) are holding/releasing/requesting this latch a lot, and what blocks in what file are being affected (ie., hot)

b) I altered the tables involved in the batch process to have more freelists and a bigger INITRANS, anything more to do ?

Regards,

Chiappa

P.S. if needed, the v$latch contains :

Code:
system@INTERP:SQL>select * from v$latch where latch#=66;

ADDR             LATCH# LEVEL# NAME                        GETS  
---------------- ------ ------ -------------------- -----------  
070000000000D2C8     66      1 cache buffers chains 85698201909  


    MISSES   SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES WAITERS_WOKEN WAITS_HOLDING_LATCH
---------- -------- -------------- ---------------- ------------- -------------------
1679551873 25044039      177651381            84283             0             1924219

 SPIN_GETS   SLEEP1  SLEEP2  SLEEP3
---------- -------- ------- -------
1661747894 13142483 2849017 1812479