文档中心MogDBMogDB StackUqbar
生态工具
文档工具
EN
v2.1

文档:v2.1

支持的版本:
v3.1/
v3.0/
v2.1/
v2.0/
v1.1

其他版本:
最新(v5.0)

使用Plan Hint进行调优


Plan Hint调优概述

Plan Hint为用户提供了直接影响执行计划生成的手段，用户可以通过指定join顺序，join、scan方法，指定结果行数，等多个手段来进行执行计划的调优，以提升查询的性能。


功能描述

Plan Hint仅支持在SELECT关键字后通过如下形式指定:

/*+ <plan hint>*/

可以同时指定多个hint，之间使用空格分隔。hint只能hint当前层的计划，对于子查询计划的hint，需要在子查询的select关键字后指定hint。

例如:

select /*+ <plan_hint1> <plan_hint2> */ * from t1, (select /*+ <plan_hint3> */ from t2) where 1=1;

其中<plan_hint1>，<plan_hint2>为外层查询的hint，<plan_hint3>为内层子查询的hint。

img 须知: 如果在视图定义（CREATE VIEW）时指定hint，则在该视图每次被应用时会使用该hint。 当使用random plan功能（参数plan_mode_seed不为0）时，查询指定的plan hint不会被使用。


支持范围

当前版本Plan Hint支持的范围如下，后续版本会进行增强。

  • 指定Join顺序的Hint - leading hint
  • 指定Join方式的Hint，仅支持除semi/anti join，unique plan之外的常用hint。
  • 指定结果集行数的Hint
  • 指定Scan方式的Hint，仅支持常用的tablescan，indexscan和indexonlyscan的hint。
  • 指定子链接块名的Hint

注意事项

不支持Agg、Sort、Setop和Subplan的hint。


示例

本章节使用同一个语句进行示例，便于Plan Hint支持的各方法作对比，示例语句及不带hint的原计划如下所示:

