# AutoHint：智能HINT推荐
#### 概述
AutoHint（智能HINT推荐）将自动识别慢SQL执行计划树中耗时较长的算子，生成候选HINT进行替换，并通过贪心策略进行探索，最终生成能够提升当前慢SQL执行性能的HINT组合推荐给用户。在提升慢SQL性能的同时可以为用户节约人工调优所带来的人力与时间成本。
![](https://support.huaweicloud.com/distributed-devg-v10-gaussdb/public_sys-resources/notice_3.0-zh-cn.png)
容灾集群下不支持智能HINT推荐。
#### 前置条件
- 数据库运行正常。
- 确保当前用户拥有super权限。
- 若反馈基数估计与自适应代价估计功能开启，需要提前运行多次查询保证反馈基数估计模型与代价估计模型收敛。
 
#### 使用指导
参考AutoHint语法运行相关查询即可得到HINT推荐结果。
#### 最佳实践
反馈基数估计功能与自适应代价关闭场景（设置enable_adaptive_cost=off，adaptive_cardest_strategy=use_statistics）：
1. 启动数据库，导入数据。
2. 执行慢SQL语句，如含有多种连接路径和连接类型的查询SQL，观察默认场景下计划的执行时间与执行树。以JOB数据集的query11d语句为例。 
   ```
   gaussdb=# EXPLAIN ANALYZE SELECT MIN(cn.name) AS from_company,
          MIN(mc.note) AS production_note,
          MIN(t.title) AS movie_based_on_book
   FROM company_name AS cn,
        company_type AS ct,
        keyword AS k,
        link_type AS lt,
        movie_companies AS mc,
        movie_keyword AS mk,
        movie_link AS ml,
        title AS t
   WHERE cn.country_code !='[pl]'
     AND ct.kind != 'production companies'
     AND ct.kind IS NOT NULL
     AND k.keyword IN ('sequel',
                       'revenge',
                       'based-on-novel')
     AND mc.note IS NOT NULL
     AND t.production_year > 1965
     AND lt.id = ml.link_type_id
     AND ml.movie_id = t.id
     AND t.id = mk.movie_id
     AND mk.keyword_id = k.id
     AND t.id = mc.movie_id
     AND mc.company_type_id = ct.id
     AND mc.company_id = cn.id
     AND ml.movie_id = mk.movie_id
     AND ml.movie_id = mc.movie_id
     AND mk.movie_id = mc.movie_id;
   ```
   
   
3. 使用AutoHint相关命令进行Hint推荐。 
   ```
   gaussdb=# AUTOHINT ANALYZE 
   SELECT MIN(cn.name) AS from_company,
          MIN(mc.note) AS production_note,
          MIN(t.title) AS movie_based_on_book
   FROM company_name AS cn,
        company_type AS ct,
        keyword AS k,
        link_type AS lt,
        movie_companies AS mc,
        movie_keyword AS mk,
        movie_link AS ml,
        title AS t
   WHERE cn.country_code !='[pl]'
     AND ct.kind != 'production companies'
     AND ct.kind IS NOT NULL
     AND k.keyword IN ('sequel',
                       'revenge',
                       'based-on-novel')
     AND mc.note IS NOT NULL
     AND t.production_year > 1965
     AND lt.id = ml.link_type_id
     AND ml.movie_id = t.id
     AND t.id = mk.movie_id
     AND mk.keyword_id = k.id
     AND t.id = mc.movie_id
     AND mc.company_type_id = ct.id
     AND mc.company_id = cn.id
     AND ml.movie_id = mk.movie_id
     AND ml.movie_id = mc.movie_id
     AND mk.movie_id = mc.movie_id;
   ```
   
   
4. 得到推荐HINT与预期提升效果。 
   ```
   AUTOHINT
   ---------------------------------------------
    Recommended hintset:
      MergeJoin(@sel$1 k@sel$1 mk@sel$1 mc@sel$1)
    Expected boost:
      planning:     -7%
      execution:    62%
      overall:      60%
   ```
   
   
5. 使用推荐的HINT，观察执行计划是否改变，以及执行性能是否得到提升。 
   ```
   gaussdb=# EXPLAIN ANALYZE SELECT /*+MergeJoin(@sel$1 k@sel$1 mk@sel$1 mc@sel$1)*/ MIN(cn.name) AS from_company,
          MIN(mc.note) AS production_note,
          MIN(t.title) AS movie_based_on_book
   FROM company_name AS cn,
        company_type AS ct,
        keyword AS k,
        link_type AS lt,
        movie_companies AS mc,
        movie_keyword AS mk,
        movie_link AS ml,
        title AS t
   WHERE cn.country_code !='[pl]'
     AND ct.kind != 'production companies'
     AND ct.kind IS NOT NULL
     AND k.keyword IN ('sequel',
                       'revenge',
                       'based-on-novel')
     AND mc.note IS NOT NULL
     AND t.production_year > 1965
     AND lt.id = ml.link_type_id
     AND ml.movie_id = t.id
     AND t.id = mk.movie_id
     AND mk.keyword_id = k.id
     AND t.id = mc.movie_id
     AND mc.company_type_id = ct.id
     AND mc.company_id = cn.id
     AND ml.movie_id = mk.movie_id
     AND ml.movie_id = mc.movie_id
     AND mk.movie_id = mc.movie_id;
   ```
   
   
反馈基数估计功能与自适应代价估计功能开启场景：
导入数据后，执行慢SQL多次，直到观察到SQL执行计划稳定后，说明反馈基数估计模型与代价估计模型已收敛，后续操作与上述步骤一致。
#### 常见问题处理
可能会遇到网络问题与最大连接数问题导致探索过程失败，此时请尝试更改最大连接数上限，或等待资源空闲后再重新使用该功能。