Shared pool latch

Possible Causes:

•         The shared pool latch is used to protect critical operations when allocating and freeing memory in the shared pool.
•         Contentions for the shared pool and library cache latches are mainly due to intense hard parsing. A hard parse applies to new cursors and cursors that are aged out and must be re-executed.
•         The cost of parsing a new SQL statement is expensive both in terms of CPU requirements and the number of times the library cache and shared pool latches may need to be acquired and released.

Actions:

•         Ways to reduce the shared pool latch are, avoid hard parses when possible, parse once, execute many.
•         Eliminating literal SQL is also useful to avoid the shared pool latch. The size of the shared_pool and use of MTS (shared server option) also greatly influences the shared pool latch.
•         The workaround is to set the initialization parameter CURSOR_SHARING to FORCE. This allows statements that differ in literal values but are otherwise identical to share a cursor and therefore reduce latch contention, memory usage, and hard parse.

Remarks:

• <Note 62143.1> explains how to identify and correct problems with the shared pool, and shared pool latch