Trace开启方式
修改配置文件或登录到数据库set global 参数名=参数值方式进行设置。
gbase_sql_trace=1 # 默认是0,表示关闭,设置为1即可开启。
gbase_sql_trace_level=3 #trace等级越高越详细。默认为3,绝大部分情况足够,极个别情况可设置为更大值。如7或者15
SQL语句
示例语句为TPC-H SQL21(10 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 trace(level=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表:共2个DC,全部命中,需要扫描(无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.