OceanBase Oracle模式下使用Outline绑定执行计划

原创张玉龙 2023-08-17

932

通过对某条 SQL 创建 Outline 可实现执行计划的绑定。
OceanBase 数据库支持通过两种方式创建 Outline，一种是通过 SQL_TEXT (用户执行的带参数的原始语句)，另一种是通过 SQL_ID 创建。

使用 SQL_TEXT 创建 Outline

CREATE [OR REPLACE] OUTLINE <outline_name> ON <stmt> [ TO <target_stmt> ];

其中 stmt 一般为一个带有 Hint 和原始参数的 DML 语句。
如果不指定 target_stmt，则表示如果数据库接受的 SQL 参数化后与 stmt 去掉 Hint 参数化文本相同，则将该 SQL 绑定 stmt 中 Hint 生成执行计划。
如果期望对含有 Hint 的语句执行固定计划，则需要 target_stmt 来指明原始的 SQL。
在使用 target_stmt 时，严格要求 stmt 与 target_stmt 在去掉 Hint 后完全匹配。

obclient> create table t1 (c1 int primary key, c2 int, c3 int, index idx_c2(c2));
obclient> insert into t1 values(1, 1, 1), (2, 2, 2), (3, 3, 3);
obclient> explain extended_noaddr select * from t1 where c1 = 1 and c2 = 1\G

优化器选择了走主键扫描，如果想让执行计划走 idx_c2 索引，使用Hint如下：

obclient> explain extended_noaddr select /*+index(t1 idx_c2)*/* from t1 where c1 = 1 and c2 = 1\G

根据如下 SQL 语句创建 Outline：

# 方式一：不指定 TO target_stmt
obclient> create outline otl_idx_c2 on select /*+index(t1 idx_c2)*/* from t1 where c1 = 1 and c2 = 1;
# 方式二：指定 TO target_stmt
obclient> create outline otl_idx_c2 on select /*+index(t1 idx_c2)*/* from t1 where c1 = 1 and c2 = 1 to select * from t1 where c1 = 1 and c2 = 1;

通过视图 gv$outline 查看已经创建的Outline，此视图的基表是 __all_outline

obclient> select * from gv$outline where outline_name = 'OTL_IDX_C2'\G

EXPLAIN 显示的执行计划不会参考Outline，还是走主键扫描

obclient> explain extended_noaddr select * from t1 where c1 = 1 and c2 = 1\G

执行SQL查看真实的执行计划，可以看到SQL走了索引 IDX_C2

obclient> set ob_enable_trace_log=1;
Query OK, 0 rows affected (0.001 sec)

obclient> select * from t1 where c1 = 1 and c2 = 1;
+----+------+------+
| C1 | C2   | C3   |
+----+------+------+
|  1 |    1 |    1 |
+----+------+------+
1 row in set (0.002 sec)

obclient [TBCS]> show trace like 'process begin'\G
*************************** 1. row ***************************
   TITLE: process begin
KEYVALUE: addr:{ip:"10.10.10.71", port:63886}, in_queue_time:11, receive_ts:1692263394000116, enqueue_ts:1692263394000117, trace_id:YB420AE6B749-0006000B2B4997F4-0-0
    TIME: 0
1 row in set (0.003 sec)

obclient [TBCS]> select svr_ip,svr_port,trace_id,tenant_id,sql_id,query_sql,plan_id from gv$sql_audit where trace_id='YB420AE6B749-0006000B2B4997F4-0-0'\G
*************************** 1. row ***************************
   SVR_IP: 10.10.10.73
 SVR_PORT: 2882
 TRACE_ID: YB420AE6B749-0006000B2B4997F4-0-0
TENANT_ID: c
   SQL_ID: F296DCC7D661BF78D15FD5E4A753B53B
QUERY_SQL: select * from t1 where c1 = 1 and c2 = 1
  PLAN_ID: 20090199
1 row in set (14.318 sec)

