环境准备

1）Centos7.9；4G内存；2CPU; IP 192.168.56.121

安装benchmarksql 5.1

下载bechmarksql地址：https://github.com/obpilot/benchmarksql-5.0

  [root@rocky8 ~]# unzip benchmarksql-5.0-master.zip

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

db=oracle
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://192.168.56.210:2883/tpcc?useUnicode=true&characterEncoding=utf-8
user=myob:sys:root
password=obAdmin123

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=16
//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

准备数据

[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

建表

[admin@oceanbase01]$ ./runSQL.sh props.ob sql.oceanbase/tableCreates.sql

加载数据

./runLoader.sh props.ob

检查数据

[admin@rocky8 obproxy-3.2.3.5]$ mysql -h192.168.56.210 -uroot@sys#myob -P2883 -pobAdmin123 -c -A oceanbase
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.25 OceanBase 3.1.5 (r100020022023091114-8a9dc4b356d043b494015503d6d91f876486fbed) (Built Sep 11 2023 14:38:58)

Copyright (c) 2000, 2024, 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> use tpcc;
Database changed
mysql> show tables;
Empty set (0.03 sec)

mysql> show tables;
+----------------+
| Tables_in_tpcc |
+----------------+
| bmsql_config   |
| bmsql_item     |
+----------------+
2 rows in set (0.27 sec)

执行测试

./runBenchmark.sh props.ob
23:10:28,228 [main] INFO   jTPCC : Term-00,
23:10:28,391 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
23:10:28,394 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0
23:10:28,394 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
23:10:28,402 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa
23:10:28,402 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier
23:10:28,492 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck
23:10:28,492 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
23:10:28,546 [main] INFO   jTPCC : Term-00,
23:10:28,549 [main] INFO   jTPCC : Term-00, db=oracle
23:10:28,549 [main] INFO   jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
23:10:28,556 [main] INFO   jTPCC : Term-00, conn=jdbc:oceanbase://192.168.56.210:2883/tpcc?useUnicode=true&characterEncoding=utf-8
23:10:28,557 [main] INFO   jTPCC : Term-00, user=myob:sys:root
23:10:28,557 [main] INFO   jTPCC : Term-00,
23:10:28,557 [main] INFO   jTPCC : Term-00, warehouses=10
23:10:28,557 [main] INFO   jTPCC : Term-00, terminals=100
23:10:28,617 [main] INFO   jTPCC : Term-00, runMins=16
23:10:28,619 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=0
23:10:28,620 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=true
23:10:28,621 [main] INFO   jTPCC : Term-00,
23:10:28,621 [main] INFO   jTPCC : Term-00, newOrderWeight=45
23:10:28,621 [main] INFO   jTPCC : Term-00, paymentWeight=43
23:10:28,621 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
23:10:28,634 [main] INFO   jTPCC : Term-00, deliveryWeight=4
23:10:28,634 [main] INFO   jTPCC : Term-00, stockLevelWeight=4
23:10:28,635 [main] INFO   jTPCC : Term-00,
23:10:28,636 [main] INFO   jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
23:10:28,638 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
23:10:28,638 [main] INFO   jTPCC : Term-00,
23:10:29,343 [main] INFO   jTPCC : Term-00, copied props.ob to my_result_2024-03-17_231029/run.properties
23:10:29,344 [main] INFO   jTPCC : Term-00, created my_result_2024-03-17_231029/data/runInfo.csv for runID 8
23:10:29,346 [main] INFO   jTPCC : Term-00, writing per transaction results to my_result_2024-03-17_231029/data/result.csv
23:10:29,392 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
23:10:29,409 [main] INFO   jTPCC : Term-00, osCollectorInterval=1
23:10:29,412 [main] INFO   jTPCC : Term-00, osCollectorSSHAddr=null
23:10:29,414 [main] INFO   jTPCC : Term-00, osCollectorDevices=null

查看执行计划

查看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’;