informix11.5SQL跟踪与监控

在 SQL 语句性能监控时，我们经常要了解 SQL 语句执行了多长时间； SQL 语句运行时占用了多少系统资源，如 CPU 占用情况、内存占用情况、磁盘 I/O 读写情况； SQL 语句等待系统资源如磁盘 I/O 及锁的时间及次数等。通过 SQL 语句对系统的资源使用及等待情况，我们可以了解到 SQL 语句运行的瓶颈，并及时调整系统资源配置，或者调整用户的应用程序。我们上面介绍的 set explain 方法，可以帮助我们了解一些 SQL 语句性能问题，但是当我们启用 SET EXPLAIN 功能时，SQL 语句性能可能已经出现了问题，为了能够让 DBA 更及时、更详细地了解 SQL 语句的资源使用情况并做出相应的调整，在 Informix 中，提供了 SQL 下钻查询特性来满足上述功能。

SQL 下钻查询特性可以收集关于系统上执行的每个 SQL 语句的统计信息，并分析语句历史。它可以帮助您回答如下问题：

SQL 语句需要多长时间
各个语句使用多少资源？
等待每个资源需要多长时间？
查询计划是什么？
统计信息存储在循环缓冲区（内存中名为 syssqltrace 的伪表）中，即存储在 sysmaster 数据库中。您可以动态地调整循环缓冲区的大小。

缺省情况下，该功能处于关闭状态，但是您可以对所有用户或一组特定用户将其打开。在启用带有缺省配置的该功能时，数据库服务器跟踪运行的上 1000 条 SQL 语句以及这些语句的概要统计信息，每个 SQL 语句占用 1K 大小的空间。

如果您想要保存大量历史信息，那么该功能需要的内存较大。 SQL 历史跟踪所需的缺省空间量为 1 兆字节。您可以根据需求增加或减少存储量。如果不想要对此使用内存，那么可以禁用 SQL 历史跟踪。

  使用 SQLTRACE 配置参数指定启动 SQL 跟踪信息

我们可以通过修改 $InformixDIR/etc/$ONCONFIG 文件中的 SQLTRACE 配置参数来控制数据库服务器启动时的缺省跟踪行为。 所设置的信息包括要跟踪的 SQL 语句数目和跟踪方式。

SQLTRACE 配置参数语法：

SQLTRACE [Level=off|low|med|high],
 [Ntraces=number of traces],
 [Size=size of each trace buffer],[Mode=global|user]


其中：

level字段，可以指定以下某个值：

Low：它用于捕获语句统计信息、语句文本和语句迭代器。当启用 SQL 跟踪时，它是缺省的跟踪级别。
Medium：此跟踪级别捕获低级跟踪中包含的所有信息，再加上表名、数据库名称和存储过程堆栈。
High：此跟踪级别捕获中级跟踪中包含的所有信息，再加上主变量。
Off：这不指定 SQL 跟踪。系统缺省为 OFF
ntraces字段，指定要跟踪的 SQL 语句的数目，其范围是 : 500 -2147483647 。

size字段， 指定跟踪缓冲区大小的千字节数。每个 SQL 语句的跟踪信息使用一个跟踪缓冲区，如果超过了此缓冲区大小，那么数据库服务器丢弃已保存的数据。其范围是 : 1K-100K 。

mode字段， 指定以下任意一项：

Global：跟踪系统上的所有用户，它是缺省值。
User：跟踪指定用户（如果想要获取一小组用户正在运行的 SQL 样本，那么指定此项）。
在设置 SQLTRACE 参数时，我们需要考虑以下一些内容：

