Skip to main content

latch: shared pool

Short Description

This latch protects the allocation of memory from the shared pool. If there is contention on this latch, it is often an indication that the shared pool is fragmented.

Detailed Description​

Contention on the Shared Pool can be associated with hard parsing. During parsing the query is checked to see if it already exists in memory. Where client connections pass in string literals, a high number of very similar versions of the SQL can accumulate in the shared pool and make it difficult for Oracle to manage.

How to reduce this wait​

Where you have control of the client software, queries can be changed to use bind variables. This will ensure cursor re-use and will minimise fragmentation. For example

SELECT * FROM emp WHERE ename='Mr Insights'

  • this is a string literal query and should be avoided.

SELECT * FROM emp WHERE ename=:v_username

  • is a bind variable query and will reduce the parsing overhead.

If your Oracle version is 9i and later, CURSOR_SHARING may be available. This feature is designed to change SQL to use bind variables 'in-flight' and can be used to reduce similar SQL fragmenting the shared pool. This can be used where you do not have access to the client software the SQL.

Search online​

If this article doesn't have the information you need you can try searching online. Remember, you can contribute suggestions to this page.