实践练习六：查看 OceanBase 执行计划

实践练习六：查看 OceanBase 执行计划

---

实验目标：使用benchmark导入数据并进行压测，查看top3sql的执行计划

1.benchmark安装并压测，查看ob执行计划

---

首先进行benchmark的安装，参考网上的下载地址：https://github.com/obpilot/benchmarksql-5.0.git

unzip benchmarksql-5.0-master.zip

# 修改配置文件中的连接串（ob实例中需要先建立一个目标库）
cat props.ob
# 修改前：
db=oracle
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://127.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8
user=tpcc@oracle0_85#obv22_stable
password=123456

warehouses=2
loadWorkers=2

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

# 修改后：
db=oracle
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://127.1:2883/targetdb?useUnicode=true&characterEncoding=utf-8
user=root@tenant_1#obalone
password=rootPWD123

warehouses=2
loadWorkers=2

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

编辑好配置文件后，需要在ob的目标库中建表。具体的建表语句 安装dir/run/sql.oceanbase的tableCreates.sql脚本中，可以提前观察下，会有两个比较明显的错误：

• 表组tablegroup=‘tpcc_group’，tpcc_group不应加双引号，可以修改为tpcc_group或者直接去掉引号
• ob的mysql模式不支持varchar2这个数据类型，需要将varchar2改为varchar

修改完成后，如果执行还有报错，则根据实际错误提示进行对sql脚本进行修改即可。

# 执行建表脚本
./runSQL.sh props.ob sql.oceanbase/tableCreates.sql

# 接下来进行数据的导入
./runLoader.sh props.ob
# 进行索引导入（理论上索引后置建立，可以加快导数的性能）
./runSQL.sh props.ob sql.oceanbase/indexCreates.sql

# 设置全局的超时变量
set global ob_query_timeout=36000000000;
set global ob_trx_timeout=36000000000;

第一次执行压测shell脚本时，会报错，提示找不到funcs.sh，但funcs.sh在 安装dir/run/下的确是有这个文件，编辑runBenchmark.sh，将funcs.sh的路径改为绝对路径

# 编辑runBenchmark.sh，修改funcs.sh的路径改为绝对路径
#!/usr/bin/env bash

if [ $# -ne 1 ] ; then
    echo "usage: $(basename $0) PROPS_FILE" >&2
    exit 2
fi

SEQ_FILE="./.jTPCC_run_seq.dat"
if [ ! -f "${SEQ_FILE}" ] ; then
    echo "0" > "${SEQ_FILE}"
fi
SEQ=$(expr $(cat "${SEQ_FILE}") + 1) || exit 1
echo "${SEQ}" > "${SEQ_FILE}"

source /home/admin/benchmarksql-5.0-master/run/funcs.sh $1

setCP || exit 1

myOPTS="-Dprop=$1 -DrunID=${SEQ}"

java -cp "$myCP" $myOPTS jTPCC

# 执行压测脚本
sh runBenchmark.sh props.ob

# 压测完成后，从GV$OB_PLAN_CACHE_PLAN_STAT中获取缓存的执行计划
select query_sql,executions,elapsed_time/executions avg_elapsed_time_ms from oceanbase.gv$ob_plan_cache_plan_stat order by executions desc limit 10 \G

通过explain [extended] 查看top3 sql的逻辑执行计划

# top1 执行计划如下：
explain extended SELECT i_price, i_name, i_data FROM bmsql_item WHERE i_id = 3605 \G
*************************** 1. row ***************************
Query Plan: Plan signature: 7114828602169245238
========================================
|ID|OPERATOR |NAME      |EST. ROWS|COST|
----------------------------------------
|0 |TABLE GET|bmsql_item|1        |3   |
========================================

Outputs & filters: 
-------------------------------------
  0 - output([bmsql_item.i_price(0x7f4d0ac3b730)], [bmsql_item.i_name(0x7f4d0ac3bd20)], [bmsql_item.i_data(0x7f4d0ac3c310)]), filter(nil), rowset=256, 
      access([bmsql_item.i_price(0x7f4d0ac3b730)], [bmsql_item.i_name(0x7f4d0ac3bd20)], [bmsql_item.i_data(0x7f4d0ac3c310)]), partitions(p0), 
      is_index_back=false, 
      range_key([bmsql_item.i_id(0x7f4d0ac3aec0)]), range[3605 ; 3605], 
      range_cond([bmsql_item.i_id(0x7f4d0ac3aec0) = 3605(0x7f4d0ac3a7e0)])

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

Qb name trace:
-------------------------------------
  stmt_id:0, stmt_type:T_EXPLAIN 
  stmt_id:1, SEL$1


Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "targetdb"."bmsql_item"@"SEL$1")
      OPTIMIZER_FEATURES_ENABLE('4.0.0.0')
      END_OUTLINE_DATA
  */

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

