解读执行计划

社区动态
#1

租户：12C60G，6节点
表名：lineitem 72分区 数据量：600037902
72并行度抛出结果需要13S，没有达到逾期，请大神们指导看看哪里有优化的空间
查询语句：
“select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= date ‘1998-12-01’ - interval ‘:1’ day (3)
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;”
执行计划：

|ID|OPERATOR |NAME |EST.ROWS |EST.TIME(us)|

|0 |PX COORDINATOR MERGE SORT | |5 |2913435 |
|1 | EXCHANGE OUT DISTR |:EX10001|5 |2913427 |
|2 | SORT | |5 |2913427 |
|3 | HASH GROUP BY | |5 |2913427 |
|4 | EXCHANGE IN DISTR | |306 |2913426 |
|5 | EXCHANGE OUT DISTR (HASH)|:EX10000|306 |2913417 |
|6 | HASH GROUP BY | |306 |2913398 |
|7 | PX BLOCK ITERATOR | |600037902|993039 |
|8 | TABLE SCAN |lineitem|600037902|993039 |

Outputs & filters:

0 - output([INTERNAL_FUNCTION(lineitem.l_returnflag(0x7f914e642230), lineitem.l_linestatus(0x7f914e6427e0), T_FUN_SUM(T_FUN_SUM(lineitem.l_quantity(0x7f914e643120))(0x7f914e642ab0))(0x7f914e737870),
T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e643450))(0x7f914e737c20), T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0)

  • cast(1, DECIMAL(1, 0))(0x7f914e64b4d0) - lineitem.l_discount(0x7f914e645700)(0x7f914e644980)(0x7f914e644ff0))(0x7f914e643df0))(0x7f914e737fd0), T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0)
  • cast(1, DECIMAL(1, 0))(0x7f914e64c520) - lineitem.l_discount(0x7f914e645700)(0x7f914e646740)(0x7f914e646db0) * cast(1, DECIMAL(1, 0))(0x7f914e64d2b0)
  • lineitem.l_tax(0x7f914e648760)(0x7f914e647960)(0x7f914e647fd0))(0x7f914e645bb0))(0x7f914e738530), T_FUN_SUM(T_FUN_SUM(lineitem.l_quantity(0x7f914e643120))(0x7f914e642ab0))(0x7f914e737870)
    / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_quantity(0x7f914e643120))(0x7f914e64ee50))(0x7f914e738f90), DECIMAL(20, 0))(0x7f914e64fa60)(0x7f914e64f1e0),
    T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e643450))(0x7f914e737c20) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e650880))(0x7f914e739340),
    DECIMAL(20, 0))(0x7f914e651490)(0x7f914e650c10), T_FUN_SUM(T_FUN_SUM(lineitem.l_discount(0x7f914e645700))(0x7f914e651f20))(0x7f914e7396f0) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_discount(0x7f914e645700))(0x7f914e6522b0))(0x7f914e739aa0),
    DECIMAL(20, 0))(0x7f914e652ec0)(0x7f914e652640), T_FUN_COUNT_SUM(T_FUN_COUNT(*)(0x7f914e64a230))(0x7f914e738c00))(0x7f914e7462c0)]), filter(nil), rowset=256
    sort_keys([lineitem.l_returnflag(0x7f914e642230), ASC], [lineitem.l_linestatus(0x7f914e6427e0), ASC])
    1 - output([lineitem.l_returnflag(0x7f914e642230)], [lineitem.l_linestatus(0x7f914e6427e0)], [INTERNAL_FUNCTION(lineitem.l_returnflag(0x7f914e642230),
    lineitem.l_linestatus(0x7f914e6427e0), T_FUN_SUM(T_FUN_SUM(lineitem.l_quantity(0x7f914e643120))(0x7f914e642ab0))(0x7f914e737870), T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e643450))(0x7f914e737c20),
    T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0) * cast(1, DECIMAL(1, 0))(0x7f914e64b4d0) - lineitem.l_discount(0x7f914e645700)(0x7f914e644980)(0x7f914e644ff0))(0x7f914e643df0))(0x7f914e737fd0),
    T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0) * cast(1, DECIMAL(1, 0))(0x7f914e64c520) - lineitem.l_discount(0x7f914e645700)(0x7f914e646740)(0x7f914e646db0)
  • cast(1, DECIMAL(1, 0))(0x7f914e64d2b0) + lineitem.l_tax(0x7f914e648760)(0x7f914e647960)(0x7f914e647fd0))(0x7f914e645bb0))(0x7f914e738530), T_FUN_SUM(T_FUN_SUM(lineitem.l_quantity(0x7f914e643120))(0x7f914e642ab0))(0x7f914e737870)
    / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_quantity(0x7f914e643120))(0x7f914e64ee50))(0x7f914e738f90), DECIMAL(20, 0))(0x7f914e64fa60)(0x7f914e64f1e0),
    T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e643450))(0x7f914e737c20) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e650880))(0x7f914e739340),
    DECIMAL(20, 0))(0x7f914e651490)(0x7f914e650c10), T_FUN_SUM(T_FUN_SUM(lineitem.l_discount(0x7f914e645700))(0x7f914e651f20))(0x7f914e7396f0) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_discount(0x7f914e645700))(0x7f914e6522b0))(0x7f914e739aa0),
    DECIMAL(20, 0))(0x7f914e652ec0)(0x7f914e652640), T_FUN_COUNT_SUM(T_FUN_COUNT(*)(0x7f914e64a230))(0x7f914e738c00))(0x7f914e7462c0)]), filter(nil), rowset=256
    dop=72
    2 - output([lineitem.l_returnflag(0x7f914e642230)], [lineitem.l_linestatus(0x7f914e6427e0)], [T_FUN_SUM(T_FUN_SUM(lineitem.l_quantity(0x7f914e643120))(0x7f914e642ab0))(0x7f914e737870)],
    [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e643450))(0x7f914e737c20)], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0)
  • cast(1, DECIMAL(1, 0))(0x7f914e64b4d0) - lineitem.l_discount(0x7f914e645700)(0x7f914e644980)(0x7f914e644ff0))(0x7f914e643df0))(0x7f914e737fd0)], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0)
  • cast(1, DECIMAL(1, 0))(0x7f914e64c520) - lineitem.l_discount(0x7f914e645700)(0x7f914e646740)(0x7f914e646db0) * cast(1, DECIMAL(1, 0))(0x7f914e64d2b0)
  • lineitem.l_tax(0x7f914e648760)(0x7f914e647960)(0x7f914e647fd0))(0x7f914e645bb0))(0x7f914e738530)], [T_FUN_COUNT_SUM(T_FUN_COUNT(*)(0x7f914e64a230))(0x7f914e738c00)],
    [T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_quantity(0x7f914e643120))(0x7f914e64ee50))(0x7f914e738f90)], [T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e650880))(0x7f914e739340)],
    [T_FUN_SUM(T_FUN_SUM(lineitem.l_discount(0x7f914e645700))(0x7f914e651f20))(0x7f914e7396f0)], [T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_discount(0x7f914e645700))(0x7f914e6522b0))(0x7f914e739aa0)]),
    filter(nil), rowset=256
    sort_keys([lineitem.l_returnflag(0x7f914e642230), ASC], [lineitem.l_linestatus(0x7f914e6427e0), ASC])
    3 - output([lineitem.l_returnflag(0x7f914e642230)], [lineitem.l_linestatus(0x7f914e6427e0)], [T_FUN_SUM(T_FUN_SUM(lineitem.l_quantity(0x7f914e643120))(0x7f914e642ab0))(0x7f914e737870)],
    [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e643450))(0x7f914e737c20)], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0)
  • cast(1, DECIMAL(1, 0))(0x7f914e64b4d0) - lineitem.l_discount(0x7f914e645700)(0x7f914e644980)(0x7f914e644ff0))(0x7f914e643df0))(0x7f914e737fd0)], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0)
  • cast(1, DECIMAL(1, 0))(0x7f914e64c520) - lineitem.l_discount(0x7f914e645700)(0x7f914e646740)(0x7f914e646db0) * cast(1, DECIMAL(1, 0))(0x7f914e64d2b0)
  • lineitem.l_tax(0x7f914e648760)(0x7f914e647960)(0x7f914e647fd0))(0x7f914e645bb0))(0x7f914e738530)], [T_FUN_COUNT_SUM(T_FUN_COUNT(*)(0x7f914e64a230))(0x7f914e738c00)],
    [T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_quantity(0x7f914e643120))(0x7f914e64ee50))(0x7f914e738f90)], [T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e650880))(0x7f914e739340)],
    [T_FUN_SUM(T_FUN_SUM(lineitem.l_discount(0x7f914e645700))(0x7f914e651f20))(0x7f914e7396f0)], [T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_discount(0x7f914e645700))(0x7f914e6522b0))(0x7f914e739aa0)]),
    filter(nil), rowset=256
    group([lineitem.l_returnflag(0x7f914e642230)], [lineitem.l_linestatus(0x7f914e6427e0)]), agg_func([T_FUN_SUM(T_FUN_SUM(lineitem.l_quantity(0x7f914e643120))(0x7f914e642ab0))(0x7f914e737870)],
    [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e643450))(0x7f914e737c20)], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0)
  • cast(1, DECIMAL(1, 0))(0x7f914e64b4d0) - lineitem.l_discount(0x7f914e645700)(0x7f914e644980)(0x7f914e644ff0))(0x7f914e643df0))(0x7f914e737fd0)], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0)
  • cast(1, DECIMAL(1, 0))(0x7f914e64c520) - lineitem.l_discount(0x7f914e645700)(0x7f914e646740)(0x7f914e646db0) * cast(1, DECIMAL(1, 0))(0x7f914e64d2b0)
  • lineitem.l_tax(0x7f914e648760)(0x7f914e647960)(0x7f914e647fd0))(0x7f914e645bb0))(0x7f914e738530)], [T_FUN_COUNT_SUM(T_FUN_COUNT(*)(0x7f914e64a230))(0x7f914e738c00)],
    [T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_quantity(0x7f914e643120))(0x7f914e64ee50))(0x7f914e738f90)], [T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e650880))(0x7f914e739340)],
    [T_FUN_SUM(T_FUN_SUM(lineitem.l_discount(0x7f914e645700))(0x7f914e651f20))(0x7f914e7396f0)], [T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_discount(0x7f914e645700))(0x7f914e6522b0))(0x7f914e739aa0)])
    4 - output([lineitem.l_returnflag(0x7f914e642230)], [lineitem.l_linestatus(0x7f914e6427e0)], [T_FUN_SUM(lineitem.l_quantity(0x7f914e643120))(0x7f914e642ab0)],
    [T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e643450)], [T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0) * cast(1, DECIMAL(1, 0))(0x7f914e64b4d0)
  • lineitem.l_discount(0x7f914e645700)(0x7f914e644980)(0x7f914e644ff0))(0x7f914e643df0)], [T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0) * cast(1, DECIMAL(1,
    0))(0x7f914e64c520) - lineitem.l_discount(0x7f914e645700)(0x7f914e646740)(0x7f914e646db0) * cast(1, DECIMAL(1, 0))(0x7f914e64d2b0) + lineitem.l_tax(0x7f914e648760)(0x7f914e647960)(0x7f914e647fd0))(0x7f914e645bb0)],
    [T_FUN_COUNT(*)(0x7f914e64a230)], [T_FUN_COUNT(lineitem.l_quantity(0x7f914e643120))(0x7f914e64ee50)], [T_FUN_COUNT(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e650880)],
    [T_FUN_SUM(lineitem.l_discount(0x7f914e645700))(0x7f914e651f20)], [T_FUN_COUNT(lineitem.l_discount(0x7f914e645700))(0x7f914e6522b0)]), filter(nil), rowset=256
    5 - output([lineitem.l_returnflag(0x7f914e642230)], [lineitem.l_linestatus(0x7f914e6427e0)], [T_FUN_SUM(lineitem.l_quantity(0x7f914e643120))(0x7f914e642ab0)],
    [T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e643450)], [T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0) * cast(1, DECIMAL(1, 0))(0x7f914e64b4d0)
  • lineitem.l_discount(0x7f914e645700)(0x7f914e644980)(0x7f914e644ff0))(0x7f914e643df0)], [T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0) * cast(1, DECIMAL(1,
    0))(0x7f914e64c520) - lineitem.l_discount(0x7f914e645700)(0x7f914e646740)(0x7f914e646db0) * cast(1, DECIMAL(1, 0))(0x7f914e64d2b0) + lineitem.l_tax(0x7f914e648760)(0x7f914e647960)(0x7f914e647fd0))(0x7f914e645bb0)],
    [T_FUN_COUNT(*)(0x7f914e64a230)], [T_FUN_COUNT(lineitem.l_quantity(0x7f914e643120))(0x7f914e64ee50)], [T_FUN_COUNT(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e650880)],
    [T_FUN_SUM(lineitem.l_discount(0x7f914e645700))(0x7f914e651f20)], [T_FUN_COUNT(lineitem.l_discount(0x7f914e645700))(0x7f914e6522b0)]), filter(nil), rowset=256
    (#keys=2, [lineitem.l_returnflag(0x7f914e642230), CHAR, 1], [lineitem.l_linestatus(0x7f914e6427e0), CHAR, 1]), dop=72
    6 - output([lineitem.l_returnflag(0x7f914e642230)], [lineitem.l_linestatus(0x7f914e6427e0)], [T_FUN_SUM(lineitem.l_quantity(0x7f914e643120))(0x7f914e642ab0)],
    [T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e643450)], [T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0) * cast(1, DECIMAL(1, 0))(0x7f914e64b4d0)
  • lineitem.l_discount(0x7f914e645700)(0x7f914e644980)(0x7f914e644ff0))(0x7f914e643df0)], [T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0) * cast(1, DECIMAL(1,
    0))(0x7f914e64c520) - lineitem.l_discount(0x7f914e645700)(0x7f914e646740)(0x7f914e646db0) * cast(1, DECIMAL(1, 0))(0x7f914e64d2b0) + lineitem.l_tax(0x7f914e648760)(0x7f914e647960)(0x7f914e647fd0))(0x7f914e645bb0)],
    [T_FUN_COUNT(*)(0x7f914e64a230)], [T_FUN_COUNT(lineitem.l_quantity(0x7f914e643120))(0x7f914e64ee50)], [T_FUN_COUNT(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e650880)],
    [T_FUN_SUM(lineitem.l_discount(0x7f914e645700))(0x7f914e651f20)], [T_FUN_COUNT(lineitem.l_discount(0x7f914e645700))(0x7f914e6522b0)]), filter(nil), rowset=256
    group([lineitem.l_returnflag(0x7f914e642230)], [lineitem.l_linestatus(0x7f914e6427e0)]), agg_func([T_FUN_SUM(lineitem.l_quantity(0x7f914e643120))(0x7f914e642ab0)],
    [T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e643450)], [T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0) * cast(1, DECIMAL(1, 0))(0x7f914e64b4d0)
  • lineitem.l_discount(0x7f914e645700)(0x7f914e644980)(0x7f914e644ff0))(0x7f914e643df0)], [T_FUN_SUM(lineitem.l_extendedprice(0x7f914e643ac0) * cast(1, DECIMAL(1,
    0))(0x7f914e64c520) - lineitem.l_discount(0x7f914e645700)(0x7f914e646740)(0x7f914e646db0) * cast(1, DECIMAL(1, 0))(0x7f914e64d2b0) + lineitem.l_tax(0x7f914e648760)(0x7f914e647960)(0x7f914e647fd0))(0x7f914e645bb0)],
    [T_FUN_COUNT(*)(0x7f914e64a230)], [T_FUN_COUNT(lineitem.l_quantity(0x7f914e643120))(0x7f914e64ee50)], [T_FUN_COUNT(lineitem.l_extendedprice(0x7f914e643ac0))(0x7f914e650880)],
    [T_FUN_SUM(lineitem.l_discount(0x7f914e645700))(0x7f914e651f20)], [T_FUN_COUNT(lineitem.l_discount(0x7f914e645700))(0x7f914e6522b0)])
    7 - output([lineitem.l_returnflag(0x7f914e642230)], [lineitem.l_linestatus(0x7f914e6427e0)], [lineitem.l_quantity(0x7f914e643120)], [lineitem.l_extendedprice(0x7f914e643ac0)],
    [lineitem.l_discount(0x7f914e645700)], [lineitem.l_tax(0x7f914e648760)]), filter(nil), rowset=256
    8 - output([lineitem.l_returnflag(0x7f914e642230)], [lineitem.l_linestatus(0x7f914e6427e0)], [lineitem.l_quantity(0x7f914e643120)], [lineitem.l_extendedprice(0x7f914e643ac0)],
    [lineitem.l_discount(0x7f914e645700)], [lineitem.l_tax(0x7f914e648760)]), filter([lineitem.l_shipdate(0x7f914e641a00) <= ‘1998-12-01’(0x7f914e641350)]),
    rowset=256
    access([lineitem.l_shipdate(0x7f914e641a00)], [lineitem.l_returnflag(0x7f914e642230)], [lineitem.l_linestatus(0x7f914e6427e0)], [lineitem.l_quantity(0x7f914e643120)],
    [lineitem.l_extendedprice(0x7f914e643ac0)], [lineitem.l_discount(0x7f914e645700)], [lineitem.l_tax(0x7f914e648760)]), partitions(p[0-71])
    is_index_back=false, is_global_index=false, filter_before_indexback[false],
    range_key([lineitem.l_orderkey(0x7f914e640b10)], [lineitem.l_linenumber(0x7f914e64e780)]), range(MIN,MIN ; MAX,MAX)always true
    Used Hint:

