Oceanbase学习之---查看SQL执行计划
案例说明：
通过benchmarksql对oceanbase实例进行tpcc压测，获取Top SQL，分析其执行计划。
一、数据库环境
本案例采用单节点的集群环境：
[admin@node203 ~]$ obclient -h127.0.0.1 -P2883 -uroot@ob_mysql -Doceanbase
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 1
Server version: OceanBase_CE 4.2.0.0 (r100010022023081817-0bdf1c0c5674e88c5ae9a8d0ae4f8077465d7fae) (Built Aug 18 2023 17:32:49)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [oceanbase]> select version();
+------------------------------+
| version() |
+------------------------------+
| 5.7.25-OceanBase_CE-v4.2.0.0 |
+------------------------------+
1 row in set (0.001 sec)
二、部署benchmarksql运行TPCC
BenchmarkSQL是一款经典的开源数据库测试工具，其包含 TPCC 测试脚本，支持MySQL、Oracle 、EnterpriseDB、PostgreSQL以及SQL Server 等数据库的性能压力测试。
Benmarksql 下载网址：https://sourceforge.net/projects/benchmarksql/
1、创建oceanbase配置文件
1）创建配置文件
[root@node203 run]# cp props.ora props.ob
2）查看配置文件
[root@node203 run]# cat props.ob|grep -v ^$|grep -v ^#
db=oracle
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://127.0.0.1:2883/test1?useUnicode=true&characterEncoding=utf-8
user=root@ob_mysql
password=
warehouses=2
loadWorkers=4
terminals=5
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=5
//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
//osCollectorSSHAddr=user@dbhost
osCollectorDevices=net_eth0 blk_sda
三、执行TPCC压测
1、创建测试数据库和用户
obclient [oceanbase]> create database test1;
Query OK, 1 row affected (0.083 sec)
obclient [oceanbase]> CREATE USER 'test01'@'%' IDENTIFIED BY 'beijing';
Query OK, 0 rows affected (0.039 sec)
obclient [oceanbase]> grant all on test1
2、执行脚本创建测试表
[root@node203 run]# ./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;
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)
);
数据库端查看：
obclient [oceanbase]> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
obclient [test1]> show tables;
+------------------+
| Tables_in_test1 |
+------------------+
| 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.002 sec)
3、加载压测数据
1）加载数据之前需要进行数据库事务超时时间设置，否则在大事务灌数据时会报错Worker 000: ERROR: Transaction is timeout
obclient [oceanbase]> set global ob_trx_timeout=36000000000;
Query OK, 0 rows affected (0.069 sec)
obclient [oceanbase]> set global ob_query_timeout=36000000000;
Query OK, 0 rows affected (0.020 sec)
2）加载数据
[root@node203 run]# ./runLoader.sh props.ob
Starting BenchmarkSQL LoadData
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://127.0.0.1:2883/test1?useUnicode=true&characterEncoding=utf-8
user=root@ob_mysql
password=***********
warehouses=2
loadWorkers=4
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
数据库查看数据信息：
obclient [test1]> select count(*) from bmsql_warehouse;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.004 sec)
obclient [test1]> select count(*) from bmsql_new_order;
+----------+
| count(*) |
+----------+
| 18000 |
+----------+
1 row in set (0.011 sec)
4、执行TPCC压测
[root@node203 run]# ./runBenchmark.sh props.ob
15:25:16,427 [main] INFO jTPCC : Term-00,
15:25:16,433 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
15:25:16,434 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
15:25:16,434 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
15:25:16,434 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
15:25:16,434 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
15:25:16,435 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
15:25:16,435 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
.......
T15:30:34,126 [Thread-1] INFO jTPCC : Term-00, age: 28MB / 224MB
15:30:34,127 [Thread-1] INFO jTPCC : Term-00,
15:30:34,127 [Thread-1] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 10.4
15:30:34,127 [Thread-1] INFO jTPCC : Term-00, Measured tpmTOTAL = 26.66
15:30:34,127 [Thread-1] INFO jTPCC : Term-00, Session Start = 2023-09-04 15:25:16
15:30:34,127 [Thread-1] INFO jTPCC : Term-00, Session End = 2023-09-04 15:30:34
15:30:34,127 [Thread-1] INFO jTPCC : Term-00, Transaction Count = 140
四、数据库获取TOP SQL及执行计划
1、获取top SQL（按照平均执行时间）
obclient [test1]> SELECT sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time,
-> round(avg(execute_time)) avg_exec_time,
-> s.svr_ip,
-> s.svr_port,
-> s.tenant_id,
-> s.plan_id
-> FROM oceanbase.gv$ob_sql_audit s
-> WHERE 1=1
-> and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) )
-> GROUP BY sql_id order by avg_exec_time desc limit 10;
+----------------------------------+----------+------------------+---------------+---------------+----------+-----------+---------+
| sql_id | count(*) | avg_elapsed_time | avg_exec_time | svr_ip | svr_port | tenant_id | plan_id |
+----------------------------------+----------+------------------+---------------+---------------+----------+-----------+---------+
| F59A700FA168324279B0DBC25E19760F | 1 | 1828512 | 1828326 | 192.168.1.203 | 2882 | 1008 | 991 |
| 5984364296F35BE1B71CD5622426385A | 2 | 1088057 | 1016201 | 192.168.1.203 | 2882 | 1008 | 981 |
| 482BA7822AE7BE644CEBEB55213E7284 | 11 | 999277 | 963763 | 192.168.1.203 | 2882 | 1008 | 986 |
| EC66B09D06D688727D0F999BFCFF5348 | 11 | 862890 | 816105 | 192.168.1.203 | 2882 | 1008 | 987 |
| 7229213613983BC5FDA15AD11EC70D01 | 151 | 778590 | 745408 | 192.168.1.203 | 2882 | 1008 | 973 |
| E1F2BDA1D7391B757859ED3704E5AFB7 | 156 | 721671 | 712495 | 192.168.1.203 | 2882 | 1008 | 976 |
| DEF7CF71AC17B0380AEE912A8FC1E307 | 1 | 612028 | 611690 | 192.168.1.203 | 2882 | 1008 | 966 |
| 2C77BA43893FFF91707B336D22240EAC | 2 | 552449 | 549931 | 192.168.1.203 | 2882 | 1008 | 975 |
| FF6301F5EC32CF6188BF1C562FDFCEE6 | 12 | 609616 | 534676 | 192.168.1.203 | 2882 | 1008 | 968 |
| AB92F1B97A8D4DADFD477BB52C65A00B | 13 | 518598 | 482895 | 192.168.1.203 | 2882 | 1008 | 960 |
+----------------------------------+----------+------------------+---------------+---------------+----------+-----------+---------+
10 rows in set (0.070 sec)
2、查看top sql的sql文本
obclient [test1]> select distinct query_sql from oceanbase.gv$ob_sql_audit
where sql_id='F59A700FA168324279B0DBC25E19760F';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| query_sql |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 < 19 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 = 6 )) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.042 sec)
obclient [test1]> select distinct query_sql from oceanbase.gv$ob_sql_audit
where sql_id='5984364296F35BE1B71CD5622426385A';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| query_sql |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT ol_i_id, ol_supply_w_id, ol_quantity,ol_amount, ol_delivery_d FROM bmsql_order_line WHERE ol_w_id = 2 AND ol_d_id = 2 AND ol_o_id = 1327 ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number |
obclient [test1]> select distinct query_sql from oceanbase.gv$ob_sql_audit
where sql_id='482BA7822AE7BE644CEBEB55213E7284';
+-------------------------------------------------------------------------------------------------------------------------------------+
| query_sql |
+-------------------------------------------------------------------------------------------------------------------------------------+
| UPDATE bmsql_order_line SET ol_delivery_d = '2023-09-04 19:29:30.481' WHERE ol_w_id = 2 AND ol_d_id = 10 AND ol_o_id = 2106 |
3、查看top sql执行计划
1）查看top 1执行计划
# 实际执行计划
obclient [test1]> SELECT * FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_EXPLAIN
-> WHERE tenant_id = 1008 AND plan_id = 991 and svr_ip='192.168.1.203' and svr_port='2882'\G
*************************** 1. row ***************************
TENANT_ID: 1008
SVR_IP: 192.168.1.203
SVR_PORT: 2882
PLAN_ID: 991
PLAN_DEPTH: 0
PLAN_LINE_ID: 0
OPERATOR: PHY_SCALAR_AGGREGATE
NAME: NULL
ROWS: 1
COST: 116712
PROPERTY: NULL
*************************** 2. row ***************************
TENANT_ID: 1008
SVR_IP: 192.168.1.203
SVR_PORT: 2882
PLAN_ID: 991
PLAN_DEPTH: 1
PLAN_LINE_ID: 1
OPERATOR: PHY_HASH_JOIN
NAME: NULL
ROWS: 1
COST: 116712
PROPERTY: NULL
*************************** 3. row ***************************
TENANT_ID: 1008
SVR_IP: 192.168.1.203
SVR_PORT: 2882
PLAN_ID: 991
PLAN_DEPTH: 2
PLAN_LINE_ID: 2
OPERATOR: PHY_TABLE_SCAN
NAME: bmsql_stock
ROWS: 1
COST: 14644
PROPERTY: table_rows:200000, physical_range_rows:200700, logical_range_rows:200000, index_back_rows:0, output_rows:0, avaiable_index_name[bmsql_stock], estimation info[table_id:500033, (table_type:11, version:-1--1--1, logical_rc:200000, physical_rc:200000), (table_type:0, version:-1--1--1, logical_rc:0, physical_rc:700)]
*************************** 4. row ***************************
TENANT_ID: 1008
SVR_IP: 192.168.1.203
SVR_PORT: 2882
PLAN_ID: 991
PLAN_DEPTH: 2
PLAN_LINE_ID: 3
OPERATOR: PHY_SUBPLAN_SCAN
NAME: NULL
ROWS: 4832
COST: 101663
PROPERTY: NULL
*************************** 5. row ***************************
TENANT_ID: 1008
SVR_IP: 192.168.1.203
SVR_PORT: 2882
PLAN_ID: 991
PLAN_DEPTH: 3
PLAN_LINE_ID: 4
OPERATOR: PHY_NESTED_LOOP_JOIN
NAME: NULL
ROWS: 4832
COST: 101650
PROPERTY: NULL
*************************** 6. row ***************************
TENANT_ID: 1008
SVR_IP: 192.168.1.203
SVR_PORT: 2882
PLAN_ID: 991
PLAN_DEPTH: 4
PLAN_LINE_ID: 5
OPERATOR: PHY_TABLE_SCAN
NAME: bmsql_order_line
ROWS: 137330
COST: 100387
PROPERTY: table_rows:1200816, physical_range_rows:1201718, logical_range_rows:1200816, index_back_rows:0, output_rows:137329, avaiable_index_name[bmsql_order_line], estimation info[table_id:500031, (table_type:10, version:-1--1--1, logical_rc:600070, physical_rc:600070), (table_type:11, version:-1--1--1, logical_rc:600650, physical_rc:601456), (table_type:0, version:-1--1--1, logical_rc:96, physical_rc:192)]
*************************** 7. row ***************************
TENANT_ID: 1008
SVR_IP: 192.168.1.203
SVR_PORT: 2882
PLAN_ID: 991
PLAN_DEPTH: 4
PLAN_LINE_ID: 6
OPERATOR: PHY_MATERIAL
NAME: NULL
ROWS: 1
COST: 7
PROPERTY: NULL
*************************** 8. row ***************************
TENANT_ID: 1008
SVR_IP: 192.168.1.203
SVR_PORT: 2882
PLAN_ID: 991
PLAN_DEPTH: 5
PLAN_LINE_ID: 7
OPERATOR: PHY_TABLE_SCAN
NAME: bmsql_district
ROWS: 1
COST: 7
PROPERTY: table_rows:19, physical_range_rows:41, logical_range_rows:19, index_back_rows:0, output_rows:0, avaiable_index_name[bmsql_district], estimation info[table_id:500025, (table_type:11, version:-1--1--1, logical_rc:18, physical_rc:18), (table_type:12, version:-1--1--1, logical_rc:1, physical_rc:5), (table_type:0, version:-1--1--1, logical_rc:0, physical_rc:18)]
8 rows in set (0.028 sec)
# 解释执行计划
obclient [test1]> 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 < 19 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 = 6 ) );
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ===================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------------------------- |
| |0 |SCALAR GROUP BY | |1 |116863 | |
| |1 |└─HASH SEMI JOIN | |1 |116863 | |
| |2 | ├─TABLE FULL SCAN |bmsql_stock |1 |14682 | |
| |3 | └─SUBPLAN SCAN |VIEW1 |4835 |101777 | |
| |4 | └─NESTED-LOOP JOIN | |4835 |101764 | |
| |5 | ├─TABLE FULL SCAN |bmsql_order_line|137401 |100500 | |
| |6 | └─MATERIAL | |1 |8 | |
| |7 | └─TABLE FULL SCAN|bmsql_district |1 |8 | |
| ===================================================================== |
| 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 |
| equal_conds([bmsql_stock.s_i_id = VIEW1.ol_i_id]), other_conds(nil) |
| 2 - output([bmsql_stock.s_i_id]), filter([bmsql_stock.s_w_id = 1], [bmsql_stock.s_quantity < 19]), rowset=256 |
| access([bmsql_stock.s_w_id], [bmsql_stock.s_quantity], [bmsql_stock.s_i_id]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false,false], |
| range_key([bmsql_stock.__pk_increment]), range(MIN ; MAX)always true |
| 3 - output([VIEW1.ol_i_id]), filter(nil), rowset=256 |
| access([VIEW1.ol_i_id]) |
| 4 - output([bmsql_order_line.ol_i_id]), filter(nil), rowset=256 |
| conds([bmsql_order_line.ol_o_id >= bmsql_district.d_next_o_id - 20], [bmsql_order_line.ol_o_id < bmsql_district.d_next_o_id]), nl_params_(nil), use_batch=false |
| 5 - output([bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), filter([bmsql_order_line.ol_d_id = 6], [bmsql_order_line.ol_w_id = 1]), rowset=256 |
| access([bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id], [bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false,false], |
| range_key([bmsql_order_line.__pk_increment]), range(MIN ; MAX)always true |
| 6 - output([bmsql_district.d_next_o_id], [bmsql_district.d_next_o_id - 20]), filter(nil), rowset=256 |
| 7 - output([bmsql_district.d_next_o_id], [bmsql_district.d_next_o_id - 20]), filter([bmsql_district.d_id = 6], [bmsql_district.d_next_o_id > bmsql_district.d_next_o_id |
| - 20], [bmsql_district.d_w_id = 1]), rowset=256 |
| access([bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_next_o_id]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false], |
| range_key([bmsql_district.__pk_increment]), range(MIN ; MAX)always true |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
36 rows in set (0.156 sec)
# 从以上对比可以获悉，SQL实际执行计划和解释执行计划基本是一致的。
2）查看top 2执行计划
# 实际执行计划
obclient [test1]> SELECT * FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_EXPLAIN
WHERE tenant_id = 1008 AND plan_id = 981 and svr_ip='192.168.1.203' and svr_port='2882'\G
*************************** 1. row ***************************
TENANT_ID: 1008
SVR_IP: 192.168.1.203
SVR_PORT: 2882
PLAN_ID: 981
PLAN_DEPTH: 0
PLAN_LINE_ID: 0
OPERATOR: PHY_SORT
NAME: NULL
ROWS: 2
COST: 115630
PROPERTY: NULL
*************************** 2. row ***************************
TENANT_ID: 1008
SVR_IP: 192.168.1.203
SVR_PORT: 2882
PLAN_ID: 981
PLAN_DEPTH: 1
PLAN_LINE_ID: 1
OPERATOR: PHY_TABLE_SCAN
NAME: bmsql_order_line
ROWS: 2
COST: 115630
PROPERTY: table_rows:1200726, physical_range_rows:1201532, logical_range_rows:1200726, index_back_rows:0, output_rows:1, avaiable_index_name[bmsql_order_line], estimation info[table_id:500031, (table_type:10, version:-1--1--1, logical_rc:600070, physical_rc:600070), (table_type:11, version:-1--1--1, logical_rc:600650, physical_rc:601456), (table_type:0, version:-1--1--1, logical_rc:6, physical_rc:6)]
2 rows in set (0.005 sec)
# 解释执行计划
obclient [test1]> explain
-> SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_delivery_d FROM bmsql_order_line WHERE ol_w_id = 2 AND ol_d_id = 2 AND ol_o_id = 1327 ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number ;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------- |
| |0 |SORT | |2 |115891 | |
| |1 |└─TABLE FULL SCAN|bmsql_order_line|2 |115891 | |
| ============================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([bmsql_order_line.ol_i_id], [bmsql_order_line.ol_supply_w_id], [bmsql_order_line.ol_quantity], [bmsql_order_line.ol_amount], [bmsql_order_line.ol_delivery_d]), filter(nil), rowset=256 |
| sort_keys([bmsql_order_line.ol_number, ASC]) |
| 1 - output([bmsql_order_line.ol_i_id], [bmsql_order_line.ol_supply_w_id], [bmsql_order_line.ol_quantity], [bmsql_order_line.ol_amount], [bmsql_order_line.ol_delivery_d], |
| [bmsql_order_line.ol_number]), filter([bmsql_order_line.ol_w_id = 2], [bmsql_order_line.ol_d_id = 2], [bmsql_order_line.ol_o_id = 1327]), rowset=256 |
| access([bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id], [bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id], [bmsql_order_line.ol_supply_w_id], |
| [bmsql_order_line.ol_quantity], [bmsql_order_line.ol_amount], [bmsql_order_line.ol_delivery_d], [bmsql_order_line.ol_number]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false], |
| range_key([bmsql_order_line.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
16 rows in set (0.195 sec)
# 从以上对比可以获悉，SQL实际执行计划和解释执行计划基本是一致的。
3）查看top 3执行计划
#实际执行计划
obclient [test1]> SELECT * FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_EXPLAIN
WHERE tenant_id = 1008 AND plan_id = 986 and svr_ip='192.168.1.203' and svr_port='2882'\G
*************************** 1. row ***************************
TENANT_ID: 1008
SVR_IP: 192.168.1.203
SVR_PORT: 2882
PLAN_ID: 986
PLAN_DEPTH: 0
PLAN_LINE_ID: 0
OPERATOR: PHY_UPDATE
NAME: NULL
ROWS: 2
COST: 115663
PROPERTY: NULL
*************************** 2. row ***************************
TENANT_ID: 1008
SVR_IP: 192.168.1.203
SVR_PORT: 2882
PLAN_ID: 986
PLAN_DEPTH: 1
PLAN_LINE_ID: 1
OPERATOR: PHY_TABLE_SCAN
NAME: bmsql_order_line
ROWS: 2
COST: 115630
PROPERTY: table_rows:1200726, physical_range_rows:1201532, logical_range_rows:1200726, index_back_rows:0, output_rows:1, avaiable_index_name[bmsql_order_line], estimation info[table_id:500031, (table_type:10, version:-1--1--1, logical_rc:600070, physical_rc:600070), (table_type:11, version:-1--1--1, logical_rc:600650, physical_rc:601456), (table_type:0, version:-1--1--1, logical_rc:6, physical_rc:6)]
# 解释执行计划
obclient [test1]> explain
-> UPDATE bmsql_order_line SET ol_delivery_d = '2023-09-04 19:29:30.481'
WHERE ol_w_id = 2 AND ol_d_id = 10 AND ol_o_id = 2106 ;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------- |
| |0 |UPDATE | |2 |115933 | |
| |1 |└─TABLE FULL SCAN|bmsql_order_line|2 |115900 | |
| ============================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output(nil), filter(nil) |
| table_columns([{bmsql_order_line: ({bmsql_order_line: (bmsql_order_line.__pk_increment, bmsql_order_line.ol_w_id, bmsql_order_line.ol_d_id, bmsql_order_line.ol_o_id, |
| bmsql_order_line.ol_number, bmsql_order_line.ol_i_id, bmsql_order_line.ol_delivery_d, bmsql_order_line.ol_amount, bmsql_order_line.ol_supply_w_id, bmsql_order_line.ol_quantity, |
| bmsql_order_line.ol_dist_info)})}]), |
| update([bmsql_order_line.ol_delivery_d=column_conv(TIMESTAMP,PS:(19,0),NULL,cast('2023-09-04 19:29:30.481', TIMESTAMP(-1, -1)))]) |
| 1 - output([bmsql_order_line.__pk_increment], [bmsql_order_line.ol_delivery_d], [bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id], [bmsql_order_line.ol_o_id], |
| [bmsql_order_line.ol_number], [bmsql_order_line.ol_i_id], [bmsql_order_line.ol_amount], [bmsql_order_line.ol_supply_w_id], [bmsql_order_line.ol_quantity], |
| [bmsql_order_line.ol_dist_info]), filter([bmsql_order_line.ol_w_id = 2], [bmsql_order_line.ol_d_id = 10], [bmsql_order_line.ol_o_id = 2106]), rowset=256 |
| access([bmsql_order_line.__pk_increment], [bmsql_order_line.ol_delivery_d], [bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id], [bmsql_order_line.ol_o_id], |
| [bmsql_order_line.ol_number], [bmsql_order_line.ol_i_id], [bmsql_order_line.ol_amount], [bmsql_order_line.ol_supply_w_id], [bmsql_order_line.ol_quantity], |
| [bmsql_order_line.ol_dist_info]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false], |
| range_key([bmsql_order_line.__pk_increment]), range(MIN ; MAX)always true |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
21 rows in set (0.003 sec)
# 从以上对比可以获悉，SQL实际执行计划和解释执行计划基本是一致的。
