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):
.. 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.
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))