简介：通过使用BenmarkSQL运行TPC-C,并查看OceanBase 的执行计划及查看方法，包括 explain 命令和查看实际执行计划。
一、准备工作：
1.机器配置：
⑴ 硬件：4核CPU，32G内存，500G硬盘容量，网卡型号： wlp1s0
⑵软件：IP地址： 192.168.43.196，软件版本：CentOS Linux
release 7.9.2009 (Core)
⑶ 准备服务器：
|
角色 |
机器 |
备注 |
|
observer obclient |
192.168.43.196 |
zone1, 监听2881和2883端口 |
|
obproxy |
192.168.43.196 |
监听2883和2884端口 |
①启动OB：
[admin@dell angel ~]$ cd /home/admin/oceanbase &&
bin/observer -i wlp1s0 -p 2881 -P 2882 -z zone1 -d /home/admin/oceanbase/store
-r '192.168.43.196:2882:2881' -c 20220221 -n obdemo -o "memory_limit=8G,cache_wash_threshold=1G,__min_full_resource_pool_memory=268435456,system_memory=3G,memory_chunk_cache_size=128M,cpu_count=16,net_thread_count=4,datafile_size=50G,stack_size=1536K"
②obproxy
cd ~/obproxy-3.2.0 && bin/obproxy -r
"192.168.43.196:2881" -p 2883 -o
"enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false"
-c obdemo
③集群
obclient -h 192.168.43.196 -u
root@sys#obdemo -P 2883 -p123 -c -A oceanbase
show databases;
select * from __all_tenant;
drop tenant lxs_tpcc_tenant1 force;
select * from
__all_resource_pool;
drop resource
pool lxs_tpcc_pool_test;
select * from __all_unit_config;
drop resource unit lxs_unit1;
④创建资源单元：
create resource unit lxs_tpcc_unit6_2_2 max_cpu=2,
max_memory='2g', max_iops=10000, max_disk_size='10g', max_session_num=1000000,
min_cpu=2, min_memory='2g', min_iops=1000;
⑤创建资源池：
create resource pool lxs_tpcc_pool_test unit = 'lxs_tpcc_unit6_2_2', unit_num =
1;
⑥创建业务租户：
create tenant lxs_tpcc_tenant1 resource_pool_list=('lxs_tpcc_pool_test');
select * from __all_tenant;
exit
obclient -uroot@lxs_tpcc_tenant1 -h 192.168.43.196 -P2883 -p123
–Doceanbase
⑦没有设置白名单无法进入
obclient -h 192.168.43.196 -u
root@sys#obdemo -P 2883 -p123 -c -A oceanbase
白名单：ALTER
TENANT lxs_tpcc_tenant1 SET VARIABLES ob_tcp_invited_nodes='%';
不能带密码：ERROR
1045 (42000): Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password:
YES)
alter user root identified by
'123'
exit
⑧修改权限
MySQL [oceanbase]> show global variables like
'%timeout%';
+---------------------+------------------+
| Variable_name | Value |
+---------------------+------------------+
| connect_timeout | 10 |
| interactive_timeout | 28800
|
| lock_wait_timeout |
31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| ob_pl_block_timeout | 3216672000000000 |
| ob_query_timeout
| 10000000 |
| ob_trx_idle_timeout | 120000000
|
| ob_trx_lock_timeout | -1
|
| ob_trx_timeout |
100000000 |
| wait_timeout | 28800 |
+---------------------+------------------+
11 rows in set (0.010 sec)
MySQL [oceanbase]> grant
all privileges on lxs_tpcc_tenant1 to tpcc ;
create database lxs_tpcc_db;
use lxs_tpcc_db;
修改会话超时时间：
set
global ob_query_timeout=36000000000;
set global ob_trx_timeout=36000000000;
2. 软件下载：
⑴ 下载测试软件：
git
clone https://github.com/obpilot/benchmarksql-5.0.git
⑵benchmarksql的目录树：
⑶配置文件 props.ob 在 run 目录下：
sudo vim
/home/admin/benchmarksql-5.0/run/props.ob
db=oracle
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://192.168.43.196:2883/lxs_tpcc_db?useUnicode=true&characterEncoding=utf-8
user=root@lxs_tpcc_tenant1
password=123
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=10
//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
~
3.数据准备建非分区表
sh runSQL.sh /home/admin/benchmarksql-5.0/run/props.ob
/home/admin/benchmarksql-5.0/run/sql.common/tableCreates.sql
[admin@dell angel run]$ sh runSQL.sh
/home/admin/benchmarksql-5.0/run/props.ob
/home/admin/benchmarksql-5.0/run/sql.common/tableCreates.sql
# ------------------------------------------------------------
# Loading SQL file /home/admin/benchmarksql-5.0/run/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 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)
);
图
二、开始测试
1.加载数据
输出：sh runLoader.sh props.ob
[admin@dell angel run]$ sh runLoader.sh
props.ob
Starting
BenchmarkSQL LoadData
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://192.168.43.196:2883/lxs_tpcc_db?useUnicode=true&characterEncoding=utf-8
user=root@lxs_tpcc_tenant1
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
图：
2.当数据初始化完后再补充两个索引。
3.查询数量
4.合并转储： alter system major freeze;
图：
合并成功。
5.性能测试
[admin@dell angel run]$ sh runBenchmark.sh props.ob
03:54:24,836
[main] INFO jTPCC : Term-00,
03:54:24,839
[main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
03:54:24,839
[main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
03:54:24,839
[main] INFO jTPCC : Term-00,
+-------------------------------------------------------------+
03:54:24,839
[main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
03:54:24,839
[main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
03:54:24,841
[main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
03:54:24,841
[main] INFO jTPCC : Term-00,
+-------------------------------------------------------------+
03:54:24,841
[main] INFO jTPCC : Term-00,
03:54:24,841
[main] INFO jTPCC : Term-00, db=oracle
03:54:24,841
[main] INFO jTPCC : Term-00,
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
03:54:24,841
[main] INFO jTPCC : Term-00,
conn=jdbc:oceanbase://192.168.43.196:2883/lxs_tpcc_db?useUnicode=true&characterEncoding=utf-8
03:54:24,842
[main] INFO jTPCC : Term-00,
user=root@lxs_tpcc_tenant1
03:54:24,842
[main] INFO jTPCC : Term-00,
03:54:24,842
[main] INFO jTPCC : Term-00,
warehouses=2
03:54:24,842
[main] INFO jTPCC : Term-00,
terminals=2
03:54:24,843
[main] INFO jTPCC : Term-00, runMins=1
03:54:24,843
[main] INFO jTPCC : Term-00,
limitTxnsPerMin=0
03:54:24,844
[main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
03:54:24,844
[main] INFO jTPCC : Term-00,
03:54:24,844
[main] INFO jTPCC : Term-00,
newOrderWeight=45
03:54:24,844
[main] INFO jTPCC : Term-00,
paymentWeight=43
03:54:24,844
[main] INFO jTPCC : Term-00,
orderStatusWeight=4
03:54:24,844
[main] INFO jTPCC : Term-00,
deliveryWeight=4
03:54:24,844
[main] INFO jTPCC : Term-00,
stockLevelWeight=4
03:54:24,844
[main] INFO jTPCC : Term-00,
03:54:24,844
[main] INFO jTPCC : Term-00,
resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
03:54:24,844
[main] INFO jTPCC : Term-00,
osCollectorScript=./misc/os_collector_linux.py
03:54:24,845
[main] INFO jTPCC : Term-00,
03:54:24,863
[main] INFO jTPCC : Term-00, copied
props.ob to my_result_2022-04-08_035424/run.properties
03:54:24,863
[main] INFO jTPCC : Term-00, created
my_result_2022-04-08_035424/data/runInfo.csv for runID 7
03:54:24,864
[main] INFO jTPCC : Term-00, writing
per transaction results to my_result_2022-04-08_035424/data/result.csv
03:54:24,864
[main] INFO jTPCC : Term-00,
osCollectorScript=./misc/os_collector_linux.py
03:54:24,864
[main] INFO jTPCC : Term-00,
osCollectorInterval=1
03:54:24,864
[main] INFO jTPCC : Term-00,
osCollectorSSHAddr=null
03:54:24,865
[main] INFO jTPCC : Term-00,
osCollectorDevices=null
03:54:24,972
[main] INFO jTPCC : Term-00,
03:54:25,238
[main] INFO jTPCC : Term-00, C value
for C_LAST during load: 225
03:54:25,238
[main] INFO jTPCC : Term-00, C value
for C_LAST this run: 144
03:54:25,238
[main] INFO jTPCC : Term-00,
Term-00, Running Average tpmTOTAL:
15.19 Current tpmTOTAL: 120 Memory Usage: 28MB / 477MB
03:55:36,365
[Thread-2] INFO jTPCC : Term-00,
03:55:36,365
[Thread-2] INFO jTPCC : Term-00,
03:55:36,365
[Thread-2] INFO jTPCC : Term-00, Measured
tpmC (NewOrders) = 7.59
03:55:36,365
[Thread-2] INFO jTPCC : Term-00,
Measured tpmTOTAL = 15.19
03:55:36,365
[Thread-2] INFO jTPCC : Term-00,
Session Start = 2022-04-08 03:54:25
03:55:36,365
[Thread-2] INFO jTPCC : Term-00,
Session End = 2022-04-08 03:55:36
03:55:36,365
[Thread-2] INFO jTPCC : Term-00,
Transaction Count = 17
图：1
图：2
6.默认情况下SQL审计是开放的，检查一下参数：
MySQL [oceanbase]> show
variables like 'ob_enable_sql_audit';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| ob_enable_sql_audit |
ON |
+---------------------+-------+
1 row in set (0.006 sec)
图：
三、 分析 TPC-C TOP SQL，并查看 3条 SQL 的 解析执行计划 和 实际执行计划：
1.查询TOP sql
MySQL [oceanbase]> SELECT
sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time,
round(avg(execute_time)) avg_exec_time FROM gv$sql_audit s WHERE 1=1 and
user_name='root' and request_time >=
time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) ) GROUP BY sql_id
order by avg_elapsed_time desc LIMIT 10;
+----------------------------------+----------+------------------+---------------+
| sql_id | count(*) |
avg_elapsed_time | avg_exec_time |
+----------------------------------+----------+------------------+---------------+
| 647F8699535CD88565AC98AE8632C2C7
| 1 | 40000 | 2186 |
| 44C1DABCE001AB879F8E1F1A0AFF9C0E
| 1 | 22716 | 16658 |
| 0FACB6E4E2F378984041D6C0319D8790
| 1 | 19931 | 17349 |
| 10345400457CEB7DA0AAA280D681047B
| 1 | 12321 | 136 |
| 751417D45B8E80EE5CBA2034458B5BC9
| 1 | 11557 | 192 |
| 8494BF392DC8D426420EC07B667D36EB
| 1 | 10446 | 188 |
| 266B117EC690A3FC1658D2A9F74872FE
| 1 | 9639 | 162 |
| 17183830D1A09D6C15DA98EDB2CFD874
| 1 | 8843 | 61 |
| 13E88F58920B0288A6F62FB8E02741C7
| 1 | 8658 | 75 |
| 585B28E5BE91CCE22E8D4656C7A54576
| 1 | 8444 | 88 |
图：
2.获取TOP 3 SQL
MySQL [oceanbase]> select
distinct query_sql from gv$sql_audit where sql_id in
('647F8699535CD88565AC98AE8632C2C7', '44C1DABCE001AB879F8E1F1A0AFF9C0E', '0FACB6E4E2F378984041D6C0319D8790');
+----------------------------------------+
| query_sql |
+----------------------------------------+
| select * from CDB_CKPT_HISTORY
where 0 |
| select * from gv$sstable where
0 |
| select * from v$sstable where
0 |
+----------------------------------------+
3 rows in set (0.021 sec)
图：
3.分析执行计划1
MySQL [oceanbase]> explain select * from CDB_CKPT_HISTORY where 0;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
===========================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
---------------------------------------------------------------------------
|0 |PX COORDINATOR MERGE SORT | |2752 |566698|
|1 | EXCHANGE OUT DISTR |:EX10000 |2752 |549570|
|2 |
SORT | |2752 |549570|
|3 |
PX PARTITION ITERATOR | |2752 |463639|
|4 |
MERGE OUTER JOIN | |2752 |463639|
|5 |
SORT | |2752 |258284|
|6 |
TABLE SCAN
|__all_server_event_history|2752
|198942|
|7 |
SORT | |1 |204975|
|8 |
TABLE SCAN
|__all_server_event_history|1
|204971|
===========================================================================
Outputs & filters:
-------------------------------------
0 -
output([__all_server_event_history.svr_ip],
[__all_server_event_history.svr_port], [__all_server_event_history.value1],
[__all_server_event_history.value2], [__all_server_event_history.value3],
[__all_server_event_history.value4], [__all_server_event_history.gmt_create],
[__all_server_event_history.gmt_create]), filter(nil), startup_filter([0]),
sort_keys([__all_server_event_history.svr_ip, ASC],
[__all_server_event_history.svr_port, ASC], [__all_server_event_history.value1,
ASC], [__all_server_event_history.value2, ASC])
1 -
output([__all_server_event_history.svr_ip],
[__all_server_event_history.svr_port], [__all_server_event_history.value1],
[__all_server_event_history.value2], [__all_server_event_history.value3],
[__all_server_event_history.value4], [__all_server_event_history.gmt_create],
[__all_server_event_history.gmt_create]), filter(nil), dop=1
2 -
output([__all_server_event_history.svr_ip],
[__all_server_event_history.svr_port], [__all_server_event_history.value1],
[__all_server_event_history.value2], [__all_server_event_history.value3],
[__all_server_event_history.value4], [__all_server_event_history.gmt_create],
[__all_server_event_history.gmt_create]), filter(nil),
sort_keys([__all_server_event_history.svr_ip, ASC],
[__all_server_event_history.svr_port, ASC], [__all_server_event_history.value1,
ASC], [__all_server_event_history.value2, ASC]), local merge sort
3 -
output([__all_server_event_history.svr_ip],
[__all_server_event_history.svr_port], [__all_server_event_history.value1],
[__all_server_event_history.value2], [__all_server_event_history.value3],
[__all_server_event_history.value4], [__all_server_event_history.gmt_create],
[__all_server_event_history.gmt_create]), filter(nil)
4 -
output([__all_server_event_history.svr_ip],
[__all_server_event_history.svr_port], [__all_server_event_history.value1],
[__all_server_event_history.value2], [__all_server_event_history.value3],
[__all_server_event_history.value4], [__all_server_event_history.gmt_create],
[__all_server_event_history.gmt_create]), filter(nil),
equal_conds([__all_server_event_history.svr_ip =
__all_server_event_history.svr_ip], [__all_server_event_history.svr_port =
__all_server_event_history.svr_port], [__all_server_event_history.value1 =
__all_server_event_history.value1], [__all_server_event_history.value2 =
__all_server_event_history.value2]),
other_conds([__all_server_event_history.event = 'minor merge start' AND
__all_server_event_history.event = 'minor merge finish' OR
__all_server_event_history.event = 'write checkpoint start' AND __all_server_event_history.event
= 'write checkpoint finish'])
5 -
output([__all_server_event_history.svr_ip],
[__all_server_event_history.svr_port], [__all_server_event_history.value1],
[__all_server_event_history.value2], [__all_server_event_history.value3],
[__all_server_event_history.value4], [__all_server_event_history.gmt_create],
[__all_server_event_history.event]), filter(nil),
sort_keys([__all_server_event_history.svr_ip, ASC],
[__all_server_event_history.svr_port, ASC], [__all_server_event_history.value1,
ASC], [__all_server_event_history.value2, ASC])
6 -
output([__all_server_event_history.svr_ip],
[__all_server_event_history.svr_port], [__all_server_event_history.event],
[__all_server_event_history.value1], [__all_server_event_history.value2],
[__all_server_event_history.value3], [__all_server_event_history.value4],
[__all_server_event_history.gmt_create]), filter(nil),
access([__all_server_event_history.svr_ip],
[__all_server_event_history.svr_port], [__all_server_event_history.event],
[__all_server_event_history.value1], [__all_server_event_history.value2],
[__all_server_event_history.value3], [__all_server_event_history.value4],
[__all_server_event_history.gmt_create]), partitions(p[0-15])
7 -
output([__all_server_event_history.gmt_create], [__all_server_event_history.svr_ip],
[__all_server_event_history.svr_port], [__all_server_event_history.value1],
[__all_server_event_history.value2], [__all_server_event_history.event = 'minor
merge finish'], [__all_server_event_history.event = 'write checkpoint
finish']), filter(nil), sort_keys([__all_server_event_history.svr_ip, ASC],
[__all_server_event_history.svr_port, ASC], [__all_server_event_history.value1,
ASC], [__all_server_event_history.value2, ASC])
8 -
output([__all_server_event_history.svr_ip], [__all_server_event_history.svr_port],
[__all_server_event_history.value1], [__all_server_event_history.value2],
[__all_server_event_history.gmt_create], [__all_server_event_history.event =
'minor merge finish'], [__all_server_event_history.event = 'write checkpoint
finish']), filter([cast(__all_server_event_history.value1, DECIMAL(-1, -1)) =
?], [__all_server_event_history.event = 'minor merge finish' OR
__all_server_event_history.event = 'write checkpoint finish'],
[__all_server_event_history.event = 'minor merge finish' OR
__all_server_event_history.event = 'write checkpoint finish']),
access([__all_server_event_history.svr_ip],
[__all_server_event_history.svr_port], [__all_server_event_history.event],
[__all_server_event_history.value1], [__all_server_event_history.value2],
[__all_server_event_history.gmt_create]), partitions(p[0-15])
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.018 sec)
图：
4.分析执行计划2
MySQL [oceanbase]> explain select * from gv$sstable where 0;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
=======================================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
-------------------------------------------------------
|0 |NESTED-LOOP JOIN CARTESIAN| |10000000 |10749857|
|1 | TABLE SCAN |M |100000
|2000000 |
|2 | MATERIAL | |100
|2313 |
|3 |
TABLE SCAN |T |100
|2000 |
=======================================================
Outputs & filters:
-------------------------------------
0 -
output([M.svr_ip], [M.svr_port], [M.table_type], [M.table_id], [T.table_name],
[T.tenant_id], [M.partition_id], [M.index_id], [M.base_version], [M.multi_version_start],
[M.snapshot_version], [M.start_log_ts], [M.end_log_ts], [M.max_log_ts],
[M.version], [M.logical_data_version], [M.size], [M.is_active], [M.ref],
[M.write_ref], [M.trx_count], [M.pending_log_persisting_row_cnt],
[M.upper_trans_version], [M.contain_uncommitted_row]), filter(nil),
startup_filter([0]),
conds(nil), nl_params_(nil)
1 -
output([M.svr_ip], [M.svr_port], [M.table_id], [M.table_type],
[M.partition_id], [M.index_id], [M.base_version], [M.multi_version_start],
[M.snapshot_version], [M.start_log_ts], [M.end_log_ts], [M.max_log_ts],
[M.version], [M.logical_data_version], [M.size], [M.is_active], [M.ref],
[M.write_ref], [M.trx_count], [M.pending_log_persisting_row_cnt],
[M.upper_trans_version], [M.contain_uncommitted_row]), filter(nil),
access([M.svr_ip], [M.svr_port], [M.table_id], [M.table_type],
[M.partition_id], [M.index_id], [M.base_version], [M.multi_version_start],
[M.snapshot_version], [M.start_log_ts], [M.end_log_ts], [M.max_log_ts], [M.version],
[M.logical_data_version], [M.size], [M.is_active], [M.ref], [M.write_ref],
[M.trx_count], [M.pending_log_persisting_row_cnt], [M.upper_trans_version],
[M.contain_uncommitted_row]), partitions(p0)
2 -
output([T.table_name], [T.tenant_id]), filter(nil)
3 -
output([T.tenant_id], [T.table_name]), filter(nil),
access([T.tenant_id], [T.table_name]), partitions(p0)
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.009 sec)
图：
5.分析执行计划3
MySQL [oceanbase]> explain select * from v$sstable where 0;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
=======================================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
-------------------------------------------------------
|0 |NESTED-LOOP JOIN CARTESIAN| |10000000 |10749857|
|1 | TABLE SCAN |M |100000
|2000000 |
|2 | MATERIAL |
|100 |2313 |
|3 |
TABLE SCAN |T |100
|2000 |
=======================================================
Outputs & filters:
-------------------------------------
0 -
output([M.table_type], [M.table_id], [T.table_name], [T.tenant_id],
[M.partition_id], [M.index_id], [M.base_version], [M.multi_version_start],
[M.snapshot_version], [M.start_log_ts], [M.end_log_ts], [M.max_log_ts],
[M.version], [M.logical_data_version], [M.size], [M.is_active], [M.ref],
[M.write_ref], [M.trx_count], [M.pending_log_persisting_row_cnt],
[M.upper_trans_version], [M.contain_uncommitted_row]), filter(nil),
startup_filter([0]),
conds(nil), nl_params_(nil)
1 -
output([M.table_id], [M.table_type], [M.partition_id], [M.index_id],
[M.base_version], [M.multi_version_start], [M.snapshot_version],
[M.start_log_ts], [M.end_log_ts], [M.max_log_ts], [M.version],
[M.logical_data_version], [M.size], [M.is_active], [M.ref], [M.write_ref], [M.trx_count],
[M.pending_log_persisting_row_cnt], [M.upper_trans_version],
[M.contain_uncommitted_row]), filter(nil),
access([M.table_id], [M.table_type], [M.partition_id], [M.index_id],
[M.base_version], [M.multi_version_start], [M.snapshot_version], [M.start_log_ts],
[M.end_log_ts], [M.max_log_ts], [M.version], [M.logical_data_version],
[M.size], [M.is_active], [M.ref], [M.write_ref], [M.trx_count],
[M.pending_log_persisting_row_cnt], [M.upper_trans_version],
[M.contain_uncommitted_row]), partitions(p0)
2 -
output([T.table_name], [T.tenant_id]), filter(nil)
3 -
output([T.tenant_id], [T.table_name]), filter(nil),
access([T.tenant_id], [T.table_name]), partitions(p0)
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.012 sec)
图：
四、总结：
1.不清楚第二条解释执行计划和第三条是一样的！
2.踩过的坑：执行命令需要绝对路径，需要设置租户权限，审计权限，