obclient [TBCS]> select SVR_IP,PLAN_ID,OPERATOR,NAME,"ROWS","COST" from gv$plan_cache_plan_explain where SVR_IP = '10.10.10.73' and SVR_PORT = '2882' and TENANT_ID = '1005' and PLAN_ID = '20090199';
+---------------+----------+----------------+------------+------+------+
| SVR_IP        | PLAN_ID  | OPERATOR       | NAME       | ROWS | COST |
+---------------+----------+----------------+------------+------+------+
| 10.10.10.73   | 20090199 | PHY_TABLE_SCAN | T1(IDX_C2) |    1 |   91 |
+---------------+----------+----------------+------------+------+------+
1 row in set (0.002 sec)

通过视图 gv$plan_cache_plan_stat 的字段 OUTLINE_ID 关联视图 gv$outline 也可以看出SQL使用了哪个Outline

obclient [TBCS]> select plan_id,sql_id,statement,query_sql,outline_version,outline_id,outline_data,hints_info from gv$plan_cache_plan_stat where SVR_IP = '10.10.10.73' and SVR_PORT = '2882' and TENANT_ID = '1005' and PLAN_ID = '20090199'\G
*************************** 1. row ***************************
        PLAN_ID: 20090199
         SQL_ID: F296DCC7D661BF78D15FD5E4A753B53B
      STATEMENT: select * from t1 where c1 = ? and c2 = ?
      QUERY_SQL: select * from t1 where c1 = 1 and c2 = 1
OUTLINE_VERSION: 1692263194651032
     OUTLINE_ID: 1105009185915888
   OUTLINE_DATA: /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "TBCS.T1"@"SEL$1" "IDX_C2") END_OUTLINE_DATA*/
     HINTS_INFO:  INDEX(@"SEL$1" "TBCS.T1"@"SEL$1" "IDX_C2")
1 row in set (0.034 sec)

由于OceanBase的快速参数化功能，示例SQL中的where条件的 c1 和 c2 被常量化，例如上面查询的 STATEMENT 字段 select * from t1 where c1 = ? and c2 = ?，这样不管 c1 和 c2 给啥值都会走这个Outline，PLAN_ID是不变的。

obclient [TBCS]> select * from t1 where c1 = 100 and c2 = 100;
Empty set (0.001 sec)

obclient [TBCS]> show trace like 'process begin'\G
*************************** 1. row ***************************
   TITLE: process begin
KEYVALUE: addr:{ip:"10.10.10.71", port:63886}, in_queue_time:11, receive_ts:1692263699373858, enqueue_ts:1692263699373860, trace_id:YB420AE6B749-0006000B2B5CB205-0-0
    TIME: 0
1 row in set (0.003 sec)

obclient [TBCS]> select svr_ip,svr_port,trace_id,tenant_id,sql_id,query_sql,plan_id from gv$sql_audit where trace_id='YB420AE6B749-0006000B2B5CB205-0-0'\G
*************************** 1. row ***************************
   SVR_IP: 10.10.10.73
 SVR_PORT: 2882
 TRACE_ID: YB420AE6B749-0006000B2B5CB205-0-0
TENANT_ID: 1005
   SQL_ID: F296DCC7D661BF78D15FD5E4A753B53B
QUERY_SQL: select * from t1 where c1 = 100 and c2 = 100
  PLAN_ID: 20090199
1 row in set (11.753 sec)

使用 SQL_ID 创建 Outline

CREATE OUTLINE outline_name ON sql_id USING HINT hint_text;

SQL_ID 为需要绑定的 SQL 对应的 SQL_ID，可以通过以下方式获取：

通过查询 gv$plan_cache_plan_stat 获取。
通过查询 gv$sql_audit 获取。
通过参数化的原始 SQL，使用 MD5 生成 SQL_ID 。可参考如下脚本生成对应 SQL 的 SQL_ID。

[root@ocp zyl]# cat sqlid.py
import hashlib
sql_text='select * from t1 where c1 = ? and c2 = ?'
sql_id=hashlib.md5(sql_text.encode('utf-8')).hexdigest().upper()
print(sql_id)

[root@ocp zyl]# python3 sqlid.py
F296DCC7D661BF78D15FD5E4A753B53B

