Informix 内存讨论之一: SQL语句高速缓存

大夫

环境:informix 9.3/9.4
需求(通过讨论,期望可得到如statspack一样的一个性能测试报告工具),本部分仅先讨论由内存触发的优化操作:
1,从整体优化Informix数据库内存设置.
2,内存优化测试方法,算法及优化说明。

SQL语句高速缓存
需求:
1,监控SQL语句执行,减少SQL解析及内存管理时间.
2,收集SQL语句执行的物理读写,逻辑读写,执行时间,执行次数等性能数据。。



SQL语句高速缓存存储分析和优化过的SQL语句，使执行相同的SQL语句用户得到如下性能改进：
1，缩短响应时间。因绕过分析和优化步骤。
2，减少占用的内存量。因用户间共享了查询结构。

下图显示SQL语句高速缓存如何优化SQL执行及其控制参数在执行过程中的作用：


启用SQL语句高速缓存：
SET STATEMENT CACHE的优先级高于STMT_CACHE设置，但前提是必须先用STMT_CACHE参数或onmode命令启用了SQL语句高速缓存。
        ONCONFIG配置参数启用
1）如果STMT_CACHE=0（缺省值）时，数据库不缓存。
2）如果STMT_CACHE=1，则数据库会在单个用户设置STMT_CACHE环境变量为1时或应用程序中执行SET STATEMENT CACHE ON时为该用户使用的SQL语句提供高速缓存。
3）如果STMT_CACHE=2，则数据库将所有用户的SQL语句缓存，除非单个用户使用STMT_CACHE环境变量或SET STATEMENT CACHE OFF关闭该功能。
        onmode -e命令动态覆盖STMT_CACHE配置参数启用
1）如果执行onmode -e enable，则数据库会在单个用户设置STMT_CACHE环境变量为1时或应用程序中执行SET STATEMENT CACHE ON时为该用户使用的SQL语句提供高速缓存。
2）如果执行onmode -e on,则数据库将所有用户的SQL语句缓存，除非单个用户使用STMT_CACHE环境变量或SET STATEMENT CACHE OFF关闭该功能。

SQL语句符合高速缓存匹配条件：
符合以下内容的SQL语句，认为是相等的：
字母大小写，注释，空格，优化设置，并行性设置，查询文本字符串，文字。

语句具备缓存的条件：
比如符合一些规则，如满足以下条件才可以：
1）必须是SELECT，INSERT，UPDATE或DELETE语句。
2）必须仅包含内置的数据类型，且不包括如BLOB，BYTE等一些大类型。
3）不能包含用户定义的SPL。
4）不能包含临时表或远程表。
等等条件。

以下参数影响数据库是否将SQL语句放入高速缓存：
1.STMT_CACHE_HITS
指定使用了高速缓存的内容来执行该SQL语句的次数。即命中数。
1）如果STMT_CACHE_HITS=0，则SQL语句直接插入高速缓存，其中包含SQL语句的文本结构加内存结构。
2）如果STMT_CACHE_HITS !=0，且该SQL语句不存在于高速缓存中，则插入一个包含SQL语句文本的唯一关键条目到高速缓存中，该SQL语句的后续执行，会增加这个关键条目的命中数。
3）如果STMT_CACHE_HITS=唯一关键字条目的命中数，则把该SQL语句的内存结构加入到高速缓存中，变为完全SQL语句高速缓存。
2.STMT_CACHE_SIZE，STMT_CACHE_NOLIMIT
STMT_CACHE_SIZ指定SQL语句高速缓存的大小。
STMT_CACHE_NOLIMIT指定是否将“QL语句高速缓存的大小”限制为STMT_CACHE_SIZE值。
缺省STMT_CACHE_SIZE=512*1024个字节。
1）如果STMT_CACHE_NOLIMIT=1（缺省），表示不限制“SQL语句高速缓存的大小”，即使将超过STMT_CACHE_SIZE指定的大小；
2）如果STMT_CACHE_NOLIMIT=0，表示受到限制，不会超过STMT_CACHE_SIZE指定的大小。

优化说明：
onstat -g ssc 显示完全共享了条目的SQL语句信息
onstat -g ssc all 显示完全共享了条目的SQL语句信息和没完全共享但已包含唯一关键字条目的信息，显示的是整个“SQL语句高速缓存内存里的信息”

如果他们之间的输出差别很大，应考虑放宽STMT_CACHE_HITS的条件，让更多的条目得到完全缓存，减少管理开销。STMT_CACHE_HITS除了可以修改ONCONFIG参数文件外，可以使用onmode -W 命令在线动态修改，能改大不能改小。



