library cache latch等待事件

#http://www.askmaclean.com/archives/library-cache-latch.html

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.

--将使用频繁的package 和过程钉在共享池内。（剧经验，对于不是特别多的package和过程，即使将所有package都钉在共享池，此部分开销也不大 一般不会超过20M，但是收益会很大）。

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.