实践练习六（必选）：OceanBase TPC-C 测试和查看执行计划

最新推荐文章于 2024-09-27 14:30:51 发布

ddttoop

最新推荐文章于 2024-09-27 14:30:51 发布

阅读量1.9k

点赞数 1

分类专栏： OceanBase 文章标签： c语言数据库开发语言

本文链接：https://blog.csdn.net/weixin_45883933/article/details/123004608

版权

OceanBase 专栏收录该内容

4 篇文章 1 订阅

订阅专栏

本文档详述了在OceanBase数据库上运行TPC-C基准测试的过程，包括使用BenchmarkSQL进行数据加载和性能测试，查看执行计划以及如何通过 Outline 进行SQL优化。在测试中，观察到SQL执行计划并分析了其效率，同时探讨了如何通过创建Outline来限制SQL并发，以达到更优的性能表现。

摘要由CSDN通过智能技术生成

实践练习六（必选）：OceanBase TPC-C 测试和查看执行计划

练习目的

本次练习目的掌握 OceanBase 的执行计划查看方法，包括 explain 命令和查看实际执行计划。

练习条件

有服务器，内存资源至少 12G*1 台，部署有 OceanBase 集群（单副本或三副本都可以）。

练习内容

请记录并分享下列内容：

（必选）使用 BenmarkSQL 运行 TPC-C ，并发数不用很高，5~10 并发即可（根据机器资源）。
（必选）分析 TPC-C TOP SQL，并查看 3条 SQL 的解析执行计划和实际执行计划。
（可选）使用 OceanBase 的 Outline 对其中一条 SQL 进行限流（限制并发为 1 ）。
（可选）导入 TPC-H schema 和数据，数据量不用太大 100M 即可。查看 TPC-H 5条 SQL 的解析执行计划和实际执行计划。

1. 使用 BenmarkSQL 运行 TPC-C

1.1 BenchmarkSQL 下载

# 下载 BenmarkSQL 
https://github.com/obpilot/benchmarksql-5.0-master/archive/refs/heads/master.zip

1.2 BenchmarkSQL 准备

[aicenter@aicenter2 package]$ mkdir /data/aicenter/software/BenchmarkSQL 
[aicenter@aicenter2 package]$ unzip benchmarksql-5.0-master-master.zip -d /data/aicenter/software/BenchmarkSQL/
[aicenter@aicenter2 package]$ cd /data/aicenter/software/BenchmarkSQL 
[aicenter@aicenter2 BenchmarkSQL]$ ll
总用量 4
drwxrwxr-x. 8 aicenter aicenter 4096 2月  29 2020 benchmarksql-5.0-master-master
[aicenter@aicenter2 BenchmarkSQL]$ ll benchmarksql-5.0-master-master/
总用量 44
drwxrwxr-x. 2 aicenter aicenter 4096 2月  29 2020 build
-rwxr-xr-x. 1 aicenter aicenter 1130 2月  29 2020 build.xml
drwxrwxr-x. 2 aicenter aicenter 4096 2月  29 2020 dist
drwxrwxr-x. 3 aicenter aicenter 4096 2月  29 2020 doc
-rwxr-xr-x. 1 aicenter aicenter 6376 2月  29 2020 HOW-TO-RUN.txt
drwxrwxr-x. 5 aicenter aicenter 4096 2月  29 2020 lib
-rwxr-xr-x. 1 aicenter aicenter 5470 2月  29 2020 README.md
drwxrwxr-x. 8 aicenter aicenter 4096 2月  29 2020 run
drwxrwxr-x. 6 aicenter aicenter 4096 2月  29 2020 src

# 验证 Java 环境 (需要有 JAVA 运行环境，版本不低于 V1.8.0)
[aicenter@aicenter2 BenchmarkSQL]$ source /etc/profile
[aicenter@aicenter2 BenchmarkSQL]$ java -version
java version "1.8.0_181"
Java(TM) SE Runtime Environment (build 1.8.0_181-b13)
Java HotSpot(TM) 64-Bit Server VM (build 25.181-b13, mixed mode)

1.3 创建测试库及用户

[aicenter@aicenter2 BenchmarkSQL]$ mysql -h 172.17.0.2 -uroot@obmysql -P2883 -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database tpcc;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE USER 'tpcc'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT ALL ON tpcc.* TO 'tpcc'@'%';
Query OK, 0 rows affected (0.04 sec)

mysql> exit
Bye

1.4 配置文件 `props.ob`

在 benchmarksql-5.0-master/run 目录下。

[aicenter@aicenter2 BenchmarkSQL]$ vim benchmarksql-5.0-master/run/props.ob 

db=oracle
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://172.17.0.2:2883/tpcc?useUnicode=true&characterEncoding=utf-8
user=tpcc@obmysql#obce-single
password=123456

warehouses=2
loadWorkers=10

terminals=10
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=10
//Number of total transactions per minute
limitTxnsPerMin=0

//Set to true to run in 4.x compatible mode. Set to false to use the
//entire configured database evenly.
terminalWarehouseFixed=true

//The following five values must add up to 100
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4

// Directory name to create for collecting detailed result data.
// Comment this out to suppress.
resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
osCollectorScript=./misc/os_collector_linux.py
osCollectorInterval=1

