更新时间:2024-10-26 GMT+08:00
分享

Plan Hint实际调优案例

本节以TPC-DS标准测试的Q24的部分语句为例,在1000X,24DN环境上,说明使用plan hint进行实际调优的过程。示例如下:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
select avg(netpaid) from
(select c_last_name
,c_first_name
,s_store_name
,ca_state
,s_state
,i_color
,i_current_price
,i_manager_id
,i_units
,i_size
,sum(ss_sales_price) netpaid
from store_sales
,store_returns
,store
,item
,customer
,customer_address
where ss_ticket_number = sr_ticket_number
and ss_item_sk = sr_item_sk
and ss_customer_sk = c_customer_sk
and ss_item_sk = i_item_sk
and ss_store_sk = s_store_sk
and c_birth_country = upper(ca_country)
and s_zip = ca_zip
and s_market_id=7
group by c_last_name
,c_first_name
,s_store_name
,ca_state
,s_state
,i_color
,i_current_price
,i_manager_id
,i_units
,i_size);
  1. 该语句的初始计划如下,运行时间110s:
    图1 语句初始计划

    该计划中,第10层算子使用broadcast性能较差,由于第11层算子估算行数为2140,比实际行数严重低估。错误行数估算主要来源于第13层算子的行数低估,根因是第13层hashjoin中,使用store_sales的(ss_ticket_number, ss_item_sk)列和store_returns的(sr_ticket_number, sr_item_sk)列进行关联,由于缺少多列相关性的估算导致行数严重低估。

  2. 使用如下的rows hint进行调优后,计划如下,运行时间318s:
    1
    2
    select avg(netpaid) from
    (select /*+rows(store_sales store_returns * 11270)*/ c_last_name ...
    
    图2 使用rows hint进行调优

    时间反而劣化了,原因是第8层hashjoin过慢引起第9层redistribute时间过慢导致,其中第9层redistribute并没有数据倾斜,hashjoin慢的原因是由于第18层redistribute后数据倾斜导致。

  3. 经过实际数据查证,customer_address的两个join列的不同值数目较少,使用其进行join容易出现数据倾斜,故把customer_address放到最后进行join。使用如下的hint进行调优后,计划如下,运行时间116s:
    1
    2
    3
    4
    select avg(netpaid) from
    (select /*+rows(store_sales store_returns *11270)
    leading((store_sales store_returns store item customer) customer_address)*/
    c_last_name ...
    
    图3 hint调优

    发现时间基本花在了第6层redistribute算子上,需要进一步优化。

  4. 由于最后一层redistribute包含倾斜,所以时间较长。为了避免倾斜,需要将item表放在最后join,由于item表的join并不能使行数减少。修改hint如下并执行,计划如下,运行时间120s:
    1
    2
    3
    4
    select avg(netpaid) from
    (select /*+rows(store_sales store_returns *11270)
    leading((customer_address (store_sales store_returns store customer) item))
    c_last_name ...
    
    图4 修改hint并执行语句

    该计划中的redistribute问题并没有解决,因为第22层item表做了broadcast,导致与customer_address表join后的倾斜并没有被消除掉。

  5. 增加如下禁止item表做broadcast的hint,使与customer_address join的表做redistribute(也可以进行join表redistribute的hint),计划如下,运行时间105s:
    1
    2
    3
    4
    5
    select avg(netpaid) from
    (select /*+rows(store_sales store_returns *11270)
    leading((customer_address (store_sales store_returns store customer) item))
    no broadcast(item)*/
    c_last_name ...
    
    图5 执行计划
  6. 发现最后一层使用单层Agg,但行数缩减较多。使用相同的hint,同时结合参数best_agg_plan=3进行双层Agg调优,最终计划如下图所示,运行时间94s,完成调优。
    图6 最终调优计划

如果有统计信息变更引起的查询劣化,可以考虑用plan hint来调整到之前的查询计划。这里以TPCH-Q17为例,在收集default_statistics_target设置为–2的统计信息之后,计划相比于默认统计信息发生劣化。

  1. 默认统计信息(default_statistics_target设置为100)的计划如下:
    图7 默认统计信息
  2. 统计信息变更(default_statistics_target设置为–2)的计划如下:
    图8 统计信息变更
  3. 经过对比,劣化的原因主要为lineitem和part表join时stream类型由BroadCast变更为Redistribute导致。可以对语句进行stream方式的hint来调整到之前的计划,例如:
    图9 调整语句

相关文档