【OceanBase】OB-hint管理及使用

1.Hint及特点

◼ 基于代价的优化器，与Oracle的Hint类似
◼ 如果使用MySQL的客户端执行带Hint的SQL语句，需要使用-c选项登陆， 否则MySQL客户端会将Hint作为注释从
用户SQL中去除，导致系统无法收到用户Hint
◼ 如果server端不认识SQL语句中的Hint，直接忽略而不报错
◼ Hint只影响数据库优化器生成计划的逻辑，而不影响SQL语句本身的语义

2.OceanBase支持的hint有以下几个特点：

不带参数的，如/*+ FUNC */
带参数的，如/*+ FUNC(param) */
多个hint可以写到同一个注释中，用逗号分隔，如/*+ FUNC1, FUNC2(param) */
SELECT语句的hint必须近接在关键字SELECT之后，其他词之前。如：SELECT /*+ FUNC */ …
UPDATE, DELETE语句的hint必须紧接在关键字UPDATE，DELETE之后

3.Hint举例

◼ 强一致/弱一致：/*+READ_CONSISTENCY(STRONG)*/， /*+READ_CONSISTENCY(WEAK)*/
◼ Server端执行语句超时时间：/*+query_timeout(100000000)*/ 单位微秒
◼ 采用sort-merge连接：/*+USE_MERGE(表名1 表名2)*/
◼ 强制使用索引：/*+INDEX(表名 索引名) */ 
◼ 采用并行：/*+PARALLEL(N)*/指定语句级别的并发度。
⚫ 当该hint指定时，会忽略系统变ob_stmt_parallel_degree的设置
◼ /*+ leading(table_name_list)*/
⚫ 指定表的连接顺序
⚫ 如果发现hint指定的table_name不存在，leading hint失效；
⚫ 如果发现hint中存在重复table，leading hint失效

4.Hint的行为理念及当前支持的Hint

◼ Hint是为了告诉优化器考虑hint中的方式 ， 其它数据库的行为更像贪心算法，不会考虑全部可能的路径最优，
hint的指定的方式就是为了告诉数据库加入到它的考虑范围
◼ OB优化器更像是动态规划，已经考虑了所有可能，因此hint告诉数据库加入到考虑范围就没有什么意义。基于这
种情况，OB的hint更多是告诉优化器按照指定行为做

--语句级别hint
MAX_CONCURRENT 
FROZEN_VERSION
QUERY_TIMEOUT
READ_CONSISTENCY
LOG_LEVEL
QB_NAME
ACTIVATE_BURIED_POINT
TRACE_LOG

--计划相关hint
FULL
INDEX
LEADING
USE_MERGE
USE_HASH
USE_NL
ORDERED
NO_REWRITE

5.查询限流的例子

Hint中使用max_concurrent
？表示需要参数化的参数
OceanBase (root@oceanbase)> create table t1(a int primary key, b int, c int);
Query OK, 0 rows affected (0.15 sec)
OceanBase (root@oceanbase)> create outline ol_1 on select/*+max_concurrent(0)*/ * from 
t1 where b =1 and c = 1;
Query OK, 0 rows affected (0.06 sec)
OceanBase (root@oceanbase)> select * from t1 where b =1 and c = 1;
ERROR 5268 (HY000): SQL reach max concurrent num 0
OceanBase (root@oceanbase)> select * from t1 where b =1 and c = 2;
Empty set (0.01 sec)
OceanBase (root@oceanbase)> create outline ol_2 on select/*+max_concurrent(0)*/ * from 
t1 where b =1 and c = ?;
Query OK, 0 rows affected (0.05 sec)
OceanBase (root@oceanbase)> select * from t1 where b =1 and c = 1;
ERROR 5268 (HY000): SQL reach max concurrent num 0
OceanBase (root@oceanbase)> select * from t1 where b =1 and c = 2;
ERROR 5268 (HY000): SQL reach max concurrent num 0

6.执行计划绑定-Outline创建和删除