create table store
(
    s_store_sk                integer               not null,
    s_store_id                char(16)              not null,
    s_rec_start_date          date                          ,
    s_rec_end_date            date                          ,
    s_closed_date_sk          integer                       ,
    s_store_name              varchar(50)                   ,
    s_number_employees        integer                       ,
    s_floor_space             integer                       ,
    s_hours                   char(20)                      ,
    s_manager                 varchar(40)                   ,
    s_market_id               integer                       ,
    s_geography_class         varchar(100)                  ,
    s_market_desc             varchar(100)                  ,
    s_market_manager          varchar(40)                   ,
    s_division_id             integer                       ,
    s_division_name           varchar(50)                   ,
    s_company_id              integer                       ,
    s_company_name            varchar(50)                   ,
    s_street_number           varchar(10)                   ,
    s_street_name             varchar(60)                   ,
    s_street_type             char(15)                      ,
    s_suite_number            char(10)                      ,
    s_city                    varchar(60)                   ,
    s_county                  varchar(30)                   ,
    s_state                   char(2)                       ,
    s_zip                     char(10)                      ,
    s_country                 varchar(20)                   ,
    s_gmt_offset              decimal(5,2)                  ,
    s_tax_precentage          decimal(5,2)                  ,
    primary key (s_store_sk)
);
create table store_sales
(
    ss_sold_date_sk           integer                       ,
    ss_sold_time_sk           integer                       ,
    ss_item_sk                integer               not null,
    ss_customer_sk            integer                       ,
    ss_cdemo_sk               integer                       ,
    ss_hdemo_sk               integer                       ,
    ss_addr_sk                integer                       ,
    ss_store_sk               integer                       ,
    ss_promo_sk               integer                       ,
    ss_ticket_number          integer               not null,
    ss_quantity               integer                       ,
    ss_wholesale_cost         decimal(7,2)                  ,
    ss_list_price             decimal(7,2)                  ,
    ss_sales_price            decimal(7,2)                  ,
    ss_ext_discount_amt       decimal(7,2)                  ,
    ss_ext_sales_price        decimal(7,2)                  ,
    ss_ext_wholesale_cost     decimal(7,2)                  ,
    ss_ext_list_price         decimal(7,2)                  ,
    ss_ext_tax                decimal(7,2)                  ,
    ss_coupon_amt             decimal(7,2)                  ,
    ss_net_paid               decimal(7,2)                  ,
    ss_net_paid_inc_tax       decimal(7,2)                  ,
    ss_net_profit             decimal(7,2)                  ,
    primary key (ss_item_sk, ss_ticket_number)
);
create table store_returns
(
    sr_returned_date_sk       integer                       ,
    sr_return_time_sk         integer                       ,
    sr_item_sk                integer               not null,
    sr_customer_sk            integer                       ,
    sr_cdemo_sk               integer                       ,
    sr_hdemo_sk               integer                       ,
    sr_addr_sk                integer                       ,
    sr_store_sk               integer                       ,
    sr_reason_sk              integer                       ,
    sr_ticket_number          integer               not null,
    sr_return_quantity        integer                       ,
    sr_return_amt             decimal(7,2)                  ,
    sr_return_tax             decimal(7,2)                  ,
    sr_return_amt_inc_tax     decimal(7,2)                  ,
    sr_fee                    decimal(7,2)                  ,
    sr_return_ship_cost       decimal(7,2)                  ,
    sr_refunded_cash          decimal(7,2)                  ,
    sr_reversed_charge        decimal(7,2)                  ,
    sr_store_credit           decimal(7,2)                  ,
    sr_net_loss               decimal(7,2)                  ,
    primary key (sr_item_sk, sr_ticket_number)
);
create table customer
(
    c_customer_sk             integer               not null,
    c_customer_id             char(16)              not null,
    c_current_cdemo_sk        integer                       ,
    c_current_hdemo_sk        integer                       ,
    c_current_addr_sk         integer                       ,
    c_first_shipto_date_sk    integer                       ,
    c_first_sales_date_sk     integer                       ,
    c_salutation              char(10)                      ,
    c_first_name              char(20)                      ,
    c_last_name               char(30)                      ,
    c_preferred_cust_flag     char(1)                       ,
    c_birth_day               integer                       ,
    c_birth_month             integer                       ,
    c_birth_year              integer                       ,
    c_birth_country           varchar(20)                   ,
    c_login                   char(13)                      ,
    c_email_address           char(50)                      ,
    c_last_review_date        char(10)                      ,
    primary key (c_customer_sk)
);
create table promotion
(
    p_promo_sk                integer               not null,
    p_promo_id                char(16)              not null,
    p_start_date_sk           integer                       ,
    p_end_date_sk             integer                       ,
    p_item_sk                 integer                       ,
    p_cost                    decimal(15,2)                 ,
    p_response_target         integer                       ,
    p_promo_name              char(50)                      ,
    p_channel_dmail           char(1)                       ,
    p_channel_email           char(1)                       ,
    p_channel_catalog         char(1)                       ,
    p_channel_tv              char(1)                       ,
    p_channel_radio           char(1)                       ,
    p_channel_press           char(1)                       ,
    p_channel_event           char(1)                       ,
    p_channel_demo            char(1)                       ,
    p_channel_details         varchar(100)                  ,
    p_purpose                 char(15)                      ,
    p_discount_active         char(1)                       ,
    primary key (p_promo_sk)
);
create table customer_address
(
    ca_address_sk             integer               not null,
    ca_address_id             char(16)              not null,
    ca_street_number          char(10)                      ,
    ca_street_name            varchar(60)                   ,
    ca_street_type            char(15)                      ,
    ca_suite_number           char(10)                      ,
    ca_city                   varchar(60)                   ,
    ca_county                 varchar(30)                   ,
    ca_state                  char(2)                       ,
    ca_zip                    char(10)                      ,
    ca_country                varchar(20)                   ,
    ca_gmt_offset             decimal(5,2)                  ,
    ca_location_type          char(20)                      ,
    primary key (ca_address_sk)
);
create table item
(
    i_item_sk                 integer               not null,
    i_item_id                 char(16)              not null,
    i_rec_start_date          date                          ,
    i_rec_end_date            date                          ,
    i_item_desc               varchar(200)                  ,
    i_current_price           decimal(7,2)                  ,
    i_wholesale_cost          decimal(7,2)                  ,
    i_brand_id                integer                       ,
    i_brand                   char(50)                      ,
    i_class_id                integer                       ,
    i_class                   char(50)                      ,
    i_category_id             integer                       ,
    i_category                char(50)                      ,
    i_manufact_id             integer                       ,
    i_manufact                char(50)                      ,
    i_size                    char(20)                      ,
    i_formulation             char(20)                      ,
    i_color                   char(20)                      ,
    i_units                   char(10)                      ,
    i_container               char(10)                      ,
    i_manager_id              integer                       ,
    i_product_name            char(50)                      ,
    primary key (i_item_sk)
);
explain
select i_product_name product_name
,i_item_sk item_sk
,s_store_name store_name
,s_zip store_zip
,ad2.ca_street_number c_street_number
,ad2.ca_street_name c_street_name
,ad2.ca_city c_city
,ad2.ca_zip c_zip
,count(*) cnt
,sum(ss_wholesale_cost) s1
,sum(ss_list_price) s2
,sum(ss_coupon_amt) s3
FROM   store_sales
,store_returns
,store
,customer
,promotion
,customer_address ad2
,item
WHERE  ss_store_sk = s_store_sk AND
ss_customer_sk = c_customer_sk AND
ss_item_sk = i_item_sk and
ss_item_sk = sr_item_sk and
ss_ticket_number = sr_ticket_number and
c_current_addr_sk = ad2.ca_address_sk and
ss_promo_sk = p_promo_sk and
i_color in ('maroon','burnished','dim','steel','navajo','chocolate') and
i_current_price between 35 and 35 + 10 and
i_current_price between 35 + 1 and 35 + 15
group by i_product_name
,i_item_sk
,s_store_name
,s_zip
,ad2.ca_street_number
,ad2.ca_street_name
,ad2.ca_city
,ad2.ca_zip
;

