Plan Hint调优概述

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

功能描述

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

  1. /*+ <plan hint>*/

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

例如：

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

其中，为外层查询的hint，为内层子查询的hint。

Plan Hint调优概述 - 图1 须知：
如果在视图定义（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的原计划如下所示：

  1. create table store
  2. (
  3.     s_store_sk                integer               not null,
  4.     s_store_id                char(16)              not null,
  5.     s_rec_start_date          date                          ,
  6.     s_rec_end_date            date                          ,
  7.     s_closed_date_sk          integer                       ,
  8.     s_store_name              varchar(50)                   ,
  9.     s_number_employees        integer                       ,
  10.     s_floor_space             integer                       ,
  11.     s_hours                   char(20)                      ,
  12.     s_manager                 varchar(40)                   ,
  13.     s_market_id               integer                       ,
  14.     s_geography_class         varchar(100)                  ,
  15.     s_market_desc             varchar(100)                  ,
  16.     s_market_manager          varchar(40)                   ,
  17.     s_division_id             integer                       ,
  18.     s_division_name           varchar(50)                   ,
  19.     s_company_id              integer                       ,
  20.     s_company_name            varchar(50)                   ,
  21.     s_street_number           varchar(10)                   ,
  22.     s_street_name             varchar(60)                   ,
  23.     s_street_type             char(15)                      ,
  24.     s_suite_number            char(10)                      ,
  25.     s_city                    varchar(60)                   ,
  26.     s_county                  varchar(30)                   ,
  27.     s_state                   char(2)                       ,
  28.     s_zip                     char(10)                      ,
  29.     s_country                 varchar(20)                   ,
  30.     s_gmt_offset              decimal(5,2)                  ,
  31.     s_tax_precentage          decimal(5,2)                  ,
  32.     primary key (s_store_sk)
  33. );
  34. create table store_sales
  35. (
  36.     ss_sold_date_sk           integer                       ,
  37.     ss_sold_time_sk           integer                       ,
  38.     ss_item_sk                integer               not null,
  39.     ss_customer_sk            integer                       ,
  40.     ss_cdemo_sk               integer                       ,
  41.     ss_hdemo_sk               integer                       ,
  42.     ss_addr_sk                integer                       ,
  43.     ss_store_sk               integer                       ,
  44.     ss_promo_sk               integer                       ,
  45.     ss_ticket_number          integer               not null,
  46.     ss_quantity               integer                       ,
  47.     ss_wholesale_cost         decimal(7,2)                  ,
  48.     ss_list_price             decimal(7,2)                  ,
  49.     ss_sales_price            decimal(7,2)                  ,
  50.     ss_ext_discount_amt       decimal(7,2)                  ,
  51.     ss_ext_sales_price        decimal(7,2)                  ,
  52.     ss_ext_wholesale_cost     decimal(7,2)                  ,
  53.     ss_ext_list_price         decimal(7,2)                  ,
  54.     ss_ext_tax                decimal(7,2)                  ,
  55.     ss_coupon_amt             decimal(7,2)                  ,
  56.     ss_net_paid               decimal(7,2)                  ,
  57.     ss_net_paid_inc_tax       decimal(7,2)                  ,
  58.     ss_net_profit             decimal(7,2)                  ,
  59.     primary key (ss_item_sk, ss_ticket_number)
  60. );
  61. create table store_returns
  62. (
  63.     sr_returned_date_sk       integer                       ,
  64.     sr_return_time_sk         integer                       ,
  65.     sr_item_sk                integer               not null,
  66.     sr_customer_sk            integer                       ,
  67.     sr_cdemo_sk               integer                       ,
  68.     sr_hdemo_sk               integer                       ,
  69.     sr_addr_sk                integer                       ,
  70.     sr_store_sk               integer                       ,
  71.     sr_reason_sk              integer                       ,
  72.     sr_ticket_number          integer               not null,
  73.     sr_return_quantity        integer                       ,
  74.     sr_return_amt             decimal(7,2)                  ,
  75.     sr_return_tax             decimal(7,2)                  ,
  76.     sr_return_amt_inc_tax     decimal(7,2)                  ,
  77.     sr_fee                    decimal(7,2)                  ,
  78.     sr_return_ship_cost       decimal(7,2)                  ,
  79.     sr_refunded_cash          decimal(7,2)                  ,
  80.     sr_reversed_charge        decimal(7,2)                  ,
  81.     sr_store_credit           decimal(7,2)                  ,
  82.     sr_net_loss               decimal(7,2)                  ,
  83.     primary key (sr_item_sk, sr_ticket_number)
  84. );
  85. create table customer
  86. (
  87.     c_customer_sk             integer               not null,
  88.     c_customer_id             char(16)              not null,
  89.     c_current_cdemo_sk        integer                       ,
  90.     c_current_hdemo_sk        integer                       ,
  91.     c_current_addr_sk         integer                       ,
  92.     c_first_shipto_date_sk    integer                       ,
  93.     c_first_sales_date_sk     integer                       ,
  94.     c_salutation              char(10)                      ,
  95.     c_first_name              char(20)                      ,
  96.     c_last_name               char(30)                      ,
  97.     c_preferred_cust_flag     char(1)                       ,
  98.     c_birth_day               integer                       ,
  99.     c_birth_month             integer                       ,
  100.     c_birth_year              integer                       ,
  101.     c_birth_country           varchar(20)                   ,
  102.     c_login                   char(13)                      ,
  103.     c_email_address           char(50)                      ,
  104.     c_last_review_date        char(10)                      ,
  105.     primary key (c_customer_sk)
  106. );
  107. create table promotion
  108. (
  109.     p_promo_sk                integer               not null,
  110.     p_promo_id                char(16)              not null,
  111.     p_start_date_sk           integer                       ,
  112.     p_end_date_sk             integer                       ,
  113.     p_item_sk                 integer                       ,
  114.     p_cost                    decimal(15,2)                 ,
  115.     p_response_target         integer                       ,
  116.     p_promo_name              char(50)                      ,
  117.     p_channel_dmail           char(1)                       ,
  118.     p_channel_email           char(1)                       ,
  119.     p_channel_catalog         char(1)                       ,
  120.     p_channel_tv              char(1)                       ,
  121.     p_channel_radio           char(1)                       ,
  122.     p_channel_press           char(1)                       ,
  123.     p_channel_event           char(1)                       ,
  124.     p_channel_demo            char(1)                       ,
  125.     p_channel_details         varchar(100)                  ,
  126.     p_purpose                 char(15)                      ,
  127.     p_discount_active         char(1)                       ,
  128.     primary key (p_promo_sk)
  129. );
  130. create table customer_address
  131. (
  132.     ca_address_sk             integer               not null,
  133.     ca_address_id             char(16)              not null,
  134.     ca_street_number          char(10)                      ,
  135.     ca_street_name            varchar(60)                   ,
  136.     ca_street_type            char(15)                      ,
  137.     ca_suite_number           char(10)                      ,
  138.     ca_city                   varchar(60)                   ,
  139.     ca_county                 varchar(30)                   ,
  140.     ca_state                  char(2)                       ,
  141.     ca_zip                    char(10)                      ,
  142.     ca_country                varchar(20)                   ,
  143.     ca_gmt_offset             decimal(5,2)                  ,
  144.     ca_location_type          char(20)                      ,
  145.     primary key (ca_address_sk)
  146. );
  147. create table item
  148. (
  149.     i_item_sk                 integer               not null,
  150.     i_item_id                 char(16)              not null,
  151.     i_rec_start_date          date                          ,
  152.     i_rec_end_date            date                          ,
  153.     i_item_desc               varchar(200)                  ,
  154.     i_current_price           decimal(7,2)                  ,
  155.     i_wholesale_cost          decimal(7,2)                  ,
  156.     i_brand_id                integer                       ,
  157.     i_brand                   char(50)                      ,
  158.     i_class_id                integer                       ,
  159.     i_class                   char(50)                      ,
  160.     i_category_id             integer                       ,
  161.     i_category                char(50)                      ,
  162.     i_manufact_id             integer                       ,
  163.     i_manufact                char(50)                      ,
  164.     i_size                    char(20)                      ,
  165.     i_formulation             char(20)                      ,
  166.     i_color                   char(20)                      ,
  167.     i_units                   char(10)                      ,
  168.     i_container               char(10)                      ,
  169.     i_manager_id              integer                       ,
  170.     i_product_name            char(50)                      ,
  171.     primary key (i_item_sk)
  172. );
  173. explain
  174. select i_product_name product_name
  175. ,i_item_sk item_sk
  176. ,s_store_name store_name
  177. ,s_zip store_zip
  178. ,ad2.ca_street_number c_street_number
  179. ,ad2.ca_street_name c_street_name
  180. ,ad2.ca_city c_city
  181. ,ad2.ca_zip c_zip
  182. ,count(*) cnt
  183. ,sum(ss_wholesale_cost) s1
  184. ,sum(ss_list_price) s2
  185. ,sum(ss_coupon_amt) s3
  186. FROM   store_sales
  187. ,store_returns
  188. ,store
  189. ,customer
  190. ,promotion
  191. ,customer_address ad2
  192. ,item
  193. WHERE  ss_store_sk = s_store_sk AND
  194. ss_customer_sk = c_customer_sk AND
  195. ss_item_sk = i_item_sk and
  196. ss_item_sk = sr_item_sk and
  197. ss_ticket_number = sr_ticket_number and
  198. c_current_addr_sk = ad2.ca_address_sk and
  199. ss_promo_sk = p_promo_sk and
  200. i_color in ('maroon','burnished','dim','steel','navajo','chocolate') and
  201. i_current_price between 35 and 35 + 10 and
  202. i_current_price between 35 + 1 and 35 + 15
  203. group by i_product_name
  204. ,i_item_sk
  205. ,s_store_name
  206. ,s_zip
  207. ,ad2.ca_street_number
  208. ,ad2.ca_street_name
  209. ,ad2.ca_city
  210. ,ad2.ca_zip
  211. ;

Plan Hint调优概述 - 图2