latch: shared pool等待事件

The shared pool latch is used to protect critical operations when allocating and freeing memory in the shared pool. Typically a miss rate for this latch less than 95% results in poor performance. There is only one latch of this type in the shared pool, so when there is contention for this latch, significant degradation in performance can occur.

Problem

ORA-4031 errors are seen in the alert log if a trace file is generated (such as when a background process receives this error instead of when a user proc may get it. This means the session cannot allocate contiguous memory in shared pool for incoming SQL statement.

Solutions

Since the number of shared pool latch gets is influenced by the volume of shared pool activity like parse operations, anything that can reduce the this activity will improve the availability of this latch and overall database performance (see Reducing the Number of SQL Statements).