关于 SQLTRACE 缓冲区的问题：
我们收集到的统计信息都保存在内存的缓冲区中，它的大小 =Ntraces*Size，如果收集的语句数量越多、收集信息越详细，需要的缓冲区就越大，占用的内存资源也就越多。因此，在配置时，要考虑自己的实际情况来选择。另外，这个缓冲区是一个循环缓冲区，当缓冲区大小不够时，它会将旧的信息丢掉，因此，如果需要保存 SQL 语句跟踪历史，要考虑它的大小问题。当我们将 SQL 跟踪特性关闭时，保存在 SQLTRACE 缓冲区中的信息也会丢掉，如果需要保存，在关闭 SQL 跟踪特性关闭前，请将这些信息保存到表或文件中，防止丢失。

关于 size 参数
size 参数的大小主要由 SQL 语句的大小及 Level 来决定。不同的 level，所收集的信息量不同，Medium 会比 low 收集的信息量大，同样，High 会比 Medium 收集的信息量大。因此，在选择时，要充分考虑好我们收集统计信息的用途。通常，Low Level 比较适合于错误诊断及性能调优，High Level 常用来做系统负载重现功能。 Medium Level 也主要用于一些错误诊断场合。

关于 Mode 参数
global 模式用于跟踪系统上所有用户的统计信息，因此，当在一个比较繁忙的系统上，可能很快就会收集到大量的信息，同时，所有用户的信息都包含在一起，分析时也比较繁琐，通常，选择 global 模式，主要用于比较系统上所有会话的资源使用情况，或者是我们不清楚要分析哪一个具体用户的资源使用情况。一般情况下，我们会使用 user 模式，这样，我们分析起来会比较清晰，同时，占用的系统资源也不会太多。

以下语句指定了数据库服务器将收集关于系统上所有用户执行的最多 2000 条 SQL 语句的低级别信息，并将分配大约 4 兆字节的内存（2000 * 2 千字节）。

SQLTRACE level=LOW,ntraces=2000,size=2,mode=global


以下语句指定了数据库服务器将收集关于系统上所有用户执行的最多 2000 条 SQL 语句的高级别信息，并将分配大约 4 兆字节的内存（2000 * 2 千字节）。

SQLTRACE level=high,ntraces=2000,size=2,mode=global


使用 SQLTRACE 配置参数方式比较适合于设置一些缺省的配置，如果需要经常变化，使用 ADMIN API 命令方式则比较方便。

使用 ADMIN API 命令指定启动 SQL 跟踪信息

如果不想设置 SQLTRACE 配置参数以重新启动服务器，那么可执行以下 ADMIN API 命令，该命令可提供与设置 SQLTRACE 相同的功能。使用 ADMIN API 的 task() and admin() 函数可以动态改变 SQLTRACE 的设置，不需要重新启动服务器，使用更加灵活。只有 Informix 用户有权力执行 ADMIN API 命令。通过 ADMIN API 命令修改的 SQLTRACE 的设置不会保存到 SQLTRACE 配置参数中，因此，当服务器重新启动后，SQLTRACE 配置参数值将会生效。

当 SQLTRACE 配置参数为 OFF，我们通过下面的 ADMIN API 命令启动 SQL 跟踪信息时，系统会使用缺省的设置值。 即：

SQLTRACE level=low,ntraces=1000,size=1,mode=global

execute function task("set sql tracing on");


我们也可以指定自己设置的值。以下语句指定了数据库服务器将收集关于系统上所有用户执行的最多 2000 条 SQL 语句的低级别信息，并将分配大约 4 兆字节的内存（2000 * 2 千字节）。

execute function task("set sql tracing on", 2000,"2k","low","global");


停止收集 SQL 语句信息，我们可以执行：

execute function task("SET SQL TRACING OFF");


当执行上述命令后，会关闭 SQL 跟踪功能，同时，跟踪缓冲区中的内容会丢失。

启用特定用户的 SQL 历史跟踪

启用特定用户的 SQL 历史跟踪，不仅可以节省系统内存资源，而且分析起来也更加清晰，一般我们会建议采用这种方式。在以用户方式启用 SQL 跟踪系统后，就可以启用对特定用户的跟踪。在指定 user 作为 SQLTRACE 配置参数中的方式后，必须执行管理 API task() 或 admin() 函数来打开对特定用户的 SQL 历史跟踪。