1.5 下载 OceanBase 驱动文件

# 下载 驱动文件 oceanbase-client-x.x.x.jar
wget https://oceanbase-aliyun-docs.oss-cn-hangzhou.aliyuncs.com/downloads/obclient/oceanbase-client-1.1.10.jar?spm=a2c4g.11186623.0.0.27aa32a9CrISr4&file=oceanbase-client-1.1.10.jar

# 把 OceanBase 数据库的驱动放到 lib/ 目录或 lib/oracle 下即可
[aicenter@aicenter2 package]$ mv oceanbase-client-1.1.10.jar /data/aicenter/software/BenchmarkSQL/benchmarksql-5.0-master/lib/
[aicenter@aicenter2 package]$ ll /data/aicenter/software/BenchmarkSQL/benchmarksql-5.0-master/lib/
总用量 4300
-rw-r--r--. 1 aicenter aicenter  346729 2月  17 16:48 apache-log4j-extras-1.1.jar
drwxr-xr-x. 2 aicenter aicenter    4096 2月  17 16:48 firebird
-rw-r--r--. 1 aicenter aicenter  489883 2月  17 16:48 log4j-1.2.17.jar
-rw-rw-r--. 1 aicenter aicenter 3549756 2月  17 17:31 oceanbase-client-1.1.10.jar
drwxr-xr-x. 2 aicenter aicenter    4096 2月  17 16:48 oracle
drwxr-xr-x. 2 aicenter aicenter    4096 2月  17 16:48 postgres

1.6 准备数据

[aicenter@aicenter2 sql.common]$ cp tableCreates.sql mysql-tableCreates.sql 
[aicenter@aicenter2 sql.common]$ sed -i 's/varchar2/varchar/g' mysql-tableCreates.sql 


# 建表 (有非分区表和分区表之别)
[aicenter@aicenter2 run]$ ./runSQL.sh props.ob sql.common/mysql-tableCreates.sql 
# ------------------------------------------------------------
# Loading SQL file sql.common/mysql-tableCreates.sql
# ------------------------------------------------------------
create table bmsql_config (
cfg_name    varchar(30) primary key,
cfg_value   varchar(50)
);
create table bmsql_warehouse (
w_id        integer   not null,
w_ytd       decimal(12,2),
w_tax       decimal(4,4),
w_name      varchar(10),
w_street_1  varchar(20),
w_street_2  varchar(20),
w_city      varchar(20),
w_state     char(2),
w_zip       char(9)
);
create table bmsql_district (
d_w_id       integer       not null,
d_id         integer       not null,
d_ytd        decimal(12,2),
d_tax        decimal(4,4),
d_next_o_id  integer,
d_name       varchar(10),
d_street_1   varchar(20),
d_street_2   varchar(20),
d_city       varchar(20),
d_state      char(2),
d_zip        char(9)
);
create table bmsql_customer (
c_w_id         integer        not null,
c_d_id         integer        not null,
c_id           integer        not null,
c_discount     decimal(4,4),
c_credit       char(2),
c_last         varchar(16),
c_first        varchar(16),
c_credit_lim   decimal(12,2),
c_balance      decimal(12,2),
c_ytd_payment  decimal(12,2),
c_payment_cnt  integer,
c_delivery_cnt integer,
c_street_1     varchar(20),
c_street_2     varchar(20),
c_city         varchar(20),
c_state        char(2),
c_zip          char(9),
c_phone        char(16),
c_since        timestamp,
c_middle       char(2),
c_data         varchar(500)
);
create sequence bmsql_hist_id_seq;
You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'sequence bmsql_hist_id_seq' at line 1
create table bmsql_history (
hist_id  integer,
h_c_id   integer,
h_c_d_id integer,
h_c_w_id integer,
h_d_id   integer,
h_w_id   integer,
h_date   timestamp,
h_amount decimal(6,2),
h_data   varchar(24)
);
create table bmsql_new_order (
no_w_id  integer   not null,
no_d_id  integer   not null,
no_o_id  integer   not null
);
create table bmsql_oorder (
o_w_id       integer      not null,
o_d_id       integer      not null,
o_id         integer      not null,
o_c_id       integer,
o_carrier_id integer,
o_ol_cnt     integer,
o_all_local  integer,
o_entry_d    timestamp
);
create table bmsql_order_line (
ol_w_id         integer   not null,
ol_d_id         integer   not null,
ol_o_id         integer   not null,
ol_number       integer   not null,
ol_i_id         integer   not null,
ol_delivery_d   timestamp,
ol_amount       decimal(6,2),
ol_supply_w_id  integer,
ol_quantity     integer,
ol_dist_info    char(24)
);
create table bmsql_item (
i_id     integer      not null,
i_name   varchar(24),
i_price  decimal(5,2),
i_data   varchar(50),
i_im_id  integer
);
create table bmsql_stock (
s_w_id       integer       not null,
s_i_id       integer       not null,
s_quantity   integer,
s_ytd        integer,
s_order_cnt  integer,
s_remote_cnt integer,
s_data       varchar(50),
s_dist_01    char(24),
s_dist_02    char(24),
s_dist_03    char(24),
s_dist_04    char(24),
s_dist_05    char(24),
s_dist_06    char(24),
s_dist_07    char(24),
s_dist_08    char(24),
s_dist_09    char(24),
s_dist_10    char(24)
);