删除现有的Outline，获取SQL的SQL_ID。

obclient> drop outline OTL_IDX_C2;
obclient [TBCS]> select * from t1 where c1 = 1 and c2 = 1;
obclient [TBCS]> show trace like 'process begin'\G
*************************** 1. row ***************************
   TITLE: process begin
KEYVALUE: addr:{ip:"10.10.10.71", port:63886}, in_queue_time:9, receive_ts:1692273469717087, enqueue_ts:1692273469717089, trace_id:YB420AE6B749-000600097AC324E5-0-0
    TIME: 0
1 row in set (0.002 sec)

obclient [TBCS]> select svr_ip,svr_port,trace_id,tenant_id,sql_id,query_sql,plan_id from gv$sql_audit where trace_id='YB420AE6B749-000600097AC324E5-0-0'\G
*************************** 1. row ***************************
   SVR_IP: 10.10.10.73
 SVR_PORT: 2882
 TRACE_ID: YB420AE6B749-000600097AC324E5-0-0
TENANT_ID: 1005
   SQL_ID: F296DCC7D661BF78D15FD5E4A753B53B
QUERY_SQL: select * from t1 where c1 = 1 and c2 = 1
  PLAN_ID: 20142876
1 row in set (11.729 sec)

obclient [TBCS]> select plan_id,sql_id,statement,query_sql,outline_version,outline_id,outline_data,hints_info from gv$plan_cache_plan_stat where SVR_IP = '10.10.10.73' and SVR_PORT = '2882' and TENANT_ID = '1005' and PLAN_ID = '20142876'\G
*************************** 1. row ***************************
        PLAN_ID: 20142876
         SQL_ID: F296DCC7D661BF78D15FD5E4A753B53B
      STATEMENT: select * from t1 where c1 = ? and c2 = ?
      QUERY_SQL: select * from t1 where c1 = 1 and c2 = 1
OUTLINE_VERSION: 0
     OUTLINE_ID: -1
   OUTLINE_DATA: /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "TBCS.T1"@"SEL$1") END_OUTLINE_DATA*/
     HINTS_INFO: NULL
1 row in set (0.035 sec)

基于 SQL_ID 创建 OUTLINE

# 绑定outline，普通索引：
CREATE OUTLINE otl_idx_c2 ON 'ED570339F2C856BA96008A29EDF04C74' USING HINT /*+INDEX(t1 idx_c2)*/ ;
# 绑定outline，主键索引：
CREATE OUTLINE otl_idx_c2 ON 'ED570339F2C856BA96008A29EDF04C74' USING HINT /*+INDEX(t1 primary)*/ ;

使用 SQL_TEXT 方式创建的 Outline 会覆盖 SQL_ID 方式创建的 Outline。SQL_TEXT 方式创建的优先级更高。
如果 SQL_ID 对应的 SQL 语句已经有 Hint，则创建 Outline 指定的 Hint 会覆盖原始语句中所有 Hint。

obclient [TBCS]> select * from gv$outline where outline_name = 'OTL_IDX_C2'\G
*************************** 1. row ***************************
        TENANT_ID: 1005
      DATABASE_ID: 1105009185915962
       OUTLINE_ID: 1105009185915889
    DATABASE_NAME: TBCS
     OUTLINE_NAME: OTL_IDX_C2
VISIBLE_SIGNATURE:
         SQL_TEXT:
   OUTLINE_TARGET:
      OUTLINE_SQL:
           SQL_ID: F296DCC7D661BF78D15FD5E4A753B53B
  OUTLINE_CONTENT: /*+index(t1 idx_c2)*/
1 row in set (0.004 sec)

obclient [TBCS]> select * from t1 where c1 = 1 and c2 = 1;
+----+------+------+
| C1 | C2   | C3   |
+----+------+------+
|  1 |    1 |    1 |
+----+------+------+
1 row in set (0.002 sec)

obclient [TBCS]> show trace like 'process begin'\G
*************************** 1. row ***************************
   TITLE: process begin
