本文主要是介绍实践练习六（必选）：查看 OceanBase 执行计划，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
实验目录：
（必选）使用 BenmarkSQL 运行 TPC-C ，并发数不用很高，5~10 并发即可（根据机器资源）。
（必选）分析 TPC-C TOP SQL，并查看 3条 SQL 的 解析执行计划 和 实际执行计划。
使用 BenmarkSQL 运行 TPC-C
准备测试用户
CREATE RESOURCE UNIT tpcc_unit max_cpu 1, memory_size '2g';
CREATE RESOURCE POOL tpcc_pool unit = 'tpcc_unit', unit_num = 1, zone_list=('zone1','zone2','zone3');
CREATE TENANT tpcc_tenant resource_pool_list=('tpcc_pool'), zone_list('zone1', 'zone2', 'zone3'), primary_zone=RANDOM, locality='F@zone1,F@zone2,F@zone3' set variables ob_compatibility_mode='mysql', ob_tcp_invited_nodes='%';
ANT部署
# 下载安装
wget http://archive.apache.org/dist/ant/binaries/apache-ant-1.10.6-bin.zip
unzip apache-ant-1.10.6-bin.zip -d /usr/local/
# 设置环境变量
sudo vim /etc/profile
#ant
export ANT_HOME=/usr/local/apache-ant-1.10.6
export PATH=/usr/local/apache-ant-1.10.6/bin:$PATH
source /etc/profile
# 检查是否安装成功
ant -version
Apache Ant(TM) version 1.10.6 compiled on May 2 2019
Benchmark SQL5 部署
项目地址：https://sourceforge.net/projects/benchmarksql/
wget https://nchc.dl.sourceforge.net/project/benchmarksql/benchmarksql-5.0.zip
unzip benchmarksql-5.0.zip -d /usr/local/# 进入 Benchmark SQL 解压后的目录，使用 Ant 编译 Benchmark SQL：
cd /usr/local/benchmarksql-5.0
ant
# 终端输出如下
Buildfile: /usr/local/benchmarksql-5.0/build.xml
init:[mkdir] Created dir: /usr/local/benchmarksql-5.0/build
compile:[javac] Compiling 11 source files to /usr/local/benchmarksql-5.0/build
dist:[mkdir] Created dir: /usr/local/benchmarksql-5.0/dist[jar] Building jar: /usr/local/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar
BUILD SUCCESSFUL
Total time: 1 second
oceanbase适配性改造
由于 Benchmark SQL5 不支持 OceanBase 数据库的 TPC-C 测试，需要如下修改 BenchMarkSQL5 部分源码。
- 修改 benchmarksql-5.0/src/client/jTPCC.java 文件，增加 OceanBase 数据库相关内容。
vim /usr/local/benchmarksql-5.0/src/client/jTPCC.java
if (iDB.equals("firebird"))dbType = DB_FIREBIRD;else if (iDB.equals("oracle"))dbType = DB_ORACLE;else if (iDB.equals("postgres"))dbType = DB_POSTGRES;else if (iDB.equals("oceanbase")) //增加 OceanBase 数据库相关内容dbType = DB_OCEANBASE;else{log.error("unknown database type '" + iDB + "'");return;}
- 修改 benchmarksql-5.0/src/client/jTPCCConfig.java 文件，增加 OceanBase 数据库类型。
vim /usr/local/benchmarksql-5.0/src/client/jTPCCConfig.java
public final static int
DB_UNKNOWN = 0,
DB_FIREBIRD = 1,
DB_ORACLE = 2,
DB_POSTGRES = 3,
DB_OCEANBASE = 4;
- 修改 benchmarksql-5.0/src/client/jTPCCConnection.java 文件，在 SQL 子查询增加 “AS L” 别名。
default:stmtStockLevelSelectLow = dbConn.prepareStatement("SELECT count(*) AS low_stock FROM (" +" SELECT s_w_id, s_i_id, s_quantity " +" FROM bmsql_stock " +" WHERE s_w_id = ? AND s_quantity < ? 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 = ? AND d_id = ? " +" ) " +" )AS L"); //增加 "AS L" 别名break;
- 重新编译修改后的源码
cd /usr/local/benchmarksql-5.0
ant - 配置文件
vi /usr/local/benchmarksql-5.0/run/prop.oceanbase
db=oceanbase
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://$host_ip:$port/tpcc?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=30000000
//请填写完整user信息
user=$user@$tenant
password=*****
warehouses=1000
loadWorkers=40
terminals=600
database=$db_name
//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
//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec
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
修改文件：benchmarksql-5.0/run/funcs.sh，添加 OceanBase 数据库类型。
vi /usr/local/benchmarksql-5.0/run/funcs.sh
function setCP()
{case "$(getProp db)" in
firebird)cp="../lib/firebird/*:../lib/*";;
oracle)cp="../lib/oracle/*"if [ ! -z "${ORACLE_HOME}" -a -d ${ORACLE_HOME}/lib ] ; thencp="${cp}:${ORACLE_HOME}/lib/*"ficp="${cp}:../lib/*";;
postgres)cp="../lib/postgres/*:../lib/*";;
oceanbase) #添加 OceanBase 数据库类型cp="../lib/oceanbase/*:../lib/*";;esacmyCP=".:${cp}:../dist/*"export myCP
}...省略case "$(getProp db)" infirebird|oracle|postgres|oceanbase) #添加 OceanBase 数据库类型;;"") echo "ERROR: missing db= config option in ${PROPS}" >&2exit 1;;*) echo "ERROR: unsupported database type 'db=$(getProp db)' in ${PROPS}" >&2exit 1;;
esac
- 添加 mysql java connector 驱动
推荐 mysql-connector-java-5.1.47.jar。
cd /usr/local/benchmarksql-5.0/
mkdir lib/oceanbase/
wget https://downloads.mysql.com/archives/get/p/3/file/mysql-connector-java-5.1.47.zip
cp mysql-connector-java-5.1.47/mysql-connector-java-5.1.47.jar /usr/local/benchmarksql-5.0/lib/oceanbase/- 修改 benchmarksql-5.0/run/runDatabaseBuild.sh。
AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"
修改为：
AFTER_LOAD="indexCreates buildFinish"
- 改造 BenchMarkSQL5 中的 SQL。
备份并重写 benchmarksql-5.0/run/sql.common/tableCreates.sql
CREATE TABLE bmsql_config (cfg_name varchar(30) PRIMARY KEY,cfg_value varchar(50)
);CREATE TABLEGROUP IF NOT EXISTS tpcc_group binding true partition by hash partitions 96;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),PRIMARY KEY(w_id)
)tablegroup='tpcc_group' partition by hash(w_id) partitions 96;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),PRIMARY KEY (d_w_id, d_id)
)tablegroup='tpcc_group' partition by hash(d_w_id) partitions 96;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),PRIMARY KEY (c_w_id, c_d_id, c_id)
)tablegroup='tpcc_group' partition by hash(c_w_id) partitions 96;CREATE TABLE bmsql_history (hist_id integer AUTO_INCREMENT,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)
)tablegroup='tpcc_group' partition by hash(h_w_id) partitions 96;CREATE TABLE bmsql_new_order (no_w_id integer not null ,no_d_id integer not null,no_o_id integer not null,PRIMARY KEY (no_w_id, no_d_id, no_o_id)
)tablegroup='tpcc_group' partition by hash(no_w_id) partitions 96;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,PRIMARY KEY (o_w_id, o_d_id, o_id)
)tablegroup='tpcc_group' partition by hash(o_w_id) partitions 96;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),PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number)
)tablegroup='tpcc_group' partition by hash(ol_w_id) partitions 96;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,PRIMARY KEY (i_id)
);CREATE TABLE bmsql_stock (s_w_id integer not null,s_i_id integer not null,s_quantity integer,s_ytd integer,s_order_cnt integer,s_remote_cnt integer,s_data varchar(50),s_dist_01 char(24),s_dist_02 char(24),s_dist_03 char(24),s_dist_04 char(24),s_dist_05 char(24),s_dist_06 char(24),s_dist_07 char(24),s_dist_08 char(24),s_dist_09 char(24),s_dist_10 char(24),PRIMARY KEY (s_w_id, s_i_id)
)tablegroup='tpcc_group' partition by hash(s_w_id) partitions 96;
备份并重写 benchmarksql-5.0/run/sql.common/tableDrops.sql
DROP TABLE bmsql_config;
DROP TABLE bmsql_new_order;
DROP TABLE bmsql_order_line;
DROP TABLE bmsql_oorder;
DROP TABLE bmsql_history;
DROP TABLE bmsql_customer;
DROP TABLE bmsql_stock;
DROP TABLE bmsql_item;
DROP TABLE bmsql_district;
DROP TABLE bmsql_warehouse;
DROP TABLEGROUP tpcc_group;
备份并重写 benchmarksql-5.0/run/sql.common/indexCreates.sql。
CREATE INDEX bmsql_customer_idx1 ON bmsql_customer (c_w_id, c_d_id, c_last, c_first) local;
CREATE INDEX bmsql_oorder_idx1 ON bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local;
备份并重写 benchmarksql-5.0/run/sql.common/indexDrops.sql
ALTER TABLE bmsql_customer DROP INDEX bmsql_customer_idx1;
ALTER TABLE bmsql_oorder DROP INDEX bmsql_oorder_idx1;
环境调优
# 调优请在 sys 租户下执行: obclient -h$host_ip -P$host_port -uroot@sys -A
# ODP 调优
ALTER proxyconfig SET proxy_mem_limited='4G';
ALTER proxyconfig set enable_compression_protocol=false;
# observer 调优
ALTER system SET enable_sql_audit=false;
select sleep(5);
ALTER system SET enable_perf_event=false;
ALTER system SET syslog_level='PERF';
alter system set enable_record_trace_log=false;
# 重启集群
obd cluster restart obtest
测试数据导入
obclient [(none)]> create database tpcc;
cd /usr/local/benchmarksql-5.0/run
./runDatabaseDestroy.sh prop.oceanbase
./runDatabaseBuild.sh prop.oceanbase
# Tips
# 如果服务器负载一直很高（特别是单机模拟多节点部署的情况），可以尝试调低cpu_count为限定值。
obclient [(none)]> SHOW PARAMETERS LIKE 'cpu_count';
+-------+--------------+-------------+----------+-----------+-----------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+---------------+-----------+
| zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level | default_value | isdefault |
+-------+----------+-----------------+----------+-----------+-----------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+---------------+-----------+
| zone2 | observer | 192.168.111.111 | 2882 | cpu_count | NULL | 0 | the number of CPU\'s in the system. If this parameter is set to zero, the number will be set according to sysconf; otherwise, this parameter is used. Range: [0,+∞) in integer | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | 0 | 1 |
| zone3 | observer | 192.168.111.111 | 3882 | cpu_count | NULL | 0 | the number of CPU\'s in the system. If this parameter is set to zero, the number will be set according to sysconf; otherwise, this parameter is used. Range: [0,+∞) in integer | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | 0 | 1 |
| zone1 | observer | 192.168.111.111 | 1882 | cpu_count | NULL | 0 | the number of CPU\'s in the system. If this parameter is set to zero, the number will be set according to sysconf; otherwise, this parameter is used. Range: [0,+∞) in integer | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | 0 | 1 |
+-------+----------+-----------------+----------+-----------+-----------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+---------------+-----------+
3 rows in set (0.008 sec)obclient [(none)]> alter system set cpu_count = 8;
Query OK, 0 rows affected (38.422 sec)
执行压测
cd /usr/local/benchmarksql-5.0/run
./runBenchmark.sh prop.oceanbase
解析计划
# 查看TOP3的SQL
SELECT sql_idFROM gv$ob_sql_audit sWHERE 1=1 and db_name= 'tpccob'and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) )GROUP BY sql_idorder by avg_elapsed_time desc limit 3;
+----------------------------------+
| sql_id |
+----------------------------------+
| F59A700FA168324279B0DBC25E19760F |
| FC3FED8CCB2946DE54F1C5BA3656023C |
| F4585305C4CB9B091C750826A7DEDD13 |
+----------------------------------+
3 rows in set (0.08 sec)
# 定位具体SQL
MySQL [oceanbase]> select QUERY_SQL from gv$ob_sql_audit where sql_id='F59A700FA168324279B0DBC25E19760F' limit 1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY_SQL |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 2 AND s_quantity < 13 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 = 2 AND d_id = 8 ) ) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)MySQL [oceanbase]> select QUERY_SQL from gv$ob_sql_audit where sql_id='FC3FED8CCB2946DE54F1C5BA3656023C' limit 1;
+----------------------------------------------------------------------------------------------------+
| QUERY_SQL |
+----------------------------------------------------------------------------------------------------+
| SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = 1 AND d_id = 1 FOR UPDATE |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)MySQL [oceanbase]> select QUERY_SQL from gv$ob_sql_audit where sql_id='F4585305C4CB9B091C750826A7DEDD13' limit 1;
+---------------------------------------------------------------------------------------+
| QUERY_SQL |
+---------------------------------------------------------------------------------------+
| UPDATE bmsql_district SET d_ytd = d_ytd + 26.58 WHERE d_w_id = 2 AND d_id = 6 |
+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)# 查看执行计划
MySQL [tpccob]> explain SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 2 AND s_quantity < 18 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 = 2 AND d_id = 10 ) ) ;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ====================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ---------------------------------------------------------------------- |
| |0 |SCALAR GROUP BY | |1 |74 | |
| |1 |└─NESTED-LOOP JOIN | |3 |74 | |
| |2 | ├─SUBPLAN SCAN |VIEW2 |3 |15 | |
| |3 | │ └─HASH DISTINCT | |3 |15 | |
| |4 | │ └─NESTED-LOOP JOIN | |3 |14 | |
| |5 | │ ├─TABLE RANGE SCAN|bmsql_order_line|57 |10 | |
| |6 | │ └─MATERIAL | |1 |3 | |
| |7 | │ └─TABLE GET |bmsql_district |1 |3 | |
| |8 | └─DISTRIBUTED TABLE GET |bmsql_stock |1 |21 | |
| ====================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256 |
| group(nil), agg_func([T_FUN_COUNT(*)]) |
| 1 - output(nil), filter(nil), rowset=256 |
| conds(nil), nl_params_([VIEW2.VIEW1.ol_i_id(:3)]), use_batch=false |
| 2 - output([VIEW2.VIEW1.ol_i_id]), filter(nil), rowset=256 |
| access([VIEW2.VIEW1.ol_i_id]) |
| 3 - output([bmsql_order_line.ol_i_id]), filter(nil), rowset=256 |
| distinct([bmsql_order_line.ol_i_id]) |
| 4 - output([bmsql_order_line.ol_i_id]), filter(nil), rowset=256 |
| conds([bmsql_order_line.ol_o_id < bmsql_district.d_next_o_id], [bmsql_order_line.ol_o_id >= bmsql_district.d_next_o_id - 20]), nl_params_(nil), use_batch=false |
| 5 - output([bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), filter(nil), rowset=256 |
| access([bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id], [bmsql_order_line.ol_o_id], [bmsql_order_line.ol_number]), range(2,10,MIN,MIN ; |
| 2,10,MAX,MAX), |
| range_cond([bmsql_order_line.ol_w_id = 2], [bmsql_order_line.ol_d_id = 10]) |
| 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_next_o_id > bmsql_district.d_next_o_id - 20]), rowset=256 |
| access([bmsql_district.d_next_o_id]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([bmsql_district.d_w_id], [bmsql_district.d_id]), range[2,10 ; 2,10], |
| range_cond([bmsql_district.d_w_id = 2], [bmsql_district.d_id = 10]) |
| 8 - output(nil), filter([bmsql_stock.s_quantity < 18]), rowset=256 |
| access([bmsql_stock.s_quantity]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([bmsql_stock.s_w_id], [bmsql_stock.s_i_id]), range(MIN ; MAX), |
| range_cond([bmsql_stock.s_w_id = 2], [bmsql_stock.s_i_id = :3]) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+explain SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = 1 AND d_id = 6 FOR UPDATE;
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| ======================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------- |
| |0 |MATERIAL | |1 |3 | |
| |1 |└─FOR UPDATE | |1 |3 | |
| |2 | └─TABLE GET|bmsql_district|1 |3 | |
| ======================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([bmsql_district.d_tax], [bmsql_district.d_next_o_id]), filter(nil), rowset=16 |
| 1 - output([bmsql_district.d_tax], [bmsql_district.d_next_o_id]), filter(nil), rowset=16 |
| lock tables(bmsql_district) |
| 2 - output([bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_tax], [bmsql_district.d_next_o_id]), filter(nil), rowset=16 |
| access([bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_tax], [bmsql_district.d_next_o_id]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([bmsql_district.d_w_id], [bmsql_district.d_id]), range[1,6 ; 1,6], |
| range_cond([bmsql_district.d_w_id = 1], [bmsql_district.d_id = 6]) |
+--------------------------------------------------------------------------------------------------------------------------------------------+explain UPDATE bmsql_district SET d_ytd = d_ytd + 3736.43 WHERE d_w_id = 2 AND d_id = 9;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================ |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------ |
| |0 |DISTRIBUTED UPDATE| |1 |35 | |
| |1 |└─TABLE GET |bmsql_district|1 |3 | |
| ============================================================ |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output(nil), filter(nil) |
| table_columns([{bmsql_district: ({bmsql_district: (bmsql_district.d_w_id, bmsql_district.d_id, bmsql_district.d_ytd, bmsql_district.d_tax, bmsql_district.d_next_o_id, |
| bmsql_district.d_name, bmsql_district.d_street_1, bmsql_district.d_street_2, bmsql_district.d_city, bmsql_district.d_state, bmsql_district.d_zip)})}]), |
| |
| update([bmsql_district.d_ytd=column_conv(DECIMAL,PS:(12,2),NULL,bmsql_district.d_ytd + 3736.43)]) |
| 1 - output([bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_ytd], [bmsql_district.d_tax], [bmsql_district.d_next_o_id], [bmsql_district.d_name], |
| [bmsql_district.d_street_1], [bmsql_district.d_street_2], [bmsql_district.d_city], [bmsql_district.d_state], [bmsql_district.d_zip]), filter(nil), rowset=16 |
| access([bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_ytd], [bmsql_district.d_tax], [bmsql_district.d_next_o_id], [bmsql_district.d_name], |
| [bmsql_district.d_street_1], [bmsql_district.d_street_2], [bmsql_district.d_city], [bmsql_district.d_state], [bmsql_district.d_zip]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([bmsql_district.d_w_id], [bmsql_district.d_id]), range[2,9 ; 2,9], |
| range_cond([bmsql_district.d_w_id = 2], [bmsql_district.d_id = 9]) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# 查看真实的执行计划
select * from GV$OB_PLAN_CACHE_PLAN_EXPLAIN where SVR_IP = '192.168.111.111' and SVR_PORT=2882 and TENANT_ID=1 and PLAN_ID=40903;
+-----------+----------------+----------+---------+------------+--------------+-------------------------+------------------+------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| TENANT_ID | SVR_IP | SVR_PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR | NAME | ROWS | COST | PROPERTY |
+-----------+----------------+----------+---------+------------+--------------+-------------------------+------------------+------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 192.168.111.111| 2882 | 40903 | 0 | 0 | PHY_SCALAR_AGGREGATE | NULL | 1 | 2709 | NULL |
| 1 | 192.168.111.111| 2882 | 40903 | 1 | 1 | PHY_HASH_JOIN | NULL | 1000 | 2691 | NULL |
| 1 | 192.168.111.111| 2882 | 40903 | 2 | 2 | PHY_TABLE_SCAN | bmsql_stock | 1000 | 2079 | table_rows:100049, physical_range_rows:25012, logical_range_rows:25012, index_back_rows:0, output_rows:1000, avaiable_index_name[bmsql_stock] |
| 1 | 192.168.111.111| 2882 | 40903 | 2 | 3 | PHY_SUBPLAN_SCAN | NULL | 3791 | 33 | NULL |
| 1 | 192.168.111.111| 2882 | 40903 | 3 | 4 | PHY_NESTED_LOOP_JOIN | NULL | 3791 | 23 | NULL |
| 1 | 192.168.111.111| 2882 | 40903 | 4 | 5 | PHY_TABLE_SCAN | bmsql_district | 1 | 2 | table_rows:20, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, avaiable_index_name[bmsql_district] |
| 1 | 192.168.111.111| 2882 | 40903 | 4 | 6 | PHY_TABLE_SCAN | bmsql_order_line | 1 | 20 | table_rows:379776, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, avaiable_index_name[bmsql_order_line] |
+-----------+----------------+----------+---------+------------+--------------+-------------------------+------------------+------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+select * from GV$OB_PLAN_CACHE_PLAN_EXPLAIN where SVR_IP = '192.168.111.111' and SVR_PORT=2882 and TENANT_ID=1 and PLAN_ID=40877;
+-----------+-----------------+----------+---------+------------+--------------+------------------+----------------+------+------+-----------------------------------------------------------------------------------------------------------------------------------+
| TENANT_ID | SVR_IP | SVR_PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR | NAME | ROWS | COST | PROPERTY |
+-----------+-----------------+----------+---------+------------+--------------+------------------+----------------+------+------+-----------------------------------------------------------------------------------------------------------------------------------+
| 1 | 192.168.111.111 | 2882 | 40877 | 0 | 0 | PHY_MATERIAL | NULL | 1 | 2 | NULL |
| 1 | 192.168.111.111 | 2882 | 40877 | 1 | 1 | PHY_LOCK | NULL | 1 | 2 | NULL |
| 1 | 192.168.111.111 | 2882 | 40877 | 2 | 2 | PHY_TABLE_SCAN | bmsql_district | 1 | 2 | table_rows:20, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, avaiable_index_name[bmsql_district] |
+-----------+-----------------+----------+---------+------------+--------------+------------------+----------------+------+------+-----------------------------------------------------------------------------------------------------------------------------------+select * from GV$OB_PLAN_CACHE_PLAN_EXPLAIN where SVR_IP = '192.168.111.111' and SVR_PORT=2882 and TENANT_ID=1 and PLAN_ID=40891;
+------------+----------------+----------+---------+------------+--------------+-----------------+----------------+------+------+-----------------------------------------------------------------------------------------------------------------------------------+
| TENANT_ID | SVR_IP | SVR_PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR | NAME | ROWS | COST | PROPERTY |
+------------+----------------+----------+---------+------------+--------------+-----------------+----------------+------+------+-----------------------------------------------------------------------------------------------------------------------------------+
| 1 | 192.168.111.111 | 2882 | 40891 | 0 | 0 | PHY_UPDATE | NULL | 1 | 34 | NULL |
| 1 | 192.168.111.111 | 2882 | 40891 | 1 | 1 | PHY_TABLE_SCAN | bmsql_district | 1 | 2 | table_rows:20, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, avaiable_index_name[bmsql_district] |
+-----------+-----------------+----------+---------+------------+--------------+-----------------+----------------+------+------+-----------------------------------------------------------------------------------------------------------------------------------+
参考：https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000507723
这篇关于实践练习六（必选）：查看 OceanBase 执行计划的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
