实验目录：
（必选）使用 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 ] ; then
cp="${cp}:${ORACLE_HOME}/lib/*"
fi
cp="${cp}:../lib/*"
;;
postgres)
cp="../lib/postgres/*:../lib/*"
;;
oceanbase) #添加 OceanBase 数据库类型
cp="../lib/oceanbase/*:../lib/*"
;;
esac
myCP=".:${cp}:../dist/*"
export myCP
}
...省略
case "$(getProp db)" in
firebird|oracle|postgres|oceanbase) #添加 OceanBase 数据库类型
;;
"") echo "ERROR: missing db= config option in ${PROPS}" >&2
exit 1
;;
*) echo "ERROR: unsupported database type 'db=$(getProp db)' in ${PROPS}" >&2
exit 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_id
FROM gv$ob_sql_audit s
WHERE 1=1
and db_name= 'tpccob'
and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) )
GROUP BY sql_id
order 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
1582
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