# 加载数据 (加载数据就是做数据初始化，仓库数越多加载时间越长。具体性能取决于机器配置。)
[aicenter@aicenter2 run]$ ./runLoader.sh props.ob
Starting BenchmarkSQL LoadData

driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://172.17.0.2:2883/tpcc?useUnicode=true&characterEncoding=utf-8
user=tpcc@obmysql#obce-single
password=***********
warehouses=2
loadWorkers=10
fileLocation (not defined)
csvNullValue (not defined - using default 'NULL')

Worker 000: Loading ITEM
Worker 001: Loading Warehouse      1
Worker 002: Loading Warehouse      2
Worker 000: Loading ITEM done
Worker 002: ERROR: Transaction is timeout
Worker 001: ERROR: Transaction is timeout
# 事务超时处理 ，调整事务超时参数
[aicenter@aicenter2 run]$ mysql -h 172.17.0.2 -uroot@obmysql -P2883 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%timeout%';
+---------------------+------------------+
| Variable_name       | Value            |
+---------------------+------------------+
| connect_timeout     | 10               |
| interactive_timeout | 28800            |
| lock_wait_timeout   | 31536000         |
| net_read_timeout    | 30               |
| net_write_timeout   | 60               |
| ob_pl_block_timeout | 3216672000000000 |
| ob_query_timeout    | 10000000         |
| ob_trx_idle_timeout | 120000000        |
| ob_trx_lock_timeout | -1               |
| ob_trx_timeout      | 100000000        |
| wait_timeout        | 28800            |
+---------------------+------------------+
11 rows in set (0.00 sec)

mysql> set global ob_trx_timeout=10000000000;
Query OK, 0 rows affected (0.92 sec)

mysql> set global ob_query_timeout=100000000;
Query OK, 0 rows affected (0.03 sec)

mysql> exit
Bye

[aicenter@aicenter2 run]$ ./runLoader.sh props.ob
Starting BenchmarkSQL LoadData

driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://172.17.0.2:2883/tpcc?useUnicode=true&characterEncoding=utf-8
user=tpcc@obmysql#obce-single
password=***********
warehouses=2
loadWorkers=10
fileLocation (not defined)
csvNullValue (not defined - using default 'NULL')

Worker 000: Loading ITEM
Worker 001: Loading Warehouse      1
Worker 002: Loading Warehouse      2
Worker 000: Loading ITEM done
Worker 002: Loading Warehouse      2 done
Worker 001: Loading Warehouse      1 done

# 创建索引 (当数据初始化完后再补充两个索引)
[aicenter@aicenter2 run]$ ./runSQL.sh props.ob sql.oceanbase/indexCreates.sql
# ------------------------------------------------------------
# Loading SQL file sql.oceanbase/indexCreates.sql
# ------------------------------------------------------------
create index bmsql_customer_idx1
on  bmsql_customer (c_w_id, c_d_id, c_last, c_first) local;
create  index bmsql_oorder_idx1
on  bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local;

1.7 性能测试

数据初始化确认没有错误（没有仓库数据报错），就可以跑性能测试。TPC-C 用 tpmC 值（Transactions per Minute）来衡量系统最大有效吞吐量。其中 Transactions 以 NewOrder Transaction 为准，即最终衡量单位为每分钟处理的订单数。

# 性能测试
[aicenter@aicenter2 run]$ ./runBenchmark.sh props.ob
19:10:13,524 [main] INFO   jTPCC : Term-00, 
19:10:13,528 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
19:10:13,528 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0
19:10:13,528 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
19:10:13,528 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa
19:10:13,528 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier
19:10:13,531 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck
19:10:13,531 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
19:10:13,531 [main] INFO   jTPCC : Term-00, 
19:10:13,531 [main] INFO   jTPCC : Term-00, db=oracle
19:10:13,532 [main] INFO   jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
19:10:13,532 [main] INFO   jTPCC : Term-00, conn=jdbc:oceanbase://172.17.0.2:2883/tpcc?useUnicode=true&characterEncoding=utf-8
19:10:13,532 [main] INFO   jTPCC : Term-00, user=tpcc@obmysql#obce-single
19:10:13,532 [main] INFO   jTPCC : Term-00, 
19:10:13,532 [main] INFO   jTPCC : Term-00, warehouses=2
19:10:13,532 [main] INFO   jTPCC : Term-00, terminals=10
19:10:13,534 [main] INFO   jTPCC : Term-00, runMins=10
19:10:13,535 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=0
19:10:13,535 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=true
19:10:13,535 [main] INFO   jTPCC : Term-00, 
19:10:13,535 [main] INFO   jTPCC : Term-00, newOrderWeight=45
19:10:13,535 [main] INFO   jTPCC : Term-00, paymentWeight=43
19:10:13,535 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
19:10:13,535 [main] INFO   jTPCC : Term-00, deliveryWeight=4
19:10:13,535 [main] INFO   jTPCC : Term-00, stockLevelWeight=4
19:10:13,535 [main] INFO   jTPCC : Term-00, 
19:10:13,536 [main] INFO   jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
19:10:13,536 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
19:10:13,536 [main] INFO   jTPCC : Term-00, 
19:10:13,580 [main] INFO   jTPCC : Term-00, copied props.ob to my_result_2022-02-17_191013/run.properties
19:10:13,581 [main] INFO   jTPCC : Term-00, created my_result_2022-02-17_191013/data/runInfo.csv for runID 6
19:10:13,581 [main] INFO   jTPCC : Term-00, writing per transaction results to my_result_2022-02-17_191013/data/result.csv
19:10:13,582 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
19:10:13,582 [main] INFO   jTPCC : Term-00, osCollectorInterval=1
19:10:13,582 [main] INFO   jTPCC : Term-00, osCollectorSSHAddr=null
19:10:13,582 [main] INFO   jTPCC : Term-00, osCollectorDevices=null
19:10:13,830 [main] INFO   jTPCC : Term-00,
19:10:14,167 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 104
19:10:14,167 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    190                          
19:21:32,141 [Thread-5] INFO   jTPCC : Term-00, 
19:21:32,141 [Thread-5] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 7.25
19:21:32,141 [Thread-5] INFO   jTPCC : Term-00, Measured tpmTOTAL = 17.08
19:21:32,141 [Thread-5] INFO   jTPCC : Term-00, Session Start     = 2022-02-17 19:10:14
19:21:32,141 [Thread-5] INFO   jTPCC : Term-00, Session End       = 2022-02-17 19:21:32
19:21:32,141 [Thread-5] INFO   jTPCC : Term-00, Transaction Count = 192

