You can monitor and tune various characteristics of the SQL statement cache. The following table shows the tools you can use to monitor the different characteristics. ISA uses information that the following onstat command-line options generate to display information about the SQL statement cache. Click the Refresh button to rerun the onstat command and display fresh information.
| SSC Characteristic to
Monitor |
Select on ISA | Displays the Output of | See |
|---|---|---|---|
| Number of times statements are read from the cache | Performance > Cache
> Statement Cache |
onstat -g ssc
onstat -g ssc all |
Number of SQL Statement Executions |
| Size of the SQL statement cache | Performance > Cache
> Statement Cache |
onstat -g ssc
onstat -g ssc all |
Monitoring and Tuning the Size of the SQL Statement Cache |
| Amount of memory used | Performance > Cache
> Statement Cache |
onstat -g ssc
onstat -g ssc all |
Memory Limit and Size |
| Usage of the SQL statement cache pools | Performance > Locks
> Latches |
onstat -g spi | Multiple SQL Statement Cache Pools |
When the SQL statement cache is enabled, the database server inserts a qualified SQL statement and its memory structures immediately in the SQL statement cache by default. If your workload has a disproportionate number of ad hoc queries, use the STMT_CACHE_HITS configuration parameter to specify the number of times an SQL statement is executed before the database server places a fully cached entry in the statement cache.
When the STMT_CACHE_HITS configuration parameter is greater than 0 and the number of times the SQL statement has been executed is less than STMT_CACHE_HITS, the database server inserts key-only entries in the cache. This specification minimizes unshared memory structures from occupying the statement cache, which leaves more memory for SQL statements that applications use often.
Monitor the number of hits on the SQL statement cache to determine if your workload is using this cache effectively. The following sections describe ways to monitor the SQL statement cache hits.
The onstat -g ssc option displays fully cached entries in the SQL statement cache. Figure 12 shows sample output for onstat -g ssc.
onstat -g ssc Statement Cache Summary: #lrus currsize maxsize Poolsize #hits nolimit 4 49456 524288 57344 0 1 Statement Cache Entries: lru hash ref_cnt hits flag heap_ptr database user ----------------- ---- -------------------------------------------------------- 0 153 0 0 -F a7e4690 vjp_stores virginia SELECT * FROM customer, orders WHERE customer.customer_num = orders.customer_num AND order_date > "01/01/97" 1 259 0 0 -F aa58c20 vjp_stores virginia SELECT * FROM customer, orders WHERE customer.customer_num = orders.customer_num AND order_date > "01/01/1997" 2 232 0 1 DF aa3d020 vjp_stores virginia SELECT C.customer_num, O.order_num FROM customer C, orders O, items I WHERE C.customer_num = O.customer_num AND O.order_num = I.order_num 3 232 1 1 -F aa8b020 vjp_stores virginia SELECT C.customer_num, O.order_num FROM customer C, orders O, items I WHERE C.customer_num = O.customer_num AND O.order_num = I.order_num Total number of entries: 4.
To monitor the number of times that the database server reads the SQL statement within the cache, look at the following output columns:
In Figure 12, the #hits column in the Statement Cache Summary portion of the output has a value of 0, which is the default value of the STMT_CACHE_HITS configuration parameter.
The first time it inserts the statement in the cache, the hits value is 0.
The hits value for individual entries indicates how much sharing of memory structures is done. Higher values in the hits column indicates that the SQL statement cache is useful in improving performance and memory usage.
For a complete description of the output fields that onstat -g ssc displays, see Output Descriptions of onstat Options for SQL Statement Cache.
Use the onstat -g ssc all option to determine how many nonshared entries exist in the cache. The onstat -g ssc all option displays the key-only entries in addition to the fully cached entries in the SQL statement cache.
You can use one of the following methods to change the STMT_CACHE_HITS parameter value:
You can use one of the following methods to update the ONCONFIG file:
onmode -W STM_CACHE_HITS 2
If you restart the database server, the value reverts the value in the ONCONFIG file. Therefore, if you want the setting to remain for subsequent restarts, modify the ONCONFIG file.
If the size of the SQL statement cache is too small, the following performance problems can occur:
The statements used most often should remain in the SQL statement cache. If the SQL statement cache is not large enough, the database server might not have enough room to keep these statements when other statements come into the cache. For subsequent executions, the database server must reparse, reoptimize, and reinsert the SQL statement into the cache. Try increasing STMT_CACHE_SIZE.
The database server tries to prevent the SQL statement cache from allocating large amounts of memory by using a threshold (70 percent of the STMT_CACHE_SIZE parameter) to determine when to remove entries from the SQL statement cache. If the new entry causes the size of the SQL statement cache to exceed the threshold, the database server removes least recently used entries (that are not currently in use) before inserting the new entry.
However, if a subsequent query needs the removed memory structures, the database server must reparse and reoptimize the SQL statement. The additional processing time to regenerate these memory structures adds to the total response time of the query.
You can set the size of the SQL statement cache in memory with the STMT_CACHE_SIZE configuration parameter. The value of the parameter is the size in kilobytes. If STMT_CACHE_SIZE is not set, the default value is 512 kilobytes.
The onstat -g ssc output shows the value of STMT_CACHE_SIZE in the maxsize column. In Figure 12, this maxsize column has a value of 524288, which is the default value (512 * 1024 = 524288).
Use the onstat -g ssc and onstat -g ssc all options to monitor the effectiveness of size of the SQL statement cache. If you do not see cache entries for the SQL statements that applications use most, the SQL statement cache might be too small or too many unshared SQL statement occupy the cache. The following sections describe how to determine these situations.
Look at the values in the following output columns in the "Statement Cache Entries" portion of the onstat -g ssc all output.
A value of F in the second position indicates that the statement is currently fully cached.
A value of -in the second position indicates that only the statement text (key-only entry) is in the cache). Entries with this -value in the second position appear in the onstat -g ssc all but not in the onstat -g ssc output.
If you do not see fully cached entries for statements that applications use most and the value in the hits column is large for the entries that do occupy the cache, then the SQL statement cache is too small.
You can use one of the following methods to change the STMT_CACHE_SIZE parameter value:
onmode -W STMT_CACHE_SIZE 1024
If you restart the database server, the value reverts the value in the ONCONFIG file. Therefore, if you want the setting to remain for subsequent restarts, modify the ONCONFIG file.
When the database server places many queries that are only used once in the cache, they might replace statements that other applications use often.
Look at the values in the following output columns in the Statement Cache Entries portion of the onstat -g ssc all output. If you see a lot of entries that have both of the following values, too many unshared SQL statements occupy the cache:
A value of F in the second position indicates that the statement is currently fully cached.
The hits column shows the number of times the SQL statement has been executed, excluding the first time it is inserted into the cache.
Increase the value of the STMT_CACHE_HITS configuration parameter to prevent unshared SQL statements from being fully cached. For information on how to change the STMT_CACHE_HITS configuration parameter, see Number of SQL Statement Executions.
Although the database server tries to clean the SQL statement cache, sometimes entries cannot be removed because they are currently in use. In this case, the size of the SQL statement cache can exceed the value of STMT_CACHE_SIZE.
The default value of the STMT_CACHE_NOLIMIT configuration parameter is 1, which means the database server inserts the statement even though the current size of the cache might be greater than the value of the STMT_CACHE_SIZE parameter.
If the value of the STMT_CACHE_NOLIMIT configuration parameter is 0, the database server does not insert either a fully-qualified or key-only entry into the SQL statement cache if the size will exceed the value of STMT_CACHE_SIZE.
Use the onstat -g ssc option to monitor the current size of the SQL statement cache. Look at the values in the following output columns of the onstat -g ssc output:
In Figure 12, the currsize column has a value of 11264.
In Figure 12, the maxsize column has a value of 524288, which is the default value (512 * 1024 = 524288).
When the SQL statement cache is full and users are currently executing all statements within it, any new SQL statements that a user executes can cause the SQL statement cache to grow beyond the size that STMT_CACHE_SIZE specifies. Once the database server is no longer using an SQL statement within the SQL statement cache, it frees memory in the SQL statement cache until the size reaches a threshold of STMT_CACHE_SIZE. However, if thousands of concurrent users are executing several ad hoc queries, the SQL statement cache can grow very large before any statements are removed. In such cases, take one of the following actions:
You can use one of the following methods to change the STMT_CACHE_NOLIMIT parameter value:
onmode -W STMT_CACHE_NOLIMIT 0
If you restart the database server, the value reverts the value in the ONCONFIG file. Therefore, if you want the setting to remain for subsequent restarts, modify the ONCONFIG file.
When the SQL statement cache is enabled, the database server allocates memory from one pool (by default) for the query structures in the following situations:
This one pool can become a bottleneck as the number of users increases. The STMT_CACHE_NUMPOOL configuration parameter allows you to configure multiple sscpools.
You can monitor the pools in the SQL statement cache to determine the following situations:
When the SQL statement cache is enabled, the database server allocates memory from an sscpool for unlinked SQL statements. The default value for the STMT_CACHE_NUMPOOL configuration parameter is 1. As the number of users increases, this one sscpool might become a bottleneck. (The number of longspins on the sscpool indicates whether or not the sscpool is a bottleneck.)
Use the onstat -g spi option to monitor the number of longspins on an sscpool. 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.
Figure 13 shows an excerpt of sample output for onstat -g spi. Figure 13 indicates that no waits occurred for any sscpool (the Name column does not list any sscpools).
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 sscpool, increase the number of sscpools in the STMT_CACHE_NUMPOOL configuration parameter to improve performance.
Use the onstat -g ssc pool option to monitor the usage of each SQL statement cache 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 sscpools by the statements that are inserted into the cache. Because not all statements that allocate memory from the sscpools are inserted into the cache, the current cache size could be smaller than the total size of the sscpools. Normally, the total size of all sscpools does not exceed the STMT_CACHE_SIZE value.
Figure 14 shows sample output for onstat -g ssc pool.
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.
The Blkpool Summary section of the onstat -g ssc pool output lists the following information for all pools in the cache.
The onstat -g ssc option lists the following summary information for the SQL statement cache.
The onstat -g ssc option lists the following information for each fully cached entry in the cache. The onstat -g ssc all option lists the following information for both the fully cached entries and key-only entries.