数据源博客

不积跬步无以至千里, 不积小流无以成江海!

Trafodion SQL执行时间过长原因分析

在数据库执行一个查询的时候,有时候会碰到因为数据量超大或者由于其他原因(如统计信息不准确导致查询计划不正确),导致SQL一直处于执行状态。那如何跟踪正在执行的时间比较长的QUERY语句的状态呢,下面具体介绍一下。
Trafodion安装目录下面,有一个工具叫”offender”,可以通过如下方式定位,

[trafodion@n12 ~]$ cdw
[trafodion@n12 esgynDB-2.2.0]$ cd export/limited-support-tools/LSO/
[trafodion@n12 LSO]$ ll
total 60
-rw-r----- 1 trafodion trafodion  1768 Nov 17 14:32 active_queries.sql
-rw-r----- 1 trafodion trafodion  1378 Nov 17 14:32 cpu_offender.sql
-rw-r----- 1 trafodion trafodion  1934 Nov 17 14:32 dead_queries.sql
-rw-r----- 1 trafodion trafodion  1659 Nov 17 14:32 inactive_queries.sql
-rw-r----- 1 trafodion trafodion  1408 Nov 17 14:32 mem_offender.sql
-rwxr-x--- 1 trafodion trafodion  6207 Nov 17 14:32 offender
-rw-r----- 1 trafodion trafodion  1760 Nov 17 14:32 queries_in_client.sql
-rw-r----- 1 trafodion trafodion  1758 Nov 17 14:32 queries_in_sql.sql
-rw-r----- 1 trafodion trafodion 17118 Nov 17 14:32 README
-rw-r----- 1 trafodion trafodion  2021 Nov 17 14:32 uninstrumented.sql

“offender”是一个shell脚本,通过不同的参数调用不同的SQL语句,相应的SQL语句也在当前目录下面能找到,以*.sql结束的文件。下面通过-h查看这个工具的详细用法,

[trafodion@n12 LSO]$ ./offender -h

    This script runs a Lightweight SQL Offender script
    syntax:  -s|--script <script-name> [-t|--time <time>] [-n|--node <node>]
             [-m|--memsize]
             [-h|--help]

    Either the short form (-x) or long form (--name) of the parameters can be
           used

    -s|--script Manditory. <script-name> must be one of:
                          cpu_offender
                          mem_offender
                          wm_mem_offender
                          dead
                          active
                          inactive
                          in_sql
                          in_client
                          dead
                          unmonitored

   -t|--time   Optional Default 30
               values: 0 < <time> < 32768
               Time span (in seconds) to search for matching offenders.
               Applies to scripts: dead,inactive,in_sql,in_client,unmonitored

   -m|--memsize Optional Default 250
                Processes exceeding the given memory threshold in MB
                Applies to mem_offender and wm_mem_offender

   -n|--node   Optional Default ALL NODES
               values: -1 < <node> < 2048
               Restricts the query to the given SQL node number

  -h|--help   Display this text and exit

基于以上帮助信息,一个比较常用的例子是使用offender查看当前正在执行的QUERY语句的信息。假设当前在数据库端有一条SQL正在执行,通过./offender -s active可以查看正在执行QUERY相关信息,包括qid,query text等。

[trafodion@n12 LSO]$ ./offender -s active
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>+>+>+>+>+>+>+>+>+>+>+>+>+>
CURRENT_TIMESTAMP           LAST_ACTIVITY_SECS    QUERY_ID                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      EXECUTE_STATE                   SOURCE_TEXT
--------------------------  --------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2016-11-30 14:07:03.786282                     6  MXID11002008570212346814433077581000000000506U3333300_602_SQL_CUR_2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           OPEN                            "SELECT TITLE AS BY_VALUE,TO_CHAR(CTIME,'YYYY-MM-DD') AS C_TIME,count(*) AS VALUE FROM trafodion.seabase.test_table WHERE CTIME >= TIMESTAMP'2016-11-01 00:00:00.000'
AND CTIME <= TIMESTAMP'2016-11-28 23:59:59.000' GROUP BY TITLE,C_TIME ORDER BY TITLE,C_                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
2016-11-30 14:07:03.786282                   -29  MXID11002010127212346654507028134000000004806U3333300_2609_1316                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               DEALLOCATED                     "select object_uid, object_type from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = 'SEABASE' and object_name = 'DESCRIBE__'  and object_type = '  '                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

