执行日志（2）文件结构_资讯

执行日志（2）文件结构

创始人

2024-02-22 09:41:35

0次

3.2文件结构

下图为trace文件结构说明图，图中trace内容为节选，主要目的是为了说明整个文件结构组成。以下为了方便阅读，对每个结构区分别说明。

/opt/yz/opt/gnode_8611/l.og/gbase/gbase_execution_plan.trc

Server Version: 8.6.1.1-debug-52264

Version Comment: 52204

Instance Name： gbase

Session ID: 1

User: root

Time： 20160603153208

GBASE_HOHE=/opt/yz/opt/gnode_8611/server/

CPUS:一4

MEM: 7993 MB

摘要区

2016-06-03 15:32:08.448 [H: 0Br 0B,D: QB] [DC: 0, 0] SQL Statement:

select

sname,

count]*) as numwait

from

省略SQL语句....

limit 109

SQL语句区

2016-06-03

08.448

OB,

6B,D

6B]

[DC

Execution Plan：

2016-06-03

08.449

OB,

@B,D

@B]

[DC

T:-l - TABLE ALIAS(T:0,™supplier：tpch Is/supplier")

2016-06-93

08.449

OB,

OB,D

GB]

LDC

T:-2 = TMP TABLE(T:-1,"supplier:tpch Is/supplier")

2016-06-03

08.449

OB,

OB,D

0B]

IDC

T:-3 = TABLE ALIAS(T:l,"lineitem:tpch ls/ll")

2016-06-03

08.449

OB,

OB,D

OB]

[DC

T:-2.JOIN{T:-3,"lineitem：tpch ls/ll")

2916-06-03

98.449

0B,

OB,D

OB]

|DC

T:-4 - TABLE ALIAS(T:2,"orders：tpch Is/orders")

2016-06-03

88.449

9B,

6B,D

8B]

[DC

T:-2.J0IN(T:-4,"orders:tpch ls/orders°'

2016-06-03

08.449

OB,

OB,D

0B]

IDC

T:-5 = TABLE ALIAS(T:3,"nation:tpch ls/nationm)

2016-06-03

08.449

0B,

GB,D

0B]

[DC

T:-2.JOIN(T:-5,"nation：tpch Is/nation”：

2016-06-03

08.449

0B,

6B,D

QB]

|DC

NOTHING BUT A MARKER

2816-06-83

98.449

0B,

0B,D

0B]

[DC

LA:-2.0 = CREATE LA(T:-2,PHYS COL(T:-1,

A：D)

2016-06-03

03.449

6B,

6B,D

0B]

[DC

A:-l = T:-2.ADD COLUMN(LA:-2.6,GROUP BY,"s name","ALL")

2016-06-03

08.449

0B,

OB,D

0B]

[DC

A:-2 = T:-2.ADD COLUMN(,COUNT,"numwait","ALL")

2816-06-03

08.449

0B,

0B,D

0B]

LDC

LA:-2.1 = CREATE LA(T:-2,PHYS COL{T:-2,A:-2))

2916-06-03

08.449

0B,

6B,D

0B]

[DC

T:-2.ADD 0RDER(LA:-2.1,DESC)

2016-06-03

08.449

OB,

OB.D

0B]

LDC

LA:-2.2 = CREATE LA(T:-2,PHYS C0L(T:-2,

A:-D)

2616-06-63

08.449

OB,

OB,D

0B]

[DC

T:-2.ADD 0RDER(LA:-2.2,ASC)

2016-06-03

08.449

OB,

OB,D

OB]

[DC

LA:-2.3 = CREATE LA(T:-2,PHYS C0L(T:-1,A:0))

2916-06-03

98.449

OB,

OB,D

OB]

|DC

LA:-2.4 = CREATE LA(T:-2,PHYS C0L(T:-3,A:2))

2016-06-03

88.449

9B,

9B,D

8B]

[DC

F:0 = CREATE FILTER(T:-2,LA:-2.3,=,LA:-

2.4,,WHERE)

2016-06-03

08.449

OB,

OB,D

0B]

LDC

LA:-2.5 = CREATE LA(T:-2,PHYS COL(T:-4,A:

2016-06-03

08.449

OB,

OB,D

OB]

[DC

LA:-2.6 = CREATE LA{T:-2,PHYS 皿仃:刁』

2816-06-83

08.449

0B,

0B,D

0B]

[DC

F:B = AND(F:0rLA:-2.5,-,LA:-2.6,：

2816-06-83

88.449

OB,

0B,D

8B]

[DC

LA:-2.7 = CREATE LA(T:-2,PHYS C0L(T:-4

扌执行计1划U顷ace

2616-06-03

08.449

6B,

6B,D

OB]

[DC

LA:-2.8 = CREATE LA(T:-2,EXPR("F"))

2016-06-03

08.449

0B,

GB,D

0B]

LDC

F:0 = AND(F:0rLA:-2.7,=lLA:-2.8,：

AR 44Q

I'M

ar n

ARI

1 nc

1 A--7 q = CRFATF 1 AfT--7 PHY。mi fT・

A- 17H

管略trace內眷

2016-06-03

16.345

143Hr

0BfD

0B]

[DC

1877,

419]

SUWARY

2816-06-03

16.345

143Mr

0BfD

0B]

[DC

1877,

419]

elapsed time: 0G:GO:07.897

2016-06-03

16.345

143Mr

6B,D

0B]

IDC

1877,

419]

data loaded from storage： 130M, 4.

184S, 419

DC.

2016-06-03

16.345

143Mr

0BrD

0B]

[DC

1877,

419]

data decompressed： QB, 0s

2916-06-03

16.345

143M,

0B,D

0B]

IDC

1877,

419]

temp space 10 stats：

2816-06-83

16.345

143M,

0B,D

0B]

IDC

1877,

419]

CB write( 0Br etime, Qsec), readl

OB, Qtime,

0sec)

2016-06-03

16.345

143M,

0B,D

QB]

IDC

1877,

419]

SRT write( OB, Otime, Osec), readl

OB, Otime,

0sec)

2016-06-03

16.345

143Mr

0BrD

0B]

[DC

1877,

419]

GDC write( GBr Otime, Qsec), readl

OB, Stime,

0sec)

2016-06-03

16.345

143M,

®B,D

0B]

|DC

1877,

419]

MAT write( 57K, 12timer 0sec),

ead( 440K, 88timer Osec)

2016-06-03

16.345

143Mr

@B,D

QB]

[DC

1877,

419]

HSJ write! 0Br Qtime, Qsec), readl

OB, Qtime,

0sec)

2016-06-03

16.345

143M,

0B,D

QB]

[DC

1877,

419]

=======================================

时间戳资源监控执行计划总结

3. 2. 1 变更要点提示

1）增加了算子时间统计（注意：Select into server时间统计是多个线程的累加，而Scan、Join、Group等运算类算子，时间统计是算子过程实际耗时），以秒为单位；

2）增加了明确的算子结束（End/Done）trace，早先版本下一个算子的

"Begin" trace被视作上一个算子的结束；

3）增加关于 One-Pass Hash Join 的 trace 说明；

3. 2. 2 摘要区

黑色加粗字：步骤执行的开始、结束；

蓝色字体：输出信息的解释（注释）说明，以"--"开始注释内容；红色字体：关键信息；

摘要区trace主要内容包括：服务端信息、客户端连接信息、服务端系统环境信息等。并非每次查询都产生摘要trace，当某个Session首次写trace 文件时会产生摘要区trace。

/opt/yz/opt/gnode_8611/log/gbase/gbase_execution_plan.trc -- trace 文件路径

Server Version: 8. 6.1. 1-debug-52204 -- gbased 服务端版本号

Version Comment: 52204

Instance Name: gbase --实例名称 Session ID: 1 -- 连接 Session ID

User: root --连接用户名

Time: 20160603153208 --时间戳

