一、环境准备
下载并安装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=abao
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
# ------------------------------------------------------------
# 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, (c) 2003, Raul Barbosa
04:01:43,898 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
04:01:43,907 [main] INFO jTPCC : Term-00, (c) 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-11-13 14:01:45
04:03:23,157 [Thread-1] INFO jTPCC : Term-00, Session End = 2022-11-13 14:04: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 | 7229213242383BC5FDA15AD11SFJOS1 | 32 |
| 2120593 | 10.0.2.4 | 2882 | E1F2BDA1D7391B453259ED37D9923DL | 45 |
| 630255 | 10.0.2.4 | 2882 | AE32C84F890055A535A28BDJFSLJLD3 | 40|
+--------------+----------+----------+----------------------------------+---------+
3 rows in set (0.116 sec)
查看sql语句及执行计划
第一条：
obclient [tpcc]> select query_sql from oceanbase.gv$plan_cache_plan_stat where sql_id='7229213242383BC5FDA15AD11SFJOS1 '; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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 = '172.0.0.1' AND port=2882 AND plan_id=32 ; +----------+------------+--------------+----------------+-------------+------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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='E1F2BDA1D7391B453259ED37D9923DL '; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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 = '127.0.0.1' AND port=2882 AND plan_id=45 ; +----------+------------+--------------+-----------------+-------------+------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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='AE32C84F890055A535A28BDJFSLJLD3 '; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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 = '127.0.0.1' AND port=2882 AND plan_id=40; +----------+------------+--------------+----------------+-------------------------------------+------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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)
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