img


Join顺序的Hint


功能描述

指明join的顺序，包括内外表顺序。


语法格式

  • 仅指定join顺序，不指定内外表顺序。

    leading(join_table_list)

  • 同时指定join顺序和内外表顺序，内外表顺序仅在最外层生效。

    leading((join_table_list))

参数说明

join_table_list为表示表join顺序的hint字符串，可以包含当前层的任意个表（别名），或对于子查询提升的场景，也可以包含子查询的hint别名，同时任意表可以使用括号指定优先级，表之间使用空格分隔。

img 须知: 表只能用单个字符串表示，不能带schema。 表如果存在别名，需要优先使用别名来表示该表。

join table list中指定的表需要满足以下要求，否则会报语义错误。

  • list中的表必须在当前层或提升的子查询中存在。
  • list中的表在当前层或提升的子查询中必须是唯一的。如果不唯一，需要使用不同的别名进行区分。
  • 同一个表只能在list里出现一次。
  • 如果表存在别名，则list中的表需要使用别名。

例如:

leading(t1 t2 t3 t4 t5)表示: t1，t2，t3，t4，t5先join，五表join顺序及内外表不限。

leading((t1 t2 t3 t4 t5))表示: t1和t2先join，t2做内表；再和t3 join，t3做内表；再和t4 join，t4做内表；再和t5 join，t5做内表。

leading(t1 (t2 t3 t4) t5)表示: t2，t3，t4先join，内外表不限；再和t1，t5 join，内外表不限。

leading((t1 (t2 t3 t4) t5))表示: t2，t3，t4先join，内外表不限；在最外层，t1再和t2，t3，t4的join表join，t1为外表，再和t5 join，t5为内表。

leading((t1 (t2 t3) t4 t5)) leading((t3 t2))表示: t2，t3先join，t2做内表；然后再和t1 join，t2，t3的join表做内表；然后再依次跟t4，t5做join，t4，t5做内表。


示例

对示例中原语句使用如下hint:

explain
select /*+ leading((((((store_sales store) promotion) item) customer) ad2) store_returns) leading((store store_sales))*/ i_product_name product_name ...