Optimization Info:
-------------------------------------
bmsql_item:table_rows:51200, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:default_stat, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback



Parameters:
-------------------------------------


1 row in set (0.002 sec)
# top2 执行计划如下：
explain extended INSERT INTO bmsql_order_line (    ol_o_id, ol_d_id, ol_w_id, ol_number,     ol_i_id, ol_supply_w_id, ol_quantity,     ol_amount, ol_dist_info) VALUES (3001, 1, 2, 1, 3605, 2, 9, 296.64, 'D9mNrTkpN6G6PgTo8SpodjF9') \G
*************************** 1. row ***************************
Query Plan: Plan signature: 3290318591324336132
===========================================
|ID|OPERATOR          |NAME|EST. ROWS|COST|
-------------------------------------------
|0 |DISTRIBUTED INSERT|    |1        |13  |
|1 | EXPRESSION       |    |1        |1   |
===========================================

Outputs & filters: 
-------------------------------------
  0 - output(nil), filter(nil), 
      columns([{bmsql_order_line: ({bmsql_order_line: (bmsql_order_line.ol_w_id(0x7f4d07a33280), bmsql_order_line.ol_d_id(0x7f4d07a32cb0), bmsql_order_line.ol_o_id(0x7f4d07a326e0), bmsql_order_line.ol_number(0x7f4d07a33850), bmsql_order_line.ol_i_id(0x7f4d07a33e20), bmsql_order_line.ol_delivery_d(0x7f4d07a372d0), bmsql_order_line.ol_amount(0x7f4d07a34f90), bmsql_order_line.ol_supply_w_id(0x7f4d07a343f0), bmsql_order_line.ol_quantity(0x7f4d07a349c0), bmsql_order_line.ol_dist_info(0x7f4d07a35560))})}]), 
      column_values([column_conv(INT,PS:(11,0),NOT NULL,__values.ol_w_id(0x7f4d07a33560))(0x7f4d07a39580)], [column_conv(INT,PS:(11,0),NOT NULL,__values.ol_d_id(0x7f4d07a32f90))(0x7f4d07a40a20)], [column_conv(INT,PS:(11,0),NOT NULL,__values.ol_o_id(0x7f4d07a329c0))(0x7f4d07a47ec0)], [column_conv(INT,PS:(11,0),NOT NULL,__values.ol_number(0x7f4d07a33b30))(0x7f4d07a4f360)], [column_conv(INT,PS:(11,0),NOT NULL,__values.ol_i_id(0x7f4d07a34100))(0x7f4d07a56800)], [column_conv(TIMESTAMP,PS:(19,0),NULL,cast(NULL, TIMESTAMP(-1, -1))(0x7f4d07a65390))(0x7f4d07a5df30)], [column_conv(DECIMAL,PS:(6,2),NULL,__values.ol_amount(0x7f4d07a35270))(0x7f4d07a65ea0)], [column_conv(INT,PS:(11,0),NULL,__values.ol_supply_w_id(0x7f4d07a346d0))(0x7f4d07a6d340)], [column_conv(INT,PS:(11,0),NULL,__values.ol_quantity(0x7f4d07a34ca0))(0x7f4d07a747e0)], [column_conv(CHAR,utf8mb4_general_ci,length:24,NULL,__values.ol_dist_info(0x7f4d07a35840))(0x7f4d07a7bc80)])
  1 - output([__values.ol_o_id(0x7f4d07a329c0)], [__values.ol_d_id(0x7f4d07a32f90)], [__values.ol_w_id(0x7f4d07a33560)], [__values.ol_number(0x7f4d07a33b30)], [__values.ol_i_id(0x7f4d07a34100)], [__values.ol_supply_w_id(0x7f4d07a346d0)], [__values.ol_quantity(0x7f4d07a34ca0)], [__values.ol_amount(0x7f4d07a35270)], [__values.ol_dist_info(0x7f4d07a35840)]), filter(nil)
      values({3001, 1, 2, 1, 3605, 2, 9, 296.64, 'D9mNrTkpN6G6PgTo8SpodjF9'})

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

