练习目的
本次练习目的掌握 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.2. 参考资料
社区版官网-文档-学习中心-入门教程：OceanBase 入门到实战教程
社区版官网-博客-入门实战：开源博客
下载benchmarksql-5.0，安装ant工具，用ant编译benchmarksql-5.0
1.准备benchmarksql
编辑props.ob配置文件
导入oceanbase-client-1.1.10.jar
2.数据准备
按非分区表建表
sh runSQL.sh props.ob sql.common/tableCreates.sql
调整事务超时时间
set global ob_timestamp_service='GTS' ;
set global autocommit=ON;
set global ob_query_timeout=36000000000;
set global ob_trx_timeout=36000000000;
set global max_allowed_packet=67108864;
set global ob_sql_work_area_percentage=100;
set global parallel_max_servers=800;
set global parallel_servers_target=800;
导入数据
sh runLoader.sh props.ob
创建两个索引
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;
3.运行 TPC-C 测试
sh runBenchmark.sh props.ob
4.查看TOP SQL
SELECT/*+ PARALLEL(15)*/ SQL_ID, COUNT(*) AS QPS, AVG(t1.elapsed_time) RT
FROM oceanbase.gv$sql_audit t1
WHERE user_name='root'
GROUP BY t1.sql_id ORDER BY RT DESC LIMIT 10;
查看执行计划
获取第一条sql的文本
select distinct query_sql from gv$sql_audit where sql_id='7229213613983BC5FDA15AD11EC70D01';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| query_sql |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 = 1 AND s_i_id = 97744 FOR UPDATE |
| 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 = 10652 FOR UPDATE |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.068 sec)
实际执行计划
SELECT ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`gv$plan_cache_plan_explain`
where tenant_id=1001 AND ip = '127.0.0.1' AND port=2882 AND plan_id=40;
*************************** 1. row ***************************
ip: 127.0.0.1
plan_depth: 0
plan_line_id: 0
operator: PHY_TABLE_SCAN
name: bmsql_stock
rows: 9
cost: 248249
property: table_rows:86530, physical_range_rows:200159, logical_range_rows:86530, index_back_rows:0, output_rows:8, est_method:local_storage, avaiable_index_name[bmsql_stock], estimation info[table_id:1100611139453798, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643245354224215-1643245354224215, logical_rc:0, physical_rc:96654), (table_type:0, version:1643245354224215-1643245354224215-9223372036854775807, logical_rc:86530, physical_rc:103505)]
1 row in set (0.013 sec)
ERROR: No query specified
解释执行计划
*************************** 1. row ***************************
Query Plan: ============================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
--------------------------------------------
|0 |TABLE SCAN|bmsql_stock|9 |248250|
============================================
Outputs & filters:
-------------------------------------
0 - output([bmsql_stock.s_quantity], [bmsql_stock.s_data], [bmsql_stock.s_dist_01], [bmsql_stock.s_dist_02], [bmsql_stock.s_dist_03], [bmsql_stock.s_dist_04], [bmsql_stock.s_dist_05], [bmsql_stock.s_dist_06], [bmsql_stock.s_dist_07], [bmsql_stock.s_dist_08], [bmsql_stock.s_dist_09], [bmsql_stock.s_dist_10]), filter([bmsql_stock.s_w_id = 2], [bmsql_stock.s_i_id = 10652]),
access([bmsql_stock.s_w_id], [bmsql_stock.s_i_id], [bmsql_stock.s_quantity], [bmsql_stock.s_data], [bmsql_stock.s_dist_01], [bmsql_stock.s_dist_02], [bmsql_stock.s_dist_03], [bmsql_stock.s_dist_04], [bmsql_stock.s_dist_05], [bmsql_stock.s_dist_06], [bmsql_stock.s_dist_07], [bmsql_stock.s_dist_08], [bmsql_stock.s_dist_09], [bmsql_stock.s_dist_10]), partitions(p0)
1 row in set (0.016 sec)
ERROR: No query specified
获取第二条语句的文本
MySQL [oceanbase]> select query_sql from gv$sql_audit where sql_id='F59A700FA168324279B0DBC25E19760F';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| query_sql |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 < 13 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 = 8 ) ) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.069 sec)
实际执行计划
MySQL [oceanbase]> SELECT ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`gv$plan_cache_plan_explain` where tenant_id=1001 AND ip = '127.0.0.1' AND port=2882 AND plan_id=42\G;
*************************** 1. row ***************************
ip: 127.0.0.1
plan_depth: 0
plan_line_id: 0
operator: PHY_SCALAR_AGGREGATE
name: NULL
rows: 1
cost: 811804
property: NULL
*************************** 2. row ***************************
ip: 127.0.0.1
plan_depth: 1
plan_line_id: 1
operator: PHY_HASH_JOIN
name: NULL
rows: 2
cost: 811804
property: NULL
*************************** 3. row ***************************
ip: 127.0.0.1
plan_depth: 2
plan_line_id: 2
operator: PHY_SUBPLAN_SCAN
name: NULL
rows: 1
cost: 612151
property: NULL
*************************** 4. row ***************************
ip: 127.0.0.1
plan_depth: 3
plan_line_id: 3
operator: PHY_NESTED_LOOP_JOIN
name: NULL
rows: 1
cost: 612151
property: NULL
*************************** 5. row ***************************
ip: 127.0.0.1
plan_depth: 4
plan_line_id: 4
operator: PHY_TABLE_SCAN
name: bmsql_order_line
rows: 40
cost: 612101
property: table_rows:404006, physical_range_rows:600330, logical_range_rows:404006, index_back_rows:0, output_rows:39, est_method:local_storage, avaiable_index_name[bmsql_order_line], estimation info[table_id:1100611139453797, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643245354224215-1643245354224215, logical_rc:0, physical_rc:173262), (table_type:0, version:1643245354224215-1643245354224215-9223372036854775807, logical_rc:404006, physical_rc:427068)]
*************************** 6. row ***************************
ip: 127.0.0.1
plan_depth: 4
plan_line_id: 5
operator: PHY_MATERIAL
name: NULL
rows: 1
cost: 50
property: NULL
*************************** 7. row ***************************
ip: 127.0.0.1
plan_depth: 5
plan_line_id: 6
operator: PHY_TABLE_SCAN
name: bmsql_district
rows: 1
cost: 50
property: table_rows:20, physical_range_rows:20, logical_range_rows:20, index_back_rows:0, output_rows:0, est_method:local_storage, avaiable_index_name[bmsql_district], estimation info[table_id:1100611139453792, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643245354224215-1643245354224215, logical_rc:0, physical_rc:0), (table_type:0, version:1643245354224215-1643245354224215-9223372036854775807, logical_rc:20, physical_rc:20)]
*************************** 8. row ***************************
ip: 127.0.0.1
plan_depth: 2
plan_line_id: 7
operator: PHY_TABLE_SCAN
name: bmsql_stock
rows: 91
cost: 199621
property: table_rows:91148, physical_range_rows:200114, logical_range_rows:91148, index_back_rows:0, output_rows:90, est_method:local_storage, avaiable_index_name[bmsql_stock], estimation info[table_id:1100611139453798, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643245354224215-1643245354224215, logical_rc:0, physical_rc:96654), (table_type:0, version:1643245354224215-1643245354224215-9223372036854775807, logical_rc:91148, physical_rc:103460)]
8 rows in set (0.005 sec)
解释执行计划
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 < 13 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 = 8))\G;
MySQL [tpccdb]> 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 < 13 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 = 8))\G;
*************************** 1. row ***************************
Query Plan: ============================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
------------------------------------------------------------
|0 |SCALAR GROUP BY | |1 |812132|
|1 | HASH RIGHT SEMI JOIN| |2 |812131|
|2 | SUBPLAN SCAN |VIEW1 |1 |612420|
|3 | NESTED-LOOP JOIN | |1 |612419|
|4 | TABLE SCAN |bmsql_order_line|37 |612369|
|5 | MATERIAL | |1 |51 |
|6 | TABLE SCAN |bmsql_district |1 |51 |
|7 | TABLE SCAN |bmsql_stock |86 |199683|
============================================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_COUNT(*)]), filter(nil),
group(nil), agg_func([T_FUN_COUNT(*)])
1 - output([1]), filter(nil),
equal_conds([bmsql_stock.s_i_id = VIEW1.ol_i_id]), other_conds(nil)
2 - output([VIEW1.ol_i_id]), filter(nil),
access([VIEW1.ol_i_id])
3 - output([bmsql_order_line.ol_i_id]), filter(nil),
conds([bmsql_order_line.ol_o_id >= bmsql_district.d_next_o_id - 20], [bmsql_order_line.ol_o_id < bmsql_district.d_next_o_id]), nl_params_(nil)
4 - output([bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), filter([bmsql_order_line.ol_w_id = 2], [bmsql_order_line.ol_d_id = 8]),
access([bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id], [bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), partitions(p0)
5 - output([bmsql_district.d_next_o_id]), filter(nil)
6 - output([bmsql_district.d_next_o_id]), filter([bmsql_district.d_w_id = 2], [bmsql_district.d_id = 8], [bmsql_district.d_next_o_id > bmsql_district.d_next_o_id - 20]),
access([bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_next_o_id]), partitions(p0)
7 - output([bmsql_stock.s_i_id]), filter([bmsql_stock.s_w_id = 2], [bmsql_stock.s_quantity < 13]),
access([bmsql_stock.s_w_id], [bmsql_stock.s_quantity], [bmsql_stock.s_i_id]), partitions(p0)
1 row in set (0.016 sec)
ERROR: No query specified
获取第三条语句的文本
MySQL [oceanbase]> select query_sql from gv$sql_audit where sql_id='5984364296F35BE1B71CD5622426385A';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| query_sql |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_delivery_d FROM bmsql_order_line WHERE ol_w_id = 2 AND ol_d_id = 4 AND ol_o_id = 1182 ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.060 sec)
实际执行计划
SELECT ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`gv$plan_cache_plan_explain`
where tenant_id=1001 AND ip = '127.0.0.1' AND port=2882 AND plan_id=27;
*************************** 1. row ***************************
ip: 127.0.0.1
plan_depth: 0
plan_line_id: 0
operator: PHY_SORT
name: NULL
rows: 1
cost: 778838
property: NULL
*************************** 2. row ***************************
ip: 127.0.0.1
plan_depth: 1
plan_line_id: 1
operator: PHY_TABLE_SCAN
name: bmsql_order_line
rows: 1
cost: 778837
property: table_rows:404006, physical_range_rows:600330, logical_range_rows:404006, index_back_rows:0, output_rows:0, est_method:local_storage, avaiable_index_name[bmsql_order_line], estimation info[table_id:1100611139453797, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643245354224215-1643245354224215, logical_rc:0, physical_rc:173262), (table_type:0, version:1643245354224215-1643245354224215-9223372036854775807, logical_rc:404006, physical_rc:427068)]
2 rows in set (0.014 sec)
解释执行计划
explain SELECT ol_i_id, ol_supply_w_id, ol_quantity,
ol_amount, ol_delivery_d
FROM bmsql_order_line WHERE ol_w_id = 2 AND ol_d_id = 4 AND ol_o_id = 1182
ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number\G;
MySQL [tpccdb]> explain SELECT ol_i_id, ol_supply_w_id, ol_quantity,
-> ol_amount, ol_delivery_d
-> FROM bmsql_order_line WHERE ol_w_id = 2 AND ol_d_id = 4 AND ol_o_id = 1182
-> ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number\G;
*************************** 1. row ***************************
Query Plan: ==================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
--------------------------------------------------
|0 |SORT | |1 |779182|
|1 | TABLE SCAN|bmsql_order_line|1 |779181|
==================================================
Outputs & filters:
-------------------------------------
0 - output([bmsql_order_line.ol_i_id], [bmsql_order_line.ol_supply_w_id], [bmsql_order_line.ol_quantity], [bmsql_order_line.ol_amount], [bmsql_order_line.ol_delivery_d]), filter(nil), sort_keys([bmsql_order_line.ol_number, ASC])
1 - output([bmsql_order_line.ol_i_id], [bmsql_order_line.ol_supply_w_id], [bmsql_order_line.ol_quantity], [bmsql_order_line.ol_amount], [bmsql_order_line.ol_delivery_d], [bmsql_order_line.ol_number]), filter([bmsql_order_line.ol_w_id = 2], [bmsql_order_line.ol_d_id = 4], [bmsql_order_line.ol_o_id = 1182]),
access([bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id], [bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id], [bmsql_order_line.ol_supply_w_id], [bmsql_order_line.ol_quantity], [bmsql_order_line.ol_amount], [bmsql_order_line.ol_delivery_d], [bmsql_order_line.ol_number]), partitions(p0)
1 row in set (0.007 sec)
ERROR: No query specified