更新STMT_CACHE_HITS参数方法：
1，更新ONCONFIG文件。
2，onmode -W命令动态修改，不保存到ONCONFIG文件。

如果SQL语句高速缓存太小，则可能发生：
1，频繁执行的SQL语句不在高速缓存中。
2，数据库花费大量时间清除SQL语句高速缓存。数据库使用阀值(STMT_CACHE_SIZE的70%)来确定何时从高速缓存中清除条目，防止分配大量内存。

更改SQL语句高速缓存大小：
onstat -g ssc all

Statement Cache Summary的#hits，为STMT_CACHE_HITS的配置值。
Statement Cache Summary的Maxsize，为STMT_CACHE_SIZE的配置值。
Statement Cache Summary的nolimit，为STMT_CACHE_NOLIMIT的配置值。
Statement Cache Summary的currsize，为SQL语句高速缓存当前使用的值。
Statement Cache Summary的#lrus   LRU队列数目。
Statement Cache Summary的poolsize  所有POOL的累积大小。
Statement Cache Entries的lur   LRU标识符。
Statement Cache Entries的hash        散列存储区标识符。
Statement Cache Entries的ref_cnt         当前使用此语句的会话数。
Statement Cache Entries的hits        执行次数（不包括第一次）
Statement Cache Entries的flag列，第一个位置为D表示该语句已被删除，“-”表示继续有效；第二个位置的值为F，表示已完全高速缓存（即在onstat –g ssc中会出现），值为“-”，表示仅缓存关键字和文本条目。
Statement Cache Entries的hits列，显示SQL语句执行次数（不包括第一次执行）。
Statement Cache Entries的heap_ptr         该语句的堆指针。
Statement Cache Entries的database        执行SQL语句的数据库
Statement Cache Entries的user        执行SQL语句的用户
Statement Cache Entries的statement         测试是否匹配时使用的语句文本



STMT_CACHE_NOLIMIT
指定当插入SQL语句到高速缓存时，由于当前正在使用而不能清除SQL语句，无法存储更多的语句，此时要插入更多的SQL语句，是否可扩展的比STMT_CACHE_SIZE大。
1，STMT_CACHE_NOLIMIT=1，缺省值。表示即使高速缓存可能大雨STMT_CACHE_SIZE大小，也插入新语句。
2，STMT_CACHE_NOLIMIT=0，表示不会扩展插入。

多个SQL语句高速缓存池
启用SQL语句高速缓存后，数据库以下情况从一个池中为查询结构分配内存：
1，在高速缓存中没找到匹配条目时。
2，当找到匹配的唯一关键字条目且命中数达到STMT_CACHE_HITS配置的参数值时。

STMT_CACHE_NUMPOOL（缺省=1）可配置多个sscpools，应确定：
sscpools数的工作负载。
SQL语句高速缓存的大小没有引起过多的内存管理。

onstat -g spi输出sscpool上的Num Waits和Num Loops，输出至少等待一次的资源。如果Num Loops太大，增加STMT_CACHE_NUMPOOL可以提高性能。

onstat -g ssc pool可以监视每个pool的使用情况。所有STMT的内存都从POOL里分配。通常pool的大小不会超过STMT_CACHE_SIZE的值。



name sscpool名称
class，始终是V，表示虚拟部分
addr   SSC池共享内存地址
totalsize   此SSC池的总大小
freesize    此SSC池的可用字节数
#allocfrag  在此SSC池中已分配的连续内存区域数
#freefrag   在此SSC池中未使用的连续内存区域数

大夫

现在的问题:
1.配置过后,可通过onstat -g ssc的方法找到缓存的SQL语句,但找不到相应的SMI接口表.
2.找不到记录如每个SQL语句的执行时间,发生的物理读写,逻辑读写,执行的用户,数据库,执行的时长等等信息。
3.这部分的性能测试标准,比如ORACLE的User calls,Recursive Call,Parse CPU to Parse Elapsd,Hard parses, Execute to Parse等等测试方法。

请知道的达官贵人,或有兴趣的好人,补充,完善一下,大家万岁


xib_jk:
这样的问题我们要顶！
我也正在困惑于这样的问题，难点在于找出sql执行慢的语句，如果找到可以用set explain 看他的执行计划，还有一个问题很困惑，SQL语句高速缓存，那么我们该从哪里入手呢？比方说7版本的informix该从哪里来统计？

sam_8286

帮忙顶一下

IFMXDBA

nice one, marked.  Thanks !

大夫

有人能补充么?

zzjijun

支持!