The only time I will increase _spin_count is if the operating system vendor's performance engineer, who must also be an Oracle expert, makes the recommendation and can clearly explain why latch acquisition response time will decrease after changing this value. This is exceptionally rare. If the engineer can't clearly explain why increasing _spin_count will help performance, he is guessing! And guessing is no way to fight performance fires or advance your career.

Using Oracle's wait interface, detecting Oracle latch contention is very straightforward. As I have mentioned, nearly every production Oracle system will have latch contention, but that does not imply there is a problem worthy of our attention. Only when the contention rises to the top of our wait event and ORTA reports is it significant and worthy of our precious time to improve the situation.

To have a realistic shot at solving latch issues, our diagnosis must also include determining the specific latch that is causing the problem. For example, knowing latching is responsible for 80% of the wait time is not enough information. Knowing 75% of the wait time is associated with the CBC latches gives us the detailed information we need to develop a latch-specific solution. So detecting latch contention implies latching is consuming a significant portion of response time and also determining the problematic latch. In nearly all cases, a single latch type will be involved-for example, the library cache latch, the CBC latch, or the LRU latch.