Latch Contention
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 1 of 1

Thread: Latch Contention

  1. #1
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360

    Latch Contention

    I've been looking at our 9204 database and have noticed that we have latch contention on the library and shared pool latches, which points to a hard parse problem. During the 30 minute snapshot there were 361 parses/sec, and 20 hard parses/sec.
    We've got CURSOR_SHARING=SIMILAR
    My first port of call was the PARSE CALLS section of Statspack. The statement with the most parse calls was (30,316):

    Code:
    SELECT PRIOR_RULE_ID, ROLLOVER_AMT, CURR_RULE_ID, ROLLOVER_BAL,
    PRIOR_BENEFIT_PKG_ID, ROLLOVER_DATE, CURR_BENEFIT_PKG_ID, RULE_T
    YPE, PRIOR_BENEFIT_REMAIN, SEQ_MEMB_ID, PRIOR_BENEFIT_LIMIT, SEQ
    _ROLLOVER_ID FROM HSD_BENEFIT_ROLLOVER_DETAILS WHERE ((SEQ_MEMB_
    ID = :1) AND (CURR_RULE_ID = :2))
    .. But it looks like it uses bind variables. Why so many parses?? The shared pool is massive and the memory section of the report shows an 80% utilisation so I doubt it's because the SQL is being agaed out of the shared pool.
    Last edited by fraze; 04-26-2006 at 11:49 PM.

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