在数据库执行一个查询的时候，有时候会碰到因为数据量超大或者由于其他原因（如统计信息不准确导致查询计划不正确），导致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.
1312
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
