Monitoring usage of the SQL statement cache

If you notice a sudden increase in response time for a query that had been using the SQL statement cache, the entry might have been dropped or deleted. You can monitor the usage of the SQL statement cache and check for a dropped or deleted entry by displaying onstat -g ssc command output.

The database server drops an entry from the cache when one of the objects that the query depends on is altered so that it invalidates the data dictionary cache entry for the query. The following operations cause a dependency check failure:

Execution of any data definition language (DDL) statement (such as ALTER TABLE, DROP INDEX, or CREATE INDEX) that might alter the query plan
Alteration of a table that is linked to another table with a referential constraint (in either direction)
Execution of UPDATE STATISTICS FOR TABLE for any table or column involved in the query
Renaming a column, database, or index with the RENAME statement

When an entry is marked as dropped or deleted, the database server must reparse and reoptimize the SQL statement the next time it executes. For example, Figure 1 shows the entries that the onstat -g ssc command displays after UPDATE STATISTICS was executed on the items and orders table between the execution of the first and second SQL statements.

The Statement Cache Entries portion of the onstat -g ssc output in Figure 1 displays a flag field that indicates whether or not an entry has been dropped or deleted from the SQL statement cache.

The first entry has a flag column with the value DF, which indicates that the entry is fully cached, but is now dropped because its entry was invalidated.
The second entry has the same statement text as the third entry, which indicates that it was reparsed and reoptimized when it was executed after the UPDATE STATISTICS statement.

Figure 1. Sample onstat -g ssc command output for a dropped entry

onstat -g ssc

...
Statement Cache Entries: 

lru hash ref_cnt hits  flag  heap_ptr  database     user
---------------------- ---- ---------------------------------------------------
...
  2  232       1    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    0   -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 
...