Spotlight on Oracle 10.10 - Getting Started Guide

Tuning The Value of SPIN_COUNT

The value of SPIN_COUNT specifies the number of times that a process actively tries to obtain a latch before it temporarily goes into sleep mode. You can use the Latch Tuning page to find the optimum value for SPIN_COUNT.

Oracle and SPIN_COUNT

Within Oracle systems, latches are locking mechanisms that prevent multiple Oracle sessions from simultaneously updating the same item within Oracle shared memory (SGA). Each latch is a serial resource, and so can be used by only one process at a time.

At high transaction rates, Oracle systems can suffer from latch contention, where multiple processes compete for a limited number of available latches. For more information on reducing this competition for resources, see Relieve Latch Contention.

If none of the suggested methods is appropriate, use this Latch Page to change the SPIN_COUNT parameter for the system. SPIN_COUNT specifies how many time a process tries to obtain a latch before it "sleeps" – that is, before it temporarily postpones the attempt. (Initially, sleep times are set to values of 0, 10, or 20 milliseconds and can increase exponentially to a maximum of 2 seconds.)

Spotlight's optimization process (started via the Latch Tuning Configuration Dialog) tries a range of values of SPIN_COUNT to discover the value that produces the best performance for your Oracle system. In the Configuration page, you can specify a number of parameters that Spotlight can apply to its optimization, including:

• Minimum and maximum values of SPIN_COUNT.
• Maximum permitted CPU utilization.
• Optimization goals.
• Constraints.
• How often to change the value of SPIN_COUNT.
• When to stop.

Spotlight stops the optimization process prematurely if the Oracle system performance drops during optimization, or if a specified constraint is met.

Related Topics

Spotlight on Oracle Drilldowns