--- 2 row(s) selected.
>>

End of MXCI Session

查询到qid之后,我们便可以通过qid的值来查看这条QUERY的运行时统计信息,如下,

SQL>get statistics for qid MXID11002008570212346814433077581000000000506U3333300_602_SQL_CUR_2 default;

Qid                      MXID11002008570212346814433077581000000000506U3333300_602_SQL_CUR_2
Compile Start Time       2016/11/30 14:06:57.763727
Compile End Time         2016/11/30 14:06:57.764222
Compile Elapsed Time                 0:00:00.000495
Execute Start Time       2016/11/30 14:06:57.764488
Execute End Time         2016/11/30 14:07:19.555138
Execute Elapsed Time                 0:00:21.790650
State                    DEALLOCATED
Rows Affected            0
SQL Error Code           100
Stats Error Code         0
Query Type               SQL_SELECT_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           1
Transaction Id           -1
Source String            SELECT TITLE AS BY_VALUE,TO_CHAR(CTIME,'YYYY-MM-DD') AS C_TIME,count(*) AS VALUE FROM trafodion.seabase.test_table WHERE CTIME >= TIMESTAMP'2016-11-01 00:00:00.000'
AND CTIME <= TIMESTAMP'2016-11-28 23:59:59.000' GROUP BY TITLE,C_TIME ORDER BY TITLE,C_
SQL Source Length        258
Rows Returned            240
First Row Returned Time  2016/11/30 14:07:19.334617
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
Stats Collection Type    OPERATOR_STATS

LC   RC   Id   PaId ExId Frag TDB Name                 DOP     Dispatches      Oper CPU Time  Est. Records Used  Act. Records Used    Details

11   .    12   .    6    0    EX_ROOT                  1               13                 54                  0                240 2243
10   .    11   12   5    0    EX_SPLIT_TOP             1               15                541          8,318,497                240
9    .    10   11   5    0    EX_SEND_TOP              6               30              1,648          8,318,497                240
8    .    9    10   5    2    EX_SEND_BOTTOM           6              252                783          8,318,497                240
7    .    8    9    5    2    EX_SPLIT_BOTTOM          6              300              2,979          8,318,497                240 56691597
6    .    7    8    4    2    EX_SORT_GRBY             6            8,199          1,779,250          8,318,497                240
5    .    6    7    3    2    EX_SORT                  6           30,479         52,564,614          8,318,497          8,332,485 0|0|0
4    .    5    6    2    2    EX_SPLIT_TOP             6           22,362            700,325          8,318,497          8,332,485
3    .    4    5    2    2    EX_SEND_TOP              72          72,560          1,643,646          8,318,497          8,332,485
2    .    3    4    2    3    EX_SEND_BOTTOM           72         137,850         13,274,528          8,318,497          8,332,485
1    .    2    3    2    3    EX_SPLIT_BOTTOM          12          28,174          7,536,300          8,318,497          8,332,485 79262875
.    .    1    2    1    3    EX_TRAF_MDAM_SELECT      12          26,443         58,452,047          8,318,497          8,332,485 trafodion.seabase.test_table|8332485|2090879780

--- SQL operation complete.

当然了,如果你想直接取消执行时间较长的QUERY也是可行的,通过control query cancel命令,关于control query cancel的详细用法请参照官方文档Trafodion SQL手册。以下例子是通过control query cancel命令取消一个qid对应的查询,

SQL>control query cancel qid MXID11002010007212346654506930135000000001506U3333300_2936_SQL_CUR_2;

--- SQL operation complete.
阅读更多
版权声明:本文为博主原创文章,如需转载,请注明出处。 https://blog.csdn.net/Post_Yuan/article/details/53407122
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