2. 查看 OceanBase 执行计划

2.1 查询 tpcc top 5

root 用户、oceanbase库。

[aicenter@aicenter2 ~]$  mysql -h 172.17.0.2 -uroot@obmysql -P2883 -p -c -A oceanbase
......
mysql> SELECT/*+ PARALLEL(15)*/ SQL_ID, COUNT(*) AS QPS, ROUND(AVG(elapsed_time)) avg_elapsed_time, ROUND(AVG(execute_time)) avg_exec_time FROM oceanbase.gv$sql_audit  WHERE tenant_name = 'obmysql' and db_name = 'tpcc' GROUP BY sql_id ORDER BY avg_elapsed_time DESC LIMIT 5;
+----------------------------------+------+------------------+---------------+
| SQL_ID                           | QPS  | avg_elapsed_time | avg_exec_time |
+----------------------------------+------+------------------+---------------+
| F86483FB17A84B0EA8381F4FA4EBA1AA |    1 |            93311 |         50416 |
| 3B6366A4BBB3F3DD91FEE90A15DF4C2A |    1 |            71864 |         71540 |
| 2D1366676CD073B5199DF5888FB3B0D9 |    1 |            69891 |         69420 |
| A4525988E72C9537ED712A53518E5C6F |    1 |            67603 |         67321 |
| 49E1673CDABDC24B09B1E20E10F29F55 |    1 |            64279 |         63929 |
+----------------------------------+------+------------------+---------------+
5 rows in set (0.20 sec)
# 查询 sql_id 对应的 query_sql
mysql> select distinct query_sql from oceanbase.gv$sql_audit where sql_id='F86483FB17A84B0EA8381F4FA4EBA1AA';
+--------------------------------------------------------------------------------------------+
| query_sql                                                                                  |
+--------------------------------------------------------------------------------------------+
| create table bmsql_config (
cfg_name    varchar(30) primary key,
cfg_value   varchar(50)
) |
+--------------------------------------------------------------------------------------------+
1 row in set (0.71 sec)

2.2 查看执行计划

OceanBase 数据库的执行计划命令有三种模式：EXPLAIN BASIC、EXPLAIN 和 EXPLAIN EXTENDED。这三种模式对执行计划展现不同粒度的细节信息:

EXPLAIN BASIC 命令用于最基本的计划展示。
EXPLAIN EXTENDED 命令用于最详细的计划展示（通常在排查问题时使用这种展示模式）。
EXPLAIN 命令所展示的信息可以帮助普通用户了解整个计划的执行方式。

[aicenter@aicenter2 ~]$ mysql -h 172.17.0.2 -utpcc@obmysql -P2883 -p -c -A tpcc
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 262163
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> explain SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance FROM bmsql_customer WHERE c_w_id = 2 AND c_d_id = 1 AND c_id = 1;
+-----------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                      |
+-----------------------------------------------------------------------------------------------------------------+
| ===================================================================
|ID|OPERATOR  |NAME                               |EST. ROWS|COST |
-------------------------------------------------------------------
|0 |TABLE SCAN|bmsql_customer(bmsql_customer_idx1)|30       |21167|
===================================================================