GBASE_HOME=/opt/yz/opt/gnode_8611/server/ -- GBASE_HOME 环境变量值 CPUS: 4 -- CPU 核数

MEM: 7993 MB --系统物理内存大小

3. 2.3 SQL语句区

此部分显示SQL语句，含回车换行。

3. 2. 4 执行计划trace

Execution Plan：--打印执行计划开始，在计划中打印出各个TempTable表名，所属数据库以及别名

T:-1 = TABLE_ALIAS(T:0,Hsupplier:tpch_1s/suppliern)

T:-2 = TMP_TABLE(T:-1,"supplier:tpch_1s/supplier")

T:-3 = TABLE_ALIAS(T:1,Hlineitem:tpch_1s/l1H)--格式为“表名：所属数据库/别

名"，supplier表属于数据库tpch，该表别名为supplier

T:-2.JOIN(T:-3,"lineitem:tpch_1s/l1")

T:-4 = TABLE_ALIAS(T:2,"orders:tpch_1s/orders")

T:-2.JOIN(T:-4,"orders:tpch_1s/orders")

T:-5 = TABLE_ALIAS(T:3,"nation:tpch_1s/nation")

T:-2.JOIN(T:-5,"nation:tpch_1s/nation")

NOTHING BUT A MARKER

LA:-2.0 = CREATE_LA(T:-2,PHYS_COL(T:-1,A:1))

A:-1 = T:-2.ADD_COLUMN(LA:-2.0,GROUP_BY,"s_name","ALL")

