Introduction

This document describes the functionality of the SQL Statement Cache (SSC) in the Informix® Dynamic Server (IDS). The SSC is an area in the shared memory that is used to store and share data structures for identical statements across user sessions. The following are the benefits of using the SSC feature:

• Reduced memory consumption because query data structures are shared among different users
• Faster query processing time because for statements present in the cache, the database server can skip parsing and optimization phases, thereby giving a better response to queries.

When the SSC feature is turned on, the database server does the following:

1. When a user user1 fires an SQL statement, the database server checks the statement cache to see if an identical statement is present or not.
2. If the statement is not present in the cache, then the database server will parse, optimize and execute the statement. It will also check if this statement qualifies to be entered in the cache. If it does, then it will add entry into the cache for the statement.
3. When user user2 executes the same statement, the database server will check the cache and find this statement in the cache. So it will not parse and optimize the statement again. It just executes the statement using the information in the cache.

There are a set of conditions that a statement should satisfy for it to enter the cache. Some of these include:

1. The Statement should be one of SELECT, UPDATE, DELETE or INSERT.
2. The Statement should not contain any user defined routines.
3. It cannot contain any Temporary or Remote tables
4. It should not contain subqueries.

For a detailed set of conditions, please refer to the IBM Informix Guide to SQL: Syntax.

Back to top

SSC Configuration Parameters

The SQL Statement cache can be configured according to every application using a set of parameters. These parameters are also used to determine which statements should be entered in the cache. We will briefly look at each parameter.

1. STMT_CACHE_HITS - This parameter is used to specify the number of times a query should get executed before the database server makes a fully cached entry in the cache. If the number of times the query is executed is less than this value, then the server will make a key-only entry in the cache for this statement which contains only the statement text. For every subsequent execution, the number of hits column is incremented. When the number of times the statement is executed becomes equal to the value of this parameter, a fully cached entry is made into the cache, which contains the statement and the query data structures.
2. STMT_CACHE_SIZE - This variable is used to specify the size of the Statement cache. The default value of the SSC size is 512 kilobytes. We will discuss some things to consider while configuring this parameter, in the next section.
3. STMT_CACHE_NOLIMIT - This variable specifies whether or not to limit the size of the cache to the STMT_CACHE_SIZE value. By default, the server will enter the statement even if the size of the cache exceeds the STMT_CACHE_SIZE value.
4. STMT_CACHE_NUMPOOL - When the server needs to create a fully-cached entry in the Statement cache, it allocates memory from a single pool by default. When the number of users using the application is very high, this may have an adverse effect on performance. In such cases, the number of pools may be configured to a higher number using this parameter.

Back to top

Using the onstat utility to monitor SSC usage

The onstat -g ssc option displays fully cached entries in the statement cache. The hits column in the output indicates the number of times the same statement has been executed. Therefore, a higher value in the hits column implies that the SQL Statement cache has been used effectively to share query structures thereby improving performance. The #hits column corresponds to the STMT_CACHE_HITS configuration parameter.

The flags column in the output indicates whether the entry is fully cached or not. When the number of times a statement is executed becomes equal to the #hits value, then a fully cached entry is made in the cache. The value of F in the flags column indicates that the entry is fully-cached. Key-only entries, where only the statement test is entered in the cache, are indicated by a - in the flags column. However these entries are found only in onstat -g ssc all output.

Listing 1: Using onstat -g ssc to monitor fully-cached entries

IBM Informix Dynamic Server Version 9.40.UC1     -- On-Line -- Up 04:34:47 
-- 32768 Kbytes 
 
Statement Cache Summary: 
#lrus   currsize  maxsize   Poolsize  #hits   nolimit 
4       551936    30720000  278528    3       1 
 
Statement Cache Entries: 
lru hash ref_cnt hits flag heap_ptr	 database           user 
----------------------------------------------------------------- 
 3  235       0    3   -F  b8b0820 	 test               uiyer 
 select count(*) from tab2, tab1 
        where tab1.htl_key = tab2.htl_key  AND tab1.doa = tab2.dt 
	 
 Total number of entries: 1.
 

onstat -g ssc all command displays both key-only entries and fully cached entries.

Listing 2: Using onstat -g ssc all to monitor all SSC entries

IBM Informix Dynamic Server Version 9.40.UC1     -- On-Line -- Up 
00:19:17 -- 32768 Kbytes 
Statement Cache Summary: 
#lrus   currsize  maxsize   Poolsize  #hits   nolimit 
4       547840    30720000  270336    3       1        
 
Statement Cache Entries: 
 
lru hash ref_cnt hits flag heap_ptr	 database           user 
------------------------------------------------------------------- 
 0   16       0    0   --  b645690 	 sysmaster          informix  
 insert into flags_text values ('systwaits', 0, 'unspecified')   
 
 0   18       0    0   --  b92d820 	 sysmaster          informix   
 insert into flags_text values ('systwaits', 4, 'mt yield 0')   
  
 insert into flags_text values ('sqltype', 46, 'SQ_WAITFOR') 
 
 ...   
 
 3  135       0    1   --  b8b2020 	 test               uiyer   
 select count(*) from 	informix.systables where tabname = 'sysindices';   
 
 3  286       0    0   --  b8b2c20 	 test               uiyer   
 insert into tab2 values (1, '10102003')   
 
 3  235       0    3   -F  b8b0820 	 test               uiyer   
 select count(*) from tab2, tab1          
         where tab1.htl_key = tab2.htl_key  AND tab1.doa = tab2.dt 
 
  Total number of entries: 256.
  

Back to top

Using the onmode utility to configure SSC parameters

The Configuration parameters can be dynamically changed by using the onmode utility.

