一、环境准备
obclient [test]> create database tpcc;
Query OK, 1 row affected (0.131 sec)
obclient [test]> create user tpcc identified by ‘royce’;
Query OK, 0 rows affected (0.048 sec)
obclient [test]> grant all on tpcc.* to tpcc;
Query OK, 0 rows affected (0.058 sec)
二、准备BenchmarkSQL
下载并安装BenchmarkSQL
[root@localhost ~]# git clone https://github.com/obpilot/benchmarksql-5.0.git
Cloning into ‘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
Receiving objects: 100% (110/110), 5.58 MiB | 5.11 MiB/s, done.
Resolving deltas: 100% (14/14), done.
编辑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@obmysql#obdemo
password=royce
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
运行创建表语句
[root@localhost run]# sh runSQL.sh props.ob sql.common/tableCreates.sql
runSQL.sh: line 14: source: funcs.sh: file not found
配置环境或者修改脚本为绝对路径
[root@localhost run]# sh runSQL.sh props.ob sql.common/tableCreates.sql
------------------------------------------------------------
Loading SQL file sql.common/tableCreates.sql
------------------------------------------------------------
create table bmsql_config (
cfg_name varchar(30) primary key,
cfg_value varchar(50)
);
create table bmsql_warehouse (
w_id integer not null,
w_ytd decimal(12,2),
w_tax decimal(4,4),
w_name varchar(10),
w_street_1 varchar(20),
w_street_2 varchar(20),
w_city varchar(20),
w_state char(2),
w_zip char(9)
);
create table bmsql_district (
d_w_id integer not null,
d_id integer not null,
d_ytd decimal(12,2),
d_tax decimal(4,4),
d_next_o_id integer,
d_name varchar(10),
d_street_1 varchar(20),
d_street_2 varchar(20),
d_city varchar(20),
d_state char(2),
d_zip char(9)
);
create table bmsql_customer (
c_w_id integer not null,
c_d_id integer not null,
c_id integer not null,
c_discount decimal(4,4),
c_credit char(2),
c_last varchar(16),
c_first varchar(16),
c_credit_lim decimal(12,2),
c_balance decimal(12,2),
c_ytd_payment decimal(12,2),
c_payment_cnt integer,
c_delivery_cnt integer,
c_street_1 varchar(20),
c_street_2 varchar(20),
c_city varchar(20),
c_state char(2),
c_zip char(9),
c_phone char(16),
c_since timestamp,
c_middle char(2),
c_data varchar(500)
);
create sequence bmsql_hist_id_seq;
You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near ‘sequence bmsql_hist_id_seq’ at line 1
create table bmsql_history (
hist_id integer,
h_c_id integer,
h_c_d_id integer,
h_c_w_id integer,
h_d_id integer,
h_w_id integer,
h_date timestamp,
h_amount decimal(6,2),
h_data varchar(24)
);
create table bmsql_new_order (
no_w_id integer not null,
no_d_id integer not null,
no_o_id integer not null
);
create table bmsql_oorder (
o_w_id integer not null,
o_d_id integer not null,
o_id integer not null,
o_c_id integer,
o_carrier_id integer,
o_ol_cnt integer,
o_all_local integer,
o_entry_d timestamp
);
create table bmsql_order_line (
ol_w_id integer not null,
ol_d_id integer not null,
ol_o_id integer not null,
ol_number integer not null,
ol_i_id integer not null,
ol_delivery_d timestamp,
ol_amount decimal(6,2),
ol_supply_w_id integer,
ol_quantity integer,
ol_dist_info char(24)
);
create table bmsql_item (
i_id integer not null,
i_name varchar(24),
i_price decimal(5,2),
i_data varchar(50),
i_im_id integer
);
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)
);
测试前先设置参数,防止超时
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;
装载数据
[root@localhost run]# sh runLoader.sh props.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=tpcc@obmysql#obdemo
password=***********
warehouses=2
loadWorkers=2
fileLocation (not defined)
csvNullValue (not defined - using default ‘NULL’)
Worker 000: Loading ITEM
Worker 001: Loading Warehouse 1
Worker 000: Loading ITEM done
Worker 000: Loading Warehouse 2
Worker 001: Loading Warehouse 1 done
Worker 000: Loading Warehouse 2 done
创建索引
obclient [tpcc]> create index bmsql_customer_idx1 on bmsql_customer (c_w_id, c_d_id, c_last, c_first) local;
Query OK, 0 rows affected (1.170 sec)
obclient [tpcc]> create index bmsql_oorder_idx1 on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local;
Query OK, 0 rows affected (0.778 sec)
运行测试
[root@localhost run]# sh runBenchmark.sh props.ob
04:01:43,890 [main] INFO jTPCC : Term-00,
04:01:43,898 [main] INFO jTPCC : Term-00, ±------------------------------------------------------------+
04:01:43,898 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
04:01:43,898 [main] INFO jTPCC : Term-00, ±------------------------------------------------------------+
04:01:43,898 [main] INFO jTPCC : Term-00, © 2003, Raul Barbosa
04:01:43,898 [main] INFO jTPCC : Term-00, © 2004-2016, Denis Lussier
04:01:43,907 [main] INFO jTPCC : Term-00, © 2016, Jan Wieck
04:01:43,908 [main] INFO jTPCC : Term-00, ±------------------------------------------------------------+
04:01:43,908 [main] INFO jTPCC : Term-00,
04:01:43,908 [main] INFO jTPCC : Term-00, db=oracle
04:01:43,908 [main] INFO jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
04:01:43,908 [main] INFO jTPCC : Term-00, conn=jdbc:oceanbase://127.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8
04:01:43,909 [main] INFO jTPCC : Term-00, user=tpcc@obmysql#obdemo
04:01:43,909 [main] INFO jTPCC : Term-00,
04:01:43,909 [main] INFO jTPCC : Term-00, warehouses=2
04:01:43,909 [main] INFO jTPCC : Term-00, terminals=2
04:01:43,912 [main] INFO jTPCC : Term-00, runMins=1
04:01:43,913 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0
04:01:43,913 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
04:01:43,913 [main] INFO jTPCC : Term-00,
04:01:43,913 [main] INFO jTPCC : Term-00, newOrderWeight=45
04:01:43,913 [main] INFO jTPCC : Term-00, paymentWeight=43
04:01:43,913 [main] INFO jTPCC : Term-00, orderStatusWeight=4
04:01:43,913 [main] INFO jTPCC : Term-00, deliveryWeight=4
04:01:43,913 [main] INFO jTPCC : Term-00, stockLevelWeight=4
04:01:43,913 [main] INFO jTPCC : Term-00,
04:01:43,914 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
04:01:43,914 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
04:01:43,914 [main] INFO jTPCC : Term-00,
04:01:44,049 [main] INFO jTPCC : Term-00, copied props.ob to my_result_2022-10-24_040144/run.properties
04:01:44,049 [main] INFO jTPCC : Term-00, created my_result_2022-10-24_040144/data/runInfo.csv for runID 6
04:01:44,050 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2022-10-24_040144/data/result.csv
04:01:44,053 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
04:01:44,053 [main] INFO jTPCC : Term-00, osCollectorInterval=1
04:01:44,053 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null
04:01:44,053 [main] INFO jTPCC : Term-00, osCollectorDevices=null
04:01:44,298 [main] INFO jTPCC : Term-00,
04:01:45,063 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 162
04:01:45,063 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 97
04:01:45,063 [main] INFO jTPCC : Term-00, Term-04:03:23,156 [Thread-1] INFO jTPCC : Term-00, mTOTAL: 48 Memory Usage: 18MB / 180MB
04:03:23,157 [Thread-1] INFO jTPCC : Term-00,
04:03:23,157 [Thread-1] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 1.83
04:03:23,157 [Thread-1] INFO jTPCC : Term-00, Measured tpmTOTAL = 3.67
04:03:23,157 [Thread-1] INFO jTPCC : Term-00, Session Start = 2022-10-24 04:01:45
04:03:23,157 [Thread-1] INFO jTPCC : Term-00, Session End = 2022-10-24 04:03:23
04:03:23,157 [Thread-1] INFO jTPCC : Term-00, Transaction Count = 5
三、TOP SQL分析
查看TOP sql
obclient [tpcc]> SELECT/+ PARALLEL(15)/avg_exe_usec, 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 3;
±-------------±---------±---------±---------------------------------±--------+
| avg_exe_usec | svr_ip | svr_port | sql_id | plan_id |
±-------------±---------±---------±---------------------------------±--------+
| 4146400 | 10.0.2.4 | 2882 | 7229213613983BC5FDA15AD11EC70D01 | 73 |
| 2120593 | 10.0.2.4 | 2882 | E1F2BDA1D7391B757859ED3704E5AFB7 | 75 |
| 630255 | 10.0.2.4 | 2882 | AE32C84F890055A535A28B262C649D41 | 64 |
±-------------±---------±---------±---------------------------------±--------+
3 rows in set (0.116 sec)
查看sql语句及执行计划
第一条:
obclient [tpcc]> select query_sql from oceanbase.gv$plan_cache_plan_stat 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 = 2 AND s_i_id = 4553 FOR UPDATE |
±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.022 sec)
查看执行计划
obclient [tpcc]> 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 = 4553 FOR UPDATE
-> ;
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
|0 |TABLE SCAN|bmsql_stock|10 |124026|
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 = 4553]),
access([bmsql_stock.__pk_increment], [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.004 sec)
实际执行计划:
obclient [tpcc]> 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 = ‘10.0.2.4’ AND port=2882 AND plan_id=73;
±---------±-----------±-------------±---------------±------------±-----±-------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ip | plan_depth | plan_line_id | operator | name | rows | cost | property |
±---------±-----------±-------------±---------------±------------±-----±-------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10.0.2.4 | 0 | 0 | PHY_TABLE_SCAN | bmsql_stock | 10 | 124025 | table_rows:100000, physical_range_rows:100000, logical_range_rows:100000, index_back_rows:0, output_rows:9, est_method:basic_stat, avaiable_index_name[bmsql_stock] |
±---------±-----------±-------------±---------------±------------±-----±-------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.024 sec)
执行计划一致
第二条:
obclient [tpcc]> select query_sql from oceanbase.gv$plan_cache_plan_stat where sql_id=‘E1F2BDA1D7391B757859ED3704E5AFB7’;
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| query_sql |
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| UPDATE bmsql_stock SET s_quantity = 102, s_ytd = s_ytd + 3, s_order_cnt = s_order_cnt + 1, s_remote_cnt = s_remote_cnt + 0 WHERE s_w_id = 1 AND s_i_id = 2947 |
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.013 sec)
查看执行计划:
obclient [tpcc]> explain UPDATE bmsql_stock SET s_quantity = 102, s_ytd = s_ytd + 3, s_order_cnt = s_order_cnt + 1, s_remote_cnt = s_remote_cnt + 0 WHERE s_w_id = 1 AND s_i_id = 2947;
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =============================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
|0 |UPDATE | |10 |130653|
|1 | TABLE SCAN|bmsql_stock|10 |130643|
Outputs & filters:
0 - output(nil), filter(nil), table_columns([{bmsql_stock: ({bmsql_stock: (bmsql_stock.__pk_increment, bmsql_stock.s_w_id, bmsql_stock.s_i_id, bmsql_stock.s_quantity, bmsql_stock.s_ytd, bmsql_stock.s_order_cnt, bmsql_stock.s_remote_cnt, 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)})}]),
update([bmsql_stock.s_quantity=?], [bmsql_stock.s_ytd=column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_ytd + 3, INT(-1, 0)))], [bmsql_stock.s_order_cnt=column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_order_cnt + 1, INT(-1, 0)))], [bmsql_stock.s_remote_cnt=column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_remote_cnt + 0, INT(-1, 0)))])
1 - output([bmsql_stock.__pk_increment], [bmsql_stock.s_w_id], [bmsql_stock.s_i_id], [bmsql_stock.s_quantity], [bmsql_stock.s_ytd], [bmsql_stock.s_order_cnt], [bmsql_stock.s_remote_cnt], [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], [?], [column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_ytd + 3, INT(-1, 0)))], [column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_order_cnt + 1, INT(-1, 0)))], [column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_remote_cnt + 0, INT(-1, 0)))]), filter([bmsql_stock.s_w_id = 1], [bmsql_stock.s_i_id = 2947]),
access([bmsql_stock.s_quantity], [bmsql_stock.s_ytd], [bmsql_stock.s_order_cnt], [bmsql_stock.s_remote_cnt], [bmsql_stock.__pk_increment], [bmsql_stock.s_w_id], [bmsql_stock.s_i_id], [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.009 sec)
实际执行计划:
obclient [tpcc]> 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 = ‘10.0.2.4’ AND port=2882 AND plan_id=75;
±---------±-----------±-------------±----------------±------------±-----±-------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ip | plan_depth | plan_line_id | operator | name | rows | cost | property |
±---------±-----------±-------------±----------------±------------±-----±-------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10.0.2.4 | 0 | 0 | PHY_UPDATE | NULL | 10 | 130652 | NULL |
| 10.0.2.4 | 1 | 1 | PHY_TABLE_SCAN | bmsql_stock | 10 | 130642 | table_rows:100000, physical_range_rows:100000, logical_range_rows:100000, index_back_rows:0, output_rows:9, est_method:basic_stat, avaiable_index_name[bmsql_stock] |
±---------±-----------±-------------±----------------±------------±-----±-------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.004 sec)
第三条:
obclient [tpcc]> select query_sql from oceanbase.gv$plan_cache_plan_stat where sql_id=‘AE32C84F890055A535A28B262C649D41’;
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| query_sql |
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 4 AND c_id = 2692 FOR UPDATE |
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.015 sec)
查看执行计划:
obclient [tpcc]> explain SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 4 AND c_id = 2692 FOR UPDATE;
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ===================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
|0 |TABLE SCAN|bmsql_customer(bmsql_customer_idx1)|30 |20867|
Outputs & filters:
0 - output([bmsql_customer.c_first], [bmsql_customer.c_middle], [bmsql_customer.c_last], [bmsql_customer.c_street_1], [bmsql_customer.c_street_2], [bmsql_customer.c_city], [bmsql_customer.c_state], [bmsql_customer.c_zip], [bmsql_customer.c_phone], [bmsql_customer.c_since], [bmsql_customer.c_credit], [bmsql_customer.c_credit_lim], [bmsql_customer.c_discount], [bmsql_customer.c_balance]), filter([bmsql_customer.c_id = 2692]),
access([bmsql_customer.__pk_increment], [bmsql_customer.c_w_id], [bmsql_customer.c_d_id], [bmsql_customer.c_id], [bmsql_customer.c_first], [bmsql_customer.c_middle], [bmsql_customer.c_last], [bmsql_customer.c_street_1], [bmsql_customer.c_street_2], [bmsql_customer.c_city], [bmsql_customer.c_state], [bmsql_customer.c_zip], [bmsql_customer.c_phone], [bmsql_customer.c_since], [bmsql_customer.c_credit], [bmsql_customer.c_credit_lim], [bmsql_customer.c_discount], [bmsql_customer.c_balance]), partitions(p0)
|
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.019 sec)
实际执行计划;
obclient [tpcc]> 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 = ‘10.0.2.4’ AND port=2882 AND plan_id=64;
±---------±-----------±-------------±---------------±------------------------------------±-----±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ip | plan_depth | plan_line_id | operator | name | rows | cost | property |
±---------±-----------±-------------±---------------±------------------------------------±-----±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10.0.2.4 | 0 | 0 | PHY_TABLE_SCAN | bmsql_customer(bmsql_customer_idx1) | 30 | 20866 | table_rows:9000, physical_range_rows:2947, logical_range_rows:2947, index_back_rows:2947, output_rows:29, est_method:local_storage, avaiable_index_name[bmsql_customer,bmsql_customer_idx1], estimation info[table_id:1100611139453789, (table_type:1, version:0-1666555187402389-1666555187402389, logical_rc:2947, physical_rc:2947), (table_type:0, version:1666554989549235-1666554989549235-9223372036854775807, logical_rc:0, physical_rc:0)] |
±---------±-----------±-------------±---------------±------------------------------------±-----±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.015 sec)
可以看出,解析执行计划和实际执行计划一致。