IQ 性能诊断工具介绍(1)

   一个系统刚上线时往往运行良好，但是过一段时间之后经常会遇到性能问题。数据库管理员往往是第一个获知并解决它们的人。如果能够掌握性能诊断、分析和解决方法并且熟知相应的工具，那么当遇到性能问题时才不会束手无策。

   在这里，以及后面的几篇文章中，我会陆续向大家介绍SYBASE IQ提供的一些性能诊断工具。在这片文章中先介绍SYBASE IQ提供的SQL Trace工具。

   一个发生性能问题的系统对于DBA来说往往是个黑盒子，他们往往并不知道系统内部在做什么、在执行什么SQL语句。这时常用的方法就是借助操作系统提供的工具、数据库提供的server层面的工具查找瓶颈。这些工作是必须的，但只做这些往往是不够的，可能还需要知道发生问题的前后一段时间内应用系统在做什么、在执行哪些SQL语句，哪些SQL语句执行的时间长。SYBASE IQ从12.6开始(包括12.7)提供了叫做SQL Trace的功能，可以实现这个目的。从IQ 15开始，又发展了这个工具，其功能变得更强大。下面将对其进行较为详细的介绍。  

 

1. SYBASE IQ 15之前的SQL Trace

  

IQ 12.6和IQ 12.7SQL Ttrace功能的具体使用方法如下：
  
   （1）在诊断之前先打开SQL Trace，打开的命令如下：
          call sa_server_option('request_level_logging', 'SQL');
          call sa_server_option('request_level_log_file','sqltrace.log');
   

        打开SQL trace之后，随后的所有提交到IQ的sql语句都会被写到sqltrace.log文件中，其位置

     在.db和.log相同的目录下，如果想改变位置，应使用绝对路径。
        注意：sqltrace.log文件可能会由于提交的sql语句数量较多而增长较快，请留有足够的文件系

     统空间。此外，在打开之后不要忘记关闭它！。

   （2）关闭SQL Trace，关闭的命令如下：
          call sa_server_option('request_level_log_file','');
          call sa_server_option('request_level_logging','NONE');

 

         打开之后不要忘记关闭它！
 
   （3）按执行时间从长到短的顺序查看sql
          perl tracetime.pl sqltrace.log format=fixed | sort -n -r 
          

        说明：tracetime.pl文件在$ASDIR-12_7/samples/asa/performancetracetime目录下
              执行上面的perl脚本输出的第1列是语句的执行时间，单位为毫秒(ms)

2. SYBASE IQ 15的SQL Trace功能增强

   IQ 15之前版本提供的SQL Trace功能是通过相应设置后，IQ可以把用户发出的SQL语句写入到Server端相应日志文件中；然后通过相应的perl脚本工具对这个日志文件进行分析，得到SQL语句执行时间、执行次数等信息；通过分析这些信息，可以找到有问题的、需要优化的SQL语句。

 

   IQ 15改变了SQL Trace log文件的格式，并且提供了新的存储过程工具sa_get_request_profile和sa_get_request_times用于读取日志文件，把SQL语句执行情况的汇总信息和详细信息分别存放在相应的全局临时表中(即satmp_request_profile和satmp_request_time)：satmp_request_profile表用于存放汇总信息;satmp_request_time表用于存放详细信息。用户可以使用标准的select语句对SQL语句执行情况的信息进行灵活的查询和分析。

   下面是设置SQL Trace的步骤（这些步骤一般由数据库管理员进行，也可以由DBA授权其他用户进行设置）：

  （1）打开SQL Trace功能。具有DBA权限的用户执行如下存储过程可以打开SQL Trace功能：
       call sa_server_option('request_level_logging', 'SQL');
       call sa_server_option('request_level_log_file','sqltrace.log');
   注意：这一步与IQ 12.7是相同的。使用上述方式打开sql trace之后，形成的sqltrace.log文件与.db文件所在目录相同。sql trace打开之后，所有用户的请求都会记录到日志文件中，如果用户有大量SQL请求，文件会增长较快，需要注意相应文件系统有较多的剩余空间。sqltrace.log文件名和存放的目录位置可以根据需要进行设置：例如:
       call sa_server_option('request_level_log_file','/sybiq/trace/mysqltrace.log');

 

  （2）为用户进行授权，以使用SQL Trace的查看和分析功能
       grant execute on sa_get_request_profile to username
       grant execute on sa_get_request_times to username
   

   在设置好SQL Trace之后(即完成上面的(1)和(2)之后)，用户发出的所有SQL请求都被记录到日志文件中，在应用运行一段时间之后应先关闭SQL Trace（这样做主要是为了节省文件所占用的存储空间）然后再进行分析。

   注意：最好不在生产系统上执行sqltrace.log的装载和分析，而应在另外的测试机上做(把生成的sqltrace.log文件拷贝过去)，避免对生产机器造成影响！分析的步骤如下：

    a. 执行存储过程读取SQL Trace log文件,把文件中的内容存入临时表
          call sa_get_request_profile('sqltrace.log');
          call sa_get_request_times('sqltrace.log');
  

    b. 执行select查询，分析语句执行情况。下面是一些分析例子
       select *

       from satmp_request_profile
       where prefix like '%insert%' or prefix like '%load%'

 

       select start_time,conn_id,millisecs,stmt
       from satmp_request_time
       where (stmt like '%from%' or stmt like '%FROM%') and
             stmt not like '%count(*%' and
             stmt not like '%sysobject%'
       order by millisecs desc

 

     

       --查询satmp_request_profile表输出的例子
       stmt_id   uses   total_ms   avg_ms  max_ms  prefix 
        1         3        9         3       3     select @@tranchained 
        2         1        6         6       6     SELECT HTML_PLAN ('select * from test1') 
        3         2       21        10       11    select * from test1 
        4         1      201       201       201   select * from t1 

 

  （3）关闭SQL Trace

       call sa_server_option('request_level_log_file','');
       call sa_server_option('request_level_logging','NONE');