Outputs & filters: 
-------------------------------------
  0 - output([bmsql_customer.c_first], [bmsql_customer.c_middle], [bmsql_customer.c_last], [bmsql_customer.c_street_1], [bmsql_customer.c_street_2], [bmsql_customer.c_city], [bmsql_customer.c_state], [bmsql_customer.c_zip], [bmsql_customer.c_phone], [bmsql_customer.c_since], [bmsql_customer.c_credit], [bmsql_customer.c_credit_lim], [bmsql_customer.c_discount], [bmsql_customer.c_balance]), filter([bmsql_customer.c_id = 1]), 
      access([bmsql_customer.c_id], [bmsql_customer.c_first], [bmsql_customer.c_middle], [bmsql_customer.c_last], [bmsql_customer.c_street_1], [bmsql_customer.c_street_2], [bmsql_customer.c_city], [bmsql_customer.c_state], [bmsql_customer.c_zip], [bmsql_customer.c_phone], [bmsql_customer.c_since], [bmsql_customer.c_credit], [bmsql_customer.c_credit_lim], [bmsql_customer.c_discount], [bmsql_customer.c_balance]), partitions(p0)
 |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (1.31 sec)

# 最详细的计划
mysql> explain extended SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance FROM bmsql_customer WHERE c_w_id = 2 AND c_d_id = 1 AND c_id = 1;
+-----------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------+
| ===================================================================
|ID|OPERATOR  |NAME                               |EST. ROWS|COST |
-------------------------------------------------------------------
|0 |TABLE SCAN|bmsql_customer(bmsql_customer_idx1)|30       |21167|
===================================================================

Outputs & filters: 
-------------------------------------
  0 - output([bmsql_customer.c_first(0x7f9705828790)], [bmsql_customer.c_middle(0x7f9701f50ab0)], [bmsql_customer.c_last(0x7f9701f50fd0)], [bmsql_customer.c_street_1(0x7f9701f514f0)], [bmsql_customer.c_street_2(0x7f9701f51a10)], [bmsql_customer.c_city(0x7f9701f51f30)], [bmsql_customer.c_state(0x7f9701f52450)], [bmsql_customer.c_zip(0x7f9701f52af0)], [bmsql_customer.c_phone(0x7f9701f53010)], [bmsql_customer.c_since(0x7f9701f53530)], [bmsql_customer.c_credit(0x7f9701f53a50)], [bmsql_customer.c_credit_lim(0x7f9701f53f70)], [bmsql_customer.c_discount(0x7f9701f54490)], [bmsql_customer.c_balance(0x7f9701f54b30)]), filter([bmsql_customer.c_id(0x7f9705828270) = 1(0x7f9705827bf0)]), 
      access([bmsql_customer.c_id(0x7f9705828270)], [bmsql_customer.c_first(0x7f9705828790)], [bmsql_customer.c_middle(0x7f9701f50ab0)], [bmsql_customer.c_last(0x7f9701f50fd0)], [bmsql_customer.c_street_1(0x7f9701f514f0)], [bmsql_customer.c_street_2(0x7f9701f51a10)], [bmsql_customer.c_city(0x7f9701f51f30)], [bmsql_customer.c_state(0x7f9701f52450)], [bmsql_customer.c_zip(0x7f9701f52af0)], [bmsql_customer.c_phone(0x7f9701f53010)], [bmsql_customer.c_since(0x7f9701f53530)], [bmsql_customer.c_credit(0x7f9701f53a50)], [bmsql_customer.c_credit_lim(0x7f9701f53f70)], [bmsql_customer.c_discount(0x7f9701f54490)], [bmsql_customer.c_balance(0x7f9701f54b30)]), partitions(p0), 
      is_index_back=true, filter_before_indexback[false], 
      range_key([bmsql_customer.c_w_id(0x7f9701f488e0)], [bmsql_customer.c_d_id(0x7f9705827490)], [bmsql_customer.c_last(0x7f9701f50fd0)], [bmsql_customer.c_first(0x7f9705828790)], [bmsql_customer.__pk_increment(0x7f9701f56450)]), range(2,1,MIN,MIN,MIN ; 2,1,MAX,MAX,MAX), 
      range_cond([bmsql_customer.c_w_id(0x7f9701f488e0) = 2(0x7f9701f48260)], [bmsql_customer.c_d_id(0x7f9705827490) = 1(0x7f9705826e10)])

Used Hint:
-------------------------------------
  /*+
  */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "tpcc.bmsql_customer"@"SEL$1" "bmsql_customer_idx1")
      END_OUTLINE_DATA
  */

Plan Type:
-------------------------------------
LOCAL

Optimization Info:
-------------------------------------

bmsql_customer:table_rows:8921, physical_range_rows:2991, logical_range_rows:2991, index_back_rows:2991, output_rows:29, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[bmsql_customer,bmsql_customer_idx1], estimation info[table_id:1100611139453789, (table_type:1, version:0-1645096193814241-1645096193814241, logical_rc:2991, physical_rc:2991), (table_type:7, version:1645096068457973-1645107181674233-1645107183407421, logical_rc:0, physical_rc:0), (table_type:7, version:1645107183407421-1645134378804137-1645134378804137, logical_rc:0, physical_rc:0), (table_type:5, version:1645107183407421-1645134378804137-1645134378804137, logical_rc:0, physical_rc:0), (table_type:0, version:1645134378804137-1645134378804137-9223372036854775807, logical_rc:0, physical_rc:0)]
Parameters
-------------------------------------
 |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3. OceanBase Outline

通过对某条 SQL 创建 Outline 可实现计划绑定。