该hint表示: 表之间的join关系是: store_sales和store先join，store_sales做内表，然后依次跟promotion, item, customer, ad2, store_returns做join。生成计划如下所示:

img


Join方式的Hint


功能描述

指明Join使用的方法，可以为Nested Loop，Hash Join和Merge Join。


语法格式

[no] nestloop|hashjoin|mergejoin(table_list)

参数说明

  • no表示hint的join方式不使用。
  • table_list为表示hint表集合的字符串，该字符串中的表与join_table_list相同，只是中间不允许出现括号指定join的优先级。

例如:

no nestloop(t1 t2 t3)表示: 生成t1，t2，t3三表连接计划时，不使用nestloop。三表连接计划可能是t2 t3先join，再跟t1 join，或t1 t2先join，再跟t3 join。此hint只hint最后一次join的join方式，对于两表连接的方法不hint。如果需要，可以单独指定，例如: 任意表均不允许nestloop连接，且希望t2 t3先join，则增加hint: no nestloop(t2 t3)。


示例

对示例中原语句使用如下hint:

explain
select /*+ nestloop(store_sales store_returns item) */ i_product_name product_name ...

该hint表示: 生成store_sales，store_returns和item三表的结果集时，最后的两表关联使用nestloop。生成计划如下所示:

img


行数的Hint


功能描述

指明中间结果集的大小，支持绝对值和相对值的hint。


语法格式

