Monitoring the number of hits on the SQL statement cache

To monitor the number of hits in the SQL statement cache, run the onstat -g ssc command.

The onstat -g ssc command displays fully cached entries in the SQL statement cache. Figure 1 shows sample output for onstat -g ssc.

Figure 1. onstat -g ssc output

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/07" 

  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/2007" 

  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 the Statement Cache Summary portion of the onstat -g ssc output, the #hits column is the value of the SQL_STMT_HITS configuration parameter.
In Figure 1, 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.

Important: The database server uses entries in the SQL statement cache only if the statements are exactly the same. The first two entries in Figure 1 are not the same because each contains a different literal value in the order_date filter.
In the Statement Cache Entries portion of the onstat -g ssc output, the hits column shows the number of times that the database server ran each individual SQL statement from the cache. In other words, the column shows the number of times that the database server uses the memory structures in the cache instead of optimizing the statements to generate them again.
The first time that it inserts the statement in the cache, the hits value is 0.
- The first two SQL statements in Figure 1 have a hits column value of 0, which indicates that each statement is inserted into the cache but not yet run from the cache.
- The last two SQL statements in Figure 1 have a hits column value of 1, which indicates that these statements ran once from the cache.
The hits value for individual entries indicates how much sharing of memory structures is done. Higher values in the hits column indicate 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 SQL statement cache information in onstat -g ssc output.