在系统上线前，可以直接在 SQL 语句中添加 Hint，控制优化器按 Hint 指定的行为进行计划生成。

但对于已上线的业务，如果出现优化器选择的计划不够优化时，则需要在线进行计划绑定，即无需业务进行 SQL 更改，而是通过 DDL 操作将一组 Hint 加入到 SQL 中，从而使优化器根据指定的一组 Hint，对该 SQL 生成更优计划。该组 Hint 称为 Outline。

3.1 创建 Outline

OceanBase 数据库支持通过两种方式创建 Outline：

一种是通过 SQL_TEXT (用户执行的带参数的原始语句)。
另一种是通过 SQL_ID 创建。

注意 : 创建 Outline 需要进入对应的数据库下执行。

使用 SQL_TEXT 创建 Outline

使用 SQL_TEXT 创建 Outline 后，会生成一个 Key-Value 对存储在 Map 中，其中 Key 为绑定的 SQL 参数化后的文本，Value 为绑定的 Hint。

使用 SQL_TEXT 创建 Outline 的语法如下：

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

说明如下：

指定 OR REPLACE 后，可以对已经存在执行计划进行替换。
其中 stmt 一般为一个带有 Hint 和原始参数的 DML 语句。
如果不指定 TO target_stmt，则表示如果数据库接受的 SQL 参数化后与 stmt 去掉 Hint 参数化文本相同，则将该 SQL 绑定 stmt 中 Hint 生成执行计划。

如果期望对含有 Hint 的语句执行固定计划，则需要 TO target_stmt 来指明原始的 SQL。

示例：

[aicenter@aicenter2 ~]$ mysql -h 172.17.0.2 -uobuser@obmysql -P2883 -p -Dtest
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX idx_c2(c2));
Query OK, 0 rows affected (0.23 sec)

mysql> INSERT INTO t1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

# 优化器选择了走主键扫描，如果数据量增大，如果执行索引 idx_c2，该 SQL 会更优化。
# 此时可以通过创建 Outline 将该 SQL 绑定索引计划并执行。
mysql> EXPLAIN SELECT * FROM t1 WHERE c2 = 1\G;
*************************** 1. row ***************************
Query Plan: ===================================
|ID|OPERATOR  |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1  |1        |37  |
===================================

Outputs & filters: 
-------------------------------------
  0 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 = 1]), 
      access([t1.c2], [t1.c1], [t1.c3]), partitions(p0)

1 row in set (0.00 sec)

# 创建 Outline
mysql> CREATE OUTLINE otl_idx_c2 ON SELECT/*+ INDEX(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1;
Query OK, 0 rows affected (1.77 sec)

使用 SQL_ID 创建 Outline

使用 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 获取。
通过参数化的原始 SQL，使用 MD5 生成 SQL_ID 。

查询 sql_id 示例:

# 查询 sql_id
[aicenter@aicenter2 ~]$ mysql -h 172.17.0.2 -uroot@obmysql -P2883 -p -c -A oceanbase
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from gv$sql where SQL_TEXT  like 'SELECT * FROM t1 WHERE c2 = %' \G;
*************************** 1. row ***************************
               CON_ID: 1001
               SVR_IP: 127.0.0.1
             SVR_PORT: 2882
              PLAN_ID: 214
               SQL_ID: ED570339F2C856BA96008A29EDF04C74
                 TYPE: 1
             SQL_TEXT: SELECT * FROM t1 WHERE c2 = ?
      PLAN_HASH_VALUE: 8480732102453075278
      FIRST_LOAD_TIME: 2022-02-18 14:23:36.158469
     LAST_ACTIVE_TIME: 2022-02-18 14:23:36.184028
         AVG_EXE_USEC: 710788
     SLOWEST_EXE_TIME: 2022-02-18 14:23:36.184028
     SLOWEST_EXE_USEC: 710788
           SLOW_COUNT: 0
            HIT_COUNT: 0
            PLAN_SIZE: 48816
           EXECUTIONS: 1
           DISK_READS: 5
        DIRECT_WRITES: 0
          BUFFER_GETS: 22
APPLICATION_WAIT_TIME: 0
CONCURRENCY_WAIT_TIME: 0
    USER_IO_WAIT_TIME: 95072
       ROWS_PROCESSED: 1
         ELAPSED_TIME: 710788
             CPU_TIME: 615652
1 row in set (0.01 sec)
# 或者 
mysql> select * from gv$plan_cache_plan_stat where query_sql like 'SELECT * FROM t1 WHERE c2 = %' \G;
*************************** 1. row ***************************
            tenant_id: 1001
               svr_ip: 127.0.0.1
             svr_port: 2882
              plan_id: 214
               sql_id: ED570339F2C856BA96008A29EDF04C74
                 type: 1
    is_bind_sensitive: 0
        is_bind_aware: 0
                db_id: 18446744073709551615
            statement: SELECT * FROM t1 WHERE c2 = ?
            query_sql: SELECT * FROM t1 WHERE c2 = 1
       special_params: 
          param_infos: {1,0,0,0,5}
             sys_vars: 45,4194304,2,4,1,0,0,32,3,1,0,1,1,0,10485760,1,1,0,1,BINARY,BINARY,AL32UTF8,AL32UTF8,BYTE,FALSE,1,100,64,200,0,13,NULL,1,1,1
            plan_hash: 8480732102453075278
      first_load_time: 2022-02-18 14:23:36.158469
       schema_version: 1645155294956104
       merged_version: 2
     last_active_time: 2022-02-18 14:23:36.184028
         avg_exe_usec: 710788
     slowest_exe_time: 2022-02-18 14:23:36.184028
     slowest_exe_usec: 710788
           slow_count: 0
            hit_count: 0
            plan_size: 48816
           executions: 1
           disk_reads: 5
        direct_writes: 0
          buffer_gets: 22
application_wait_time: 0
concurrency_wait_time: 0
    user_io_wait_time: 95072
       rows_processed: 1
         elapsed_time: 710788
             cpu_time: 615652
         large_querys: 0
 delayed_large_querys: 0
    delayed_px_querys: 0
      outline_version: 1645155294842920
           outline_id: 1100611139404777
         outline_data: /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2") END_OUTLINE_DATA*/
         acs_sel_info: 
           table_scan: 0
            evolution: 0
       evo_executions: 0
         evo_cpu_time: 0
        timeout_count: 0
           ps_stmt_id: -1
               sessid: 0
          temp_tables: 
           is_use_jit: 0
          object_type: SQL_PLAN
           hints_info:  INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2")
     hints_all_worked: 1
         pl_schema_id: NULL
