Shared Pool and Library Cache Latch Contention

A main cause of shared pool or library cache latch contention(memory fragmentation) is unnecessary parsing. There are a number of techniques that you can use to identify unnecessary parsing and many types of unnecessary parsing:

• Unshared SQL: Identify similar SQL statements that could be shared if literals were replaced with bind variables.
• Reparsed Sharable SQL: If the parse_calls value is close to the executions value for a given statement, then you might be continually reparsing that statement.

It is bad to have the PARSE_CALLS value close to the EXECUTIONS value.

If many small statements are hard parsed, shared pool fragmentation is likely to result. As the shared pool becomes more fragmented, the amount of time required to complete a hard parse increases. As the process of executing many unique statements continues, resource contention worsens. To avoid it followings are recommended;

• Increase the shared_pool %20 for minor contention problems
• Flush shared pool once a day(if possible due to web tier infrastructure restrictions).
• Pin frequently used PL/SQL functions and packages in the shared pool.

To find how PARSE_CALLS are closer to the EXECUTIONS run the following statement;

select sql_text, parse_calls, executions
  from v$sqlarea
  where parse_calls > 300
   and executions < 2*parse_calls
   and executions > 1;

Following statement displays latch content analysis.

select n.name "Latch Type", misses*100/(gets+1) "Misses/Gets (%)",
       immediate_misses*100/(immediate_gets+1) "Immediate Misses/Gets (%)"
from v$latchname n,v$latch l
where n.latch# = l.latch#
  and n.name in('%cache buffer%','%protect%');