如果全局 SQL 跟踪被禁用，那么可执行管理 API task() 或 admin() 函数来启用对特定用户的 SQL 跟踪。

要启用特定用户的 SQL 历史跟踪，您可以执行 task 或 admin() 函数，并指定 set sql tracing on 和定义用户的信息。

如果需要对 session 30 启用 SQL 语句跟踪，我们可以执行：

execute function task("set sql tracing on", 1000, 1,"low","user");
 execute function task("set sql user tracing on",30)


如果需要对当前连接到系统的用户（只要它们未作为用户 root 或 Informix 登录）启用 SQL 语句跟踪，我们可以执行：

dbaccess sysadmin -<<END
 execute function task("set sql tracing on", 1000, 1,"low","user");
 select task("set sql user tracing on", sid)
 FROM sysmaster:syssessions WHERE username not in ("root","Informix");
 END


如果需要停止对 session 30 进行跟踪，我们可以执行：

execute function task( “ set sql user tracing off",30);


SQL 跟踪信息显示及分析

我们可以通过执行 onstat -g his 命令或查询sysmaster数据库中的 syssqltrace 伪表来获取 SQL 下钻查询信息。

onstat – g his 命令

我们可以通过执行 onstat -g his 命令来显示 SQL 下钻查询信息。

语法：

>>-onstat-- -g--his--------------------------------------------><


onstat -g his 选项显示 SQLTRACE 收集到的信息并以格式化输出。缺省情况下，只有 DBSA 可以查看 onstat -g his syssqltrace 信息。然而，当 UNSECURE_ONSTAT = 1 时，所有的用户可以查看此信息。 onstat -g his 选项会将所收集到的所有信息全部显示出来，目前还不能够只显示某一特定的用户会话或 SQL 语句的信息。因此，它比较适合小数据量的显示，它的好处是比较方便。

onstat -g his 选项的输出主要包含三部分内容：trace profile, statement text and statement statistics.

Trace profile:这是 onstat -g his 命令输出的前几行信息，用于描述跟踪的级别、跟踪模式、跟踪的 SQL 语句条数、跟踪缓冲区的大小及跟踪缓冲区保持的时间。如下所示：

onstat -g his 的输出示例（Trace profile 部分）：

Statement history:

 Trace Level                  High
 Trace Mode                   User
 Number of traces            50000
 Current Stmt ID                 3
 Trace Buffer size           12264
 Duration of buffer             37 Seconds
 Trace Flags            0x00007F21
 Control Block          0x4b8cd018

 ... ...



Statement text and iterators:onstat – g his 命令输出的接下几行用来描述被跟踪的 SQL 语句以及查询中用到的迭代器及查询计划信息。 SQL 语句部分的内容根据跟踪级别的不同而有所不同。如果跟踪级别是 LOW，只是显示被跟踪的 SQL 语句及正在使用的数据库的十六进制描述。如果踪级别是 medium，将显示数据库的名称，SQL 语句，SQL 语句中用到的表名称及存储过程的调用堆栈信息。如果跟踪级别是 high，除了显示 medium level 的信息外，还将显示 SQL 语句中用到的宿主变量信息。如下所示：

onstat -g his 的输出示例（Statement text and iterators: 部分）：

... ...
 Statement # 3:     @ 0x4b907018
 Database:        sysmaster
 Statement text:
  select first 2 substr(tabname,1,20) as table, isreads as reads from
    sysptprof where isreads > 10 order by isreads desc

  SELECT using tables [ systabnames sysptntab ]

 Iterator/Explain
 ================
    ID   Left  Right   Est Cost   Est Rows   Num Rows    Partnum Type
     3      0      0          9         33         40         20 Seq Scan
     4      0      0          1        100          1         15 Index Scan
     2      3      4         28         33         40          0 Nested Join
     1      2      0          1         33          2          0 Sort
     4      0      0         18         92         92     Disk Scan
     2      3      4        287       1380       5060     Nested Join
     1      2      0          1          1       5060     Insert

 ... ...



Statement information and statistics:接下来的部分主要包含 SQL 语句及性能统计信息，也是我们进行监控的最主要的部分。通过它，我们可以发现 SQL 语句相关的内存使用情况、磁盘 I/O 情况、锁使用及争用情况，CPU 使用情况，排序及索引使用情况等信息。据此，我们可以进行相应的调整。 我们又可以将其细化为下面三部分内容：

Statement information：描述下边一些信息：

运行命令的用户的用户标识
数据库会话标识
数据库的名称
SQL 语句的类型
SQL 语句执行的持续时间
该语句完成的时间
带有语句类型的 SQL 语句文本或函数调用列表（也称为堆栈跟踪），例如： procedure1() calls procedure2() calls procedure3()
RSAM statistics：描述下边一些信息：

缓冲区读取和写入的数目
页面读取和写入的数目
排序和磁盘排序的数目
锁请求和等待的数目
逻辑日志记录的数目
SQL statistics：描述下边一些信息：

估计的行数
优化器估计成本
返回的行数
SQL/ISAM 错误
数据库隔离级别
SQL 语句内存使用量。
onstat -g his 的输出示例（Statement information and statistics 部分）：

... ...

 Statement information:
  Sess_id  User_id  Stmt Type          Finish Time    Run Time
  26       501      SELECT             23:31:01       0.0054

 Statement Statistics:
  Page       Buffer     Read       Buffer     Page       Buffer     Write
  Read       Read       % Cache    IDX Read   Write      Write      % Cache
  0          410        100.00     0          0          0          0.00

  Lock       Lock       LK Wait    Log        Num        Disk       Memory
  Requests   Waits      Time (S)   Space      Sorts      Sorts      Sorts
  0          0          0.0000     0.000 B    1          0          1

  Total      Total      Avg        Max        Avg        I/O Wait   Avg Rows
  Executions Time (S)   Time (S)   Time (S)   IO Wait    Time (S)   Per Sec
  1          0.0108     0.0108     0.0054     0.000000   0.000000   370.1291

  Estimated  Estimated  Actual     SQL        ISAM       Isolation  SQL
  Cost       Rows       Rows       Error      Error      Level      Memory
  28         33         2          0          0          CR         34176



输出描述 ：

Page Read：已从磁盘读取的页数
Buffer Reads: 从缓冲池读取而不是从磁盘读取页面的次数
Read % Cache: 应从缓冲池读取页面的次数的百分比
Buffer IDX Read: 索引页的缓冲区读取数
Page Write: 写入磁盘的页数
Buffer Write：修改并发送回缓冲池的页数
Write % Cache：页面写入缓冲池而不是写入磁盘的次数的百分比
Lock Requests：该语句所需的锁的总数
Lock Waits：该 SQL 语句等待锁的次数
LK Wait Time：在该 SQL 语句执行期间，用于等待锁的时间（以秒为单位）
Log Space：
Num Sorts：用于执行语句的排序总数
Disk Sorts：对于该 SQL 语句，对磁盘执行的排序的次数
Memory Sorts
Total Executions：该语句已执行的总次数，或者该游标重用的次数
Total Time：执行该语句的总时间（以秒为单位）
Avg Time：执行该语句的平均时间（以秒为单位）
Max Time：运行 SQL 语句的总时间（以秒为单位），不包括应用程序使用的任何时间
LK Wait Time：语句等待应用程序锁的时间量
Avg IO Wait：语句等待 I/O 的时间量，不包括任何异步 I/O 。
Avg Rows Per Sec：该语句每秒产生的平均行数
Estimated Cost：与 SQL 语句关联的代价
Estimated Rows：返回的估计行数，由语句的优化程序估计
Actual Rows：对于该语句返回的行数
SQL Error：SQL 错误号
ISAM Error：RSAM/ISAM 错误号
Isolation Level：该语句运行时使用的隔离级别
SQL Memory：该 SQL 语句需要的字节数
syssqltrace 伪表

在 Informix 中，提供了 3 张内存伪表用来保存 SQL 跟踪信息。其中，syssqltrace 表用来提供被跟踪的每一个 SQL 语句的详细的跟踪信息。它的大小由 ntraces*size 来决定。我们可以动态调整其大小。我们可以通过 sysmaster 数据库来访问这 3 张内存伪表。

syssqltrace 表的输出信息同执行 onstat -g his 命令输出的 Statement information and statistics 部分的内容相似。

由于我们可以使用 SQL 语句访问 SQL 跟踪信息，所以，采用该种方法比较适合查询有关单个 SQL 语句或一组 SQL 语句的详细跟踪信息。

syssqltrace 表的基本结构如下：

列 类型 描述
sql_id int8 唯一 SQL 执行标识
sql_address int8 代码块中的语句的地址
sql_sid int 运行 SQL 语句的用户的数据库会话标识
sql_uid int 运行 SQL 的语句的用户标识
sql_stmttype int 语句类型
sql_stmtname char(40) 显示为单词的语句类型
sql_finishtime int 此语句的完成时间（UNIX）
sql_begintxtime int 此事务的启动时间
sql_runtime float 语句执行时间
sql_pgreads int 此 SQL 语句的磁盘读取数
sql_bfreads int 此 SQL 语句的缓冲区读取数
sql_rdcache float 从缓冲池读取页的时间百分比
sql_bfidxreads int 索引页缓冲区读取数
sql_pgwrites int 写入磁盘的页数
sql_bfwrites int 已修改并返回到缓冲池的页数
sql_wrcache float 页已写入缓冲池，但未写入磁盘的时间百分比
sql_lockreq int 此 SQL 语句所需锁总数
sql_lockwaits int SQL 语句等待锁的次数
sql_lockwttime float SQL 语句期间系统等待锁定的时间
sql_logspace int 逻辑日志中 SQL 语句所用空间量
sql_sorttotal int 为语句运行的排序数
sql_sortdisk int 磁盘上运行的排序数
sql_sortmem int 内存中运行的排序数
sql_executions int SQL 语句运行的次数
sql_totaltime float 运行语句所用时间总量
sql_avgtime float 运行语句所用平均时间量
sql_maxtime float 执行 SQL 语句所用最大时间量
sql_numiowaits int I/O 操作必须等待的次数
sql_avgiowaits float SQL 语句必须等待的平均时间量
sql_totaliowaits float SQL 语句必须等待 I/O 的时间总量。这不包含任何异步 I/O 。
sql_rowspersec float 产生的平均行数（每秒）
sql_estcost int 与 SQL 语句关联的成本
sql_estrows int 按照优化器的预测为 SQL 语句返回的预估行数
sql_actualrows int 为 SQL 语句返回的行数
sql_sqlerror int SQL 错误号
sql_isamerror int RSAM/ISAM 错误号
sql_isollevel int SQL 语句的隔离级别。
sql_sqlmemory int 执行 SQL 语句所需字节数
sql_numiterators int 语句所用迭代器数
sql_database char(128) 数据库名
sql_numtables int 执行 SQL 语句中所用表数
sql_tablelist char(4096) SQL 语句中直接引用的表名列表。如果 SQL 语句击发对其他表执行语句的触发器，将不列出其他这些表。
sql_statement char(1600) 已运行的 SQL 语句


如果我们要查看会话 30 的 SQL 跟踪信息，可以执行：

select * from syssqltrace where sql_id =30;


syssqltrace_info 伪表

syssqltrace_info 伪表也是一张内存表，用来保存 tracing profile 信息。我们可以通过 sysmaster 数据库来访问这张内存伪表。 tracing profile 信息主要用于描述跟踪的级别、跟踪模式、跟踪的 SQL 语句条数、跟踪缓冲区的大小及跟踪缓冲区保持的时间。

syssqltrace_info 伪表的输出内容同执行 onstat -g his 命令输出的 tracing profile 部分的内容相似。

syssqltrace_info 表的基本结构如下：

列 类型 描述
flags integer SQL 跟踪标志
ntraces integer 要跟踪的项数
tracesize integer 为各 SQL 跟踪项存储的文本的大小
duration integer 跟踪缓冲区（以秒为单位）
sqlseen int8 启动或调整大小以来跟踪的 SQL 项数
starttime integer 跟踪的启用时间
memoryused int8 SQL 跟踪所用内存的字节数


syssqltrace_iter 伪表

syssqltrace_iter 伪表也是一张内存表，用来保存 Statement text and iterators 信息。我们可以通过 sysmaster 数据库来访问这张内存伪表。 Statement text and iterators 信息主要用来描述被跟踪的 SQL 语句以及查询中用到的迭代器及查询计划信息。这个表常用来查询特定 SQL 语句的查询计划信息。

syssqltrace_iter 伪表的输出内容同执行 onstat -g his 命令输出的 Statement text and iterators 部分的内容相似。

syssqltrace_iter 表的基本结构如下：

列 类型 描述
sql_id int8 SQL 执行标识
sql_address int8 SQL 语句块的地址
sql_itr_address int8 迭代器的地址
sql_itr_id int 迭代器标识
sql_itr_left int 向左的迭代器标识
sql_itr_right int 向右的迭代器标识
sql_itr_cost int 迭代器成本
sql_itr_estrows int 迭代器预估行数
sql_itr_numrows int 迭代器实际处理的行数
sql_itr_type int 迭代器类型
sql_itr_misc int 迭代器杂项标志
sql_it_info char(256) 显示为文本的迭代器杂项标志


如果我们要查看 sql_id=15 的查询所使用的迭代器及类型，我们可以执行：

select  sql_itr_type,
        substr(sql_itr_info,1,20) as iterator_info,
        sql_itr_numrows
 from    syssqltrace_iter
 where   sql_id = 14;



通过执行 onstat -g his 命令或查询sysmaster数据库中的 syssqltrace 伪表，我们可以查看系统中运行的 SQL 语句、执行 SQL 所用的资源、运行 SQL 花费的时间、磁盘 / 页面 / 缓冲区读和写的数量、使用的锁数量、排序数量和使用的内存量。另外，还可以查看 Informix 优化器估计的运行 SQL 所要花费的时间。这样，我们可以了解到 SQL 语句占用资源情况及存在的资源瓶颈，并进行相应的调整。另外，我们还可以对比 Informix 优化器估计的返回行数和实际的返回行数（sql_estrows 和 sql_actualrows）。如果这两个数值差异很大，就说明 Informix 优化器并不掌握关于表中行和索引数量的正确的统计数据。这意味着需要运行 update statistics，从而向优化器提供正确的数据。

另外，通过查询 syssqltrace 表，我们还可以发现系统中运行时间最长的 SQL 查询、带有太多表连接操作的查询、不希望的连接类型、返回记录太多的查询、存在锁等待的查询等信息，以便于我们进行及时的调整及改进来提高 SQL 语句的性能。

如果我们要发现运行时间最长的 SQL 查询，我们可以执行：

select  first 5
        substr(sql_statement,1,50) as statement
        , sql_avgtime as Average_Time
        , sql_executions as Number_of_times
 from syssqltrace
 order by sql_avgtime desc ；



如果我们要发现返回记录太多的查询，我们可以执行：

select  first 5
        sql_estrows as est_rows
        , sql_actualrows as actual_rows
        , substr(sql_statement,1,30) as statement
 from syssqltrace
 order by sql_actualrows desc ；