查看 OceanBase 执行计划

本次练习是必选练习之一。

练习目的

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

练习条件

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

练习内容

请记录并分享下列内容：

（必选）1、使用 BenmarkSQL 运行 TPC-C ，并发数不用很高，5~10 并发即可（根据机器资源）。

（必选）2、分析 TPC-C TOP SQL，并查看 3条 SQL 的 解析执行计划 和 实际执行计划。

1、环境准备
下载地址： https://github.com/obpilot/benchmarksql-5.0
jdk环境使用前面配置

数据库环境使用

修改配置文件 props.ob (在run目录下)

db=oracle
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://192.168.153.131:2883/test?useUnicode=true&characterEncoding=utf-8
user=test@test#obdemo
password=test_test

warehouses=10
loadWorkers=10

terminals=100
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=15
//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

2. 准备数据
   [admin@oceanbase01]$ unzip ~/benchmarksql-5.0.zip
   sql 文件在 benchmarksql-5.0-master/run/sql.oceanbase 中，看了一些建表语句，使用了 varchar2 类型，ob为 mysql 模式，需要修改：

cp tableCreates.sql tableCreates_1.sql
sed -i ‘s/varchar2/varchar/g’ tableCreates_1.sql

3.建表
./runSQL.sh props.ob sql.oceanbase/tableCreates_1.sql

4.加载数据
./runLoader.sh props.ob

5.检查数据

[admin@oceanbase01 run]$ obclient -h192.168.153.131 -uroot@obmysql#obdemo -P2883 -c -A test -p
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)
Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MySQL [test]> show tables;
±-----------------+
| Tables_in_test |
±-----------------+
| bmsql_config   |
| bmsql_customer |
| bmsql_district |
| bmsql_history  |
| bmsql_item     |
| bmsql_new_order |
| bmsql_oorder   |
| bmsql_order_line |
| bmsql_stock    |
| bmsql_warehouse |
±-----------------+
10 rows in set (0.001 sec)

MySQL [test]> select count(*) from bmsql_config;
±---------+
| count() |
±---------+
| 4 |
±---------+
1 row in set (0.002 sec)

MySQL [test]> select count(*) from bmsql_customer;
±---------+
| count() |
±---------+
| 60000 |
±---------+
1 row in set (0.206 sec)

MySQL [test]> select count(*) from bmsql_customer ;
±---------+
| count() |
±---------+
| 60000 |
±---------+
1 row in set (0.163 sec)

MySQL [test]> select count(*) from bmsql_history ;
±---------+
| count() |
±---------+
| 60000 |
±---------+
1 row in set (0.057 sec)

MySQL [test]> select count(*) from bmsql_item ;
±---------+
| count() |
±---------+
| 100000 |
±---------+
1 row in set (0.084 sec)

MySQL [test]> select count(*) from bmsql_oorder ;
±---------+
| count() |
±---------+
| 60000 |
±---------+
1 row in set (0.047 sec)

MySQL [test]> select count(*) from bmsql_order_line;
±---------+
| count() |
±---------+
| 601529 |
±---------+
1 row in set (0.461 sec)

MySQL [test]> select count(*) from bmsql_stock ;
±---------+
| count() |
±---------+
| 200000 |
±---------+
1 row in set (0.680 sec)
复制

6.增加索引

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;
复制

7.执行测试
4.3 执行性能测试

sh runBenchmark.sh props.ob

查看执行计划

查看top10 sql

SELECT /*+ PARALLEL(15) */ avg_exe_usec,tenant_id, svr_ip, svr_port, sql_id, plan_id FROM oceanbase.gv$plan_cache_plan_stat WHERE tenant_id=1001 ORDER BY avg_exe_usec DESC LIMIT 10 ;
复制

根据topsql的sql_id获取sql语句

select query_sql from gv$plan_cache_plan_stat where sql_id=‘F59A700FA168324279B0DBC25E19760F’;
复制

使用gv$plan_cache_plan_explain查看sql实际执行计划：

select plan_line_id,operator,name,rows,cost from oceanbase.gv$plan_cache_plan_explain where ip =‘172.17.0.13’ and port=‘2882’ and tenant_id=‘1001’ and plan_id=‘77’;
复制

使用explain查看解析执行计划：

explain SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 FROM bmsql_stock WHERE s_w_id = 2 AND s_i_id = 25350 FOR UPDATE;
复制

再查看一条top3 sql的执行计划：

MySQL [tpcc]>select plan_line_id,operator,name,rows,cost from oceanbase.gv$plan_cache_plan_explain where ip =‘172.17.0.13’ and port=‘2882’ and tenant_id=‘1001’ and plan_id=‘79’;
复制

使用explain查询：

MySQL [tpcc]> explain SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 2 AND s_quantity < 10 AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 2 AND d_id = 3 ) );
复制