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

947人阅读 评论(0) 收藏 举报
分类:

在数据库执行一个查询的时候,有时候会碰到因为数据量超大或者由于其他原因(如统计信息不准确导致查询计划不正确),导致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.
查看评论

关于飞机射击类游戏的设计原理 (加入时间:2001-3-4)

作者:尹龙水平有限,请别见怪:)   众所周知,飞机射击类游戏几乎是我们接触到的第一类计算机游戏,不论是在街机中还是在计算机中,这一类游戏都是非常容易上手和招人喜爱的。就算是在这个即时战略游戏、3D第...
  • ghj1976
  • ghj1976
  • 2001-04-25 18:16:00
  • 1970

Trafodion 开发环境搭建

Trafodion是Apache下面的开源孵化项目,既然是开源,任何人都可以修改并提交你的代码,Trafodion欢迎大家通过各种方式为开源社区贡献一份力量,最直接的方式当然是提交你的代码。要想将自己...
  • Post_Yuan
  • Post_Yuan
  • 2017-11-15 11:56:14
  • 842

sql优化(查询大数据量时sql执行时间过长)

问题:Oracle数据库 sql查询的优化(成交额统计表的sql查询时间过长进行的优化) 解决办法:对sql语句中使用视图的部分替换为子查询,对查询表条件字段建立索引 引发的问题:在什么情况下建立...
  • u013167242
  • u013167242
  • 2015-11-29 18:01:15
  • 4316

SQL 语句执行时间分析

以下语句可以进行SQL 语句执行时间分析 use Work--数据库名 go set statistics profile on set statistics io on set statistics...
  • typing_yes_no
  • typing_yes_no
  • 2015-09-02 17:58:17
  • 4320

ORACLE 查看 执行时间长的SQL

1. ORACLE  查看 执行时间长的SQL SELECT S.SQL_TEXT,        S.SQL_FULLTEXT,        S.SQL_ID,        ROUN...
  • tangh999
  • tangh999
  • 2017-06-06 15:24:55
  • 2297

Trafodion 使用snapshot扫描大表

在Trafodion数据库中,我们经常需要对一个超大表进行扫描,如count,或者查询大批量的数量,这无疑对性能是一个严峻的考验。 针对这种超大数据集的查询计算,Trafodion利用HBase s...
  • Post_Yuan
  • Post_Yuan
  • 2017-04-19 10:16:58
  • 423

为使用ODBC连接trafodion软件,安装ODBC和配置

1. 确认是否安装unixODBC: [trafodion@esgyn-01 testtxt]$ rpm -qa|grep unixODBC unixODBC-2.2.14-14.el6.x86_6...
  • freedom_824
  • freedom_824
  • 2017-08-25 00:27:22
  • 207

trafodion(esgynDB)离线安装

一、 准备阶段 主要包括提前下载需要安装的cm和cdh、esgynDB的包以及其依赖的包。 cm包: cdh包: esgynDB包: dev.zip为python installe...
  • yangpengpeng2015
  • yangpengpeng2015
  • 2017-05-25 14:53:18
  • 679

Trafodion CQD-number_of_countstar_rows_parallel_threshold

在之前的一篇文章中,我们介绍了Trafodion的hbase_coprocessors这个CQD。hbase_coprocessors用于控制Trafodoin中的count(*)此类语句是否下推到H...
  • Post_Yuan
  • Post_Yuan
  • 2018-04-03 10:29:26
  • 24

Trafodion 性能优化之加载数据

Trafodion提供两种加载数据的方式,(1)Trickle Load(使用upsert/insert) (2)Bulk Load。Trickle Load使用HBase的写路径,而Bulk Loa...
  • Post_Yuan
  • Post_Yuan
  • 2016-12-30 12:30:10
  • 521
    个人资料
    持之以恒
    等级:
    访问量: 22万+
    积分: 4884
    排名: 7311
    Apache Trafodion中国交流群
    QQ:233105278
    最新评论