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