/*+

  PARALLEL(72)

*/
Qb name trace:

stmt_id:0, stmt_type:T_EXPLAIN
stmt_id:1, SEL$1
Outline Data:

/*+
BEGIN_OUTLINE_DATA
GBY_PUSHDOWN(@“SEL$1”)
USE_HASH_AGGREGATION(@“SEL$1”)
FULL(@“SEL$1” “test”.“lineitem”@“SEL$1”)
PARALLEL(72)
OPTIMIZER_FEATURES_ENABLE(‘4.0.0.0’)
END_OUTLINE_DATA
*/
Optimization Info:

lineitem:
table_rows:600037902
physical_range_rows:600037902
logical_range_rows:600037902
index_back_rows:0
output_rows:600037902
est_method:basic_stat
optimization_method:cost_based
avaiable_index_name:[lineitem]
stats version:1684161234120200
Plan Type:
DISTRIBUTED
Note:
Degree of Parallelism is 72 because of hint

1 个赞
#2

普通机械盘

1 个赞
#3

不如截个图，排版太乱了。

1 个赞
#4

初步看全表扫描了。 DDL 是啥，l_shipdate 上有索引吗

#5

提供的执行计划乱行，且没有表结构和索引信息，查询结果是多少行也未知。
提供思路，目前执行计划是并行从72个分区全表扫描，过滤结果。如果查询结果的行数占表总行数的比例很低，5%以下，那么创建该 l_shipdate列的本地分区索引。如果查询返回结果集很大，72个分区扩大到144个分区或者更多，再测测。