KEYVALUE: addr:{ip:"10.10.10.71", port:63886}, in_queue_time:15, receive_ts:1692274942409736, enqueue_ts:1692274942409740, trace_id:YB420AE6B749-0006000B2B5CBA98-0-0
    TIME: 0
1 row in set (0.002 sec)

obclient [TBCS]> select svr_ip,svr_port,trace_id,tenant_id,sql_id,query_sql,plan_id from gv$sql_audit where trace_id='YB420AE6B749-0006000B2B5CBA98-0-0';
+---------------+----------+-----------------------------------+-----------+----------------------------------+------------------------------------------+----------+
| SVR_IP        | SVR_PORT | TRACE_ID                          | TENANT_ID | SQL_ID                           | QUERY_SQL                                | PLAN_ID  |
+---------------+----------+-----------------------------------+-----------+----------------------------------+------------------------------------------+----------+
| 10.10.10.73   |     2882 | YB420AE6B749-0006000B2B5CBA98-0-0 |      1005 | F296DCC7D661BF78D15FD5E4A753B53B | select * from t1 where c1 = 1 and c2 = 1 | 20150965 |
+---------------+----------+-----------------------------------+-----------+----------------------------------+------------------------------------------+----------+
1 row in set (11.941 sec)

obclient [TBCS]> select SVR_IP,PLAN_ID,OPERATOR,NAME,"ROWS","COST" from gv$plan_cache_plan_explain where SVR_IP = '10.10.10.73' and SVR_PORT = '2882' and TENANT_ID = '1005' and PLAN_ID = '20150965';
+---------------+----------+----------------+------------+------+------+
| SVR_IP        | PLAN_ID  | OPERATOR       | NAME       | ROWS | COST |
+---------------+----------+----------------+------------+------+------+
| 10.10.10.73   | 20150965 | PHY_TABLE_SCAN | T1(IDX_C2) |    1 |    7 |
+---------------+----------+----------------+------------+------+------+
1 row in set (0.001 sec)

obclient [TBCS]> select plan_id,sql_id,statement,query_sql,outline_version,outline_id,outline_data,hints_info from gv$plan_cache_plan_stat where SVR_IP = '10.10.10.73' and SVR_PORT = '2882' and TENANT_ID = '1005' and PLAN_ID = '20150965'\G
*************************** 1. row ***************************
        PLAN_ID: 20150965
         SQL_ID: F296DCC7D661BF78D15FD5E4A753B53B
      STATEMENT: select * from t1 where c1 = ? and c2 = ?
      QUERY_SQL: select * from t1 where c1 = 1 and c2 = 1
OUTLINE_VERSION: 1692274817272232
     OUTLINE_ID: 1105009185915889
   OUTLINE_DATA: /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "TBCS.T1"@"SEL$1" "IDX_C2") END_OUTLINE_DATA*/
     HINTS_INFO:  INDEX(@"SEL$1" "TBCS.T1"@"SEL$1" "IDX_C2")
1 row in set (0.031 sec)

Outline Data 也属于 Hint，因此可以用在计划绑定的过程中，如下例所示：

obclient [TBCS]> drop outline OTL_IDX_C2;
Query OK, 0 rows affected (1.106 sec)

obclient [TBCS]> explain extended select /*+index(t1 idx_c2)*/* from t1 where c1 = 1 and c2 = 1\G
*************************** 1. row ***************************
Query Plan: ========================================
|ID|OPERATOR |NAME      |EST. ROWS|COST|
----------------------------------------
|0 |TABLE GET|T1(IDX_C2)|1        |8   |
========================================

... ...

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "TBCS.T1"@"SEL$1" "IDX_C2")
      END_OUTLINE_DATA
  */

CREATE OUTLINE otl_idx_c2 ON 'F296DCC7D661BF78D15FD5E4A753B53B' USING HINT
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "TBCS.T1"@"SEL$1" "IDX_C2")
      END_OUTLINE_DATA
  */;

obclient [TBCS]> select * from gv$outline where outline_name = 'OTL_IDX_C2'\G
*************************** 1. row ***************************
        TENANT_ID: 1005
      DATABASE_ID: 1105009185915962
       OUTLINE_ID: 1105009185915890
    DATABASE_NAME: TBCS
     OUTLINE_NAME: OTL_IDX_C2
