Size of SQL statement cache pools and the current cache

Use the onstat -g ssc pool option to monitor the usage of each SQL statement cache (SSC) pool.

The onstat -g ssc pool command displays the size of each pool. The onstat -g ssc option displays the cumulative size of the SQL statement cache in the currsize column. This current size is the size of memory allocated from the SSC pools by the statements that are inserted into the cache. Because not all statements that allocate memory from the SSC pools are inserted into the cache, the current cache size could be smaller than the total size of the SSC pools. Normally, the total size of all SSC pools does not exceed the STMT_CACHE_SIZE value.

Figure 1 shows sample output for onstat -g ssc pool.

Figure 1. onstat -g ssc pool output

onstat -g ssc pool


Pool Summary:
name         class addr     totalsize freesize #allocfrag #freefrag
sscpool0     V     a7e4020  57344      2352     52         7

Blkpool Summary:
name         class addr     size      #blks

The Pool Summary section of the onstat -g ssc pool output lists the following information for each pool in the cache.

Column	Description
name	The name of the SQL statement cache (SSC) pool
class	The shared-memory segment type in which the pool has been created. For SSC pools, this value is always “V” for the virtual portion of shared-memory.
addr	The shared-memory address of the SSC pool structure
totalsize	The total size, in bytes, of this SSC pool
freesize	the number of free bytes in this SSC pool
#allocfrag	The number of contiguous areas of memory in this SSC pool that are allocated
#freefrag	The number of contiguous areas of memory that are not used in this SSC pool

The Blkpool Summary section of the onstat -g ssc pool output lists the following information for all pools in the cache.

Column	Description
name	The name of the SSC pool
class	The shared-memory segment type in which the pool has been created. For SSC pools, this value is always “V” for the virtual portion of shared-memory.
addr	The shared-memory address of the SSC pool structure
totalsize	The total size, in bytes, of this SSC pool
#blks	The number of 8-kilobyte blocks that make up all the SSC pools