Informix 11.5 SQL 语句性能监控方法及实现（2）

字数 14867阅读 2314评论 0赞 0

<DIV>
<DIV class=tit>Informix 11.5 SQL 语句性能监控方法及实现(2)</DIV>
<TABLE style="WIDTH: 100%; TABLE-LAYOUT: fixed">
<TBODY>
<TR>
<TD>
<DIV id=blog_text class=cnt>
<P><A name=1.3.SQL语句优化|outline><SPAN class=smalltitle><STRONG><FONT size=3>SQL 语句优化</FONT></STRONG></SPAN></A></P>
<P>通过对 SQL 语句查询计划的分析，我们可以知道 SQL 语句在执行过程中是采用什么样的访问方法，是顺序扫描还是索引扫描；表之间连接采用什么样的方法，是嵌套循环连接还是哈希连接；表之间访问的顺序是什么；是否产生了临时表；该查询的成本是多少。依此，我们就可以考虑，为了提高 SQL 语句性能，我们是不是要创建合适的索引， 是不是要调整一下表之间连接的顺序，是不是要修改一下 SQL 语句的写法等。通常，我们在调整时，可以比较一下改变之前及改变之后的查询成本，保证查询成本有一个明显的减少。另外，我们还可以通过设置 OPTCOMPIND 参数来指定数据访问方法 ；通过访问计划指示、连接次序指示、连接计划指示、目标指示来指定数据访问方法、表连接顺序、表连接方法及数据返回结果集；通过执行 update statistics 语句提高 SQL 语句性能。关于 OPTCOMPIND 参数及查询指示的具体使用方法，请参考 Informix 信息中心相关内容。</P><BR>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="72">
<TBODY sizcache="1" sizset="72">
<TR>
<TD><a class="example-image-link" href="http://www.ibm.com/i/v14/rules/blue_rule.gif" width="100%" data-lightbox="example-set"><img class="example-image" src="http://www.ibm.com/i/v14/rules/blue_rule.gif" width="100%"    height=1></a><BR><a class="example-image-link" href="http://www.ibm.com/i/c.gif" data-lightbox="example-set"><img class="example-image" src="http://www.ibm.com/i/c.gif"  border=0   width=8 height=6></a></TD></TR></TBODY></TABLE>
<TABLE class=no-print cellSpacing=0 cellPadding=0 align=right sizcache="2" sizset="73">
<TBODY sizcache="2" sizset="74">
<TR align=right sizcache="2" sizset="74">
<TD sizcache="2" sizset="74"><a class="example-image-link" href="http://www.ibm.com/i/c.gif" width="100%" data-lightbox="example-set"><img class="example-image" src="http://www.ibm.com/i/c.gif" width="100%"    height=4></a><BR>
<TABLE border=0 cellSpacing=0 cellPadding=0 sizcache="2" sizset="74">
<TBODY sizcache="1" sizset="74">
<TR>
<TD vAlign=center><a class="example-image-link" href="http://www.ibm.com/i/v14/icons/u_bold.gif" data-lightbox="example-set"><img class="example-image" src="http://www.ibm.com/i/v14/icons/u_bold.gif"  border=0   width=16 height=16></a></TD>
<TD vAlign=top align=right><A class=fbox href="http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0904zhanggy2/index.html#main" cmimpressionsent="1"><STRONG><FONT color=#996699 face=Verdana>回页首</FONT></STRONG></A></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR><BR>
<P><A name=2.SQL下钻查询特性|outline><SPAN class=atitle><STRONG><FONT size=5>SQL 下钻查询特性</FONT></STRONG></SPAN></A></P>
<P>在 SQL 语句性能监控时，我们经常要了解 SQL 语句执行了多长时间； SQL 语句运行时占用了多少系统资源，如 CPU 占用情况、内存占用情况、磁盘 I/O 读写情况； SQL 语句等待系统资源如磁盘 I/O 及锁的时间及次数等。通过 SQL 语句对系统的资源使用及等待情况，我们可以了解到 SQL 语句运行的瓶颈，并及时调整系统资源配置，或者调整用户的应用程序。我们上面介绍的 set explain 方法，可以帮助我们了解一些 SQL 语句性能问题，但是当我们启用 SET EXPLAIN 功能时，SQL 语句性能可能已经出现了问题，为了能够让 DBA 更及时、更详细地了解 SQL 语句的资源使用情况并做出相应的调整，在 Informix 中，提供了 SQL 下钻查询特性来满足上述功能。</P>
<P>SQL 下钻查询特性可以收集关于系统上执行的每个 SQL 语句的统计信息，并分析语句历史。它可以帮助您回答如下问题：</P>
<UL>
<LI>SQL 语句需要多长时间 
<LI>各个语句使用多少资源？ 
<LI>等待每个资源需要多长时间？ 
<LI>查询计划是什么？ </LI></UL>
<P>统计信息存储在循环缓冲区（内存中名为 syssqltrace 的伪表）中，即存储在 sysmaster 数据库中。您可以动态地调整循环缓冲区的大小。</P>
<P>缺省情况下，该功能处于关闭状态，但是您可以对所有用户或一组特定用户将其打开。在启用带有缺省配置的该功能时，数据库服务器跟踪运行的上 1000 条 SQL 语句以及这些语句的概要统计信息，每个 SQL 语句占用 1K 大小的空间。</P>
<P>如果您想要保存大量历史信息，那么该功能需要的内存较大。 SQL 历史跟踪所需的缺省空间量为 1 兆字节。您可以根据需求增加或减少存储量。如果不想要对此使用内存，那么可以禁用 SQL 历史跟踪。</P>
<P><A name="2.1. 使用 SQLTRACE 配置参数指定启动 SQL 跟踪信息|outline"><SPAN class=smalltitle><STRONG><FONT size=3>使用 SQLTRACE 配置参数指定启动 SQL 跟踪信息</FONT></STRONG></SPAN></A></P>
<P>我们可以通过修改 $InformixDIR/etc/$ONCONFIG 文件中的 SQLTRACE 配置参数来控制数据库服务器启动时的缺省跟踪行为。 所设置的信息包括要跟踪的 SQL 语句数目和跟踪方式。</P>
<P><STRONG>SQLTRACE 配置参数语法：</STRONG></P>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="75">
<TBODY sizcache="1" sizset="75">
<TR>
<TD class=code-outline>SQLTRACE [Level=off|low|med|high], 
 [Ntraces=number of traces], 
 [Size=size of each trace buffer],[Mode=global|user]</TD></TR></TBODY></TABLE><BR>
