OBCP第四章 SQL调优-Hint

Hint及特点

基于代价的优化器，与Oracle的Hint类似

如果使用MySQL的客户端执行带Hint的SQL语句，需要使用-c选项登陆，否则MySQL客户端会将Hint作为注释从用户SQL中去除，导致系统无法收到用户Hint

如果server端不认识SQL语句中的Hint，直接忽略而不报错

Hint只影响数据库优化器生成计划的逻辑，而不影响SQL语句本身的语义

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

不带参数的，如/*+ FUNC */

带参数的，如/*+ FUNC(param) */

多个hint可以写到同一个注释中，用逗号分隔，如/*+ FUNC1, FUNC2(param) */

SELECT语句的hint必须近接在关键字SELECT之后，其他词之前。如：SELECT /*+ FUNC */ …

UPDATE, DELETE语句的hint必须紧接在关键字UPDATE，DELETE之后

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失效

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

查询限流的例子

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

执行计划绑定-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 创建生效

确定 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;

执行计划管理

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是否有已验证的计划，如果有，则优先使用已验证计划，新计划需要通过演进验证后再使用。

执行计划管理-DBMS_SPM系统包

DBMS_SPM 是操作 SPM 的命令包，可支持加载、更改以及删除 Plan Baseline 信息

LOAD_PLANS_FROM_CURSOR_CACHE

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;