Spotlight on Oracle 10.6 - Getting Started Guide

Result Cache Latch Sleep Ratio Alarm

Spotlight raises the Result Cache Latch Sleep Ratio alarm when:

• The rate of latch sleeps for the Result Cache latch is a high percentage of the rate of latch sleeps for all latches, and
• The total time taken by the Latch Wait event exceeds 2% of the total database time.

This indicates that there is contention on the Result Cache latch, where multiple processes are competing to use the latch and are causing a significant degradation in response time.

To reduce contention on the Result Cache latch, consider reducing the number of result sets added to the cache:

• View the SQL & Application Workload | Result Cache |Contents Page to find statements that can be excluded from the cache via Result Cache Parameters and Hints.
• Reduce the size of the result cache to exclude result sets that are too large.