<P>其中：</P>
<P><STRONG>level</STRONG>字段，可以指定以下某个值：</P>
<UL>
<LI>Low：它用于捕获语句统计信息、语句文本和语句迭代器。当启用 SQL 跟踪时，它是缺省的跟踪级别。 
<LI>Medium：此跟踪级别捕获低级跟踪中包含的所有信息，再加上表名、数据库名称和存储过程堆栈。 
<LI>High：此跟踪级别捕获中级跟踪中包含的所有信息，再加上主变量。 
<LI>Off：这不指定 SQL 跟踪。系统缺省为 OFF </LI></UL>
<P><STRONG>ntraces</STRONG>字段，指定要跟踪的 SQL 语句的数目，其范围是 : 500 -2147483647 。</P>
<P><STRONG>size</STRONG>字段， 指定跟踪缓冲区大小的千字节数。每个 SQL 语句的跟踪信息使用一个跟踪缓冲区，如果超过了此缓冲区大小，那么数据库服务器丢弃已保存的数据。其范围是 : 1K-100K 。</P>
<P><STRONG>mode</STRONG>字段， 指定以下任意一项：</P>
<UL>
<LI>Global：跟踪系统上的所有用户，它是缺省值。 
<LI>User：跟踪指定用户（如果想要获取一小组用户正在运行的 SQL 样本，那么指定此项）。 </LI></UL>
<P>在设置 SQLTRACE 参数时，我们需要考虑以下一些内容：</P>
<UL>
<LI>关于 SQLTRACE 缓冲区的问题： 
<P>我们收集到的统计信息都保存在内存的缓冲区中，它的大小 =Ntraces*Size，如果收集的语句数量越多、收集信息越详细，需要的缓冲区就越大，占用的内存资源也就越多。因此，在配置时，要考虑自己的实际情况来选择。另外，这个缓冲区是一个循环缓冲区，当缓冲区大小不够时，它会将旧的信息丢掉，因此，如果需要保存 SQL 语句跟踪历史，要考虑它的大小问题。当我们将 SQL 跟踪特性关闭时，保存在 SQLTRACE 缓冲区中的信息也会丢掉，如果需要保存，在关闭 SQL 跟踪特性关闭前，请将这些信息保存到表或文件中，防止丢失。</P>
<LI>关于 size 参数 
<P>size 参数的大小主要由 SQL 语句的大小及 Level 来决定。不同的 level，所收集的信息量不同，Medium 会比 low 收集的信息量大，同样，High 会比 Medium 收集的信息量大。因此，在选择时，要充分考虑好我们收集统计信息的用途。通常，Low Level 比较适合于错误诊断及性能调优，High Level 常用来做系统负载重现功能。 Medium Level 也主要用于一些错误诊断场合。</P>
<LI>关于 Mode 参数 
<P>global 模式用于跟踪系统上所有用户的统计信息，因此，当在一个比较繁忙的系统上，可能很快就会收集到大量的信息，同时，所有用户的信息都包含在一起，分析时也比较繁琐，通常，选择 global 模式，主要用于比较系统上所有会话的资源使用情况，或者是我们不清楚要分析哪一个具体用户的资源使用情况。一般情况下，我们会使用 user 模式，这样，我们分析起来会比较清晰，同时，占用的系统资源也不会太多。</P></LI></UL>
<P>以下语句指定了数据库服务器将收集关于系统上所有用户执行的最多 2000 条 SQL 语句的低级别信息，并将分配大约 4 兆字节的内存（2000 * 2 千字节）。</P>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="76">
<TBODY sizcache="1" sizset="76">
<TR>
<TD class=code-outline>SQLTRACE level=LOW,ntraces=2000,size=2,mode=global</TD></TR></TBODY></TABLE><BR>
<P>以下语句指定了数据库服务器将收集关于系统上所有用户执行的最多 2000 条 SQL 语句的高级别信息，并将分配大约 4 兆字节的内存（2000 * 2 千字节）。</P>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="77">
<TBODY sizcache="1" sizset="77">
<TR>
<TD class=code-outline>SQLTRACE level=high,ntraces=2000,size=2,mode=global</TD></TR></TBODY></TABLE><BR>
<P>使用 SQLTRACE 配置参数方式比较适合于设置一些缺省的配置，如果需要经常变化，使用 ADMIN API 命令方式则比较方便。</P>
<P><A name="2.2.使用ADMIN API 命令指定启动 SQL 跟踪信息|outline"><SPAN class=smalltitle><STRONG><FONT size=3>使用 ADMIN API 命令指定启动 SQL 跟踪信息</FONT></STRONG></SPAN></A></P>
<P>如果不想设置 SQLTRACE 配置参数以重新启动服务器，那么可执行以下 ADMIN API 命令，该命令可提供与设置 SQLTRACE 相同的功能。使用 ADMIN API 的 task() and admin() 函数可以动态改变 SQLTRACE 的设置，不需要重新启动服务器，使用更加灵活。只有 Informix 用户有权力执行 ADMIN API 命令。通过 ADMIN API 命令修改的 SQLTRACE 的设置不会保存到 SQLTRACE 配置参数中，因此，当服务器重新启动后，SQLTRACE 配置参数值将会生效。</P>
<P>当 SQLTRACE 配置参数为 OFF，我们通过下面的 ADMIN API 命令启动 SQL 跟踪信息时，系统会使用缺省的设置值。 即：</P>
<P><STRONG>SQLTRACE level=low,ntraces=1000,size=1,mode=global</STRONG></P>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="78">
<TBODY sizcache="1" sizset="78">
<TR>
<TD class=code-outline>execute function task("set sql tracing on");</TD></TR></TBODY></TABLE><BR>
<P>我们也可以指定自己设置的值。以下语句指定了数据库服务器将收集关于系统上所有用户执行的最多 2000 条 SQL 语句的低级别信息，并将分配大约 4 兆字节的内存（2000 * 2 千字节）。</P>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="79">
<TBODY sizcache="1" sizset="79">
<TR>
<TD class=code-outline>execute function task("set sql tracing on", 2000,"2k","low","global");</TD></TR></TBODY></TABLE><BR>
<P>停止收集 SQL 语句信息，我们可以执行：</P>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="80">
<TBODY sizcache="1" sizset="80">
<TR>
<TD class=code-outline>execute function task("SET SQL TRACING OFF");</TD></TR></TBODY></TABLE><BR>
<P>当执行上述命令后，会关闭 SQL 跟踪功能，同时，跟踪缓冲区中的内容会丢失。</P>
<P><A name="2.3.启用特定用户的 SQL 历史跟踪|outline"><SPAN class=smalltitle><STRONG><FONT size=3>启用特定用户的 SQL 历史跟踪</FONT></STRONG></SPAN></A></P>
<P>启用特定用户的 SQL 历史跟踪，不仅可以节省系统内存资源，而且分析起来也更加清晰，一般我们会建议采用这种方式。在以用户方式启用 SQL 跟踪系统后，就可以启用对特定用户的跟踪。在指定 user 作为 SQLTRACE 配置参数中的方式后，必须执行管理 API task() 或 admin() 函数来打开对特定用户的 SQL 历史跟踪。</P>
<P>如果全局 SQL 跟踪被禁用，那么可执行管理 API task() 或 admin() 函数来启用对特定用户的 SQL 跟踪。</P>
<P>要启用特定用户的 SQL 历史跟踪，您可以执行 task 或 admin() 函数，并指定 set sql tracing on 和定义用户的信息。</P>
<P>如果需要对 session 30 启用 SQL 语句跟踪，我们可以执行：</P>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="81">
<TBODY sizcache="1" sizset="81">
<TR>
<TD class=code-outline>execute function task("set sql tracing on", 1000, 1,"low","user"); 
 execute function task("set sql user tracing on",<EM>30</EM>)</TD></TR></TBODY></TABLE><BR>
