This latch serializes access to the objects in the library cache. Every time a SQL statement, a PL/SQL block or a stored object (procedure, package, function, trigger) is parsed or executed, this latch is acquired.
Problem – Fragmentation of the shared pool
Excessive execution of a statement can cause library cache contention.
Many versions of one SQL statement in the shared pool
Excessive parsing of SQL statements (even soft parsing) because of non-reusable queries or large objects being loaded in the shared pool forcing out the smaller heavily used queries
Solutions
Use bind variables whenever possible. Reuse of code means less parsing and less use of shared pool space.
Pin packages and procedures that are heavily executed in the shared pool. This prevents the heavily used code from being flushed out and thus needing to be parsed in.
Increase SESSION_CACHED_CURSORS parameter. This helps if the user repeatedly parses the same statements.
Use fully qualified table names. Example: “select * from owner.table” instead of “select * from table”
If using EXECUTE IMMEDIATE with bind variables, try using DBMS_SQL instead.
If the statement is complex and heavily executed, the load on the library cache could be reduced by breaking it up into multiple smaller statements.
Reducing the versions of SQL statements reduces the load on library cache as well. Oracle deals with various bind variable sizes by creating multiple versions of the statement. One possible solution would be writing more smaller queries rather than one large one.
Specify objects over a certain size be loaded into the large pool instead of the shared pool.
It’s possible that the shared pool is too small. If the above remedies have been attempted and contention is still evident, look into increasing the shared pool gradually.
Please also refer to Oracle’s MetaLink’s Note:146599.1 for information on this latch.
Comment