Enterprise Edition Home | Express Edition Home | Previous Page | Next Page   Improving Individual Query Performance > SQL Statement Cache >

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. 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:

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 79 shows the entries that onstat -g ssc 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 79 displays a flag field that indicates whether or not an entry has been dropped or deleted from the SQL statement cache.

Figure 79. Sample onstat -g ssc Output for 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 
...
Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]