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.
About this task
The onstat -g ssc command displays
fully cached entries in the SQL statement
cache. onstat -g ssc output 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 onstat -g ssc output, 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 onstat -g ssc output 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 onstat -g ssc output 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 onstat -g ssc output 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.