You can use the onmode -W option to change the value of the SSC parameters as below:

onmode -W {STMT_CACHE_NOLIMIT {0|1} | STMT_CACHE_HITS amp;lt;#hitsamp;gt;}

You can also enable, turn on/off or clean up the SSC using the onmode -e amp;lt;modeamp;gt; utility, where amp;lt;modeamp;gt; := {ENABLE|ON|OFF|FLUSH}.

When the SSC mode is set to ENABLE, the SSC is only utilized by individual sessions that explicitly request use of the SSC. This allows applications that do few adhoc queries to take advantage of the SSC without having large numbers of adhoc queries from other applications waste space or force cached statements out of the SSC. Session request use of the SSC via their environment (STMT_CACHE=1), or by executing the statement SET STATEMENT CACHE ON.

When the SSC is flushed, statements that are in active use will be marked and subsequently flushed when they are released. When the SSC is turned off, it disables sharing immediately. Statements still using the SSC will continue to use the shared version until they are released.

Back to top

Tuning the SSC Configuration Parameters to improve performance

The Statement cache size plays a very important role in improving the performance of the database server. The onstat -g ssc output shows the value of this parameter in the maxsize column. The following are some tips that can be used to tune the SSC size:

1. Check whether frequently executed statements are in the cache or not. If not, then chances are that the SSC size is too small forcing these statements to be removed from the cache to allow for new statements to enter the cache. Try to increase the SSC size in this case.
2. If there are a lot of queries getting executed repeatedly, then the SSC may become full and the server may end up spending a lot of time in cleaning up the cache. To accommodate the new queries that qualify to be entered in the cache, the server will cleanup the least recently used entries from the cache. It may happen however that the new statement could have used one of these entries, but finally the query has to be parsed and optimized again, leading to more processing time. If such a situation arises, it would help to increase the SSC size so that these occurrences are reduced.

Another configuration parameter that needs to be fine-tuned based on your application needs is the STMT_CACHE_HITS parameter. Following factors can be useful in determining a value for this parameter:

1. The onstat -g ssc output shows the fully-cached entries in the cache. However, if the frequently used queries are not found in the cache, one reason could be that the number of hits value is too high, . due to whichThis means the statement is taking a long time before entering the cache. In such cases, try to reduce the STMT_CACHE_HITS value.
2. If the number of hits value is too low, the server may end up adding entries in the cache which have been executed just once, which can cause the queries that are executed often to be replaced. This can be determined by looking at the onstat -g ssc all output. If there a lot of queries having entries with F in the flags column and #hits column is just 0, check the hits column value in the output. If this column has a value of 0, this indicates that these statements are not being re-executed. Increase the STMT_CACHE_HITS parameter, so that only statements which are executed a certain number of times can enter the cache.
3. As a general rule, a higher value in the hits column of onstat -g ssc output, indicates that SSC is being efficiently used to share query structures.

Back to top

Statement cache pools

When the server needs to create a fully-cached entry in the Statement cache, it allocates memory from a single pool by default. When the number of users using the application is very high, this may have an adverse effect on performance. In such cases, the number of pools may be configured to a higher number using the STMT_CACHE_NUMPOOL variable.

You can use the onstat -g ssc pool command to monitor the number and size of sscpools. The following output shows that there are two sscpools configured. The size of each pool can be found under the totalsize column.

Listing 3: Using onstat -g ssc pool to monitor sscpools usage

IBM Informix Dynamic Server Version 9.40.UC1     -- On-Line -- Up 00:00:24 
--32768 Kbytes 
 
Pool Summary: 
name         class addr     totalsize freesize #allocfrag #freefrag 
sscpool0     V     b645020  8192      2808     2          2          
 
Blkpool Summary: 
name         class addr     size      #blks      
 
Pool Summary: 
name         class addr     totalsize freesize #allocfrag #freefrag  
sscpool1     V     b646020  8192      1424     6          2          
 
Blkpool Summary: 
name         class addr     size      #blks      

The onstat -g spi command can be used to determine whether the number of sscpools needs to be increased or not. The output shows the resources which required at least one wait before a latch could be acquired on them.

Listing 4: Using onstat -g spi to monitor resources

IBM Informix Dynamic Server Version 9.40.UC1     -- On-Line -- Up 00:00:59 
--32768 Kbytes 
 
Spin locks with waits: 
 
Num Waits   Num Loops   Avg Loop/Wait    Name 
1           1           1.00             mutex lock, name = AIOreq 
1           6           6.00             shmcb sh_lock  

The Num Waits column indicates the number of times the thread had to wait for a latch (ownership of the lock). The Num Loops column indicates the total wait time, measured by the total number of times the threads had to loop or spin before getting a latch. In the above output, the Name column does not show any sscpool. This implies that the current configured value need not be changed. However, if the Name column had shown a sscpool entry, then a high value in the num of loops column, would indicate that the number of sscpools needs to be increased

Back to top

Conclusion

Depending on the type of application, the SQL Statement cache feature can help improve the performance of your queries. In applications that use a lot of ad hoc queries, the number of times that the same query will be executed more than once will be minimal. In such cases, it is not recommended to use SSC. However in cases where the applications use a lot of prepared statements, or where the same query is executed multiple times, turning on the SSC feature can prove highly beneficial to improve the performance and get faster results.

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• SSC Configuration Parameters
• Using the onstat utility to monitor SSC usage
• Using the onmode utility to configure SSC parameters
• Tuning the SSC Configuration Parameters to improve performance
• Statement cache pools
• Conclusion
• About the author
• Comments

Next steps from IBM

• Try: Informix Ultimate Edition
• Buy: Informix Ultimate Edition

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers