Sessions constantly waiting for latch
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Sessions constantly waiting for latch

  1. #1
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350

    Sessions constantly waiting for latch

    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

  2. #2
    Join Date
    Jul 2003
    Posts
    59
    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.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    look the queries they are running

  4. #4
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    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 its not the case, mainly due the complicated logic needed. Im investiganting the possibility of reverse indexes (the PKs are generated by sequences, and the access is by index), but apart this Im short of other ideas, any comments will be mostly welcome and appreciated.

    []s

    Chiappa

  5. #5
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350

    another complementary point ...

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

    Regards,

    Chiappa

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width