<P>如果需要对当前连接到系统的用户（只要它们未作为用户 root 或 Informix 登录）启用 SQL 语句跟踪，我们可以执行：</P>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="82">
<TBODY sizcache="1" sizset="82">
<TR>
<TD class=code-outline>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</TD></TR></TBODY></TABLE><BR>
<P>如果需要停止对 session 30 进行跟踪，我们可以执行：</P>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="83">
<TBODY sizcache="1" sizset="83">
<TR>
<TD class=code-outline>execute function task( “ set sql user tracing off",30);</TD></TR></TBODY></TABLE><BR>
<P><A name=2.4.SQL跟踪信息显示及分析|outline><SPAN class=smalltitle><STRONG><FONT size=3>SQL 跟踪信息显示及分析</FONT></STRONG></SPAN></A></P>
<P>我们可以通过执行 onstat -g his 命令或查询<STRONG>sysmaster</STRONG>数据库中的 syssqltrace 伪表来获取 SQL 下钻查询信息。</P>
<P sizcache="2" sizset="84"><A name="2.4.1.onstat –g his命令|outline" sizcache="2" sizset="84"><STRONG>onstat – g his 命令</STRONG></A><A name=OLE_LINK1 sizcache="2" sizset="86"><STRONG>Statement text and iterators:</STRONG>onstat – g his 命令输出的接下几行用来描述被跟踪的 SQL 语句以及查询中用到的迭代器及查询计划信息。 SQL 语句部分的内容根据跟踪级别的不同而有所不同。如果跟踪级别是 LOW，只是显示被跟踪的 SQL 语句及正在使用的数据库的十六进制描述。如果踪级别是 medium，将显示数据库的名称，SQL 语句，SQL 语句中用到的表名称及存储过程的调用堆栈信息。如果跟踪级别是 high，除了显示 medium level 的信息外，还将显示 SQL 语句中用到的宿主变量信息。如下所示：</A><A name=2.4.2.syssqltrace伪表|outline sizcache="2" sizset="88"><STRONG>syssqltrace 伪表</STRONG></A><A name=2.4.3.syssqltrace_info伪表|outline sizcache="2" sizset="133"><STRONG>syssqltrace_info 伪表</STRONG></A><A name=2.4.4.syssqltrace_iter伪表|outline sizcache="2" sizset="141"><STRONG>syssqltrace_iter 伪表</STRONG></A><A name=3.结论|outline><SPAN class=atitle><STRONG><FONT size=5>结论</FONT></STRONG></SPAN></A></P>
<P>我们可以通过执行 onstat -g his 命令来显示 SQL 下钻查询信息。</P>
<P>语法：</P>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="84">
<TBODY sizcache="1" sizset="84">
<TR>
<TD class=code-outline>>>-onstat-- -g--his--------------------------------------------><</TD></TR></TBODY></TABLE><BR>
<P>onstat -g his 选项显示 SQLTRACE 收集到的信息并以格式化输出。缺省情况下，只有 DBSA 可以查看 onstat -g his syssqltrace 信息。然而，当 UNSECURE_ONSTAT = 1 时，所有的用户可以查看此信息。 onstat -g his 选项会将所收集到的所有信息全部显示出来，目前还不能够只显示某一特定的用户会话或 SQL 语句的信息。因此，它比较适合小数据量的显示，它的好处是比较方便。</P>
<P>onstat -g his 选项的输出主要包含三部分内容：trace profile, statement text and statement statistics.</P>
<P><STRONG>Trace profile:</STRONG>这是 onstat -g his 命令输出的前几行信息，用于描述跟踪的级别、跟踪模式、跟踪的 SQL 语句条数、跟踪缓冲区的大小及跟踪缓冲区保持的时间。如下所示：</P>
<P>onstat -g his 的输出示例（Trace profile 部分）：</P>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="85">
<TBODY sizcache="1" sizset="85">
<TR>
<TD class=code-outline>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

... ...</TD></TR></TBODY></TABLE><BR><BR>
<P sizcache="2" sizset="86"></P>
<P>onstat -g his 的输出示例（Statement text and iterators: 部分）：</P>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="86">
<TBODY sizcache="1" sizset="86">
<TR>
<TD class=code-outline>... ... 
 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

... ...</TD></TR></TBODY></TABLE><BR><BR>
<P><STRONG>Statement information and statistics:</STRONG>接下来的部分主要包含 SQL 语句及性能统计信息，也是我们进行监控的最主要的部分。通过它，我们可以发现 SQL 语句相关的内存使用情况、磁盘 I/O 情况、锁使用及争用情况，CPU 使用情况，排序及索引使用情况等信息。据此，我们可以进行相应的调整。 我们又可以将其细化为下面三部分内容：</P>
<P><STRONG>Statement information</STRONG>：描述下边一些信息：</P>
<UL>
<LI>运行命令的用户的用户标识 
<LI>数据库会话标识 
<LI>数据库的名称 
<LI>SQL 语句的类型 
<LI>SQL 语句执行的持续时间 
<LI>该语句完成的时间 
<LI>带有语句类型的 SQL 语句文本或函数调用列表（也称为堆栈跟踪），例如： procedure1() calls procedure2() calls procedure3() </LI></UL>
<P><STRONG>RSAM statistics</STRONG>：描述下边一些信息：</P>
<UL>
<LI>缓冲区读取和写入的数目 
<LI>页面读取和写入的数目 
<LI>排序和磁盘排序的数目 
<LI>锁请求和等待的数目 
<LI>逻辑日志记录的数目 </LI></UL>
<P><STRONG>SQL statistics</STRONG>：描述下边一些信息：</P>
<UL>
<LI>估计的行数 
<LI>优化器估计成本 
<LI>返回的行数 
<LI>SQL/ISAM 错误 
<LI>数据库隔离级别 
<LI>SQL 语句内存使用量。 </LI></UL>
<P>onstat -g his 的输出示例（Statement information and statistics 部分）：</P>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="87">
<TBODY sizcache="1" sizset="87">
<TR>
<TD class=code-outline>... ...

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</TD></TR></TBODY></TABLE><BR><BR>
<P>输出描述 ：</P>
<UL>
<LI><STRONG>Page Read</STRONG>：已从磁盘读取的页数 
<LI><STRONG>Buffer Reads</STRONG>: 从缓冲池读取而不是从磁盘读取页面的次数 
<LI><STRONG>Read % Cache</STRONG>: 应从缓冲池读取页面的次数的百分比 
<LI><STRONG>Buffer IDX Read</STRONG>: 索引页的缓冲区读取数 
<LI><STRONG>Page Write</STRONG>: 写入磁盘的页数 
<LI><STRONG>Buffer Write</STRONG>：修改并发送回缓冲池的页数 
<LI><STRONG>Write % Cache</STRONG>：页面写入缓冲池而不是写入磁盘的次数的百分比 
<LI><STRONG>Lock Requests</STRONG>：该语句所需的锁的总数 
<LI><STRONG>Lock Waits</STRONG>：该 SQL 语句等待锁的次数 
<LI><STRONG>LK Wait Time</STRONG>：在该 SQL 语句执行期间，用于等待锁的时间（以秒为单位） 
<LI><STRONG>Log Space</STRONG>： 
<LI><STRONG>Num Sorts</STRONG>：用于执行语句的排序总数 
<LI><STRONG>Disk Sorts</STRONG>：对于该 SQL 语句，对磁盘执行的排序的次数 
<LI><STRONG>Memory Sorts</STRONG> 
<LI><STRONG>Total Executions</STRONG>：该语句已执行的总次数，或者该游标重用的次数 
<LI><STRONG>Total Time</STRONG>：执行该语句的总时间（以秒为单位） 
<LI><STRONG>Avg Time</STRONG>：执行该语句的平均时间（以秒为单位） 
<LI><STRONG>Max Time</STRONG>：运行 SQL 语句的总时间（以秒为单位），不包括应用程序使用的任何时间 
<LI><STRONG>LK Wait Time</STRONG>：语句等待应用程序锁的时间量 
<LI><STRONG>Avg IO Wait</STRONG>：语句等待 I/O 的时间量，不包括任何异步 I/O 。 
<LI><STRONG>Avg Rows Per Sec</STRONG>：该语句每秒产生的平均行数 
<LI><STRONG>Estimated Cost</STRONG>：与 SQL 语句关联的代价 
<LI><STRONG>Estimated Rows</STRONG>：返回的估计行数，由语句的优化程序估计 
<LI><STRONG>Actual Rows</STRONG>：对于该语句返回的行数 
<LI><STRONG>SQL Error</STRONG>：SQL 错误号 
<LI><STRONG>ISAM Error</STRONG>：RSAM/ISAM 错误号 
<LI><STRONG>Isolation Level</STRONG>：该语句运行时使用的隔离级别 
<LI><STRONG>SQL Memory</STRONG>：该 SQL 语句需要的字节数 </LI></UL>
<P sizcache="2" sizset="88"></P>
<P>在 Informix 中，提供了 3 张内存伪表用来保存 SQL 跟踪信息。其中，syssqltrace 表用来提供被跟踪的每一个 SQL 语句的详细的跟踪信息。它的大小由 ntraces*size 来决定。我们可以动态调整其大小。我们可以通过 sysmaster 数据库来访问这 3 张内存伪表。</P>
<P>syssqltrace 表的输出信息同执行 onstat -g his 命令输出的 Statement information and statistics 部分的内容相似。</P>
<P>由于我们可以使用 SQL 语句访问 SQL 跟踪信息，所以，采用该种方法比较适合查询有关单个 SQL 语句或一组 SQL 语句的详细跟踪信息。</P>
<P>syssqltrace 表的基本结构如下：</P>
<TABLE class=data-table-1 border=0 cellSpacing=0 cellPadding=0 sizcache="2" sizset="88">
<TBODY sizcache="1" sizset="88">
<TR>
<TD><A name=wq263>列</A></TD>
<TD>类型</TD>
<TD>描述</TD></TR>
<TR>
<TD>sql_id</TD>
<TD>int8</TD>
<TD>唯一 SQL 执行标识</TD></TR>
<TR>
<TD>sql_address</TD>
<TD>int8</TD>
<TD>代码块中的语句的地址</TD></TR>
<TR>
<TD>sql_sid</TD>
<TD>int</TD>
<TD>运行 SQL 语句的用户的数据库会话标识</TD></TR>
<TR>
<TD>sql_uid</TD>
<TD>int</TD>
<TD>运行 SQL 的语句的用户标识</TD></TR>
<TR>
<TD>sql_stmttype</TD>
<TD>int</TD>
<TD>语句类型</TD></TR>
<TR>
<TD>sql_stmtname</TD>
<TD>char(40)</TD>
<TD>显示为单词的语句类型</TD></TR>
<TR>
<TD>sql_finishtime</TD>
<TD>int</TD>
<TD>此语句的完成时间（UNIX）</TD></TR>
<TR>
<TD>sql_begintxtime</TD>
<TD>int</TD>
<TD>此事务的启动时间</TD></TR>
<TR>
<TD>sql_runtime</TD>
<TD>float</TD>
<TD>语句执行时间</TD></TR>
<TR>
<TD>sql_pgreads</TD>
<TD>int</TD>
<TD>此 SQL 语句的磁盘读取数</TD></TR>
<TR>
<TD>sql_bfreads</TD>
<TD>int</TD>
<TD>此 SQL 语句的缓冲区读取数</TD></TR>
<TR>
<TD>sql_rdcache</TD>
<TD>float</TD>
<TD>从缓冲池读取页的时间百分比</TD></TR>
<TR>
<TD>sql_bfidxreads</TD>
<TD>int</TD>
<TD>索引页缓冲区读取数</TD></TR>
<TR>
<TD>sql_pgwrites</TD>
<TD>int</TD>
<TD>写入磁盘的页数</TD></TR>
<TR>
<TD>sql_bfwrites</TD>
<TD>int</TD>
<TD>已修改并返回到缓冲池的页数</TD></TR>
<TR>
<TD>sql_wrcache</TD>
<TD>float</TD>
<TD>页已写入缓冲池，但未写入磁盘的时间百分比</TD></TR>
<TR>
<TD>sql_lockreq</TD>
<TD>int</TD>
<TD>此 SQL 语句所需锁总数</TD></TR>
<TR>
<TD>sql_lockwaits</TD>
<TD>int</TD>
<TD>SQL 语句等待锁的次数</TD></TR>
<TR>
<TD>sql_lockwttime</TD>
<TD>float</TD>
<TD>SQL 语句期间系统等待锁定的时间</TD></TR>
<TR>
<TD>sql_logspace</TD>
<TD>int</TD>
<TD>逻辑日志中 SQL 语句所用空间量</TD></TR>
<TR>
<TD>sql_sorttotal</TD>
<TD>int</TD>
<TD>为语句运行的排序数</TD></TR>
<TR>
<TD>sql_sortdisk</TD>
<TD>int</TD>
<TD>磁盘上运行的排序数</TD></TR>
<TR>
<TD>sql_sortmem</TD>
<TD>int</TD>
<TD>内存中运行的排序数</TD></TR>
<TR>
<TD>sql_executions</TD>
<TD>int</TD>
<TD>SQL 语句运行的次数</TD></TR>
<TR>
<TD>sql_totaltime</TD>
<TD>float</TD>
<TD>运行语句所用时间总量</TD></TR>
<TR>
<TD>sql_avgtime</TD>
<TD>float</TD>
<TD>运行语句所用平均时间量</TD></TR>
<TR>
<TD>sql_maxtime</TD>
<TD>float</TD>
<TD>执行 SQL 语句所用最大时间量</TD></TR>
<TR>
<TD>sql_numiowaits</TD>
<TD>int</TD>
<TD>I/O 操作必须等待的次数</TD></TR>
<TR>
<TD>sql_avgiowaits</TD>
<TD>float</TD>
<TD>SQL 语句必须等待的平均时间量</TD></TR>
<TR>
<TD>sql_totaliowaits</TD>
<TD>float</TD>
<TD>SQL 语句必须等待 I/O 的时间总量。这不包含任何异步 I/O 。</TD></TR>
<TR>
<TD>sql_rowspersec</TD>
<TD>float</TD>
<TD>产生的平均行数（每秒）</TD></TR>
<TR>
<TD>sql_estcost</TD>
<TD>int</TD>
<TD>与 SQL 语句关联的成本</TD></TR>
<TR>
<TD>sql_estrows</TD>
<TD>int</TD>
<TD>按照优化器的预测为 SQL 语句返回的预估行数</TD></TR>
<TR>
<TD>sql_actualrows</TD>
<TD>int</TD>
<TD>为 SQL 语句返回的行数</TD></TR>
<TR>
<TD>sql_sqlerror</TD>
<TD>int</TD>
<TD>SQL 错误号</TD></TR>
<TR>
<TD>sql_isamerror</TD>
<TD>int</TD>
<TD>RSAM/ISAM 错误号</TD></TR>
<TR>
<TD>sql_isollevel</TD>
<TD>int</TD>
<TD>SQL 语句的隔离级别。</TD></TR>
<TR>
<TD>sql_sqlmemory</TD>
<TD>int</TD>
<TD>执行 SQL 语句所需字节数</TD></TR>
<TR>
<TD>sql_numiterators</TD>
<TD>int</TD>
<TD>语句所用迭代器数</TD></TR>
<TR>
<TD>sql_database</TD>
<TD>char(128)</TD>
<TD>数据库名</TD></TR>
<TR>
<TD>sql_numtables</TD>
<TD>int</TD>
<TD>执行 SQL 语句中所用表数</TD></TR>
<TR>
<TD>sql_tablelist</TD>
<TD>char(4096)</TD>
<TD>SQL 语句中直接引用的表名列表。如果 SQL 语句击发对其他表执行语句的触发器，将不列出其他这些表。</TD></TR>
<TR>
<TD>sql_statement</TD>
<TD>char(1600)</TD>
<TD>已运行的 SQL 语句</TD></TR></TBODY></TABLE><BR>
<P>如果我们要查看会话 30 的 SQL 跟踪信息，可以执行：</P>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="132">
<TBODY sizcache="1" sizset="132">
<TR>
<TD class=code-outline>select * from syssqltrace where sql_id =30;</TD></TR></TBODY></TABLE><BR>
<P sizcache="2" sizset="133"></P>
<P>syssqltrace_info 伪表也是一张内存表，用来保存 tracing profile 信息。我们可以通过 sysmaster 数据库来访问这张内存伪表。 tracing profile 信息主要用于描述跟踪的级别、跟踪模式、跟踪的 SQL 语句条数、跟踪缓冲区的大小及跟踪缓冲区保持的时间。</P>
<P>syssqltrace_info 伪表的输出内容同执行 onstat -g his 命令输出的 tracing profile 部分的内容相似。</P>
<P>syssqltrace_info 表的基本结构如下：</P>
<TABLE class=data-table-1 border=0 cellSpacing=0 cellPadding=0 sizcache="2" sizset="133">
<TBODY sizcache="1" sizset="133">
<TR>
<TD>列</TD>
<TD>类型</TD>
<TD>描述</TD></TR>
<TR>
<TD>flags</TD>
<TD>integer</TD>
<TD>SQL 跟踪标志</TD></TR>
<TR>
<TD>ntraces</TD>
<TD>integer</TD>
<TD>要跟踪的项数</TD></TR>
<TR>
<TD>tracesize</TD>
<TD>integer</TD>
<TD>为各 SQL 跟踪项存储的文本的大小</TD></TR>
<TR>
<TD>duration</TD>
<TD>integer</TD>
<TD>跟踪缓冲区（以秒为单位）</TD></TR>
<TR>
<TD>sqlseen</TD>
<TD>int8</TD>
<TD>启动或调整大小以来跟踪的 SQL 项数</TD></TR>
<TR>
<TD>starttime</TD>
<TD>integer</TD>
<TD>跟踪的启用时间</TD></TR>
<TR>
<TD>memoryused</TD>
<TD>int8</TD>
<TD>SQL 跟踪所用内存的字节数</TD></TR></TBODY></TABLE><BR>
<P sizcache="2" sizset="141"></P>
<P>syssqltrace_iter 伪表也是一张内存表，用来保存 Statement text and iterators 信息。我们可以通过 sysmaster 数据库来访问这张内存伪表。 Statement text and iterators 信息主要用来描述被跟踪的 SQL 语句以及查询中用到的迭代器及查询计划信息。这个表常用来查询特定 SQL 语句的查询计划信息。</P>
<P>syssqltrace_iter 伪表的输出内容同执行 onstat -g his 命令输出的 Statement text and iterators 部分的内容相似。</P>
<P>syssqltrace_iter 表的基本结构如下：</P>
<TABLE class=data-table-1 border=0 cellSpacing=0 cellPadding=0 sizcache="2" sizset="141">
<TBODY sizcache="1" sizset="141">
<TR>
<TD>列</TD>
<TD>类型</TD>
<TD>描述</TD></TR>
<TR>
<TD>sql_id</TD>
<TD>int8</TD>
<TD>SQL 执行标识</TD></TR>
<TR>
<TD>sql_address</TD>
<TD>int8</TD>
<TD>SQL 语句块的地址</TD></TR>
<TR>
<TD>sql_itr_address</TD>
<TD>int8</TD>
<TD>迭代器的地址</TD></TR>
<TR>
<TD>sql_itr_id</TD>
<TD>int</TD>
<TD>迭代器标识</TD></TR>
<TR>
<TD>sql_itr_left</TD>
<TD>int</TD>
<TD>向左的迭代器标识</TD></TR>
<TR>
<TD>sql_itr_right</TD>
<TD>int</TD>
<TD>向右的迭代器标识</TD></TR>
<TR>
<TD>sql_itr_cost</TD>
<TD>int</TD>
<TD>迭代器成本</TD></TR>
<TR>
<TD>sql_itr_estrows</TD>
<TD>int</TD>
<TD>迭代器预估行数</TD></TR>
<TR>
<TD>sql_itr_numrows</TD>
<TD>int</TD>
<TD>迭代器实际处理的行数</TD></TR>
<TR>
<TD>sql_itr_type</TD>
<TD>int</TD>
<TD>迭代器类型</TD></TR>
<TR>
<TD>sql_itr_misc</TD>
<TD>int</TD>
<TD>迭代器杂项标志</TD></TR>
<TR>
<TD>sql_it_info</TD>
<TD>char(256)</TD>
<TD>显示为文本的迭代器杂项标志</TD></TR></TBODY></TABLE><BR>
<P>如果我们要查看 sql_id=15 的查询所使用的迭代器及类型，我们可以执行：</P>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="154">
<TBODY sizcache="1" sizset="154">
<TR>
<TD class=code-outline>select  sql_itr_type, 
        substr(sql_itr_info,1,20) as iterator_info, 
        sql_itr_numrows 
 from    syssqltrace_iter 
 where   sql_id = 14;</TD></TR></TBODY></TABLE><BR><BR>
<P>通过执行 onstat -g his 命令或查询<STRONG>sysmaster</STRONG>数据库中的 syssqltrace 伪表，我们可以查看系统中运行的 SQL 语句、执行 SQL 所用的资源、运行 SQL 花费的时间、磁盘 / 页面 / 缓冲区读和写的数量、使用的锁数量、排序数量和使用的内存量。另外，还可以查看 Informix 优化器估计的运行 SQL 所要花费的时间。这样，我们可以了解到 SQL 语句占用资源情况及存在的资源瓶颈，并进行相应的调整。另外，我们还可以对比 Informix 优化器估计的返回行数和实际的返回行数（sql_estrows 和 sql_actualrows）。如果这两个数值差异很大，就说明 Informix 优化器并不掌握关于表中行和索引数量的正确的统计数据。这意味着需要运行 update statistics，从而向优化器提供正确的数据。</P>
<P>另外，通过查询 syssqltrace 表，我们还可以发现系统中运行时间最长的 SQL 查询、带有太多表连接操作的查询、不希望的连接类型、返回记录太多的查询、存在锁等待的查询等信息，以便于我们进行及时的调整及改进来提高 SQL 语句的性能。</P>
<P>如果我们要发现运行时间最长的 SQL 查询，我们可以执行：</P>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="155">
<TBODY sizcache="1" sizset="155">
<TR>
<TD class=code-outline>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 ；</TD></TR></TBODY></TABLE><BR><BR>
<P>如果我们要发现返回记录太多的查询，我们可以执行：</P>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="156">
<TBODY sizcache="1" sizset="156">
<TR>
<TD class=code-outline>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 ；</TD></TR></TBODY></TABLE><BR><BR><BR>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="157">
<TBODY sizcache="1" sizset="157">
<TR>
<TD><a class="example-image-link" href="http://www.ibm.com/i/v14/rules/blue_rule.gif" width="100%" data-lightbox="example-set"><img class="example-image" src="http://www.ibm.com/i/v14/rules/blue_rule.gif" width="100%"    height=1></a><BR><a class="example-image-link" href="http://www.ibm.com/i/c.gif" data-lightbox="example-set"><img class="example-image" src="http://www.ibm.com/i/c.gif"  border=0   width=8 height=6></a></TD></TR></TBODY></TABLE>
<TABLE class=no-print cellSpacing=0 cellPadding=0 align=right sizcache="2" sizset="158">
<TBODY sizcache="2" sizset="159">
<TR align=right sizcache="2" sizset="159">
<TD sizcache="2" sizset="159"><a class="example-image-link" href="http://www.ibm.com/i/c.gif" width="100%" data-lightbox="example-set"><img class="example-image" src="http://www.ibm.com/i/c.gif" width="100%"    height=4></a><BR>
<TABLE border=0 cellSpacing=0 cellPadding=0 sizcache="2" sizset="159">
<TBODY sizcache="1" sizset="159">
<TR>
<TD vAlign=center><a class="example-image-link" href="http://www.ibm.com/i/v14/icons/u_bold.gif" data-lightbox="example-set"><img class="example-image" src="http://www.ibm.com/i/v14/icons/u_bold.gif"  border=0   width=16 height=16></a></TD>
<TD vAlign=top align=right><A class=fbox href="http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0904zhanggy2/index.html#main" cmimpressionsent="1"><STRONG><FONT color=#996699 face=Verdana>回页首</FONT></STRONG></A></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR><BR>
<P></P>
<P>本文主要给大家介绍了 Informix 数据库对 SQL 语句性能监控的两种基本的方法及使用，关于 SQL 语句调优的相应方法及建议，大家可以参考 Informix 信息中心中性能调整部分。</P><BR><BR>
<P><A name=resources><SPAN class=atitle><STRONG><FONT size=5>参考资料 </FONT></STRONG></SPAN></A></P><STRONG>学习</STRONG><BR>
<UL>
<LI>参考<a href="http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp" cmimpressionsent="1" target="_blank"><FONT color=#5c81a7>Informix 11.5 信息中心</FONT></A>, 获得更多 Informix 11.5 SQL 语句监控及优化相关知识。 
<LI>在<a href="http://www.ibm.com/developerworks/cn/data/" cmimpressionsent="1" target="_blank"><FONT color=#5c81a7>developerWorks 中国网站 Information Management 专区</FONT></A>中学习更多信息管理方面的知识 
<LI>通过 Information Management 专区 <a href="http://www.ibm.com/developerworks/cn/data/products/informix/" cmimpressionsent="1" target="_blank"><FONT color=#5c81a7>Informix 专题</FONT></A>，了解 Informix 产品和技术相关的更多内容。 
<LI>查看<a href="http://www.ibm.com/developerworks/cn/data/library/techarticles/0211desai/0211desai.html" cmimpressionsent="1" target="_blank"><FONT color=#5c81a7>IBM Informix Dynamic Server 优化器概述</FONT></A>，了解 Informix 优化器的详细内容。 
<LI>查看<a href="http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0410fan/" cmimpressionsent="1" target="_blank"><FONT color=#5c81a7>第 2 部分 : 调优 Informix SQL</FONT></A>，了解调优 Informix SQL 的详细内容。 
<LI>IBM 红皮书<a href="http://www.redbooks.ibm.com/abstracts/sg247465.html?Open" cmimpressionsent="1" target="_blank"><FONT color=#5c81a7>Informix Dynamic Server 11: Advanced Functionality for Modern Business</FONT></A>，了解 Informix 的高级函数功能。 </LI></UL><STRONG>讨论</STRONG><BR>
<UL>
<LI>参与<a href="http://www.ibm.com/developerworks/blogs/" cmimpressionsent="1" target="_blank"><FONT color=#5c81a7>developerWorks blogs</FONT></A>并加入<a href="http://www.ibm.com/developerworks/community?S_TACT=105AGX52&S_CMP=content" cmimpressionsent="1" doneonce="true" target="_blank"><FONT color=#5c81a7>developerWorks 社区</FONT></A>。 </LI></UL><BR><BR>
<P><A name=author><SPAN class=atitle><STRONG><FONT size=5>关于作者</FONT></STRONG></SPAN></A></P>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" sizcache="2" sizset="160">
<TBODY sizcache="1" sizset="160">
<TR>
<TD colSpan=3><STRONG><FONT size=5><a class="example-image-link" href="http://www.ibm.com/i/c.gif" width="100%" data-lightbox="example-set"><img class="example-image" src="http://www.ibm.com/i/c.gif" width="100%"    height=5></a></FONT></STRONG></TD></TR>
<TR vAlign=top align=left>
<TD>
<P></P></TD>
<TD><STRONG><FONT size=5><a class="example-image-link" href="http://www.ibm.com/i/c.gif" data-lightbox="example-set"><img class="example-image" src="http://www.ibm.com/i/c.gif"    width=4 height=5></a></FONT></STRONG></TD>
<TD width="100%">
<P>张光业，目前在 IBM 软件部工作，主要负责开发商在数据库和数据仓库方面产品的技术支持和培训，在 IT 行业有丰富的工作经验。是 IBM 认证的 DB2 UDB 高级技术专家。</P></TD></TR></TBODY></TABLE></DIV></TD></TR></TBODY></TABLE><BR></DIV>

数据库

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

添加新评论0 条评论

Ctrl+Enter 发表

匿名评论

Informix 11.5 SQL 语句性能监控方法及实现（2）

添加新评论0 条评论

作者其他文章

相关文章

相关问题

相关资料