Ways to reduce the shared pool latch

The shared pool latch is used to protect critical operations when allocating and freeing memory in the shared pool. If an application makes use of literal (unshared) SQL then this can severely limit scalability and throughput. 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. Before Oracle9, there use to be just one such latch to the entire database to protects the allocation of memory in the library cache. In Oracle9 multiple childs were introduced to relieve contention on this resource


To reduce the shared pool latch :

•  Avoid hard parses when possible, parse once, execute many 
•  Eliminate 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