对于已上线的业务，如果出现优化器选择的计划不够优化时，则需要在线进行计划绑定，即无需业务进行 SQL 更改，
而是通过 DDL 操作将一组 Hint 加入到 SQL 中，从而使优化器根据指定的一组 Hint，对该 SQL 生成更优计划。
该组Hint 称为 Outline。
◼ 创建 Outline
⚫ 使用 SQL_TEXT 创建 Outline
CREATE [OR REPLACE] OUTLINE <outline_name> ON <stmt> [ TO <target_stmt> ];
使用 SQL_ID 创建 Outline
CREATE OUTLINE outline_name ON sql_id USING HINT hint_text;

删除Outline
DROP OUTLINE outline_name;
注：删除 Outline 需要在 outline_name 中指定 Database 名，或者在 USE DATABASE 命令后执行删除操作。

7.执行计划绑定-确定 Outline 创建生效

确定 Outline 创建生效
⚫ 确定 Outline 创建成功
SELECT * FROM oceanbase.gv$outline WHERE OUTLINE_NAME = 'outline_name';
--确定新的 SQL 是否通过绑定的 Outline 生成了新执行计划
SELECT SQL_ID, PLAN_ID, STATEMENT, OUTLINE_ID, OUTLINE_DATA 
FROM oceanbase.gv$plan_cache_plan_stat 
WHERE STATEMENT LIKE '%sql_text%';

--确定生成的执行计划是否符合预期
SELECT OPERATOR, NAME FROM oceanbase.gv$plan_cache_plan_explain 
WHERE TENANT_ID = tenant_id AND IP = 'IP_address' 
AND PORT = port_num AND PLAN_ID = plan_id;

8.执行计划管理

◼ SQL Plan Management（SPM）是一种稳定执行计划、控制计划演进的机制，确保新生成的计划在经过验证后才
能使用，保证计划性能朝好的方向不断更新。SPM 基于 SQL Plan Baseline 实现，SQL Plan Baseline 是执行计划
的一个基线，用于持久化存储已经验证过的执行计划信息（Outline Data 等信息），每个执行计划可对应一个 Plan
Baseline，通过该 Plan Baseline 可复现一个执行计划。
◼ SPM 机制包含如下过程：
⚫ 计划捕获
对于新生成的计划，如果 SQL Plan Baseline 为空，则直接加入 SQL Plan Baseline，否则通过演进验证新生成
计划比 SQL Plan Baseline 中计划性能更优后加入 SQL Plan Baseline，并删除旧的 Plan Baseline。
⚫ 计划演进
如果相同 SQL 新捕获的计划和 SQL Plan Baseline 中的计划不一样，则通过流量灰度验证新计划的性能是否比
以前验证过的计划更优。如果更优，则将新计划加入 SQL Plan Baseline，并执行新计划，否则仍使用旧计划。
⚫ 计划选择。
在优化器新生成计划时，会查看 SQL Plan Baseline 是否有已验证的计划，如果有，则优先使用已验证计划，新
计划需要通过演进验证后再使用。

9.执行计划管理-DBMS_SPM系统包

DBMS_SPM 是操作 SPM 的命令包，可支持加载、更改以及删除 Plan Baseline 信息
LOAD_PLANS_FROM_CURSOR_CACHE 用于将 Plan Cache 中执行计划对应的 Plan Baseline 信息加载到
__all_tenant_plan_baseline 表中。

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
fixed IN VARCHAR2 := 'NO',
enabled IN VARCHAR2 := 'YES')
RETURN PLS_INTEGER;

--ALTER_SQL_PLAN_BASELINE
ALTER_SQL_PLAN_BASELINE 用于修改 Plan Baseline 中某些属性。
DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( 
	sql_handle IN VARCHAR2 := NULL,
	plan_name IN VARCHAR2 := NULL,
	attribute_name IN VARCHAR2,
	attribute_value IN VARCHAR2)
RETURN PLS_INTEGER;

--DROP_SQL_PLAN_BASELINE
DROP_SQL_PLAN_BASELINE 用于删掉某个 Plan Baseline。
DBMS_SPM.DROP_SQL_PLAN_BASELINE (
	sql_handle IN VARCHAR2 := NULL,
	plan_name IN VARCHAR2 := NULL)
RETURN PLS_INTEGER;

10.总结

执行计划加载到基线中这个，这个和Oracle的功能一样，非常使用，能够空值SQL选择合适的执行计划。