rows(table_list #|+|-|* const)

参数说明

  • #，+，-，*****，进行行数估算hint的四种操作符号。#表示直接使用后面的行数进行hint。+，-，*表示对原来估算的行数进行加、减、乘操作，运算后的行数最小值为1行。table_list为hint对应的单表或多表join结果集，与Join方式的Hint中table_list相同。
  • const可以是任意非负数，支持科学计数法。

例如:

rows(t1 #5)表示: 指定t1表的结果集为5行。

rows(t1 t2 t3 *1000)表示: 指定t1, t2, t3 join完的结果集的行数乘以1000。


建议

  • 推荐使用两个表的hint。对于两个表的采用*操作符的hint，只要两个表出现在join的两端，都会触发hint。例如: 设置hint为rows(t1 t2 * 3)，对于(t1 t3 t4)和(t2 t5 t6)join时，由于t1和t2出现在join的两端，所以其join的结果集也会应用该hint规则乘以3。
  • rows hint支持在单表、多表、function table及subquery scan table的结果集上指定hint。

示例

对示例中原语句使用如下hint：

explain
select /*+ rows(store_sales store_returns *50) */ i_product_name product_name ...

该hint表示: store_sales，store_returns关联的结果集估算行数在原估算行数基础上乘以50。生成计划如下所示:

img


Scan方式的Hint


功能描述

指明scan使用的方法，可以是tablescan、indexscan和indexonlyscan。


语法格式

[no] tablescan|indexscan|indexonlyscan(table [index])

参数说明

  • no表示hint的scan方式不使用。
  • table表示hint指定的表，只能指定一个表，如果表存在别名应优先使用别名进行hint。
  • index表示使用indexscan或indexonlyscan的hint时，指定的索引名称，当前只能指定一个。

img 说明: 对于indexscan或indexonlyscan，只有hint的索引属于hint的表时，才能使用该hint。 scan hint支持在行列存表、obs表、子查询表上指定。


示例

为了hint使用索引扫描，需要首先在表item的i_item_sk列上创建索引，名称为i。

create index i on item(i_item_sk);

对示例中原语句使用如下hint：

explain
select /*+ indexscan(item i) */ i_product_name product_name ...

该hint表示: item表使用索引i进行扫描。生成计划如下所示:

img


子链接块名的hint


功能描述

指明子链接块的名称。


语法格式

blockname (table)

参数说明

  • table表示为该子链接块hint的别名的名称。

img 说明:

  • blockname hint仅在对应的子链接块提升时才会被上层查询使用。目前支持的子链接提升包括IN子链接提升、EXISTS子链接提升和包含Agg等值相关子链接提升。该hint通常会和前面章节提到的hint联合使用。
  • 对于FROM关键字后的子查询，则需要使用子查询的别名进行hint，blockname hint不会被用到。
  • 如果子链接中含有多个表，则提升后这些表可与外层表以任意优化顺序连接，hint也不会被用到。

示例

explain select /*+nestloop(store_sales tt) */ * from store_sales where ss_item_sk in (select /*+blockname(tt)*/ i_item_sk from item group by 1);

该hint表示: 子链接的别名为tt，提升后与上层的store_sales表关联时使用nestloop。生成计划如下所示:

img


Hint的错误、冲突及告警

Plan Hint的结果会体现在计划的变化上，可以通过explain来查看变化。

Hint中的错误不会影响语句的执行，只是不能生效，该错误会根据语句类型以不同方式提示用户。对于explain语句，hint的错误会以warning形式显示在界面上，对于非explain语句，会以debug1级别日志显示在日志中，关键字为PLANHINT。

hint的错误分为以下类型:

  • 语法错误

    语法规则树归约失败，会报错，指出出错的位置。

    例如: hint关键字错误，leading hint或join hint指定2个表以下，其它hint未指定表等。一旦发现语法错误，则立即终止hint的解析，所以此时只有错误前面的解析完的hint有效。

    例如:

    leading((t1 t2)) nestloop(t1) rows(t1 t2 #10)

    nestloop(t1)存在语法错误，则终止解析，可用hint只有之前解析的leading((t1 t2))。

  • 语义错误

    • 表不存在，存在多个，或在leading或join中出现多次，均会报语义错误。
    • scanhint中的index不存在，会报语义错误。
    • 另外，如果子查询提升后，同一层出现多个名称相同的表，且其中某个表需要被hint，hint会存在歧义，无法使用，需要为相同表增加别名规避。

  • hint重复或冲突

    如果存在hint重复或冲突，只有第一个hint生效，其它hint均会失效，会给出提示。

    • hint重复是指，hint的方法及表名均相同。例如: nestloop(t1 t2) nestloop(t1 t2)。

    • hint冲突是指，table list一样的hint，存在不一样的hint，hint的冲突仅对于每一类hint方法检测冲突。

      例如: nestloop (t1 t2) hashjoin (t1 t2)，则后面与前面冲突，此时hashjoin的hint失效。注意:nestloop(t1 t2)和no mergejoin(t1 t2)不冲突。

      img 须知: leading hint中的多个表会进行拆解。例如: leading ((t1 t2 t3))会拆解成: leading((t1 t2)) leading(((t1 t2) t3))，此时如果存在leading((t2 t1))，则两者冲突，后面的会被丢弃。（例外: 指定内外表的hint若与不指定内外表的hint重复，则始终丢弃不指定内外表的hint。）

  • 子链接提升后hint失效

    子链接提升后的hint失效，会给出提示。通常出现在子链接中存在多个表连接的场景。提升后，子链接中的多个表不再作为一个整体出现在join中。

  • 列类型不支持重分布

    • 对于skew hint来说，目的是为了进行重分布时的调优，所以当hint列的类型不支持重分布时，hint将无效。

  • hint未被使用

    • 非等值join使用hashjoin hint或mergejoin hint。
    • 不包含索引的表使用indexscan hint或indexonlyscan hint。
    • 通常只有在索引列上使用过滤条件才会生成相应的索引路径，全表扫描将不会使用索引，因此使用indexscan hint或indexonlyscan hint将不会使用。
    • indexonlyscan只有输出列仅包含索引列才会使用，否则指定时hint不会被使用。
    • 多个表存在等值连接时，仅尝试有等值连接条件的表的连接，此时没有关联条件的表之间的路径将不会生成，所以指定相应的leading，join，rows hint将不使用，例如: t1 t2 t3表join，t1和t2, t2和t3有等值连接条件，则t1和t3不会优先连接，leading(t1 t3)不会被使用。
    • 生成stream计划时，如果表的分布列与join列相同，则不会生成redistribute的计划；如果不同，且另一表分布列与join列相同，只能生成redistribute的计划，不会生成broadcast的计划，指定相应的hint则不会被使用。
    • 如果子链接未被提升，则blockname hint不会被使用。
    • 对于skew hint，hint未被使用可能由于:
      • 计划中不需要进行重分布。
      • hint指定的列为包含分布键。
      • hint指定倾斜信息有误或不完整，如对于join优化未指定值。
      • 倾斜优化的GUC参数处于关闭状态。

优化器GUC参数的Hint

功能描述

设置本次查询执行内生效的查询优化相关GUC参数。hint的推荐使用场景可以参考各guc参数的说明，此处不作赘述。

语法格式

set(param value)

参数说明

  • param表示参数名。

  • value表示参数的取值。

  • 目前支持使用Hint设置生效的参数有

    • 布尔类：

      enable_bitmapscan, enable_hashagg, enable_hashjoin, enable_indexscan, enable_indexonlyscan, enable_material, enable_mergejoin, enable_nestloop, enable_index_nestloop, enable_seqscan, enable_sort, enable_tidscan

    • 整形类：

      query_dop

    • 浮点类：

      cost_weight_index, default_limit_rows, seq_page_cost, random_page_cost, cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost, effective_cache_size

img 说明:

  • 设置不在白名单中的参数，参数取值不合法，或hint语法错误时，不会影响查询执行的正确性。使用explain(verbose on)执行可以看到hint解析错误的报错提示。
  • GUC参数的hint只在最外层查询生效——子查询内的GUC参数hint不生效。
  • 视图定义内的GUC参数hint不生效。
  • CREATE TABLE … AS … 查询最外层的GUC参数hint可以生效。

Custom Plan和Generic Plan选择的Hint

功能描述

对于以PBE方式执行的查询语句和DML语句，优化器会基于规则、代价、参数等因素选择生成Custom Plan或Generic Plan执行。用户可以通过use_cplan/use_gplan的hint指定使用哪种计划执行方式。

语法格式

  • 指定使用Custom Plan：

    use_cplan

  • 指定使用Generic Plan：

    use_gplan

img 说明:

  • 对于非PBE方式执行的SQL语句，设置本hint不会影响执行方式。
  • 本Hint的优先级仅高于基于代价的选择和plan_cache_mode参数，即plan_cache_mode无法强制选择执行方式的语句本hint也无法生效。

示例

强制使用Custom Plan

set enable_fast_query_shipping = off;
create table t (a int, b int, c int);
prepare p as select /*+ use_cplan */ * from t where a = $1;
explain execute p(1);

计划如下。可以看到过滤条件为入参的实际值，即此计划为Custom Plan。

img

强制使用Generic Plan

deallocate p;
prepare p as select /*+ use_gplan */ * from t where a = $1;
explain execute p(1);

计划如下。可以看到过滤条件为待填充的入参，即此计划为Custom Plan。

img

指定子查询不展开的Hint

功能描述

数据库在对查询进行逻辑优化时通常会将可以提升的子查询提升到上层来避免嵌套执行，但对于某些本身选择率较低且可以使用索引过滤访问页面的子查询，嵌套执行不会导致性能下降过多，而提升之后扩大了查询路径的搜索范围，可能导致性能变差。对于此类情况，可以使用no_expand Hint进行调试。大多数情况下不建议使用此hint。

语法格式

no_expand

示例

正常的查询执行

explain select * from t1 where t1.a in (select t2.a from t2);

计划

img

加入no_expand

explain select * from t1 where t1.a in (select /*+ no_expand*/ t2.a from t2);

计划

img

指定不使用全局计划缓存的Hint

功能描述

全局计划缓存打开时，可以通过no_gpc Hint来强制单个查询语句不在全局共享计划缓存，只保留会话生命周期的计划缓存。

语法格式

no_gpc

img 说明: 本参数仅在enable_global_plancache=on时对PBE执行的语句生效。

示例

img

dbe_perf.global_plancache_status视图中无结果即没有计划被全局缓存。

首页

反馈
Copyright © 2011-2024 www.enmotech.com All rights reserved.