日志说明
GBase 8a日志分为三类:
trace日志记录查询计划;
express. log记录SQL执行过程以及执行过程中的警告和错误;
system. log主要记录数据库服务启停以及crash信息,日志存放路径为 /GBase/log/gbase8a。
当需要分析SQL的查询计划时请查看trace日志,trace日志详细记录了 SQL的查询和执行计划,通过分析这些计划来确定SQL的计划是否是最优的。
当需要分析SQL执行过程中的时间消耗以及执行过程中出现的警告和错误 时请查看express日志,express日志详细记录了 SQL执行过程中各个步骤的 时间以及警告和错误,但缺省只记录SQL执行过程中的警告和错误,此时可以 通过分析这些警告和错误来定位问题的原因。
当GBase 8a正常启停和启动失败以及运行过程中出现crash时,信息都会 记录到system. log中,通过分析这些日志来定位启动失败以及运行过程中出现 crash的原因。当运行过程中出现crash时,system. log中记录了宕机的堆栈 信息,core文件中记录了宕机的详细的堆栈信息,如果用户希望看到详细的堆 栈信息,则需要在$HOME/GBase/config路径下的gbase_8a_gbase8a. cnf配置 文件中,将“core-file”参数前的注释符号去掉。可以通过gdp方式查 看详细的crash信息:
gdp /GBase/server/bin/gbased
thread apply all bt
trace日志(查询计划)
在GBase配置文件中设置如下配置项后,查询计划可输出到.trc文件中:
gbase_sql_trace =1
gbase_sql_trace_level = 3
示例:
SQL语句
示例语句为TPC-H SQL21,测试环境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 13. l_suppkey <> 11. l_suppkey
AND 13. 1_receiptdate > 13. 1_commitdate
)
AND s_nationkey = n_nationkey
AND n_name = 'SAUDI ARABIA'
GROUP BY
s_name
ORDER BY
numwa i t desc,
s_name
LIMIT 100;
trace日志信息(不包括资源监控部分)
其中,黑色加粗字体表示步骤执行的幵始,蓝色字体表示输出信息的解释 说明。
2012-12-27 18:34:20.961 Execution Plan:--打印执行计划,在计划中打印出各个 TempTable表名,所属数据库以及别名
T:-1 = TABLE_ALIAS(T:0, "supplier:tpch/supplier")
T:-2 = TMP_TABLE (T:-1, "supp1ier:tpch/supp1ier")— 格式为“表名:所属数据库/ 别名”,supplier表属于数据库tpch,该表别名为supplier
T:-3 = TABLE_ALIAS(T:1, "1ineitem:tpch/11")
T:-2. JOIN (T:-3, "lineitem: tpch/11")
T:-4 = TABLE_ALIAS(T:2, "orders:tpch/orders")
T:-2. JOIN (T:-4, "orders:tpch/orders")
T:-5 = TABLE_ALIAS(T:3, "nation:tpch/nation")
T:-2. JOIN (T:-5, "nation: tpch/nation")
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(<nu11>, 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, <null>,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, <null>)
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, <null>)
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, <null>)
T:-6 = TABLE_ALIAS(T:1, "lineitem:tpch/l2")
T:-7 = TMP_TABLE(T:-6, "lineitem:tpch/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, <null>,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, <null>)
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, <null>, <null>)
T:-8 = TABLE_ALIAS(T:1, "lineitem:tpch/l3")
T:-9 = TMP_TABLE(T:-8, "lineitem:tpch/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, <null>,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, <null>)
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, <null>)
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,<null>, <null>)
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,<null>)
LA:-2. 15 = CREATE_LA(T:-2, PHYS_COL(T:-5,A:1))
LA:-2. 16 = CREATE_LA(T:-2, EXPR("SAUDI ARABIA"))
F:0 = AND (F:0,LA:-2. 15, =,LA:-2. 16,<null>)
T:-2. FILTER (F:0)
T:-2. MODE (LIMIT,0, 100)
RESULT(T:-2)
2012-12-27 18:34:20. 962 Start Query Execution --幵始输出详细的执行过程
2012-12-27 18:34:20.963 Tables:--打印所有主查询中涉及到的表,并给出其别
名,表名,所属数据库名,表中数据的行数以及DC数
2012-12-27 18:34:20.963 T0: supplier(tpch. supplier), 10000
rows, 1 DC
2012-12-27 18:34:20.963 T1: l1 (tpch. lineitem), 6001215
rows, 92 DC -- T1表:表别名l1(所属数据库tpch.表实际名lineitem) ,共 6001215
行,占92个DC
2012-12-27 18:34:20.963 T2: orders(tpch.orders), 1500000
rows, 23 DC
2012-12-27 18:34:20.963 T3: nation(tpch. nation), 25
rows, 1 DC
2012-12-27 18:34:20.963 Condition Weight (non-join):--打印出主查询中所有
涉及到的执行条件
2012-12-27 18:34:20.963 cnd (0): nation. N_NAME BET. "SAUDI ARABIA" AND
"SAUDI ARABIA" (2. 56)-- 条件 cnd (0): nation. N_NAME 的值等于"SAUDI ARABIA"
2012-12-27 18:34:20.963 cnd(1): orders. O_ORDERSTATUS BET. "F" AND "F"
(13. 52)
2012-12-27 18:34:20.963 cnd(2): l1. L_RECEIPTDATE > l1. L_COMMITDATE
(14. 91)
2012-12-27 18:34:20.963 cnd (3): (exists cond.)
(14. 91)--条件 cnd(3) : exists 子查询
2012-12-27 18:34:20. 963 cnd ⑷: (not exists cond.)
(14. 91)--条件 cnd (4) : not exists 子查询
2012-12-27
2012-12-27
18:34:20. 963 BEGIN Smart Scan --智能索引过滤幵始
found 1 DC to scan (with
18:34:20. 964 T0:
total
1 DC,
1个DC需要扫描
过滤结果:T0表:共1个DC,命中
FULL DC).
(无Full状态)
0
0
0
过滤条件cnd(0),线程0扫描25行,过滤后得到1行
2012-12-27 18:34: 20. 975 merge row num: 1 一 过滤条件 cnd (0)结束,结果为 1 row
2012-12-27 18:34:21.069 ( 1)cnd(1): scanned 393216 rows,
rows --过滤条件cnd(1):线程1扫描393216行,过滤后得到191291行
2012-12-27 18:34:21. 073 ( 3)cnd(1): scanned 320352 rows,
rows --过滤条件cnd (1):线程3扫描320352行,过滤后得到155884行
2012-