南大通用 GBase 8a SQL 执行计划说明及示例分析

Trace开启方式

修改配置文件或登录到数据库set global 参数名=参数值方式进行设置。

gbase_sql_trace=1 # 默认是0,表示关闭,设置为1即可开启。

gbase_sql_trace_level=3  #trace等级越高越详细。默认为3,绝大部分情况足够,极个别情况可设置为更大值。如7或者15

SQL语句

示例语句为TPC-H SQL2110 Scale),测试环境CPU 4core,打开并行

 

select

s_name,

count(*) as numwait

from

supplier,

lineitem l1,

orders,

nation

where

s_suppkey = l1.l_suppkey

and o_orderkey = l1.l_orderkey

and o_orderstatus = 'F'

and l1.l_receiptdate > l1.l_commitdate

and exists (

select

*

from

lineitem l2

where

l2.l_orderkey = l1.l_orderkey

and l2.l_suppkey <> l1.l_suppkey

)

and not exists (

select

*

from

lineitem l3

where

l3.l_orderkey = l1.l_orderkey

and l3.l_suppkey <> l1.l_suppkey

and l3.l_receiptdate > l3.l_commitdate

)

and s_nationkey = n_nationkey

and n_name = 'SAUDI ARABIA'

group by

s_name

order by

numwait desc,

s_name

limit 100;

 

trace信息

注:为了方便阅读,这里不列出资源监控部分,下一节专门说明。本文档最后一节附有完整的SQL tracelevel=3的输出)

 

2023-05-17 16:08:36.324  Start Query Execution

2023-05-17 16:08:36.326  BEGIN Smart Scan -- 智能索引过滤开始

2023-05-17 16:08:36.328  T0:  total 2 DC,     found 2 DC to scan(with 0 FULL DC). –- T0表:共2DC,全部命中,需要扫描(无Full状态)

2023-05-17 16:08:36.328  T1:  total 916 DC,   found 916 DC to scan(with 0 FULL DC).

2023-05-17 16:08:36.328  T2:  total 229 DC,   found 229 DC to scan(with 0 FULL DC).

2023-05-17 16:08:36.328  T3:  total 1 DC,     found 1 DC to scan(with 0 FULL DC).

2023-05-17 16:08:36.328  BEGIN Scan -- 表扫描过滤开始

2023-05-17 16:08:36.328  using parallel scan  -- 第一个过滤条件开始,使用并行scan

2023-05-17 16:08:36.339  merge row num: 1     -- 第一个过滤条件结束,结果为 1 row

2023-05-17 16:08:36.339  using parallel scan

2023-05-17 16:08:37.592  merge row num: 7309184

2023-05-17 16:08:37.592  using parallel scan

2023-05-17 16:08:45.483  merge row num: 37929348

2023-05-17 16:08:45.484  using parallel scan  -- 这个过滤条件是exists子查询

2023-05-17 16:08:45.496  do not support parallel scan , run normal scan -- 相关子查询不适用普通的并行scan

2023-05-17 16:08:45.498  using serially scan

2023-05-17 16:08:45.509  | BEGIN Smart Scan -- 子查询内部的智能索引过滤开始(一个|代表一层子查询,嵌套累加)

2023-05-17 16:08:45.509  | T0:        total 916 DC,   found 916 DC to scan(with 0 FULL DC).

2023-05-17 16:08:45.509  | T1:        total 916 DC,   found 916 DC to scan(with 0 FULL DC).

2023-05-17 16:08:45.509  | BEGIN Scan

2023-05-17 16:08:45.509  | T0:        total 916 DC,   found 916 DC after scan(with 916 FULL DC).

2023-05-17 16:08:45.510  | T1:        total 916 DC,   found 916 DC after scan(with 0 FULL DC).

2023-05-17 16:08:45.510  | BEGIN Join -- exists相关子查询优化为semi-join, join开始

2023-05-17 16:08:45.524  | divide to 4 blocks(using round-robin): 9486804, 9491138, 9489208, 9462198. 并行join数据分块(下面针对线程(0)进行说明)

2023-05-17 16:08:52.992  | (0)created hash on 7372800/9486804 rows already.  由于hj_buffer不足以装下hashtab,需要多趟hash

2023-05-17 16:08:53.255  | (1)created hash on 7372800/9491138 rows already.

2023-05-17 16:08:54.128  | (3)created hash on 7372800/9462198 rows already.

2023-05-17 16:08:54.187  | (2)created hash on 7372800/9489208 rows already.

2023-05-17 16:09:15.648  | (1)scanned 12124160 rows,  and produced 29490678 rows already.

2023-05-17 16:09:16.028  | (0)scanned 12124160 rows,  and produced 29495308 rows already. 线程0扫描了12124160条数据,共匹配上29495308条结果

2023-05-17 16:09:16.753  | (1)created hash on all 9491138 rows already.

2023-05-17 16:09:17.115  | (0)created hash on all 9486804 rows already.  -- 第二趟hash,在剩余的数据上建hashtab(9486804 7372800)

2023-05-17 16:09:21.793  | (1)scanned 3866624 rows,   and produced 37965426 rows already.

2023-05-17 16:09:21.793  | (1)skipped 86.68% DCs by smart index.

2023-05-17 16:09:21.794  | (1)inner join(T0 - T1),    using [semi]hash join,          produced 9151948 rows.

  • 25
    点赞
  • 14
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值