做如下两个小测试
- 1.使用 BenmarkSQL 运行 TPC-C 。
- 2.分析 TPC-C TOP SQL，查看 3条 SQL 的 解析执行计划 和 实际执行计划。
参考ob官方视频：
https://www.oceanbase.com/video/9000787
ob官方文档
https://www.oceanbase.com/docs/community-observer-cn-10000000000901541
1.BenchmarkSQL安装配置
################
- 注意：
测试环境需要有 Java 运行环境，且版本不低于 V1.8.0
[admin@obcp ~]$ 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)
- 注意：
BenchmarkSQL 的官方下载地址为：https://sourceforge.net/projects/benchmarksql/
但是由于 Benchmark SQL5 不支持 OceanBase 数据库的 TPC-C 测试，需要通过修改 BenchMarkSQL5 部分源码支持 OceanBase 数据库
下面是ob官方文档中关于修改源码的介绍
https://www.oceanbase.com/docs/community-observer-cn-10000000000901541
######################
这里我直接选择github上的包了[root@obcp benchmark-git]# git clone https://github.com/obpilot/benchmarksql-5.0.git
正克隆到 'benchmarksql-5.0'...
remote: Enumerating objects: 110, done.
remote: Counting objects: 100% (110/110), done.
remote: Compressing objects: 100% (90/90), done.
remote: Total 110 (delta 14), reused 105 (delta 12), pack-reused 0
接收对象中: 100% (110/110), 5.58 MiB | 42.00 KiB/s, done.
处理 delta 中: 100% (14/14), done.
[root@obcp admin]# chown -R admin:admin benchmarksql-5.0/
[admin@obcp ~]$ cd benchmarksql-5.0/run/;ll
总用量 56
-rwxr-xr-x 1 admin admin 1100 9月 3 23:05 funcs.sh
-rwxr-xr-x 1 admin admin 2123 9月 3 23:05 generateGraphs.sh
-rwxr-xr-x 1 admin admin 7256 9月 3 23:05 generateReport.sh
-rwxr-xr-x 1 admin admin 962 9月 3 23:05 log4j.properties
drwxr-xr-x 2 admin admin 218 9月 3 23:05 misc
-rw-r--r-- 1 admin admin 1063 9月 3 23:05 props.fb
-rwxr-xr-x 1 admin admin 951 9月 3 23:05 props.ob ##这里有ob了，如果在bench下的是没有的
-rw-r--r-- 1 admin admin 947 9月 3 23:05 props.ora
-rw-r--r-- 1 admin admin 1021 9月 3 23:05 props.pg
-rwxr-xr-x 1 admin admin 385 9月 3 23:05 runBenchmark.sh
-rwxr-xr-x 1 admin admin 528 9月 3 23:05 runDatabaseBuild.sh
-rwxr-xr-x 1 admin admin 330 9月 3 23:05 runDatabaseDestroy.sh
-rwxr-xr-x 1 admin admin 200 9月 3 23:05 runLoader.sh
-rwxr-xr-x 1 admin admin 1207 9月 3 23:05 runSQL.sh
drwxr-xr-x 2 admin admin 170 9月 3 23:05 sql.common
[admin@obcp run]$ cp props.ob my_ob.ob
[admin@obcp run]$ vim my_ob.ob
db=oracle
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase:///127.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8
user=root@obmysql
password=rootPWD123warehouses=2
loadWorkers=10 #10个并发
terminals=10 #这里我设置的模拟10个客户端
###内容如上，主要是将conn的链接信息，以及user、password信息填写准确
2.创建表
- 1.创建测试数据库
root@db 23:18: [(none)]>create database tpcc; Query OK, 1 row affected (0.233 sec)
- 2.修改创建表的sql文件
[admin@obcp ~]$ cd /home/admin/benchmarksql-5.0/run/sql.oceanbase[admin@obcp sql.oceanbase]$ sed -i 's/varchar2/varchar/g' tableCreates.sql ##修改数据类型varchar2-->varchar##社区版只能创建mysql租户，不识别varchar2，因此需要修改下[admin@obcp sql.oceanbase]$ sed -i 's/"tpcc_group"/tpcc_group/g' t.sql##文件中的创建语句给tablegroup加了双引号，我的4.0版本使用会报错，所以就去掉引号了root@db 00:03: [tpcc]>create tablegroup "tpcc_group" ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '"tpcc_group"' at line 1
- 3.创建表
[admin@obcp run]$ ./runSQL.sh my_ob.ob ./sql.oceanbase/tableCreates.sql # ------------------------------------------------------------ # Loading SQL file ./sql.oceanbase/tableCreates.sql # ------------------------------------------------------------ create table bmsql_config ( cfg_name varchar(30) primary key, cfg_value varchar(50) ); create tablegroup tpcc_group ; create table bmsql_warehouse ( w_id integer not null, ....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), PRIMARY KEY (s_w_id, s_i_id) )tablegroup='tpcc_group' ;
- 4.检查创建的表
root@db 00:08: [tpcc]>show tables; +------------------+ | Tables_in_tpcc | +------------------+ | 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.004 sec)
- 5.创建索引
[admin@obcp run]$ ./runSQL.sh my_ob.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;
3.加载数据
- 1.加载数据
[admin@obcp run]$ ./runLoader.sh my_ob.ob Starting BenchmarkSQL LoadData driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver conn=jdbc:oceanbase://127.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8 user=root@sys#obmysql 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
- 2.查看数据
root@db 10:53: [tpcc]>select count(1) , 'bmsql_config' from bmsql_configunionselect count(1) , 'bmsql_customer' from bmsql_customerunionselect count(1) , 'bmsql_district' from bmsql_districtunionselect count(1) , 'bmsql_history' from bmsql_historyunionselect count(1) , 'bmsql_item' from bmsql_itemunionselect count(1) , 'bmsql_new_order' from bmsql_new_orderunionselect count(1) , 'bmsql_oorder' from bmsql_oorderunionselect count(1) , 'bmsql_order_line' from bmsql_order_lineunionselect count(1) , 'bmsql_stock' from bmsql_stockunionselect count(1) , 'bmsql_warehouse' from bmsql_warehouse; +----------+------------------+ | count(1) | bmsql_config | +----------+------------------+ | 4 | bmsql_config | | 60000 | bmsql_customer | | 20 | bmsql_district | | 60000 | bmsql_history | | 100000 | bmsql_item | | 18000 | bmsql_new_order | | 60000 | bmsql_oorder | | 599288 | bmsql_order_line | | 200000 | bmsql_stock | | 2 | bmsql_warehouse | +----------+------------------+ 10 rows in set (3.387 sec)
4.开始压测
[admin@obcp run]$ ./runBenchmark.sh my_ob.ob 11:35:15,381 [main] INFO jTPCC : Term-00, 11:35:15,385 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 11:35:15,385 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0 11:35:15,385 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 11:35:15,385 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa 11:35:15,385 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier 11:35:15,388 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck 11:35:15,388 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 11:35:15,388 [main] INFO jTPCC : Term-00, 11:35:15,388 [main] INFO jTPCC : Term-00, db=oracle 11:35:15,388 [main] INFO jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver 11:35:15,388 [main] INFO jTPCC : Term-00, conn=jdbc:oceanbase://127.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8 11:35:15,388 [main] INFO jTPCC : Term-00, user=root@sys 11:35:15,388 [main] INFO jTPCC : Term-00, 11:35:15,389 [main] INFO jTPCC : Term-00, warehouses=2 11:35:15,389 [main] INFO jTPCC : Term-00, terminals=10 11:35:15,392 [main] INFO jTPCC : Term-00, runMins=1 11:35:15,392 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0 11:35:15,392 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true 11:35:15,392 [main] INFO jTPCC : Term-00, 11:35:15,392 [main] INFO jTPCC : Term-00, newOrderWeight=45 11:35:15,393 [main] INFO jTPCC : Term-00, paymentWeight=43 11:35:15,393 [main] INFO jTPCC : Term-00, orderStatusWeight=4 11:35:15,393 [main] INFO jTPCC : Term-00, deliveryWeight=4 11:35:15,393 [main] INFO jTPCC : Term-00, stockLevelWeight=4 11:35:15,393 [main] INFO jTPCC : Term-00, 11:35:15,393 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS 11:35:15,394 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py 11:35:15,394 [main] INFO jTPCC : Term-00, 11:35:15,421 [main] INFO jTPCC : Term-00, copied my_ob.ob to my_result_2023-09-04_113515/run.properties 11:35:15,422 [main] INFO jTPCC : Term-00, created my_result_2023-09-04_113515/data/runInfo.csv for runID 6 11:35:15,422 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2023-09-04_113515/data/result.csv 11:35:15,424 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py 11:35:15,424 [main] INFO jTPCC : Term-00, osCollectorInterval=1 11:35:15,424 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null 11:35:15,424 [main] INFO jTPCC : Term-00, osCollectorDevices=null 11:35:15,534 [main] INFO jTPCC : Term-00, 11:35:15,937 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 160 11:35:15,937 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 70 11:35:15,937 [main] INFO jTPCC : Term-00,Term-00, Running Average tpmTOTAL: 2721.90 Current tpmTOTAL: 17844 Memory Usage: 36MB / 142MB11:36:16,488 [Thread-6] INFO jTPCC : Term-00, t tpmTOTAL: 816 Memory Usage: 11MB / 148MB 11:36:16,488 [Thread-6] INFO jTPCC : Term-00, 11:36:16,488 [Thread-6] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 1270.22 11:36:16,488 [Thread-6] INFO jTPCC : Term-00, Measured tpmTOTAL = 2725.05 11:36:16,488 [Thread-6] INFO jTPCC : Term-00, Session Start = 2023-09-04 11:35:16 11:36:16,488 [Thread-6] INFO jTPCC : Term-00, Session End = 2023-09-04 11:36:16 11:36:16,488 [Thread-6] INFO jTPCC : Term-00, Transaction Count = 2730
5.分析 TPC-C TOP SQL
- 1.展示最近3条top Sql
root@db 12:09: [oceanbase]>SELECT avg_exe_usec,tenant_id, svr_ip, svr_port, sql_id, plan_id FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT ORDER BY avg_exe_usec DESC LIMIT 3 ; +--------------+-----------+----------------+----------+----------------------------------+---------+ | avg_exe_usec | tenant_id | svr_ip | svr_port | sql_id | plan_id | +--------------+-----------+----------------+----------+----------------------------------+---------+ 17191 | 20230901 | 192.168.61.144 | 2882 | FC3FED8CCB2946DE54F1C5BA3656023C | 533 | | 16648 | 20230901 | 192.168.61.144 | 2882 | F59A700FA168324279B0DBC25E19760F | 572 | | 15181 | 20230901 | 192.168.61.144 | 2882 | F95CD7A05C7064A028D76E0088B147F7 | 571 |
- 2.查看第1条sql的内容
root@db 13:18: [oceanbase]>select query_sql from oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT where sql_id='FC3FED8CCB2946DE54F1C5BA3656023C'\G *************************** 1. row *************************** query_sql: SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = 2 AND d_id = 8 FOR UPDATE 1 row in set (0.004 sec)
- 3.查看第1条sql解析执行计划
root@db 13:20: [tpcc]>explain SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = 2 AND d_id = 8 FOR UPDATE; +---------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +---------------------------------------------------------------------------------------------------------------------------------------------+ | ===================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------- | | |0 |MATERIAL | |1 |3 | | | |1 | FOR UPDATE| |1 |3 | | | |2 | TABLE GET|bmsql_district|1 |3 | | | ===================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([bmsql_district.d_tax], [bmsql_district.d_next_o_id]), filter(nil), rowset=256 | | 1 - output([bmsql_district.d_tax], [bmsql_district.d_next_o_id]), filter(nil), rowset=256 | | lock tables(bmsql_district) | | 2 - output([bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_tax], [bmsql_district.d_next_o_id]), filter(nil), rowset=256 | | access([bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_tax], [bmsql_district.d_next_o_id]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([bmsql_district.d_w_id], [bmsql_district.d_id]), range[2,8 ; 2,8], | | range_cond([bmsql_district.d_w_id = 2], [bmsql_district.d_id = 8]) | +---------------------------------------------------------------------------------------------------------------------------------------------+ 17 rows in set (0.004 sec)
- 4.第1条语句的实际执行计划
root@db 13:40: [tpcc]> SELECT SVR_IP, plan_depth, plan_line_id,operator,name,rows,cost,propertyfrom oceanbase.`GV$OB_PLAN_CACHE_PLAN_EXPLAIN`where tenant_id=20230901 AND SVR_IP = '192.168.61.144' AND SVR_PORT=2882 AND plan_id=533; +----------------+------------+--------------+------------------+----------------+------+------+------------------------------------------------+ | SVR_IP | plan_depth | plan_line_id | operator | name | rows | cost | property +----------------+------------+--------------+------------------+----------------+------+------+-------------------------------------------------------------+ | 192.168.61.144 | 0 | 0 | PHY_MATERIAL | NULL | 1 | 2 | NULL | 192.168.61.144 | 1 | 1 | PHY_LOCK | NULL | 1 | 2 | NULL | 192.168.61.144 | 2 | 2 | PHY_TABLE_SCAN | bmsql_district | 1 | 2 | table_rows:18, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:default_stat, avaiable_index_name[bmsql_district] |+----------------+------------+--------------+------------------+----------------+------+------+-------------------------------------------------------------+ 3 rows in set (0.001 sec)#####注意################上面第4条，需要把where条件都补充齐全，否则是查不到数据的；##开始不知道，直接查表是空的，一定要注意#########################
- 5.第2条语句的执行计划
root@db 13:41: [tpcc]>select query_sql from oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT where sql_id='F59A700FA168324279B0DBC25E19760F'\G *************************** 1. row *************************** query_sql: SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stockWHERE s_w_id = 1 AND s_quantity < 14 AND s_i_id IN (SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_idAND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_idWHERE d_w_id = 1 AND d_id = 5 ) ) 1 row in set (0.004 sec)root@db 13:52: [tpcc]>explain SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock -> WHERE s_w_id = 1 AND s_quantity < 14 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 = 1 AND d_id = 5 ) ); +----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------------------------+ | ======================================================================= | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------------------------- | | |0 |SCALAR GROUP BY | |1 |2707 | | | |1 | NESTED-LOOP JOIN | |9900 |2527 | | | |2 | SUBPLAN SCAN |VIEW2 |100 |703 | | | |3 | HASH DISTINCT | |100 |703 | | | |4 | NESTED-LOOP JOIN | |1329 |576 | | | |5 | TABLE GET |bmsql_district |1 |3 | | | |6 | DISTRIBUTED TABLE SCAN|bmsql_order_line|11959 |1420 | | | |7 | DISTRIBUTED TABLE GET |bmsql_stock |1 |18 | | | ======================================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256 | | group(nil), agg_func([T_FUN_COUNT(*)]) | | 1 - output(nil), filter(nil), rowset=256 | | conds(nil), nl_params_([VIEW2.VIEW1.ol_i_id]), batch_join=true | | 2 - output([VIEW2.VIEW1.ol_i_id]), filter(nil), rowset=256 | | access([VIEW2.VIEW1.ol_i_id]) | | 3 - output([bmsql_order_line.ol_i_id]), filter(nil), rowset=256 | | distinct([bmsql_order_line.ol_i_id]) | | 4 - output([bmsql_order_line.ol_i_id]), filter(nil), rowset=256 | | conds(nil), nl_params_([bmsql_district.d_next_o_id]), batch_join=true | | 5 - output([bmsql_district.d_next_o_id]), filter([bmsql_district.d_next_o_id > bmsql_district.d_next_o_id - 20]), rowset=256 | | access([bmsql_district.d_next_o_id]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([bmsql_district.d_w_id], [bmsql_district.d_id]), range[1,5 ; 1,5], | | range_cond([bmsql_district.d_w_id = 1], [bmsql_district.d_id = 5]) | | 6 - output([bmsql_order_line.ol_i_id]), filter(nil), rowset=256 | | access([GROUP_ID], [bmsql_order_line.ol_i_id]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id], [bmsql_order_line.ol_o_id], [bmsql_order_line.ol_number]), range(MIN ; MAX), | | range_cond([bmsql_order_line.ol_w_id = 1], [bmsql_order_line.ol_d_id = 5], [bmsql_order_line.ol_o_id >= :0 - 20], [bmsql_order_line.ol_o_id < :0]) | | 7 - output(nil), filter([bmsql_stock.s_quantity < 14]), rowset=256 | | access([GROUP_ID], [bmsql_stock.s_quantity]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([bmsql_stock.s_w_id], [bmsql_stock.s_i_id]), range(MIN ; MAX), | | range_cond([bmsql_stock.s_w_id = 1], [bmsql_stock.s_i_id = :3]) | +----------------------------------------------------------------------------------------------------------------------------------------------------------+ 39 rows in set (0.057 sec)
- 6.第2条语句的实际执行计划
root@db 13:49: [tpcc]>SELECT SVR_IP, plan_depth, plan_line_id,operator,name,rows,cost,propertyfrom oceanbase.`GV$OB_PLAN_CACHE_PLAN_EXPLAIN`where tenant_id=20230901 AND SVR_IP = '192.168.61.144' AND SVR_PORT=2882 AND plan_id=572; +----------------+------------+--------------+--------------------------+------------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | SVR_IP | plan_depth | plan_line_id | operator | name | rows | cost | property | +----------------+------------+--------------+--------------------------+------------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 192.168.61.144 | 0 | 0 | PHY_SCALAR_AGGREGATE | NULL | 1 | 2612 | NULL | | 192.168.61.144 | 1 | 1 | PHY_NESTED_LOOP_JOIN | NULL | 9900 | 2432 | NULL | | 192.168.61.144 | 2 | 2 | PHY_SUBPLAN_SCAN | NULL | 100 | 608 | NULL | | 192.168.61.144 | 3 | 3 | PHY_HASH_DISTINCT | NULL | 100 | 608 | NULL | | 192.168.61.144 | 4 | 4 | PHY_NESTED_LOOP_JOIN | NULL | 1194 | 492 | NULL | | 192.168.61.144 | 5 | 5 | PHY_TABLE_SCAN | bmsql_district | 1 | 2 | table_rows:18, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:0, est_method:default_stat, avaiable_index_name[bmsql_district] | | 192.168.61.144 | 5 | 6 | PHY_TABLE_SCAN | bmsql_order_line | 10738 | 1201 | table_rows:650901, physical_range_rows:11458, logical_range_rows:10737, index_back_rows:0, output_rows:10737, est_method:local_storage, avaiable_index_name[bmsql_order_line], estimation info[table_id:500014, (table_type:11, version:-1--1--1, logical_rc:30024, physical_rc:30024), (table_type:12, version:-1--1--1, logical_rc:202, physical_rc:376), (table_type:0, version:-1--1--1, logical_rc:1987, physical_rc:3975)] | | 192.168.61.144 | 2 | 7 | PHY_TABLE_SCAN | bmsql_stock | 1 | 17 | table_rows:200000, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:0, est_method:local_storage, avaiable_index_name[bmsql_stock], estimation info[table_id:500016, (table_type:11, version:-1--1--1, logical_rc:200000, physical_rc:200000), (table_type:12, version:-1--1--1, logical_rc:0, physical_rc:7049), (table_type:0, version:-1--1--1, logical_rc:0, physical_rc:28991)] | +----------------+------------+--------------+--------------------------+------------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 8 rows in set (0.001 sec)
- 7.第3条语句的执行计划
root@db 13:50: [tpcc]>select query_sql from oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT where sql_id='F95CD7A05C7064A028D76E0088B147F7'\G *************************** 1. row *************************** query_sql: DELETE FROM bmsql_new_order WHERE no_w_id = 2 AND no_d_id = 1 AND no_o_id = 2313 1 row in set (0.004 sec)root@db 13:57: [tpcc]>explain DELETE FROM bmsql_new_order WHERE no_w_id = 2 AND no_d_id = 1 AND no_o_id = 2313; +--------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------------------+ | ===================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------- | | |0 |DELETE | |1 |15 | | | |1 | TABLE GET|bmsql_new_order|1 |3 | | | ===================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output(nil), filter(nil) | | table_columns([{bmsql_new_order: ({bmsql_new_order: (bmsql_new_order.no_w_id, bmsql_new_order.no_d_id, bmsql_new_order.no_o_id)})}]) | | 1 - output([bmsql_new_order.no_w_id], [bmsql_new_order.no_d_id], [bmsql_new_order.no_o_id]), filter(nil), rowset=256 | | access([bmsql_new_order.no_w_id], [bmsql_new_order.no_d_id], [bmsql_new_order.no_o_id]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([bmsql_new_order.no_w_id], [bmsql_new_order.no_d_id], [bmsql_new_order.no_o_id]), range[2,1,2313 ; 2,1,2313], | | range_cond([bmsql_new_order.no_w_id = 2], [bmsql_new_order.no_d_id = 1], [bmsql_new_order.no_o_id = 2313]) | +--------------------------------------------------------------------------------------------------------------------------------------------+ 15 rows in set (0.002 sec)
- 8.第3条语句的实际执行计划
root@db 13:58: [tpcc]> SELECT SVR_IP, plan_depth, plan_line_id,operator,name,rows,cost,propertyfrom oceanbase.`GV$OB_PLAN_CACHE_PLAN_EXPLAIN`where tenant_id=20230901 AND SVR_IP = '192.168.61.144' AND SVR_PORT=2882 AND plan_id=571; +----------------+------------+--------------+----------------+------------------+------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | SVR_IP | plan_depth | plan_line_id | operator | name | rows | cost | property | +----------------+------------+--------------+----------------+------------------+------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 192.168.61.144 | 0 | 0 | PHY_TABLE_SCAN | bmsql_order_line | 13 | 6 | table_rows:650867, physical_range_rows:13, logical_range_rows:13, index_back_rows:0, output_rows:13, est_method:local_storage, avaiable_index_name[bmsql_order_line], estimation info[table_id:500014, (table_type:11, version:-1--1--1, logical_rc:13, physical_rc:13), (table_type:12, version:-1--1--1, logical_rc:0, physical_rc:0), (table_type:0, version:-1--1--1, logical_rc:0, physical_rc:0)] | +----------------+------------+--------------+----------------+------------------+------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
小实验暂时做完了，遇到的主要的问题就是这个，where都条件都要补充完全，我直接查表是没有数据的；
SELECT SVR_IP, plan_depth, plan_line_id,operator,name,rows,cost,propertyfrom oceanbase.`GV$OB_PLAN_CACHE_PLAN_EXPLAIN`where tenant_id=20230901 AND SVR_IP = '192.168.61.144' AND SVR_PORT=2882 AND plan_id=572;
另外就是因为版本的原因，官方视频中的一些视图，和4.X版本有出入，4.X版本的v$视图好多都变成了v$ob_开头，这个要注意；
runBenchMark的时候，如果事务超时，可以重新设置下时间；
最后就是，第一次接触分布式数据库，还是有很多需要学习的，虽然之前做的小实验都 很简单，但是确实占用了不少时间，以后争取慢慢积累进步吧。
（争取后边坚持写写使用的文章，记录从0开始学ob！！！）
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
