oracle latch优化（Oracle latch optimization）.doc

oracle latch优化（Oracle latch optimization） Oracle latch optimization Personal classification: ORACLE STATPACT Causes of Latch, diagnosis and countermeasures Latch contention usually means that this process holds the Latch for too long. If the latch contention is obvious, the system performance will decrease significantly. In high concurrency environments, latch contention happens often, and you cant completely eliminate the latch contention. There will always be a latch free waiting event in v $system_event. You only need to focus on the latch contention when the total time of the time_waited relative instance startup is obvious. When the waiting time for the latch is significant within the system, you can find a significant latch through the column in the column in v $latch: Select the name, gets, misses, immediate_gets, immediate_misses, sleepsfromv $latchorder by sleeps; Immediate immediatenamegetsmissesgetsmissessleeps -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- the enqueue hash chains427709504279001964shared pool91066505400002632row cache objects690598872793840907517enqueues804433143301670013761library cache6944717210334946582719044328cache buffers chains 16910402521166249615326895909127478... for different latch, the reasons and countermeasures. A detailed description of all the latch can be written as a book. Here we only select the five most common ones: Shared pool, library cache, cache buffers, cache buffers lru chain and row cache objects. Shared pool and library cache latch Oracles Shared pool consists of different structures. Includes: data dictionary cache, SQL area and library cache. You can look at other structures through v $sgastat. The Shared pool latch is mainly used to protect the memory structure of the Shared pool, which needs to be obtained first when the Shared pool memory is all