is_batched_multi_stmt: 0
1 row in set (0.02 sec)

mysql> exit
Bye

使用 SQL_ID 绑定 Outline，如下例所示：

[aicenter@aicenter2 ~]$ mysql -h 172.17.0.2 -uobuser@obmysql -P2883 -p -Dtest
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 262165
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

# 限制并发为 1 
mysql> CREATE OUTLINE otl2_idx_c2 ON "ED570339F2C856BA96008A29EDF04C74" USING HINT /*+ parallel(1)*/ ;
Query OK, 0 rows affected (0.04 sec)

Outline Data 是优化器为了完全复现某一计划而生成的一组 Hint 信息，以 BEGIN_OUTLINE_DATA 开始，并以 END_OUTLINE_DATA 结束。

Outline Data 可以通过 EXPLAIN EXTENDED 命令获得，如下例所示：

mysql> EXPLAIN EXTENDED SELECT/*+ index(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1\G;
*************************** 1. row ***************************
Query Plan: =========================================
|ID|OPERATOR  |NAME      |EST. ROWS|COST|
-----------------------------------------
|0 |TABLE SCAN|t1(idx_c2)|1        |88  |
=========================================

Outputs & filters: 
-------------------------------------
  0 - output([t1.c1(0x7f96cbc98f70)], [t1.c2(0x7f96cbc98ce0)], [t1.c3(0x7f96cbc99200)]), filter(nil), 
      access([t1.c2(0x7f96cbc98ce0)], [t1.c1(0x7f96cbc98f70)], [t1.c3(0x7f96cbc99200)]), partitions(p0), 
      is_index_back=true, 
      range_key([t1.c2(0x7f96cbc98ce0)], [t1.c1(0x7f96cbc98f70)]), range(1,MIN ; 1,MAX), 
      range_cond([t1.c2(0x7f96cbc98ce0) = 1(0x7f96cbc98660)])

Used Hint:
-------------------------------------
  /*+
      INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2")
  */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2")
      END_OUTLINE_DATA
  */

Plan Type:
-------------------------------------
LOCAL

Optimization Info:
-------------------------------------

t1:table_rows:3, physical_range_rows:1, logical_range_rows:1, index_back_rows:1, output_rows:1, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_c2], pruned_index_name[t1], estimation info[table_id:1100611139453792, (table_type:1, version:0-1645161295182407-1645161295182407, logical_rc:1, physical_rc:1), (table_type:7, version:1645161285065011-1645161295182407-1645161316877637, logical_rc:0, physical_rc:0), (table_type:5, version:1645161285065011-1645161295182407-1645161316877637, logical_rc:0, physical_rc:0), (table_type:0, version:1645161316877637-1645161316877637-9223372036854775807, logical_rc:0, physical_rc:0)]
Parameters
-------------------------------------

1 row in set (0.00 sec)

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

mysql> CREATE OUTLINE otl3_idx_c2 ON "ED570339F2C856BA96008A29EDF04C74" USING HINT 
    -> /*+
    ->       BEGIN_OUTLINE_DATA
    ->       INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2")
    ->       END_OUTLINE_DATA
    ->   */
    -> ;
ERROR 5264 (HY000): Outline '' already exists
mysql>

3.2 确定 Outline 创建生效

确定创建的 Outline 是否成功且符合预期，需要进行如下三步的验证：

确定 Outline 创建成功。

通过查看 gv$outline 视图，确认是否成功创建对应名称的 Outline。

mysql> SELECT * FROM oceanbase.gv$outline WHERE OUTLINE_NAME = 'otl_idx_c2'\G;
*************************** 1. row ***************************
        tenant_id: 1001
      database_id: 1100611139404776
       outline_id: 1100611139404777
    database_name: test
     outline_name: otl_idx_c2
