Informix SQL-Tracing

Informix 11 版本开始提供的新特性－－SQL-Tracing，我们可以利用该特性进行快速、有效的数据库性能调优。SQL-Tracing通过跟踪、捕获数据库一段时间内锁执行的SQL语句，记录SQL语句执行的详细信息，包含SQL语句执行计划、执行开销等。把这些信息存储在sysmaster数据库的三个字典表中(注意：这三个表为内存表,关闭sqltrace后，数据就删除了，但是内存空间不会收回)：

syssqltrace
syssqltrace_iter
syssqltrace_hvar

我们可以通过查询分析这些信息，有效的找到有执行慢、顺序扫描的SQL语句，可以帮助我们快速解决系统中存在的SQL性能问题。

SQL-Tracing提供打开、关闭开关，在需要进行跟踪数据库时打开，分析完成后关闭。

SQL-Tracing可以通过OAT进行开启和关闭，也可以利用OAT提供的性能分析报表(Report)进行分析，找到性能慢的SQL。

如下方法介绍通过后台SQL语句的方法打开、关闭SQL-Tracing，同时通过SQL语句找到有问题的SQL语句。

打开SQLTRACE跟踪SQL，

echo 'execute function task ("set sql tracing on",100000, "1k", "low","demodb");' | dbaccess sysadmin

说明：

demodb为跟踪的数据库名

100000为最多跟踪的SQL语句个数，超过这个数字时，将最早跟踪的SQL删除

1k为每个SQL占用的内存，对于有特别大的SQL语句，需要设置更大的值，如2k,4k

关闭SQLTRACE功能

echo ' execute function sysadmin:task("SET SQL TRACING OFF"); ' | dbaccess sysadmin

说明：跟踪分析完成后，一定要关闭。SQL-Tracing开启下将对系统有2%－5%的性能消耗。另外，关闭后，跟踪的信息（内存）将字典释放，故一定要分析完成后，再关闭，或者定期把捕获的信息转存到自定义的表(创建三个和sql－tracing字典表一致的表即可)中，供进一步分析使用。

分析

我们可以对SQL-Tracing捕获的结果进行分析，

--Sequence Scan SQL

select distinct sql_statement

     from sysmaster:Syssqltrace t

     inner join sysmaster:syssqltrace_iter i

     on t.sql_id = i.sql_id

     where i.sql_itr_info='Seq Scan'

     ;

查询速度慢SQL

可以通过不同的指标进行排名

echo "select first 20 * from sysmaster:syssqltrace order by sql_totaltime"| dbaccess demodb

sql_id            3

sql_address       2103269416

sql_sid           147

sql_uid           502

sql_stmttype      2

sql_stmtname      SELECT

sql_finishtime    1262581727

sql_begintxtime   306465594

sql_runtime       9.335050063883

sql_pgreads       0

sql_bfreads       1166108

sql_rdcache       100.0000000000

sql_bfidxreads    0

sql_pgwrites      0

sql_bfwrites      0

sql_wrcache       0.00

sql_lockreq       1000000

sql_lockwaits     0

sql_lockwttime    0.00

sql_logspace      0

sql_sorttotal     0

sql_sortdisk      0

sql_sortmem       0

sql_executions    1

sql_totaltime     28.00198770185

sql_avgtime       28.00198770185

sql_maxtime       9.335050063883

sql_numiowaits    0

sql_avgiowaits    0.00

sql_totaliowaits  0.00

sql_rowspersec    0.107123153401

sql_estcost       53816

sql_estrows       1

sql_actualrows    1

sql_sqlerror      0

sql_isamerror     0

sql_isollevel     2

sql_sqlmemory     16128

sql_numiterators  2

sql_database      <None>

sql_numtables     0

sql_tablelist     None

sql_statement     select count(city) from customer_t where city matches 'citya* '

sql_stmtlen       63

sql_stmthash      2789718969

sql_pdq           0

sql_num_hvars     0

sql_dbspartnum    8388672

注意事项：

1.捕获SQL个数的设置，内存消耗问题。

在SQLTRACE开启时，需要使用大量的虚拟内存段，该内存段使用的量和需要捕获的SQL语句个数及大小有关系：

如：

echo 'execute function task ("set sql tracing on",100000, "1k", "low","demodb");' | dbaccess sysadmin

需要的内存空间为： 100000 X 1k=100M

如：

echo 'execute function task ("set sql tracing on",100000, "2k", "low","demodb");' | dbaccess sysadmin

需要的内存空间为： 100000 X 2k=200M

如：

echo 'execute function task ("set sql tracing on",1000000, "5k", "low","dwadb");' | dbaccess sysadmin

则需要5G的内存。

所以需要根据实际情况来设置合理大小。

不要设置过大，否则将导致虚拟内存过大，可能把服务器的内存都使用完毕。当SQLTRACE停止后，不能释放，重启Informix后才可以释放。

可以通过onstat -g seg和onstat -m监控在sqltrace开启时需要使用的内存情况。

2.sysadmin问题

在11.5多少版本中，sqltrace将在sysadmin数据库中参数几个表mon_sys*trace*,这些表在SQLTRACE关闭后，数据仍然不释放。需要手工进行truncate释放空间。在11.7版本,SQLTRACE进行了改进，不再产生类似的表数据。

特别是sysadmin创建rootdbs情况，当rootdbs较小时，可能导致rootdbs空间满的问题。可以采取的措施，将sysadmin数据库从rootdbs迁移到其他dbspace上，可以在线完成，不需重启数据库。

由于保存的SQLTRACE信息在sysadmin数据库存储大量的信息，建议将sysadmin数据库从rootdbs中迁移到其他的数据库表空间中。步骤如下：

重建sysadmin
当需要将sysadmin数据库的dbspace修改为非rootdbs，我们需要重建。

方法如下：

baccess sysadmin <<!
execute function task("reset sysadmin", "bigdatadbs");
!

输出内容：
Database selected.

(expression)  SCHAPI: 'sysadmin' database will be moved to 'bigdatadbs'. See on
              line message log.

1 row(s) retrieved.
Database closed.

查看online.log确认，onstat -m
15:29:24  SCHAPI: 'sysadmin' database will be moved to 'bigdatadbs'. See online message log.
15:29:24  Building 'sysadmin' database ...
15:29:39  Unloading Module <SPLNULL>

通过系统表查看是否已经修改成功：
dbaccess sysmaster
SELECT trim(name) dbname,trim(owner) owner,created,
TRIM(DBINFO('dbspace',partnum)) AS dbspace,
CASE WHEN is_logging+is_buff_log=1 THEN "Unbuffered logging"
     WHEN is_logging+is_buff_log=2 THEN "Buffered logging"
     WHEN is_logging+is_buff_log=0 THEN "No logging"
ELSE "" END Logging_mode
FROM sysdatabases;

-------------------------------------------------------------------------------
dbname        sysadmin
owner         informix
created       09/30/2011
dbspace       bigdatadbs
logging_mode  Unbuffered logging