遇到Library cache load lock 等待事件

遇到Library cache load lock 等待事件：

在Troubleshooting Library Cache: Lock, Pin and Load Lock (Doc ID 444560.1)这篇文章中，详细解释了该等待事件：

 

If an object is not in memory, then a library cache lock cannot be acquired on it.
 The object has to be loaded into the memory to acquire the lock.
 The session tries to find the load lock for the database object so that it can load the object.
 In order to prevent multiple processes requesting the load of the same object simultaneously, the other requesting sessions have to wait for the library cache load lock as the lock is busy with loading the object into the memory.

 The waits on the library cache load lock is due to the objects not being available in memory.
 The unavailability of the library cache object in the library cache is due to the undersized shared pool causing frequent reloads or too many hard parse as a result of unshared sqls.

有如下几种方法避免该等待事件的发生:

•Increase the shared pool ( to avoid high reloads)
•Increase the session cached cursors (to avoid the cursors flushing out of shared pool)
•Set cursor_sharing to force (to reduce hard parsing)--again this may change the plan and performance of query. This will change literals to use binds; so plan may change: 

 

后来总结了一下：

该db上之所以发生Library cache load lock等待事件，是因为将shared_pool_size的大小减小了。看来，貌似简单的操作，可能会引起很多问题。于是后来找时间加大了shared_pool_size

ＤＢＡ在生产库上的一举一动都可能引起各种问题。慎重！