Qb name trace:
-------------------------------------
  stmt_id:0, stmt_type:T_EXPLAIN 
  stmt_id:1, INS$1


Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_DISTRIBUTED_DML(@"INS$1")
      OPTIMIZER_FEATURES_ENABLE('4.0.0.0')
      END_OUTLINE_DATA
  */

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

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

Parameters:
-------------------------------------


1 row in set (0.001 sec)
# top3 执行计划如下：
explain extended UPDATE bmsql_stock SET s_quantity = 108, s_ytd = s_ytd + 9, s_order_cnt =s_order_cnt + 1, s_remote_cnt = s_remote_cnt + 0 WHERE s_w_id = 2 AND s_i_id = 3605 \G
*************************** 1. row ***************************
Query Plan: Plan signature: 16898120345442609721
==========================================
|ID|OPERATOR  |NAME       |EST. ROWS|COST|
------------------------------------------
|0 |UPDATE    |           |1        |36  |
|1 | TABLE GET|bmsql_stock|1        |4   |
==========================================

Outputs & filters: 
-------------------------------------
  0 - output(nil), filter(nil), table_columns([{bmsql_stock: ({bmsql_stock: (bmsql_stock.s_w_id(0x7f4d33e54710), bmsql_stock.s_i_id(0x7f4d33e549f0), bmsql_stock.s_quantity(0x7f4d33e30810), bmsql_stock.s_ytd(0x7f4d33e30da0), bmsql_stock.s_order_cnt(0x7f4d33e31d90), bmsql_stock.s_remote_cnt(0x7f4d33e32d80), bmsql_stock.s_data(0x7f4d33e54cd0), bmsql_stock.s_dist_01(0x7f4d33e54fb0), bmsql_stock.s_dist_02(0x7f4d33e55290), bmsql_stock.s_dist_03(0x7f4d33e57500), bmsql_stock.s_dist_04(0x7f4d33e577e0), bmsql_stock.s_dist_05(0x7f4d33e57ac0), bmsql_stock.s_dist_06(0x7f4d33e57da0), bmsql_stock.s_dist_07(0x7f4d33e58080), bmsql_stock.s_dist_08(0x7f4d33e58360), bmsql_stock.s_dist_09(0x7f4d33e58640), bmsql_stock.s_dist_10(0x7f4d33e58920))})}]),
      update([bmsql_stock.s_quantity(0x7f4d33e30810)=column_conv(INT,PS:(11,0),NULL,cast(108, INT(-1, 0))(0x7f4d33e3b200))(0x7f4d33e33da0)], [bmsql_stock.s_ytd(0x7f4d33e30da0)=column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_ytd(0x7f4d33e30da0) + 9(0x7f4d33e31620), INT(-1, 0))(0x7f4d33e43330))(0x7f4d33e3bd00)], [bmsql_stock.s_order_cnt(0x7f4d33e31d90)=column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_order_cnt(0x7f4d33e31d90) + 1(0x7f4d33e32610), INT(-1, 0))(0x7f4d33e4b4f0))(0x7f4d33e43ec0)], [bmsql_stock.s_remote_cnt(0x7f4d33e32d80)=column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_remote_cnt(0x7f4d33e32d80) + 0(0x7f4d33e33600), INT(-1, 0))(0x7f4d33e536b0))(0x7f4d33e4c080)])
  1 - output([bmsql_stock.s_w_id(0x7f4d33e54710)], [bmsql_stock.s_i_id(0x7f4d33e549f0)], [bmsql_stock.s_quantity(0x7f4d33e30810)], [bmsql_stock.s_ytd(0x7f4d33e30da0)], [bmsql_stock.s_order_cnt(0x7f4d33e31d90)], [bmsql_stock.s_remote_cnt(0x7f4d33e32d80)], [bmsql_stock.s_data(0x7f4d33e54cd0)], [bmsql_stock.s_dist_01(0x7f4d33e54fb0)], [bmsql_stock.s_dist_02(0x7f4d33e55290)], [bmsql_stock.s_dist_03(0x7f4d33e57500)], [bmsql_stock.s_dist_04(0x7f4d33e577e0)], [bmsql_stock.s_dist_05(0x7f4d33e57ac0)], [bmsql_stock.s_dist_06(0x7f4d33e57da0)], [bmsql_stock.s_dist_07(0x7f4d33e58080)], [bmsql_stock.s_dist_08(0x7f4d33e58360)], [bmsql_stock.s_dist_09(0x7f4d33e58640)], [bmsql_stock.s_dist_10(0x7f4d33e58920)]), filter(nil), rowset=256, 
      access([bmsql_stock.s_w_id(0x7f4d33e54710)], [bmsql_stock.s_i_id(0x7f4d33e549f0)], [bmsql_stock.s_quantity(0x7f4d33e30810)], [bmsql_stock.s_ytd(0x7f4d33e30da0)], [bmsql_stock.s_order_cnt(0x7f4d33e31d90)], [bmsql_stock.s_remote_cnt(0x7f4d33e32d80)], [bmsql_stock.s_data(0x7f4d33e54cd0)], [bmsql_stock.s_dist_01(0x7f4d33e54fb0)], [bmsql_stock.s_dist_02(0x7f4d33e55290)], [bmsql_stock.s_dist_03(0x7f4d33e57500)], [bmsql_stock.s_dist_04(0x7f4d33e577e0)], [bmsql_stock.s_dist_05(0x7f4d33e57ac0)], [bmsql_stock.s_dist_06(0x7f4d33e57da0)], [bmsql_stock.s_dist_07(0x7f4d33e58080)], [bmsql_stock.s_dist_08(0x7f4d33e58360)], [bmsql_stock.s_dist_09(0x7f4d33e58640)], [bmsql_stock.s_dist_10(0x7f4d33e58920)]), partitions(p0), 
      is_index_back=false, 
      range_key([bmsql_stock.s_w_id(0x7f4d33e54710)], [bmsql_stock.s_i_id(0x7f4d33e549f0)]), range[2,3605 ; 2,3605], 
      range_cond([bmsql_stock.s_w_id(0x7f4d33e54710) = 2(0x7f4d33e5b140)], [bmsql_stock.s_i_id(0x7f4d33e549f0) = 3605(0x7f4d33e5c050)])

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

Qb name trace:
-------------------------------------
  stmt_id:0, stmt_type:T_EXPLAIN 
  stmt_id:1, UPD$1


Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"UPD$1" "targetdb"."bmsql_stock"@"UPD$1")
      OPTIMIZER_FEATURES_ENABLE('4.0.0.0')
      END_OUTLINE_DATA
  */

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

Optimization Info:
-------------------------------------
bmsql_stock:table_rows:100018, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:default_stat, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback



Parameters:
-------------------------------------


1 row in set (0.002 sec)
-------------------------------
bmsql_stock:table_rows:100018, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:default_stat, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback



Parameters:
-------------------------------------


1 row in set (0.002 sec)