Sessions constantly waiting for latch

[JChiappa]

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 :

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

[tombest]

You didn't supply the code you are running, but from your description, there's a good chance it could perform better if it were not so procedural. Typically, writing a cursor loop that then does other things to other tables is slower than the single (or maybe 2) DML statements you could have written that would just perform the insert or update in one shot. Consider that as an option. It's not always the best way, no, but it usually is.

[pando]

look the queries they are running

[JChiappa]

Agreed - in truth, the code (very big to post, but anyway) is like :

CREATE PROCEDURE P(PAR_CUSTOMER in number) IS
cursor C_big_cursor IS select nnn FROM very_big_table1, very_big_table2
where nnnnn ....
and CUSTOMER=PAR_CUSTOMER;
BEGIN
for r in select * from C_big_cursor loop
if condition ...
insert into tab1...
elsif ....
update...tab2
if condition2 ...
update...tab1..
and lots more DMLs and sub-selects, with very complicated logics....
...
end loop;
END;

and in some days I have many simultaneous sessions executing the same procedure, each one using one customer id. Of course, I can have various records for various customers in a given Oracle block, so when the first session is modifying one record in the block, the block is latched, and the others sessions waiting to modify others records in the same block must wait.
True, if some part of this could be executed as a unique SQL statement in just one session the waits would drop drastically, but it´s not the case, mainly due the complicated logic needed. I´m investiganting the possibility of reverse indexes (the PKs are generated by sequences, and the access is by index), but apart this I´m short of other ideas, any comments will be mostly welcome and appreciated.

[]s

Chiappa

[JChiappa]

And I was forgetting, I´m researching about bulk inserts & forall - dunno if it matters in stored PL/SQL in the above described conditions ...

Regards,

Chiappa