VISIBLE_SIGNATURE:
         SQL_TEXT:
   OUTLINE_TARGET:
      OUTLINE_SQL:
           SQL_ID: F296DCC7D661BF78D15FD5E4A753B53B
  OUTLINE_CONTENT: /*+BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "TBCS.T1"@"SEL$1" "IDX_C2")
      END_OUTLINE_DATA
  */
1 row in set (0.003 sec)

obclient [TBCS]> select * from t1 where c1 = 2 and c2 = 4;
Empty set (0.002 sec)

obclient [TBCS]> show trace like 'process begin'\G
*************************** 1. row ***************************
   TITLE: process begin
KEYVALUE: addr:{ip:"10.10.10.71", port:63886}, in_queue_time:4, receive_ts:1692276072748683, enqueue_ts:1692276072748684, trace_id:YB420AE6B749-000600095B632C3E-0-0
    TIME: 0
1 row in set (0.003 sec)

obclient [TBCS]> select svr_ip,svr_port,trace_id,tenant_id,sql_id,query_sql,plan_id from gv$sql_audit where trace_id='YB420AE6B749-000600095B632C3E-0-0';
+---------------+----------+-----------------------------------+-----------+----------------------------------+------------------------------------------+----------+
| SVR_IP        | SVR_PORT | TRACE_ID                          | TENANT_ID | SQL_ID                           | QUERY_SQL                                | PLAN_ID  |
+---------------+----------+-----------------------------------+-----------+----------------------------------+------------------------------------------+----------+
| 10.10.10.73   |     2882 | YB420AE6B749-000600095B632C3E-0-0 |      1005 | F296DCC7D661BF78D15FD5E4A753B53B | select * from t1 where c1 = 2 and c2 = 4 | 20157788 |
+---------------+----------+-----------------------------------+-----------+----------------------------------+------------------------------------------+----------+
1 row in set (12.808 sec)

obclient [TBCS]> select SVR_IP,PLAN_ID,OPERATOR,NAME,"ROWS","COST" from gv$plan_cache_plan_explain where SVR_IP = '10.10.10.73' and SVR_PORT = '2882' and TENANT_ID = '1005' and PLAN_ID = '20157788';
+---------------+----------+----------------+------------+------+------+
| SVR_IP        | PLAN_ID  | OPERATOR       | NAME       | ROWS | COST |
+---------------+----------+----------------+------------+------+------+
| 10.10.10.73   | 20157788 | PHY_TABLE_SCAN | T1(IDX_C2) |    1 |    7 |
+---------------+----------+----------------+------------+------+------+
1 row in set (0.001 sec)

obclient [TBCS]> select plan_id,sql_id,statement,query_sql,outline_version,outline_id,outline_data,hints_info from gv$plan_cache_plan_stat where SVR_IP = '10.10.10.73' and SVR_PORT = '2882' and TENANT_ID = '1005' and PLAN_ID = '20157788'\G
*************************** 1. row ***************************
        PLAN_ID: 20157788
         SQL_ID: F296DCC7D661BF78D15FD5E4A753B53B
      STATEMENT: select * from t1 where c1 = ? and c2 = ?
      QUERY_SQL: select * from t1 where c1 = 2 and c2 = 4
OUTLINE_VERSION: 1692275860767040
     OUTLINE_ID: 1105009185915890
   OUTLINE_DATA: /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "TBCS.T1"@"SEL$1" "IDX_C2") END_OUTLINE_DATA*/
     HINTS_INFO:  INDEX(@"SEL$1" "TBCS.T1"@"SEL$1" "IDX_C2")
1 row in set (0.033 sec)

墨力计划 oceanbase

最后修改时间：2023-08-18 06:27:53

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

OceanBase 学习笔记（共11篇）

OceanBase

OceanBase Oracle模式下使用Outline绑定执行计划

使用 SQL_TEXT 创建 Outline

使用 SQL_ID 创建 Outline

文章被以下合辑收录

评论