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 :
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 ?
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.
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.
Reply With Quote
Bookmarks