visible_signature: SELECT * FROM t1 WHERE c2 = ?
         sql_text: SELECT/*+ INDEX(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1
   outline_target: 
      outline_sql: SELECT /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2") END_OUTLINE_DATA*/* FROM t1 WHERE c2 = 1

确定新的 SQL 是否通过绑定的 Outline 生成了新执行计划。

当绑定 Outline 的 SQL 执行新的查询后，查询 gv$plan_cache_plan_stat 表中该 SQL 对应的计划信息中的 outline_id。如果 outline_id 与在 gv$outline 中查到的 outline_id 相同，则表示是按绑定的 Outline 生成的执行计划，否则不是。

mysql> SELECT SQL_ID, PLAN_ID, STATEMENT, OUTLINE_ID, OUTLINE_DATA FROM oceanbase.gv$plan_cache_plan_stat WHERE STATEMENT LIKE '%SELECT * FROM t1 WHERE c2 =%'\G
*************************** 1. row ***************************
      SQL_ID: ED570339F2C856BA96008A29EDF04C74
     PLAN_ID: 214
   STATEMENT: SELECT * FROM t1 WHERE c2 = ?
  OUTLINE_ID: 1100611139404777
OUTLINE_DATA: /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2") END_OUTLINE_DATA*/

# 查询gv$outline
mysql> select * from gv$outline where outline_name = 'otl_idx_c2' \G;
*************************** 1. row ***************************
        tenant_id: 1001
      database_id: 1100611139404776
       outline_id: 1100611139404777
    database_name: test
     outline_name: otl_idx_c2
visible_signature: SELECT * FROM t1 WHERE c2 = ?
         sql_text: SELECT/*+ INDEX(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1
   outline_target: 
      outline_sql: SELECT /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2") END_OUTLINE_DATA*/* FROM t1 WHERE c2 = 1
      
# out line 未生效
# CREATE OUTLINE otl2_idx_c2 ON "ED570339F2C856BA96008A29EDF04C74" USING HINT /*+ parallel(1)*/ ;
mysql> select * from gv$outline where outline_name = 'otl2_idx_c2' \G;
*************************** 1. row ***************************
        tenant_id: 1001
      database_id: 1100611139404776
       outline_id: 1100611139404778
    database_name: test
     outline_name: otl2_idx_c2
visible_signature: 
         sql_text: 
   outline_target: 
      outline_sql: 
1 row in set (0.01 sec)

# 使用SQL语句创建outline
mysql> CREATE OUTLINE otl3_idx_c2 ON SELECT /*+ parallel(1)*/ * FROM t1 WHERE c2 = 1;
Query OK, 0 rows affected (0.06 sec)

# 查看是否生效
mysql> select * from gv$outline where outline_name = 'otl3_idx_c2' \G;
*************************** 1. row ***************************
        tenant_id: 1001
      database_id: 1100611139404776
       outline_id: 1100611139404779
    database_name: test
     outline_name: otl3_idx_c2
visible_signature: SELECT  * FROM t1 WHERE c2 = ?
         sql_text: SELECT /*+ parallel(1)*/ * FROM t1 WHERE c2 = 1
   outline_target: 
      outline_sql: SELECT /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "test.t1"@"SEL$1") PARALLEL(1) END_OUTLINE_DATA*/ * FROM t1 WHERE c2 = 1
1 row in set (0.00 sec)

确定生成的执行计划是否符合预期。

确定是通过绑定的 Outline 生成的计划后，需要确定生成的计划是否符合预期，可以通过查询 gv$plan_cache_plan_stat 表查看 plan_cache 中缓存的执行计划形状。

3.3 删除 Outline

删除 Outline 后，对应 SQL 将不再依据所绑定的 Outline 重新生成执行计划。

删除 Outline 的语法如下：

DROP OUTLINE outline_name;

删除前面创建的 outline：

mysql> DROP OUTLINE otl_idx_c2;
Query OK, 0 rows affected (0.06 sec)

mysql> DROP OUTLINE otl2_idx_c2;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP OUTLINE otl3_idx_c2;
Query OK, 0 rows affected (0.02 sec)

查看 oceanbase.gv$outline，确认是否删除outline：

mysql> SELECT * FROM oceanbase.gv$outline;
Empty set (0.00 sec)

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

ddttoop

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

实践练习六（必选）：OceanBase TPC-C 测试和查看 执行计划

实践练习六（必选）：OceanBase TPC-C 测试和查看 执行计划

练习目的

练习条件

练习内容

1. 使用 BenmarkSQL 运行 TPC-C

1.1 BenchmarkSQL 下载

1.2 BenchmarkSQL 准备

1.3 创建测试库及用户

1.4 配置文件 props.ob

1.5 下载 OceanBase 驱动文件

1.6 准备数据

1.7 性能测试

2. 查看 OceanBase 执行计划

2.1 查询 tpcc top 5

2.2 查看执行计划

3. OceanBase Outline

3.1 创建 Outline

3.2 确定 Outline 创建生效

3.3 删除 Outline

实践练习六（必选）：OceanBase TPC-C 测试和查看执行计划

实践练习六（必选）：OceanBase TPC-C 测试和查看执行计划

1.4 配置文件 `props.ob`