A:-2 = T:-2.ADD_COLUMN(,COUNT,"numwait”,"ALL")

LA:-2.1 = CREATE_LA(T:-2,PHYS_COL(T:-2,A:-2))

T:-2.ADD_ORDER(LA:-2.1,DESC)

LA:-2.2 = CREATE_LA(T:-2,PHYS_COL(T:-2,A:-1))

T:-2.ADD_ORDER(LA:-2.2,ASC)

LA:-2.3 = CREATE_LA(T:-2,PHYS_COL(T:-1,A:0))

LA:-2.4 = CREATE_LA(T:-2,PHYS_COL(T:-3,A:2))

F:0 = CREATE_FILTER(T:-2,LA:-2.3,=,LA:-2.4,,WHERE)

LA:-2.5 = CREATE_LA(T:-2,PHYS_COL(T:-4,A:0))

LA:-2.6 = CREATE_LA(T:-2,PHYS_COL(T:-3,A:0))

F:0 = AND(F:0,LA:-2.5,=,LA:-2.6,)

LA:-2.7 = CREATE_LA(T:-2,PHYS_COL(T:-4,A:2))

LA:-2.8 = CREATE_LA(T:-2,EXPR("F"))

F:0 = AND(F:0,LA:-2.7,=,LA:-2.8,)

LA:-2.9 = CREATE_LA(T:-2,PHYS_COL(T:-3,A:12))

LA:-2.10 = CREATE_LA(T:-2,PHYS_COL(T:-3,A:11))

F:0 = AND(F:0,LA:-2.9,>,LA:-2.10,)

T:-6 = TABLE_ALIAS(T:1,"lineitem:tpch_1s/l2")

T:-7 = TMP_TABLE(T:-6,"lineitem:tpch_1s/l2")

LA:-7.0 = CREATE_LA(T:-7,EXPR("1"))

A:-1 = T:-7.ADD_COLUMN(LA:-7.0,LIST,"Not_used”,"ALL")

LA:-7.1 = CREATE_LA(T:-7,PHYS_COL(T:-6,A:0))

LA:-7.2 = CREATE_LA(T:-7,EXPR("EXPRESS_FIELD(T:-3,A:0)"))

F:1 = CREATE_FILTER(T:-7,LA:-7.1,=,LA:-7.2,,WHERE)

LA:-7.3 = CREATE_LA(T:-7,PHYS_COL(T:-6,A:2))

LA:-7.4 = CREATE_LA(T:-7,EXPR("EXPRESS_FIELD(T:-3,A:2)"))

F:1 = AND(F:1,LA:-7.3,<>,LA:-7.4,)

T:-7.FILTER(F:1)

T:-7.MODE(LIMIT,0,1)

LA:-2.11 = CREATE_LA(T:-2,SUBQUERY(T:-7))

F:0 = AND(F:0,LA:-2.11,EXISTS,,)

T:-8 = TABLE_ALIAS(T:1,"lineitem:tpch_1s/l3")

T:-9 = TMP_TABLE(T:-8,"lineitem:tpch_1s/l3")

LA:-9.0 = CREATE_LA(T:-9,EXPR("1"))

A:-1 = T:-9.ADD_COLUMN(LA:-9.0,LIST,"Not_used","ALL")

LA:-9.1 = CREATE_LA(T:-9,PHYS_COL(T:-8,A:0))

LA:-9.2 = CREATE_LA(T:-9,EXPR("EXPRESS_FIELD(T:-3,A:0)"))

F:2 = CREATE_FILTER(T:-9,LA:-9.1,=,LA:-9.2,,WHERE)

LA:-9.3 = CREATE_LA(T:-9,PHYS_COL(T:-8,A:2))

LA:-9.4 = CREATE_LA(T:-9,EXPR("EXPRESS_FIELD(T:-3,A:2)"))

F:2 = AND(F:2,LA:-9.3,<>,LA:-9.4,)

LA:-9.5 = CREATE_LA(T:-9,PHYS_COL(T:-8,A:12))

LA:-9.6 = CREATE_LA(T:-9,PHYS_COL(T:-8,A:11))

F:2 = AND(F:2,LA:-9.5,>,LA:-9.6,)

T:-9.FILTER(F:2)

T:-9.MODE(LIMIT,0,1)

LA:-2.12 = CREATE_LA(T:-2,SUBQUERY(T:-9))

F:0 = AND(F:0,LA:-2.12,NOT EXISTS,,)

LA:-2.13 = CREATE_LA(T:-2,PHYS_COL(T:-1,A:3))

LA:-2.14 = CREATE_LA(T:-2,PHYS_COL(T:-5,A:0))

F:0 = AND(F:0,LA:-2.13,=,LA:-2.14,)

LA:-2.15 = CREATE_LA(T:-2,PHYS_COL(T:-5,A:1))

LA:-2.16 = CREATE_LA(T:-2,EXPR(HSAUDI ARABIA")) F:0 = AND(F:0,LA:-2.15,=,LA:-2.16,)

T:-2.FILTER(F:0)

T:-2.MODE(LIMIT,0,100)

RESULT(T:-2)

--打印执行计划结束

Start Query Execution 一开始输出详细的执行过程

Tables:--打印所有主査询中涉及到的表，并给出其别名，表名，所属数据库名，表中数据的行数以及DC数

1 DC

92 DC-- T1表：表别名11(所占92个DC

23 DC

T0: supplier(tpch_1s.supplier), 10000 rows,

T1: 11(tpch_1s .lineitem), 6001215 rows,

属数据库tpch.表实际名lineitem),共6001215行,

T2: orders(tpch_1s.orders), 1500000 rows,

T3: nation(tpch_1s.nation), 25 rows, 1 DC

Condition Weight (non-join):--打印出主査询中所有涉及到的执行条件

cnd(0): nation.N_NAME BET. "SAUDI ARABIA" AND "SAUDI ARABIA"

(2.56)-- 条件 cnd(0) ： nation.N_NAME 的值等于"SAUDI ARABIA"

cnd(1): orders.O_ORDERSTATUS BET. "F" AND "F" (13.52)

cnd(2): l1.L_RECEIPTDATE > l1.L_COMMITDATE (14.91)

cnd(3): (not exists cond.) (30.78) 一条件 cnd(3)： not exists 子查询

cnd(4): (exists cond.) (32.17) 一条件 cnd(4) ： exists 子查询

BEGIN Smart Scan --智能索引过滤开始

T0: total 1 DC, found 1 DC to scan(with 0 FULL DC). 一过滤结果：T0 表:

共1个DC,命中1个DC需要扫描(无Full状态)

T1: total 92 DC,

T2: total 23 DC,

T3: total 1 DC,

found 92 DC to scan(with 0 FULL DC). found 23 DC to scan(with 0 FULL DC). found 1 DC to scan(with 0 FULL DC).

END Smart Scan. (time used: 0.001s)--智能索引过滤结束(耗时 o.ooi秒)

BEGIN Scan --外层表扫描过滤开始(使用并行)

(0)cnd(0) scanned 25 rows, found 1 rows (time used: 0.003s) 一过滤条件

cnd(0)，线程0扫描25行，过滤后得到1行，耗时0.003秒

cnd(0) Done. scanned 25 rows, found 1 rows (time used: 0.013s) 一一过滤条件

cnd(0)结束，结果为1行，耗时0.003秒

(3)cnd(1) scanned 320352 rows, found 156126 rows (time used: 0.143s) 一过滤条件cnd(1),线程3扫描320352行，过滤后得到156126行，耗时0.143秒

(0)cnd(1) scanned 393216 rows, found 191099 rows (time used: 0.148s) 一过滤条件cnd(1),线程0扫描393216行，过滤后得到191099行，耗时0.148秒

(2)cnd(1) scanned 393216 rows, found 190917 rows (time used: 0.152s) 一过滤条件cnd(1),线程2扫描393216行，过滤后得到190917行，耗时0.152秒

cnd(1) scanned 393216 rows, found 191271 rows (time used: 0.152s) 一过滤条件cnd(1),线程1扫描393216行，过滤后得到191271行，耗时0.152秒

cnd(1) Done. scanned 1500000 rows, found 729413 rows (time used: 0.153s) 一过滤条件cnd(1)结束，4个线程共得到结果729413行，耗时0.153秒

cnd(2) scanned 1507328 rows, found 952725 rows (time used: 1.362s) 一过滤条件cnd(2),线程2扫描1507328行，过滤后得到952725行，耗时1.362秒

(0)cnd(2) scanned 1507328 rows, found 953329 rows (time used: 1.394s) 一过滤条件cnd(2),线程0扫描1507328行，过滤后得到953329行，耗时1.394秒

(1)cnd(2) scanned 1507328 rows, found 951861 rows (time used: 1.445s) 一过滤条件cnd(2),线程1扫描1507328行，过滤后得到951861行，耗时1.445秒

--过滤条件cnd（3）开始

| Tables:--（子査询开始，一个，|，代表一层子査询，嵌套累加）打印所有在此层子査询中表中数据的行数以及dc数

92 DC

92 DC

cnd(2) scanned 1479231 rows, found 935381 rows (time used: 1.464s) 一过滤条件cnd(2),线程3扫描1479231行，过滤后得到935381行，耗时1.464秒 cnd(2) Done. scanned 6001215 rows, found 3793296 rows (time used: 1.465s) 一过滤条件cnd(2 )结束，4个线程共得到结果3793296行，耗时1.465秒

涉及到的表信息，给出其别名，表名，所属数据库名，

| T0: l3(tpch_1s .lineitem), 6001215 rows,

| T1: 11(tpch_1s .lineitem), 6001215 rows,

| Condition Weight (non-join):

| cnd(0): l3.L_RECEIPTDATE > l3.L_COMMITDATE (14.91)

| BEGIN Smart Scan --子査询智能索引过滤开始

| T0: total 92 DC, found 92 DC to scan(with 0 FULL DC).

| T1: total 92 DC, found 92 DC to scan(with 0 FULL DC).

| END Smart Scan. (time used: 0.000s) 一子査询智能索引过滤结束(耗时o.ooo 秒)

| BEGIN Scan --子査询表扫描过滤开始

| ( 2)cnd(0) scanned 1507328 rows, found 952725 rows (time used: 0.718s)

| ( 3)cnd(0) scanned 1479231 rows,

| ( 1)cnd(0) scanned 1507328 rows,

| ( 0)cnd(0) scanned 1507328 rows,

| cnd(0) Done. scanned 6001215 rows, found 935381 rows (time used: 0.723s) found 951861 rows (time used: 0.809s) found 953329 rows (time used: 0.816s) found 3793296 rows (time used: 0.817s)

| T0: total 92 DC, found 92 DC after scan(with 0 FULL DC).

| T1: total 92 DC, found 92 DC after scan(with 0 FULL DC).

| END Scan. （time used: 0.817s）--子査询表扫描过滤结束（耗时0.817秒）

| Condition Weight (join):--打印出此层子査询中所有涉及到的(join)执行条件

| cnd(1): 13.L_ORDERKEY = lineitem.L_ORDERKEY (28.27)

| cnd(2): l3.L_SUPPKEY <> lineitem.L_SUPPKEY (62.41)

| BEGIN Join -- exists 相关子査询优化为 semi-join, join开始

| cnd(1, 2):--同时执行子查询过滤条件cnd(1)和cnd(2)(因为条件1, 2中的过滤条

件涉及到的是同一个表，所以可以在一次查询中进行过滤)

| divide to 4 blocks(using round-robin): 953329, 951861, 952725, 935381.一并行 join数据分块(下面针对线程(0)进行说明)

| ( 2)Hash tree is used, size: 16777216

| ( 3)Hash tree is used, size: 16777216

| ( 0)Hash tree is used, size: 16777216 一线程 0 使用的 Hash树，大小 16777216Byte

| ( 1)Hash tree is used, size: 16777216 | ( 3)created hash on all 935381 rows already.

| ( 2)created hash on 945581/952725 rows already.

| ( 0)created hash on 945254/953329 rows already. 一线程 0 开始，由于 hj_buffer 不足以装下hashtab，需要多趟hash

| ( 1)created hash on 945018/951861 rows already.

| ( 3)scanned 976762 rows, and produced 2368991 rows already.

| ( 3)skipped 73.91% DCs by smart index.

| ( 3)inner join(T0 - T1), using [semi]hash join, produced 852498

rows.

| ( 2)scanned 993904 rows, and produced 2393058 rows already.

| ( 2)created hash on all 952725 rows already.

| ( 1)scanned 951861 rows, and produced 2387745 rows already.

| ( 1)created hash on all 951861 rows already.

| ( 2)scanned 82850 rows, and produced 2410775 rows already.

| ( 2)skipped 85.86% DCs by smart index.

| ( 2)inner join(T0 - T1), rows.

| ( 0)scanned 953329 rows,

束，扫描了 953329条数据，

using [semi]hash join,

produced 867844

and produced 2390632 rows already.一线程 0 第 1 趟结共匹配上2390632条结果

| ( 0)created hash on all 953329 rows already. 一线程 0 第 2 趟 hash 开始，在剩余的数据上建 hashtab(953329 - 945254)

| ( 1)scanned 82685 rows, and produced 2405074 rows already.

| ( 1)skipped 86.41% DCs by smart index.

produced 866938

| ( 1)inner join(T0 - T1), using [semi]hash join,

rows.

| ( 0)scanned 82854 rows, and produced 2410584 rows already.

| ( 0)skipped 86.41% DCs by smart index. -- 智能索引直接过滤掉 86.41%的 DC, join 时不需要全部扫描

produced 868336

| ( 0)inner join(T0 - T1), using [semi]hash join,

rows.--线程0最终共得到868336条结果(去重之后)

| Parallel join done. Merged tuple: 3455616 rows一线程 0-4 经过过滤共得到 3455616 行

| cnd(1, 2) Done(time used: 3.792s) --过滤条件 cnd(1)和 cnd(2)结束，耗时 3.792 秒

| END Join(time used: 3.792s) 一 exists 相关子査询结束，耗时 3.792 秒

cnd(3) scanned 935381 rows, found 82883 rows (time used: 0.164s) — 过

滤条件cnd(3),线程3,共扫描935381行，过滤后得到82883行

(2)cnd(3) scanned 952725 rows, found 84881 rows (time used: 0.191s)一过

滤条件cnd(3),线程2,共扫描952725行，过滤后得到84881行

(0)cnd(3) scanned 953329 rows, found 84993 rows (time used: 0.194s)一过

滤条件cnd(3),线程0,共扫描953329行，过滤后得到84993行

(1)cnd(3) scanned 951861 rows, found 84923 rows (time used: 0.198s)一过

滤条件cnd(3),线程1,共扫描3793296行，过滤后得到84923行

cnd(3) Done. scanned 3793296 rows, found 337680 rows (time used: 4.808s)一过滤条件cnd(3)结束，4个线程共扫描3793296行，过滤后得到337680行

--过滤条件cnd(3)结束

--过滤条件cnd(4)开始

| Tables:

| T0: l2(tpch_1s .lineitem),

6001215 rows,

92 DC

| T1: 11(tpch_1s .lineitem),

| Condition Weight (non-join):

| BEGIN Smart Scan

| T0: total 92 DC, found 92 DC to scan(with 0 FULL DC).--智能索引结果:

TO表：共92个DC,命中92个DC需要扫描(无Full状态)

| T1: total 92 DC, found 92 DC to scan(with 0 FULL DC).

| END Smart Scan. (time used: 0.000s)

| BEGIN Scan

| T0: total 92 DC, found 92 DC after scan(with 92 FULL DC).

| T1: total 92 DC, found 92 DC after scan(with 0 FULL DC).

| END Scan. (time used: 0.000s)

| Condition Weight (join):

| cnd(0): l2.L_ORDERKEY = lineitem.L_ORDERKEY (24.08)

| cnd(1): l2.L_SUPPKEY <> lineitem.L_SUPPKEY (57.65)

| BEGIN Join

| cnd(0, 1):

| divide to 4 blocks(using round-robin): 1507328, 1507328, 1507328, 1479231.

| ( 0)Hash tree is used, size: 16777216

| ( 2)Hash tree is used, size: 16777216

| ( 1)Hash tree is used, size: 16777216

| ( 3)Hash tree is used, size: 16777216

| ( 1)created hash on all 337680 rows already.

| ( 0)created hash on all 337680 rows already.

| ( 3)created hash on all 337680 rows already.

| ( 2)created hash on all 337680 rows already.

| ( 0)scanned 1507328 rows,

| ( 0)inner join(T0 - T1),

rows.

| ( 3)scanned 1479231 rows,

| ( 3)inner join(T0 - T1),

rows.

| ( 1)scanned 1507328 rows,

| ( 1)inner join(T0 - T1),

rows.

| ( 2)scanned 1507328 rows,

and produced 95770 rows already. using [semi]hash join,

and produced 94179 rows already. using [semi]hash join,

and produced 96380 rows already. using [semi]hash join,

and produced 96285 rows already.

produced

50502

49729

50994

| ( 2)inner join(T0 - T1), using [semi]hash join, produced 50867

rows.

| Parallel join done. Merged tuple: 202092 rows

| cnd(0, 1) Done(time used: 1.036s)

| END Join(time used: 1.036s)

(0)cnd(4) scanned 84993 rows, found 50503 rows (time used: 0.017s)一过

滤条件cnd(4)，线程0共扫描84993行，过滤后得到50503行，耗时0.017秒

( 2)cnd(4) scanned 84881 rows,

( 1)cnd(4) scanned 84923 rows,

( 3)cnd(4) scanned 82883 rows,

cnd(4) Done. scanned 337680 rows,

found 50867 rows (time used: 0.018s)

found 50993 rows (time used: 0.018s)

found 49729 rows (time used: 0.019s)

found 202092 rows (time used: 1.056s)一

过滤条件cnd(4)结束，4个线程共扫描337680行，过滤后得到202092行

--过滤条件cnd(4)开始结束

T0:

果，

T1:

T2:

T3:

total 1 DC, found 1 DC after scan(with 1 FULL DC). 一表扫描最终结

T0表所有DC数据全部命中

found 92 DC after scan(with 0 FULL DC). found 23 DC after scan(with 0 FULL DC). found 1 DC after scan(with 0 FULL DC).

total 92 DC,

total 23 DC,

total 1 DC,

END Scan. (time used: 7.508s)--外层表扫描过滤结束，耗时7.508秒

Condition Weight (join):--打印主査询部分的所有需要进行join的条件

cnd(3): supplier.S_NATIONKEY = nation.N_NATIONKEY (19.69)

cnd(4): supplier.S_SUPPKEY = l1.L_SUPPKEY (28.55)

cnd(5): orders.O_ORDERKEY = l1.L_ORDERKEY (24.09)

BEGIN Join

cnd(3): -- 执行 join 条件 cnd(3)开始

divide to 1 blocks(using round-robin): 10000.

(0)Hash tree is used, size: 68

(0)created hash on all 1 rows already.

( 0)scanned 10000 rows, and produced 411 rows already.

( 0)inner join(T0 - T3), using hash join, produced 411 rows.

Parallel join done. Merged tuple: 411 rows -- T0 与 T3 的 join结束，结果 411 行

cnd(3) Done(time used: 0.093s) -- 执行 join 条件 cnd(3)结束

cnd(4): divide to 4 blocks(using round-robin): 52686, 48810, 50867, 49729.

(0)Hash tree is used, size: 20944

(3)Hash tree is used, size: 20944

( 2)Hash tree is used, size: 20944

( 1)Hash tree is used, size: 20944

(0)created hash on all 411 rows already.

created hash on all 411 rows already.
created hash on all 411 rows already.

(1)created hash on all 411 rows already.

(3)scanned 49729 rows, and produced 2090 rows already.

(3)inner join(T0 - T1), using hash join, produced 2090 rows.

(1)scanned 48810 rows, and produced 1963 rows already.

( 1)inner join(T0 - T1), using hash join, produced 1963 rows.

( 2)scanned 50867 rows, and produced 2090 rows already.

( 2)inner join(T0 - T1), using hash join, produced 2090 rows.

(0)scanned 52686 rows, and produced 2214 rows already.

( 0)inner join(T0 - T1), using hash join, produced 2214 rows.

Parallel join done. Merged tuple: 8357 rows-- T0 与 T1 的 join结束，结果 8357 行 cnd(4) Done(time used: 0.081s) cnd(5):

divide to 4 blocks(using round-robin): 191099, 191271, 190917, 156126. ( 3)Hash tree is used, size: 479468

( 1)Hash tree is used, size: 479468

( 2)Hash tree is used, size: 479468

( 0)Hash tree is used, size: 479468

( 3)created hash on all 8357 rows already.

( 1)created hash on all 8357 rows already.

( 0)created hash on all 8357 rows already.

( 2)created hash on all 8357 rows already.

(3)scanned 156126 rows, and produced 913 rows already.

( 3)inner join(T1 - T2), using hash join, produced 913 rows.

( 1)scanned 191271 rows, and produced 1065 rows already.

( 1)inner join(T1 - T2), using hash join, produced 1065 rows.

( 2)scanned 190917 rows, and produced 1073 rows already.

( 2)inner join(T1 - T2), using hash join, produced 1073 rows.

( 0)scanned 191099 rows, and produced 1090 rows already.

(0)inner join(T1 - T2), using hash join, produced 1090 rows.

Parallel join done. Merged tuple: 4141 rows-- T1 与 T2 的 join结束，结果 4141 行 cnd(5) Done(time used: 0.183s)

END Join(time used: 0.358s)--主査询部分，join 结束

BEGIN Parallel Aggregation(4141 rows) 一 开始并行分组聚集操作 op buffer size: 67108864, tuple width: 36. op buffer can hold 1864135 rows total tuples(per thread): 1035, tuples in op buffer(per thread): 466033 begin distinct ratio sampling...--采样评估，以确定数据切分方法 finish distinct ratio sampling: less than 2 cells. (time used: 0.000s)

Begin parallel splitting for aggregation (split_type = 1)-- 并行划分开始 (0)split by hash already(4141 rows). -- 数据分块，采用 hash 划分 divide to 4 blocks(using hash[parallel]): 949, 951, 1146, 1095.

End parallel splitting for aggregation (split_type = 1)-- 并行划分结束

(0)BEGIN Aggregation(949 rows)--线程0开始聚集操作，需处理949行

( 2)BEGIN Aggregation(1146 rows)

( 3)BEGIN Aggregation(1095 rows)

( 1)BEGIN Aggregation(951 rows)

(0)Commit 98 result rows..一线程 0 提交 98 个结果

( 2)Commit 110 result rows.

( 3)Commit 107 result rows.

( 1)Commit 96 result rows.

(0)produced total 98 groups.一线程0经过聚集，得到98个分组

( 2)produced total 110 groups.

( 3)produced total 107 groups.

( 1)produced total 96 groups.

produced total 411 groups. 一并行分组聚集结束，共得到411个分组

END Parallel Aggregation. (time used: 0.010s)一 结束并行分组聚集操作，耗时 0.010秒

limit sorter initialized, total_bytes: 64, key_bytes: 28.

BEGIN Parallel Sort(411 rows)--开始并行排序

divide to 1 blocks: 411.一并行排序分块，共分1块

( 0)BEGIN Sort(411 rows)

(0)limit sorter initialized, total_bytes: 64, key_bytes: 28. 一一 Order By Limit，并行
线程内部使用Limit Sorter,行宽64bytes，排序列28bytes

BEGIN Merge.--并行排序结果归并

Send 100 rows already

parallel sort done.--并行排序结束

ResultSender: send 100 rows.一 发送结果 100 行

output result done.一- sql 执行结束

One-Pass Hash Join 执行计划 trace

在启用或评估使用One-Pass Hash Join算法进行Join计算时，trace为本算法信息，为避免重复，以下仅节选与Join算子相关的trace信息，其它算子trace与上述章节类似。

BEGIN Scan --外层表扫描过滤开始(使用并行) ( 0)cnd(0) cnd(0) Done.

( 3)cnd(1)

( 2)cnd(1)

( 1)cnd(1)

( 0)cnd(1) cnd(1) Done.

( 3)cnd(2)

( 1)cnd(2)

( 0)cnd(2)

( 2)cnd(2) cnd(2) Done.

scanned 25 rows, found 1 rows (time used: 0.170s)

scanned 25 rows, found 1 rows (time used: 0.639s)

scanned 320352 rows, scanned 393216 rows, scanned 393216 rows, scanned 393216 rows, scanned 1500000 rows,

scanned 1479231

scanned 1507328

scanned 6001215

found 156126 rows (time used: 1.855s) found 190917 rows (time used: 2.193s) found 191271 rows (time used: 2.283s) found 191099 rows (time used: 2.379s) found 729413 rows (time used: 2.497s)

rows, found 935381 rows (time used: 1.427s) rows, found 951861 rows (time used: 1.434s) rows, found 953329 rows (time used: 1.443s) rows, found 952725 rows (time used: 1.563s) rows, found 3793296 rows (time used: 1.565s)

--过滤条件cnd(3)开始

嵌套累加）

92 DC

| Tables:--(子査询开始，一个代表一层子査询，

| T0: l3(tpch_1s.lineitem), 6001215 rows,

| T1: l1(tpch_1s.lineitem), 6001215 rows,

| Condition Weight (non-join):

| cnd(0): l3.L_RECEIPTDATE > l3.L_COMMITDATE (14.91)

| BEGIN Smart Scan --子査询智能索引过滤开始

| T0: total 92 DC, found 92 DC to scan(with 0 FULL DC).

| T1: total 92 DC, found 92 DC to scan(with 0 FULL DC).

| END Smart Scan. （time used: 0.000s） 一子査询智能索引过滤结束（耗时o.ooo 秒）

| BEGIN Scan --子查询表扫描过滤开始

| ( 3)cnd(0) scanned 1479231 rows,

| ( 1)cnd(0) scanned 1507328 rows,

| ( 2)cnd(0) scanned 1507328 rows,

| ( 0)cnd(0) scanned 1507328 rows,

| cnd(0) Done. scanned 6001215 rows,

found 935381 rows (time used: 0.675s) found 951861 rows (time used: 0.676s) found 952725 rows (time used: 0.686s) found 953329 rows (time used: 0.738s) found 3793296 rows (time used: 0.739s)

| T0: total 92 DC, found 92 DC after scan(with 0 FULL DC).

| T1: total 92 DC, found 92 DC after scan(with 0 FULL DC).

| END Scan. （time used: 0.739s） 一子査询表扫描过滤结束（耗时0.739秒）

| Condition Weight (join):

| cnd(1): 13.L_ORDERKEY = lineitem.L_ORDERKEY (28.27)

| cnd(2): l3.L_SUPPKEY <> lineitem.L_SUPPKEY (62.41)

| BEGIN Join -- exists 相关子査询优化为 semi-join, join 开始，使用 One-Pass 算法

| cnd(1, 2):--同时执行子查询过滤条件cnd(1)和cnd(2)

| op buffer size: 67108864, tuple width: 20. op buffer can hold 3355443 rows -- 算

子 buffer 67108864B，行宽 20B，算子 Buffer 可容纳 3355443 行

| Begin one-pass hash partitioning: divide 3793296 tuples into 20 parts. mat_bujsize = 559104 --开始并行One-Pass划分表1,要将3793296行划分为20 个分片

| ( 2)mat partition thread: divide 948324 tuples into 20 parts, min 46722 tuples, max 47814 tuples, avg 47416 tuples. -- 并行 Hash划分线程 2 结束，948324 行划分为 20 个分片，分片命中行数统计：最少46722行，最多47814行，平均47416行

| ( 1)mat partition thread: divide 948324 tuples into 20 parts, min 46812 tuples, max 47990 tuples, avg 47416 tuples.

| ( 3)mat partition thread: divide 948324 tuples into 20 parts, min 46805 tuples, max 47623 tuples, avg 47416 tuples.

| ( 0)mat partition thread: divide 948324 tuples into 20 parts, min 46982 tuples, max 47856 tuples, avg 47416 tuples.

| Finish one-pass hash partitioning: divide 3793296 tuples into 20 parts. mat_bujsize = 559104. (time used: 2.130s) -- 结束并行 One-Pass 划分表 1,耗时

2.130 秒

| Begin one-pass hash partitioning: divide 3793296 tuples into 20 parts. mat_buf_size = 559104 -- 开始并行 One-Pass 划分表 2

| ( 0)mat partition thread: divide 948324 tuples into 20 parts, min 46982 tuples, max 47856 tuples, avg 47416 tuples.

| ( 1)mat partition thread: divide 948324 tuples into 20 parts, min 46812 tuples, max 47990 tuples, avg 47416 tuples.

| ( 3)mat partition thread: divide 948324 tuples into 20 parts, min 46805 tuples, max 47623 tuples, avg 47416 tuples.

| ( 2)mat partition thread: divide 948324 tuples into 20 parts, min 46722 tuples, max 47814 tuples, avg 47416 tuples.

| Finish one-pass hash partitioning: divide 3793296 tuples into 20 parts. mat_bujsize = 559104. (time used: 1.474s) -- 结束并行 hash 划分表 2

| Finish One-Pash Hash Join preparation: divided each side into 20 partitions -- 两边的表One-Pass Hash划分结束

| ( 2)Hash tree is used, size: 16777216

| ( 3)Hash tree is used, size: 16777216

| ( 1)Hash tree is used, size: 16777216

| ( 0)Hash tree is used, size: 16777216 一线程 0 使用的 Hash树，大小 16777216Byte

| ( 3)created hash on all 189464 rows already.(semi)(pass 1/5)

| ( 2)created hash on all 189750 rows already.(semi)(pass 1/5)

| ( 3)scanned 189464 rows, and produced 172689 rows already.

| ( 2)scanned 189750 rows, and produced 172919 rows already.

| ( 1)created hash on all 190371 rows already.(semi)(pass 1/5)

| ( 0)created hash on all 189378 rows already.(semi)(pass 1/5) -- 以上总划分成 20 个分片，现有4个线程，在每个线程处理20/4=5个分片，“pass 1/5”表示线程0正在处理属于自己的第1个分片(以下简称“n/5分片”)，线程0第1/5分片开始，需要扫描189378 行

| ( 3)created hash on all 190360 rows already.(semi)(pass 2/5)

and produced 173472 rows already.

and produced 346057 rows already.

and produced 172342 rows already. 一线程 0 第 1/5 共匹配上172342行结果

| ( 2)created hash on all 190065 rows already.(semi)(pass 2/5) | ( 1)scanned 190371 rows,

| ( 3)scanned 190360 rows,

| ( 0)scanned 189378 rows,

分片结束，扫描了 189378行，

| ( 2)scanned 190065 rows, and produced 346124 rows already.

| ( 1)created hash on all 189265 rows already.(semi)(pass 2/5)

| ( 3)created hash on all 189234 rows already.(semi)(pass 3/5)

| ( 0)created hash on all 189438 rows already.(semi)(pass 2/5) 一线程 0 第 2/5 分片

开始，需要扫描189378行

| ( 2)created hash on all 190138 rows already.(semi)(pass 3/5)

and produced 345887 rows already.

and produced 518680 rows already.

and produced 519459 rows already.

and produced 344948 rows already. 一线程 0 第 2/5 共匹配上344948行结果

| ( 1)scanned 189265 rows,

| ( 3)scanned 189234 rows,

| ( 2)scanned 190138 rows,

| ( 0)scanned 189438 rows,

分片结束，扫描了 189378行，

| ( 3)created hash on all 190263 rows already.(semi)(pass 4/5)

| ( 1)created hash on all 189193 rows already.(semi)(pass 3/5)

| ( 2)created hash on all 189755 rows already.(semi)(pass 4/5)

| ( 0)created hash on all 189166 rows already.(semi)(pass 3/5) 一线程 0 第 3/5 分片

开始，需要扫描189166行

and produced 692205 rows already.

and produced 518106 rows already.

and produced 692334 rows already.

and produced 517033 rows already. 一线程 0 第 3/5 共匹配上517033行结果

| ( 3)scanned 190263 rows,

| ( 1)scanned 189193 rows,

| ( 2)scanned 189755 rows,

| ( 0)scanned 189166 rows,

分片结束，扫描了 189166行，

| ( 3)created hash on all 189636 rows already.(semi)(pass 5/5)

| ( 1)created hash on all 190126 rows already.(semi)(pass 4/5)

| ( 2)created hash on all 189417 rows already.(semi)(pass 5/5)

| ( 0)created hash on all 188919 rows already.(semi)(pass 4/5) 一线程 0 第 4/5 分片

开始，需要扫描188919行

| ( 3)scanned 189636 rows,

| ( 3)inner join(T0 - T1),

rows.

| ( 1)scanned 190126 rows,

| ( 2)scanned 189417 rows,

| ( 2)inner join(T0 - T1),

rows.

| ( 0)scanned 188919 rows,

分片结束，扫描了 188919行，

and produced 864986 rows already. using [semi]hash join, produced

and produced 691468 rows already. and produced 864760 rows already. using [semi]hash join, produced

864986

864760

and produced 689061 rows already. 一线程 0 第 4/5 共匹配上689061行结果

| ( 1)created hash on all 189733 rows already.(semi)(pass 5/5)

| ( 0)created hash on all 189625 rows already.(semi)(pass 5/5) 一线程 0 第 5/5 分片

开始，需要扫描189625行

| ( 1)scanned 189733 rows,

| ( 1)inner join(T0 - T1),

| ( 0)inner join(T0 - T1),

using [semi]hash join,

produced 861597

and produced 864273 rows already.

using [semi]hash join, produced 864273 rows.

| ( 0)scanned 189625 rows, and produced 861597 rows already. 一线程 0 第 5/5 分片结束，扫描了 189625行，共匹配上861597行结果

rows.--线程0的5分片全结束，最终匹配上861597行结果

| Parallel join done. Merged tuple: 3455616 rows 一线程 0-4 经过过滤共得到 3455616 行

| cnd(1, 2) Done(time used: 6.396s) --过滤条件 cnd(1 )和 cnd(2)结束，耗时 6.396 秒

| END Join(time used: 6.396s) 一 exists 相关子査询结束，耗时 6.396 秒

( 3)cnd(3) scanned 935381 rows, found 82883 rows (time used: 0.154s)

(0)cnd(3) scanned 953329 rows, found 84993 rows (time used: 0.158s)一过

滤条件cnd(3),线程0,共扫描953329行，过滤后得到84993行

( 1)cnd(3) scanned 951861 rows, found 84923 rows (time used: 0.160s)

( 2)cnd(3) scanned 952725 rows, found 84881 rows (time used: 0.169s)

cnd(3) Done. scanned 3793296 rows, found 337680 rows (time used: 7.309s)一过滤条件cnd(3)结束，4个线程共扫描3793296行，过滤后得到337680行

--过滤条件cnd(3)结束

--过滤条件cnd(4)开始

| Tables:

| T0: l2(tpch_1s.lineitem), 6001215 rows, 92 DC

| T1: l1(tpch_1s.lineitem), 6001215 rows, 92 DC

| Condition Weight (non-join):

| BEGIN Smart Scan

| T0: total 92 DC, found 92 DC to scan(with 0 FULL DC).

| T1: total 92 DC, found 92 DC to scan(with 0 FULL DC).

| END Smart Scan. (time used: 0.000s)

| BEGIN Scan

| T0: total 92 DC, found 92 DC after scan(with 92 FULL DC).

| T1: total 92 DC, found 92 DC after scan(with 0 FULL DC).

| END Scan. (time used: 0.000s) | Condition Weight (join):

| cnd(0): l2.L_ORDERKEY = lineitem.L_ORDERKEY (24.08)

| cnd(1): l2.L_SUPPKEY <> lineitem.L_SUPPKEY (57.65)

| BEGIN Join --使用 One-Pass Hash Join 算法，同上

| cnd(0, 1):

| op buffer size: 67108864, tuple width: 20. op buffer can hold 3355443 rows | Begin one-pass hash partitioning: divide 337680 tuples into 4 parts. mat_bujsize = 2796032

| ( 3)mat partition thread: divide 84420 tuples into 4 parts, min 20847 tuples, max 21258 tuples, avg 21105 tuples.

| ( 0)mat partition thread: divide 84420 tuples into 4 parts, min 21007 tuples, max 21228 tuples, avg 21105 tuples.

| ( 1)mat partition thread: divide 84420 tuples into 4 parts, min 20917 tuples, max 21282 tuples, avg 21105 tuples.

| ( 2)mat partition thread: divide 84420 tuples into 4 parts, min 20825 tuples, max 21310 tuples, avg 21105 tuples.

| Finish one-pass hash partitioning: divide 337680 tuples into 4 parts. mat_bujsize = 2796032. (time used: 0.275s) | Begin one-pass hash partitioning: divide 6001215 tuples into 4 parts. mat_buf_size

= 2796032

| ( 0)mat partition thread: divide 1500303 tuples into 4 parts, min 374715 tuples, max 375380 tuples, avg 375075 tuples.

| ( 2)mat partition thread: divide 1500303 tuples into 4 parts, min 373992 tuples, max 375740 tuples, avg 375075 tuples.

| ( 1)mat partition thread: divide 1500303 tuples into 4 parts, min 374068 tuples, max 375807 tuples, avg 375075 tuples.

| ( 3)mat partition thread: divide 1500306 tuples into 4 parts, min 374139 tuples, max 375718 tuples, avg 375076 tuples.

| Finish one-pass hash partitioning: divide 6001215 tuples into 4 parts. mat_buf_size = 2796032. (time used: 2.012s)

| Finish One-Pash Hash Join preparation: divided each side into 4 partitions | ( 0)Hash tree is used, size: 16777216

| ( 2)Hash tree is used, size: 16777216

| ( 3)Hash tree is used, size: 16777216

| ( 1)Hash tree is used, size: 16777216

| ( 3)created hash on all 83971 rows already. (pass 1/1)

| ( 2)created hash on all 84365 rows already. (pass 1/1)

| ( 1)created hash on all 84415 rows already. (pass 1/1)

| ( 0)created hash on all 84929 rows already. (pass 1/1)

| ( 3)scanned 1500705 rows,

and produced 95140 rows already.

| ( 3)inner join(T0 - T1), rows.

| ( 1)scanned 1501579 rows,

| ( 1)inner join(T0 - T1),

using [semi]hash join,

and produced 95767 rows already. using [semi]hash join,

produced

50553

50206

rows.

| ( 2)scanned 1500137 rows,

and produced 95229 rows already.

| ( 2)inner join(T0 - T1),

produced

50441

using [semi]hash join,

rows.

and produced 96478 rows already.

| ( 0)scanned 1498794 rows,

| ( 0)inner join(T0 - T1),

produced

50892

using [semi]hash join,

rows.

| Parallel join done. Merged tuple: 202092 rows

| cnd(0, 1) Done(time used: 3.532s)

| END Join(time used: 3.532s)

( 3)cnd(4)

( 1)cnd(4)

( 2)cnd(4)

( 0)cnd(4)

scanned 82883

scanned 84923

scanned 84881

scanned 84993

滤条件cnd(4)，线程0共扫描

rows,

84993

found 49729 rows (time used: 0.015s)

found 50993 rows (time used: 0.015s)

found 50867 rows (time used: 0.015s)

found 50503 rows (time used: 0.016s)一过

行，过滤后得到50503行，耗时0.016秒

T0:	total 1 DC,
T1:	total 92 DC,
T2:	total 23 DC,
T3:	total 1 DC,

cnd(4) Done. scanned 337680 rows, found 202092 rows (time used: 3.549s)一过滤条件cnd(4)结束，4个线程共扫描337680行，过滤后得到202092行

--过滤条件cnd(4)开始结束

found 1 DC after scan(with 1 FULL DC), found 92 DC after scan(with 0 FULL DC). found 23 DC after scan(with 0 FULL DC). found 1 DC after scan(with 0 FULL DC).

END Scan. (time used: 15.583s)--外层表扫描过滤结束，耗时15.583秒

Condition Weight (join):--打印主査询部分的所有需要进行join的条件

cnd(3): supplier.S_NATIONKEY = nation.N_NATIONKEY (19.69)

cnd(4): supplier.S_SUPPKEY = l1.L_SUPPKEY (28.55)

cnd(5): orders.O_ORDERKEY = l1.L_ORDERKEY (24.09)

BEGIN Join --使用 One-Pass Hash Join 算法，同上

cnd(3):--执行 join 条件 cnd(3)开始

op buffer size: 67108864, tuple width: 12. op buffer can hold 5592405 rows

Begin one-pass hash partitioning: divide 1 tuples into 4 parts. mat_bujsize = 2796032

( 0)mat partition thread: divide 0 tuples into 4 parts, min 0 tuples, max 0 tuples, avg 0 tuples.

( 1)mat partition thread: divide 0 tuples into 4 parts, min 0 tuples, max 0 tuples, avg 0 tuples.

( 2)mat partition thread: divide 0 tuples into 4 parts, min 0 tuples, max 0 tuples, avg 0 tuples.

( 3)mat partition thread: divide 1 tuples into 4 parts, min 0 tuples, max 1 tuples, avg 0 tuples.

Finish one-pass hash partitioning: divide 1 tuples into 4 parts. mat_buf_size = 2796032. (time used: 0.002s)

Begin one-pass hash partitioning: divide 10000 tuples into 4 parts. mat_buf_size = 2796032

( 0)mat partition thread: divide 2500 tuples into 4 parts, min 563 tuples, max 730 tuples, avg 625 tuples.

( 1)mat partition thread: divide 2500 tuples into 4 parts, min 585 tuples, max 682 tuples, avg 625 tuples.

( 2)mat partition thread: divide 2500 tuples into 4 parts, min 577 tuples, max 711 tuples, avg 625 tuples.

( 3)mat partition thread: divide 2500 tuples into 4 parts, min 588 tuples, max 714 tuples, avg 625 tuples.

Finish one-pass hash partitioning: divide 10000 tuples into 4 parts. mat_buf_size =

2796032. (time used: 0.010s)

Finish One-Pash Hash Join preparation: divided each side into 4 partitions

( 0)Hash tree is used, size: 68

(3)Hash tree is used, size: 68

Hash tree is used, size: 68
Hash tree is used, size: 68

using hash join, using hash join, using hash join,

produced 0 rows. produced 0 rows. produced 0 rows.

( 0)inner join(T0 - T3),

( 3)inner join(T0 - T3),

( 1)inner join(T0 - T3), ( 2)created hash on all 1 rows already. (pass 1/1)

(2)scanned 2837 rows, and produced 411 rows already.

( 2)inner join(T0 - T3), using hash join, produced 411 rows.

Begin rowid sorting: 4 inputs --(为了优化后续物化效率)进行行号排序开始。以上4 个线程结果作为排序输入

sort rowid: max_dc_segment_no = 2, total_tuple_no = 411, dop = 4,

average_thread_tuple_no = 103 一 de segment 为 2,共 411 行，4 个排序线程，平均每线程103处理行。名词解释：默认每256个DC作为一个de_window，则平均每个线程处理dc_window/dop个dc_window，均值小于1时，则部分线程无任务。 max_de_segment_no=MAX(de_window/dop, dop)

sort rowid: thread[0] processes 1 dc_window (411 tuples) from dc_window[0] 一一线程0处理1个de_window，共411行，起始de_window编号为0

sort rowid: thread[1] processes 1 dc_window (0 tuples) from dc_window[1] 一一线程 0处理0个de_window，共0行，起始de_window编号为1

Finish rowid sorting: 4 inputs, 411 rows. (time used: 0.011s) -- 行号排序结束 Parallel join done. Merged tuple: 411 rows 一 T0 与 T3 的 join结束，合并得到结果 411行

cnd(3) Done(time used: 0.034s) -- 执行 join 条件 cnd(3)结束

cnd(4):

op buffer size: 67108864, tuple width: 12. op buffer can hold 5592405 rows

Begin one-pass hash partitioning: divide 411 tuples into 4 parts. mat_buf_size = 2796032

( 0)mat partition thread: divide 102 tuples into 4 parts, min 23 tuples, max 30 tuples, avg 25 tuples.

( 2)mat partition thread: divide 102 tuples into 4 parts, min 22 tuples, max 33 tuples, avg 25 tuples.

( 1)mat partition thread: divide 102 tuples into 4 parts, min 22 tuples, max 29 tuples, avg 25 tuples.

( 3)mat partition thread: divide 105 tuples into 4 parts, min 19 tuples, max 32 tuples, avg 26 tuples.

Finish one-pass hash partitioning: divide 411 tuples into 4 parts. mat_buf_size = 2796032. (time used: 0.009s)

Begin one-pass hash partitioning: divide 202092 tuples into 4 parts. mat_buf_size = 2796032

( 1)mat partition thread: divide

12690 tuples, avg 12630 tuples.

( 2)mat partition thread: divide

12828 tuples, avg 12630 tuples.

( 0)mat partition thread: divide

12686 tuples, avg 12630 tuples.

( 3)mat partition thread: divide

12724 tuples, avg 12630 tuples.

Finish one-pass hash partitioning: divide 202092 tuples into 4 parts. mat_buf_size = 2796032. (time used: 0.044s)

Finish One-Pash Hash Join preparation: divided each side into 4 partitions

50523

tuples into

parts, min

12596 tuples, max

12412 tuples, max

12595 tuples, max

12568 tuples, max

( 1)Hash tree is used, size: 20944

( 0)Hash tree is used, size: 20944

( 2)Hash tree is used, size: 20944

( 3)Hash tree is used, size: 20944

( 2)created hash on all 93 rows already. (pass 1/1)

( 3)created hash on all 95 rows already. (pass 1/1)

( 1)created hash on all 104 rows already. (pass 1/1)

(0)created hash on all 119 rows already. (pass 1/1)

( 3)scanned 50928 rows,

( 1)scanned 50414 rows,

( 2)scanned 50548 rows,

( 0)scanned 50202 rows,

and produced 1947 rows already. and produced 2129 rows already. and produced 1896 rows already. and produced 2385 rows already.

( 2)inner join(T0 - T1),

( 1)inner join(T0 - T1),

( 0)inner join(T0 - T1),

( 3)inner join(T0 - T1),

using hash join, using hash join, using hash join, using hash join,

produced 1896 rows. produced 2129 rows. produced 2385 rows. produced 1947 rows.

Begin rowid sorting: 4 inputs

total_tuple_no = 8357, dop = 4,

sort rowid: max_dc_segment_no = 2,

average_thread_tuple_no = 2090

sort rowid: thread[0] processes 1 dc_window (8357 tuples) from dc_window[0]

sort rowid: thread[1] processes 1 dc_window (0 tuples) from dc_window[1]

Finish rowid sorting: 4 inputs, 8357 rows. (time used: 0.022s)

Parallel join done. Merged tuple: 8357 rows-- T0 与 T1 的 join结束，结果 8357 行 cnd(4) Done(time used: 0.215s) cnd(5):

op buffer size: 67108864, tuple width: 20. op buffer can hold 3355443 rows

Begin one-pass hash partitioning: divide 8357 tuples into 4 parts. mat_buf_size = 2796032

mat partition thread: divide 2089 tuples into 4 parts, min 516 tuples, max 534 tuples, avg 522 tuples.

( 2)mat partition thread: divide 2089 tuples into 4 parts, min 480 tuples, max 543 tuples, avg 522 tuples.

( 3)mat partition thread: divide 2090 tuples into 4 parts, min 497 tuples, max 551 tuples, avg 522 tuples.

( 0)mat partition thread: divide 2089 tuples into 4 parts, min 511 tuples, max 534 tuples, avg 522 tuples.

Finish one-pass hash partitioning: divide 8357 tuples into 4 parts. mat_buf_size = 2796032. (time used: 0.018s)

Begin one-pass hash partitioning: divide 729413 tuples into 4 parts. mat_buf_size = 2796032

( 0)mat partition thread: divide 182353 tuples into 4 parts, min 45495 tuples, max 45658 tuples, avg 45588 tuples.

( 3)mat partition thread: divide 182354 tuples into 4 parts, min 45436 tuples, max 45721 tuples, avg 45588 tuples.

( 1)mat partition thread: divide 182353 tuples into 4 parts, min 45386 tuples, max 45819 tuples, avg 45588 tuples.

( 2)mat partition thread: divide 182353 tuples into 4 parts, min 45434 tuples, max 45818 tuples, avg 45588 tuples.

Finish one-pass hash partitioning: divide 729413 tuples into 4 parts. mat_buf_size = 2796032. (time used: 0.137s)

Finish One-Pash Hash Join preparation: divided each side into 4 partitions ( 2)Hash tree is used, size: 479468

( 0)Hash tree is used, size: 479468

( 1)Hash tree is used, size: 479468

(3)Hash tree is used, size: 479468

(0)created hash on all 2146 rows already. (pass 1/1)

(3)created hash on all 2082 rows already. (pass 1/1)

created hash on all 2041 rows already. (pass 1/1)

created hash on all 2088 rows already. (pass 1/1)
scanned 182451 rows, and produced 995 rows already.

inner join(T1 - T2), using hash join, produced 995 rows.
scanned 182533 rows, and produced 1054 rows already.

( 3)inner join(T1 - T2), using hash join, produced 1054 rows.

(0)scanned 182657 rows, and produced 1069 rows already.

( 0)inner join(T1 - T2), using hash join, produced 1069 rows.

(1)scanned 181772 rows, and produced 1023 rows already.

( 1)inner join(T1 - T2), using hash join, produced 1023 rows.

Begin rowid sorting: 4 inputs

sort rowid: max_dc_segment_no = 2, total_tuple_no = 4141, dop = 4,

average_thread_tuple_no = 1036

sort rowid: thread[0] processes 1 dc_window (4141 tuples) from dc_window[0]

sort rowid: thread[1] processes 1 dc_window (0 tuples) from dc_window[1]

Finish rowid sorting: 4 inputs, 4141 rows. (time used: 0.003s)

Parallel join done. Merged tuple: 4141 rows-- T1 与 T2 的 join结束，结果 4141 行 cnd(5) Done(time used: 0.278s)

END Join(time used: 0.528s)--主査询部分，join 结束

3. 2.5 执行计划总结(SUMMARY)

SUMMARY --执行计划总结

elapsed time: 00:00:07.897 -- sql 执行时间

data loaded from storage: 130M, 4.184s, 419 DC.-- 总共从磁盘读了 130MB

(共419个DC)的数据，用时4.184s

data decompressed: 0B, 0s.一总共解压了 0B 数据，用时 0s

temp space IO stats:--查询过程中临时表空间使用情况

CB write( 0B, 0time, 0sec), read( 0B, 0time, 0sec)-- 物化中间结果

(读写大小，读写请求次数，读写花费时间)

SRT write( 0B, 0time, 0sec), read( 0B, 0time, 0sec)-- 排序中间结果

GDC write( 0B, 0time, 0sec), read( 0B, 0time, 0sec)-- groupby

distinct中间结果

MAT write( 57K, 12time, 0sec), read( 440K, 88time, 0sec)-- join 中间结果

HSJ write( 0B, Otime, 0sec), read( 0B, Otime, Osec)-- One-Pass Hash Join分片文件中间结果

3. 2. 6 资源监控

[M:1.45G, 27M,D

[M:1.45G, 41M,D

[M:1.50G, 54M,D

[M:1.50G, 48M,D

[M:1.50G, 41M,D

3899]

3901]

4110]

--[内存：数据堆，large堆，磁盘:

633K] [DC: 19006： 633K] [DC: 19500： 633K] [DC: 19500：

633K] [DC: 19500

临时表空间］［访问DC数：内存命中，磁盘访问］

-- (1).数据堆：data heap的使用状况，全局状态

--(2). Large堆：算子buffer的使用情况，全局状态

--(3).临时表空间：session级，语句执行过程中占用的临时磁盘空间

--(4).内存中访问DC总数：session级可以看岀查询中处理的数据量语句

执行过程中，从内存中访问的DC总数

--(5).磁盘中访问DC总数：session级可以看到在什么步聚引起的IO 语

句执行过程中，从磁盘中访问的DC总数

上一篇：关于小康生活的标准是什么的信息小康生活的标准是什么图片小康生活的标准是什么

下一篇：二战转折点是啥（二战转折点是什么意思）二战转折点是啥中国二战转折点

执行日志（2）文件结构

相关内容

热门资讯