Number of SQL statement cache pools

When the SQL statement cache (SSC) is enabled, the database server allocates memory from the SSC pool for unlinked SQL statements. The default value for the STMT_CACHE_NUMPOOL configuration parameter is 1. As the number of users increases, this one SSC pool might become a bottleneck.

The number of longspins on the SSC pool indicates whether or not the SSC pool is a bottleneck.

Use the onstat -g spi option to monitor the number of longspins on an SSC pool. The onstat -g spi command displays a list of the resources in the system for which a wait was required before a latch on the resource could be obtained. During the wait, the thread spins (or loops), trying to acquire the resource. The onstat -g spi output displays the number of times a wait (Num Waits column) was required for the resource and the number of total loops (Num Loops column). The onstat -g spi output displays only resources that have at least one wait.

onstat -g spi output shows an excerpt of sample output for onstat -g spi. onstat -g spi output indicates that no waits occurred for any SSC pool (the Name column does not list any SSC pools).

Figure 1: onstat -g spi output

Spin locks with waits:
Num Waits   Num Loops   Avg Loop/Wait    Name
34477       387761      11.25            mtcb sleeping_lock
312         10205       32.71            mtcb vproc_list_lock

If you see an excessive number of longspins (Num Loops column) on an SSC pool, increase the number of SSC pools in the STMT_CACHE_NUMPOOL configuration parameter to improve performance.