-spin n

n

The number of times a process tries to acquire a latch before pausing.

Use Spin Lock Retries (-spin) to set a value to use the spin lock mechanism or a system of semaphores and queues. If the value of n is greater than zero (0), a spin lock algorithm is used for shared-memory data structures. When a process has to lock a shared-memory structure, the process tries up to n times to acquire the latch for that structure. If it has not acquired the latch in n tries, then the process pauses, or naps. The length of the pause increases gradually if the process repeatedly fails to acquire a latch. After the allotted nap time, the process wakes up and tries to acquire the lock again. If it fails to acquire the lock, it will retry up to the number of tries specified by n.

If the value of n is zero (0), a system of semaphores and queues is used instead of spin locks. The spin lock algorithm is much more efficient than using semaphores when you have multiple processors.

On multi-processor machines, the default value is 6000 * # of CPUs. If this causes too much CPU usage, reduce the value. If you have many fast processors, a value as high as 10,000 * # of CPUs might be effective.

You can evaluate the -spin values through the PROMON utility's R&D options. See PROMON R&D Advanced Options. The -spin parameter controls the performance indicator called resource waits. By setting the -spin value higher, you can reduce the resource waits. Note that when setting the -spin value higher ceases to reduce the resource waits, continuing to set it higher can adversely effect CPU utilization.

To change this parameter while the database is running, use PROMON or the _DbParams VST.

To view the resource waits value:

• Access PROMON and enter R&D at the main menu.
• Choose option 3, Other Displays, then choose option 1, Performance Indicators, to view the resource waits. Resource waits is the last item reported in the listing.

You can use this parameter to support continuous operation of the database. For more information, see Change database parameters while the database is running.