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

Invalidating a statement

You can selectively invalidate entries of your choice by setting the sysmaster:syssscelem:valid column to 0 as user Informix

For example, Figure 2 shows the entries that the onstat -g ssc command displays before and after invalidating a query from the items table

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

Figure 2. Sample onstat -g ssc command output for an invalidate entry

onstat -g ssc snipit

...
Statement Cache Entries: 

uniqid lru hash ref_cnt hits  flag  heap_ptr       database           user
----------------------------- ---- ---------------------------------------------------
...
    7   1 2404       0    0      F  463d0438       stores_demo        informix
  
select count(*) from items
  
...

Invalidate it:
 
update syssscelem set valid = 0 where uniqid = 7;

Confirm it is invalid with onstat -g ssc:
 
Statement Cache Entries: 
   
uniqid lru hash ref_cnt hits  flag  heap_ptr       database           user
----------------------------- ---- ---------------------------------------------------
...
    7   1 2404       0    0     DF  463d0438       stores_demo        informix
    
select count(*) from items

The user can confirm a flag of ‘D’ in ‘onstat -g ssc’ output and can query sysmaster:syssscelem to confirm ‘valid’ column is 0.

Note: Invalid entry cannot be changed to valid.

Locking a statement

You can lock an entry of your choice in the Statement Cache even when UPDATE STATISTICS is executed on tables in the sql statement.

For example, Figure 3 shows the entries that the onstat -g ssc command displays after UPDATE STATISTICS was executed on the items table between the execution of the first and second SQL statements.

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

Figure 3. Sample onstat -g ssc command output for locking an entry

onstat -g ssc snipit

...
Statement Cache Entries: 

uniqid lru hash ref_cnt hits  flag  heap_ptr       database           user
----------------------------- ---- ---------------------------------------------------
...
    7   1 2404       0    0      F  463d0438       stores_demo        informix
  select count(*) from items
  
  3  232       1    0   -F  aa8b020  vjp_stores   virginia

...

Lock it:
 
update syssscelem set locked = 1 where uniqid = 7;

Confirm it is locked with onstat -g ssc:
 
Statement Cache Entries: 
   
uniqid lru hash ref_cnt hits  flag  heap_ptr       database           user
----------------------------- ---- ---------------------------------------------------
...
    7   1 2404       0    0     FL  463d0438       stores_demo        informix
    
select count(*) from items

The user can confirm a flag of ‘L’ in ‘onstat -g ssc’ output and can query sysmaster:syssscelem to confirm ‘locked’ column is 1.

Note: Statements can be locked and unlocked as many times as desired.