实践练习六（必选）：查看 OceanBase 执行计划
1、引言
1.1 关于此文档
本文档主要介绍在 RHEL 8.5 环境中，掌握 OceanBase 的执行计划查看方法，包括 explain 命令和查看实际执行计划。
1.2 参考资料
- 社区版官网-文档-学习中心-入门教程：OceanBase 入门到实战教程
- 社区版官网-博客-入门实战：开源博客
2、SYSBENCH 测试
SYSBENCH 是一个支持多线程的跨平台模块化基准测试工具，可以执行 CPU/内存/线程/IO/数据库等方面的性能测试，用于评估系统在运行高负载的数据库时相关核心参数的性能表现。
SYSBENCH 可以做多种性能测试。本文主要介绍对数据库性能（OLTP）的测试。
2.1 SYSBENCH 安装
-
下载地址 https://github.com/akopytov/sysbench/archive/refs/tags/1.0.20.zip
[root@obce01 ~]# wget https://github.com/akopytov/sysbench/archive/refs/tags/1.0.20.zip复制 -
编译安装
[root@obce01 ~]# unzip 1.0.20.zip [root@obce01 ~]# cd sysbench-1.0.20 [root@obce01 ~]# yum -y install automake libtool [root@obce01 ~]# yum -y install mysql.x86_64 mysql-devel.x86_64 --allowerasing [root@obce01 ~]# ./autogen.sh [root@obce01 ~]# ./configure --prefix=/usr/sysbench/ --with-mysql-includes=/usr/include/mysql/ --with-mysql-libs=/usr/lib64/mysql/ --with-mysql [root@obce01 ~]# make [root@obce01 ~]# make install [root@obce01 ~]# cp /usr/sysbench/bin/sysbench /usr/local/bin/ [root@obce01 ~]# cd /usr/sysbench/ [root@obce01 ~]# bin/sysbench --help复制 -
查看帮助命令，验证 SYSBENCH 是否安装成功。
2.2 参数设置
OS 参数
ffffffff 表示使用 32 个核，根据实际配置进行修改。例如：ECS 为 8 核，则输入 ff。
sudo sh -c 'for x in /sys/class/net/eth0/queues/rx-*; do echo ff>$x/rps_cpus; done' sudo sh -c "echo 32768 > /proc/sys/net/core/rps_sock_flow_entries" sudo sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-0/rps_flow_cnt"复制
OceanBase 参数
-
SYS 租户参数
-- obclient -h10.10.10.112 -uroot@sys -P2881 -padmin123 -A oceanbase alter system set enable_auto_leader_switch=false; alter system set enable_one_phase_commit=false; alter system set enable_monotonic_weak_read = true; alter system set weak_read_version_refresh_interval='5s'; alter system _ob_minor_merge_schedule_interval='5s'; alter system set memory_limit_percentage = 90; -- OceanBase 数据库占系统总内存的比例，提高 OceanBase 数据库可用的内存量 alter system set memstore_limit_percentage = 55; -- memstore 占租户的内存比，尽量增大 memstore 的空间（但是可能对读操作有负面影响） alter system set freeze_trigger_percentage = 70; -- 启动 major/minor freeze 的时机，让转储（minor freeze）尽早启动，memstore 内存尽早释放 alter system set minor_freeze_times = 50; -- minor freeze 的次数，尽量不在测试期间触发 major freeze alter system set minor_warm_up_duration_time = 0; -- 加快 minor freeze alter system set merge_thread_count = 32; -- 增大合并的线程数 alter system set minor_merge_concurrency = 8; -- 增大转储的线程数，期望提高转储的速度 alter system set _mini_merge_concurrency = 4; -- 增大 mini_merge 的线程数，期望提高 mini_merge 的速度（默认值为 3）。调大为 8 以后，发现会导致压测中 CPU 使用率有时飙升至 90%，对性能有影响复制 -
PROXY 参数
-- obclient -h 10.10.10.112 -u root@proxysys -P 2883 -padmin123 alter proxyconfig set proxy_mem_limited='4G'; -- 防止 oom，可根据实际环境动态调整 alter proxyconfig set enable_compression_protocol=false; -- 关闭压缩，降低 CPU 百分率 alter proxyconfig set work_thread_num=32; -- 调整工作线程数，寻找最优性能 alter proxyconfig set enable_compression_protocol=false; alter proxyconfig set enable_metadb_used=false; alter proxyconfig set enable_standby=false; alter proxyconfig set enable_strict_stat_time=false; alter proxyconfig set use_local_dbconfig=true;复制 -
租户参数
-- obclient -h10.10.10.112 -uroot@my_mysql_tenant -P2881 -padmin123 -A oceanbase -- 数据库下租户设置，防止事务超时 set global ob_timestamp_service='GTS' ; set global autocommit=ON; set global ob_query_timeout=36000000000; set global ob_trx_timeout=36000000000; set global max_allowed_packet=67108864; set global ob_sql_work_area_percentage=100; set global parallel_max_servers=800; set global parallel_servers_target=800;复制
2.3 数据准备
-
准备数据库账户
-
[admin@obce01 sbin]$ obclient -h10.10.10.112 -uroot@my_mysql_tenant -P2881 -padmin123 -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 3221487788 Server version: 5.7.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:32) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [oceanbase]> create database sysbenchdb; Query OK, 1 row affected (0.212 sec) MySQL [oceanbase]> grant all privileges on sysbenchdb.* to u_sysbench identified by 'admin'; Query OK, 0 rows affected (0.360 sec) MySQL [oceanbase]>复制 -
初始化表和数据
SYSBENCH 参数说明：
--tables：指定表的数量。--table_size：指定表的数据量。--threads：指定并发数。--mysql-ignore-errors：指定忽略的错误号，忽略后就继续跑。否则，报错就中断。--time: 指定运行时间。--report-interval：报告间隔。
sysbench --test=./oltp_read_only.lua --mysql-host=10.10.10.112 --mysql-port=2883 --mysql-db=sysbenchdb --mysql-user="obcetest:my_mysql_tenant:u_sysbench" --mysql-password=admin --tables=12 --table_size=1000000 --threads=1 --time=300 --report-interval=3 --db-driver=mysql --db-ps-mode=disable --skip-trx=on --mysql-ignore-errors=6002,6004,4012,2013,4016,1062 prepare复制
常见初始化问题：
FATAL: mysql_drv_query() returned error 4030 (Over tenant memory limits) for query ...复制
这是典型的由于增量内存消耗速度高于转储速度而造成的问题。如果租户内存很小，这里写入的并发就不能太高
2.4 场景测试
-
只读测试
只读测试的 SQL 都是读。
[admin@obce01 sysbench]$ sysbench --test=./oltp_read_only.lua --mysql-host=10.10.10.112 --mysql-port=2883 --mysql-db=sysbenchdb --mysql-user="obcetest:my_mysql_tenant:u_sysbench" --mysql-password=admin --tables=12 --table_size=1000000 --threads=6 --time=300 --report-interval=60 --db-driver=mysql --db-ps-mode=disable --skip-trx=on --mysql-ignore-errors=6002,6004,4012,2013,4016,1062 run复制
-
纯写场景
纯写场景就是
insert、update和deleteSQL 都有。如果表是分区表，大概率会有跨节点的分布式事务。[admin@obce01 sysbench]$ sysbench --test=./oltp_write_only.lua --mysql-host=10.10.10.112 --mysql-port=2883 --mysql-db=sysbenchdb --mysql-user="obcetest:my_mysql_tenant:u_sysbench" --mysql-password=admin --tables=12 --table_size=1000000 --threads=6 --time=300 --report-interval=60 --db-driver=mysql --db-ps-mode=disable --skip-trx=on --mysql-ignore-errors=6002,6004,4012,2013,4016,1062 run复制
-
读写混合场景
[admin@obce01 sysbench]$ sysbench --test=./oltp_read_write.lua --mysql-host=10.10.10.112 --mysql-port=2883 --mysql-db=sysbenchdb --mysql-user="obcetest:my_mysql_tenant:u_sysbench" --mysql-password=admin --tables=12 --table_size=1000000 --threads=6 --time=300 --report-interval=60 --db-driver=mysql --db-ps-mode=disable --skip-trx=on --mysql-ignore-errors=6002,6004,4012,2013,4016,1062 run复制
3 使用 BenchmarkSQL 运行 TPC-C 测试
3.1 开启SQL审计
-- 在租户里开启或关闭 SQL 审计功能
set global ob_enable_sql_audit = on;
复制默认已开启
3.2 BenchmarkSQL 下载安装
BenchmarkSQL 是开源的项目，官方下载地址为：https://sourceforge.net/projects/benchmarksql/
OceanBase 团队修改版本下载地址 https://github.com/obpilot/benchmarksql-5.0
[admin@obce01 ~]$ wget https://github.com/obpilot/benchmarksql-5.0/archive/refs/heads/master.zip --2022-03-05 15:28:30-- https://github.com/obpilot/benchmarksql-5.0/archive/refs/heads/master.zip Resolving github.com (github.com)... 20.205.243.166 Connecting to github.com (github.com)|20.205.243.166|:443... connected. HTTP request sent, awaiting response... 302 Found Location: https://codeload.github.com/obpilot/benchmarksql-5.0/zip/refs/heads/master [following] --2022-03-05 15:28:45-- https://codeload.github.com/obpilot/benchmarksql-5.0/zip/refs/heads/master Resolving codeload.github.com (codeload.github.com)... 20.205.243.165 Connecting to codeload.github.com (codeload.github.com)|20.205.243.165|:443... connected. HTTP request sent, awaiting response... 200 OK Length: unspecified [application/zip] Saving to: ‘master.zip’ master.zip [ <=> ] 5.60M 3.30MB/s in 1.7s 2022-03-05 15:28:48 (3.30 MB/s) - ‘master.zip’ saved [5870500] [admin@obce01 ~]$ unzip master.zip [admin@obce01 ~]$复制
3.3 TPCC 数据初始化
1、建表
建表脚本通常放在 run/sql.common 下或者其他指定目录下，修改后如下。
[admin@obce01 sql.common]$ pwd
/home/admin/benchmarksql-5.0-master/run/sql.common
[admin@obce01 sql.common]$ cat 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)
);
[admin@obce01 sql.common]$
复制props.ob 配置文件如下
db=oracle driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver conn=jdbc:oceanbase://127.1:2883/tpcctest?useUnicode=true&characterEncoding=utf-8 user=tpcctest@my_mysql_tenant#obcetest password=admin123 warehouses=2 loadWorkers=2 terminals=5 //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复制
运行命令建表。
[admin@obce01 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)
);
......
复制2、加载数据
[admin@obce01 run]$ ./runLoader.sh props.ob Starting BenchmarkSQL LoadData driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver conn=jdbc:oceanbase://127.1:2883/tpcctest?useUnicode=true&characterEncoding=utf-8 user=tpcctest@my_mysql_tenant#obcetest 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复制
3、创建索引
当数据初始化完后创建索引。
[admin@obce01 run]$ ./runSQL.sh props.ob sql.common/indexCreates.sql
# ------------------------------------------------------------
# Loading SQL file sql.common/indexCreates.sql
# ------------------------------------------------------------
alter table bmsql_warehouse add constraint bmsql_warehouse_pkey
primary key (w_id);
Not supported feature or function
alter table bmsql_district add constraint bmsql_district_pkey
primary key (d_w_id, d_id);
Not supported feature or function
alter table bmsql_customer add constraint bmsql_customer_pkey
primary key (c_w_id, c_d_id, c_id);
Not supported feature or function
create index bmsql_customer_idx1
on bmsql_customer (c_w_id, c_d_id, c_last, c_first);
alter table bmsql_oorder add constraint bmsql_oorder_pkey
primary key (o_w_id, o_d_id, o_id);
Not supported feature or function
create unique index bmsql_oorder_idx1
on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);
alter table bmsql_new_order add constraint bmsql_new_order_pkey
primary key (no_w_id, no_d_id, no_o_id);
Not supported feature or function
alter table bmsql_order_line add constraint bmsql_order_line_pkey
primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);
Not supported feature or function
alter table bmsql_stock add constraint bmsql_stock_pkey
primary key (s_w_id, s_i_id);
Not supported feature or function
alter table bmsql_item add constraint bmsql_item_pkey
primary key (i_id);
Not supported feature or function
复制3.4 5个并发测试
TPC-C 用 tpmC 值（Transactions per Minute）来衡量系统最大有效吞吐量。 其中 Transactions 以 NewOrder Transaction 为准，即最终衡量单位为每分钟处理的订单数。
[admin@obce01 run]$ ./runBenchmark.sh props.ob 18:06:43,374 [main] INFO jTPCC : Term-00, 18:06:43,377 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 18:06:43,377 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0 18:06:43,377 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 18:06:43,377 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa 18:06:43,378 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier 18:06:43,381 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck 18:06:43,382 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 18:06:43,382 [main] INFO jTPCC : Term-00, 18:06:43,383 [main] INFO jTPCC : Term-00, db=oracle 18:06:43,383 [main] INFO jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver 18:06:43,383 [main] INFO jTPCC : Term-00, conn=jdbc:oceanbase://127.1:2883/tpcctest?useUnicode=true&characterEncoding=utf-8 18:06:43,383 [main] INFO jTPCC : Term-00, user=tpcctest@my_mysql_tenant#obcetest 18:06:43,384 [main] INFO jTPCC : Term-00, 18:06:43,384 [main] INFO jTPCC : Term-00, warehouses=2 18:06:43,384 [main] INFO jTPCC : Term-00, terminals=5 18:06:43,387 [main] INFO jTPCC : Term-00, runMins=10 18:06:43,387 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0 18:06:43,387 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true 18:06:43,387 [main] INFO jTPCC : Term-00, 18:06:43,387 [main] INFO jTPCC : Term-00, newOrderWeight=45 18:06:43,387 [main] INFO jTPCC : Term-00, paymentWeight=43 18:06:43,388 [main] INFO jTPCC : Term-00, orderStatusWeight=4 18:06:43,388 [main] INFO jTPCC : Term-00, deliveryWeight=4 18:06:43,388 [main] INFO jTPCC : Term-00, stockLevelWeight=4 18:06:43,389 [main] INFO jTPCC : Term-00, 18:06:43,389 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS 18:06:43,389 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py 18:06:43,390 [main] INFO jTPCC : Term-00, 18:06:43,414 [main] INFO jTPCC : Term-00, copied props.ob to my_result_2022-03-05_180643/run.properties 18:06:43,414 [main] INFO jTPCC : Term-00, created my_result_2022-03-05_180643/data/runInfo.csv for runID 11 18:06:43,415 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2022-03-05_180643/data/result.csv 18:06:43,418 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py 18:06:43,419 [main] INFO jTPCC : Term-00, osCollectorInterval=1 18:06:43,419 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null 18:06:43,419 [main] INFO jTPCC : Term-00, osCollectorDevices=null 18:06:43,539 [main] INFO jTPCC : Term-00, 18:06:43,912 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 128 18:06:43,912 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 237 18:06:43,913 [main] INFO jTPCC : Term-00, Term-00, Running Average tpmTOTAL: 11.03 Current tpmTOTAL: 732 Memory Usage: 45MB / 180MB 18:17:30,894 [Thread-5] INFO jTPCC : Term-00, 18:17:30,894 [Thread-5] INFO jTPCC : Term-00, 18:17:30,895 [Thread-5] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 5.47 18:17:30,895 [Thread-5] INFO jTPCC : Term-00, Measured tpmTOTAL = 11.03 18:17:30,895 [Thread-5] INFO jTPCC : Term-00, Session Start = 2022-03-05 18:06:44 18:17:30,895 [Thread-5] INFO jTPCC : Term-00, Session End = 2022-03-05 18:17:30 18:17:30,896 [Thread-5] INFO jTPCC : Term-00, Transaction Count = 118复制
4、TPC-C TOP SQL分析
分析 TPC-C TOP SQL，并查看 3条 SQL 的 解析执行计划 和 实际执行计划
4.1 查看TOP SQL
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 s.tenant_id = 1002 and user_name='tpcctest' GROUP BY sql_id order by avg_elapsed_time desc limit 20;复制
4.2 查看执行计划
通过explain 命令，获取SQL解释执行计划。
获得 plan_id 后，通过使用 tenant_id 和 plan_id 访问 v$plan_cache_plan_explain 来展示该执行计划。这里展示的计划为物理执行计划即实际执行计划，在算子命名上会与 EXPLAIN 所展示的逻辑执行计划有所不同。
注意
如果访问 gv$plan_cache_plan_explain，必须给定 IP、Port、tenant_id 和 plan_id 这四列的值。
如果访问 v$plan_cache_plan_explain，必须给定 tenant_id 和 plan_id 的值，否则系统将返回空集。
-
第一条SQL
获取SQL文本
MySQL [oceanbase]> select distinct planid, query_sql from gv$sql_audit where sql_id='F59A700FA168324279B0DBC25E19760F'\G *************************** 1. row *************************** 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 < 20 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 = 1 ) ) 1 row in set (0.004 sec)复制解释执行计划
MySQL [tpcctest]> 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 < 20 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 = 1 ) ) \G *************************** 1. row *************************** Query Plan: ============================================================ |ID|OPERATOR |NAME |EST. ROWS|COST | ------------------------------------------------------------ |0 |SCALAR GROUP BY | |1 |781503| |1 | HASH RIGHT SEMI JOIN| |2 |781503| |2 | SUBPLAN SCAN |VIEW1 |1 |579107| |3 | NESTED-LOOP JOIN | |1 |579107| |4 | TABLE SCAN |bmsql_order_line|15 |579057| |5 | MATERIAL | |1 |51 | |6 | TABLE SCAN |bmsql_district |1 |51 | |7 | TABLE SCAN |bmsql_stock |5 |202394| ============================================================ Outputs & filters: ------------------------------------- 0 - output([T_FUN_COUNT(*)]), filter(nil), group(nil), agg_func([T_FUN_COUNT(*)]) 1 - output([1]), filter(nil), equal_conds([bmsql_stock.s_i_id = VIEW1.ol_i_id]), other_conds(nil) 2 - output([VIEW1.ol_i_id]), filter(nil), access([VIEW1.ol_i_id]) 3 - output([bmsql_order_line.ol_i_id]), filter(nil), 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) 4 - output([bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), filter([bmsql_order_line.ol_w_id = 1], [bmsql_order_line.ol_d_id = 1]), 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) 5 - output([bmsql_district.d_next_o_id]), filter(nil) 6 - output([bmsql_district.d_next_o_id]), filter([bmsql_district.d_w_id = 1], [bmsql_district.d_id = 1], [bmsql_district.d_next_o_id > bmsql_district.d_next_o_id - 20]), access([bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_next_o_id]), partitions(p0) 7 - output([bmsql_stock.s_i_id]), filter([bmsql_stock.s_w_id = 1], [bmsql_stock.s_quantity < 20]), access([bmsql_stock.s_w_id], [bmsql_stock.s_quantity], [bmsql_stock.s_i_id]), partitions(p0) 1 row in set (0.118 sec)复制实际执行计划
MySQL [oceanbase]> SELECT * FROM v$plan_cache_plan_explain WHERE tenant_id = 1002 AND plan_id = 54; +-----------+--------------+------+---------+------------+--------------+-------------------------+------------------+------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | TENANT_ID | IP | PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR | NAME | ROWS | COST | PROPERTY | +-----------+--------------+------+---------+------------+--------------+-------------------------+------------------+------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1002 | 10.10.10.112 | 2882 | 54 | 0 | 0 | PHY_SCALAR_AGGREGATE | NULL | 1 | 816739 | NULL | | 1002 | 10.10.10.112 | 2882 | 54 | 1 | 1 | PHY_HASH_JOIN | NULL | 2 | 816739 | NULL | | 1002 | 10.10.10.112 | 2882 | 54 | 2 | 2 | PHY_SUBPLAN_SCAN | NULL | 1 | 614819 | NULL | | 1002 | 10.10.10.112 | 2882 | 54 | 3 | 3 | PHY_NESTED_LOOP_JOIN | NULL | 1 | 614819 | NULL | | 1002 | 10.10.10.112 | 2882 | 54 | 4 | 4 | PHY_TABLE_SCAN | bmsql_order_line | 16 | 614769 | table_rows:163009, physical_range_rows:602943, logical_range_rows:163009, index_back_rows:0, output_rows:15, est_method:local_storage, avaiable_index_name[bmsql_order_line], estimation info[table_id:1101710651081627, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1646467767296202-1646467767296202, logical_rc:0, physical_rc:276925), (table_type:0, version:1646467767296202-1646467767296202-9223372036854775807, logical_rc:163009, physical_rc:326018)] | | 1002 | 10.10.10.112 | 2882 | 54 | 4 | 5 | PHY_MATERIAL | NULL | 1 | 50 | NULL | | 1002 | 10.10.10.112 | 2882 | 54 | 5 | 6 | PHY_TABLE_SCAN | bmsql_district | 1 | 50 | table_rows:20, physical_range_rows:20, logical_range_rows:20, index_back_rows:0, output_rows:0, est_method:local_storage, avaiable_index_name[bmsql_district], estimation info[table_id:1101710651081622, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1646467767296202-1646467767296202, logical_rc:0, physical_rc:0), (table_type:0, version:1646467767296202-1646467767296202-9223372036854775807, logical_rc:20, physical_rc:20)] | | 1002 | 10.10.10.112 | 2882 | 54 | 2 | 7 | PHY_TABLE_SCAN | bmsql_stock | 5 | 201917 | table_rows:4634, physical_range_rows:202421, logical_range_rows:4634, index_back_rows:0, output_rows:4, est_method:local_storage, avaiable_index_name[bmsql_stock], estimation info[table_id:1101710651081629, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1646467770294122-1646467770294122, logical_rc:0, physical_rc:193152), (table_type:0, version:1646467770294122-1646467770294122-9223372036854775807, logical_rc:4634, physical_rc:9269)] | +-----------+--------------+------+---------+------------+--------------+-------------------------+------------------+------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 8 rows in set (0.002 sec)复制 -
第二条SQL
获取SQL文本
MySQL [oceanbase]> select distinct PLAN_ID, query_sql from gv$sql_audit where sql_id='7229213613983BC5FDA15AD11EC70D01'\G *************************** 1. row *************************** PLAN_ID: 41 query_sql: SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 FROM bmsql_stock WHERE s_w_id = 2 AND s_i_id = 9230 FOR UPDATE *************************** 2. row *************************** PLAN_ID: 40 query_sql: SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 FROM bmsql_stock WHERE s_w_id = 1 AND s_i_id = 13087 FOR UPDATE *************************** 3. row *************************** PLAN_ID: 42 query_sql: SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 FROM bmsql_stock WHERE s_w_id = 1 AND s_i_id = 16437 FOR UPDATE 3 rows in set (0.004 sec) MySQL [oceanbase]>复制解释执行计划
MySQL [tpcctest]> explain SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 FROM bmsql_stock WHERE s_w_id = 2 AND s_i_id = 9230 FOR UPDATE\G *************************** 1. row *************************** Query Plan: ============================================ |ID|OPERATOR |NAME |EST. ROWS|COST | -------------------------------------------- |0 |TABLE SCAN|bmsql_stock|1 |251623| ============================================ Outputs & filters: ------------------------------------- 0 - output([bmsql_stock.s_quantity], [bmsql_stock.s_data], [bmsql_stock.s_dist_01], [bmsql_stock.s_dist_02], [bmsql_stock.s_dist_03], [bmsql_stock.s_dist_04], [bmsql_stock.s_dist_05], [bmsql_stock.s_dist_06], [bmsql_stock.s_dist_07], [bmsql_stock.s_dist_08], [bmsql_stock.s_dist_09], [bmsql_stock.s_dist_10]), filter([bmsql_stock.s_w_id = 2], [bmsql_stock.s_i_id = 9230]), access([bmsql_stock.__pk_increment], [bmsql_stock.s_w_id], [bmsql_stock.s_i_id], [bmsql_stock.s_quantity], [bmsql_stock.s_data], [bmsql_stock.s_dist_01], [bmsql_stock.s_dist_02], [bmsql_stock.s_dist_03], [bmsql_stock.s_dist_04], [bmsql_stock.s_dist_05], [bmsql_stock.s_dist_06], [bmsql_stock.s_dist_07], [bmsql_stock.s_dist_08], [bmsql_stock.s_dist_09], [bmsql_stock.s_dist_10]), partitions(p0) 1 row in set (0.022 sec) MySQL [tpcctest]>复制实际执行计划
MySQL [oceanbase]> SELECT * FROM v$plan_cache_plan_explain WHERE tenant_id = 1002 AND plan_id = 41; +-----------+--------------+------+---------+------------+--------------+----------------+-------------+------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | TENANT_ID | IP | PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR | NAME | ROWS | COST | PROPERTY | +-----------+--------------+------+---------+------------+--------------+----------------+-------------+------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1002 | 10.10.10.112 | 2882 | 41 | 0 | 0 | PHY_TABLE_SCAN | bmsql_stock | 1 | 250119 | table_rows:4266, physical_range_rows:201685, logical_range_rows:4266, index_back_rows:0, output_rows:0, est_method:local_storage, avaiable_index_name[bmsql_stock], estimation info[table_id:1101710651081629, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1646467770294122-1646467770294122, logical_rc:0, physical_rc:193152), (table_type:0, version:1646467770294122-1646467770294122-9223372036854775807, logical_rc:4266, physical_rc:8533)] | +-----------+--------------+------+---------+------------+--------------+----------------+-------------+------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.002 sec)复制 -
第三条SQL
获取SQL文本
MySQL [oceanbase]> select distinct PLAN_ID, query_sql from gv$sql_audit where sql_id='482BA7822AE7BE644CEBEB55213E7284'\G
*************************** 1. row ***************************
PLAN_ID: 51
query_sql: UPDATE bmsql_order_line SET ol_delivery_d = '2022-03-05 19:45:34.271' WHERE ol_w_id = 2 AND ol_d_id = 1 AND ol_o_id = 2110
1 row in set (0.004 sec)
MySQL [oceanbase]>
复制解释执行计划
MySQL [tpcctest]> explain UPDATE bmsql_order_line SET ol_delivery_d = '2022-03-05 19:45:34.271' WHERE ol_w_id = 2 AND ol_d_id = 1 AND ol_o_id = 2110\G
*************************** 1. row ***************************
Query Plan: ==================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
--------------------------------------------------
|0 |UPDATE | |1 |749325|
|1 | TABLE SCAN|bmsql_order_line|1 |749325|
==================================================
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=?])
1 - output([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], [?]), filter([bmsql_order_line.ol_w_id = 2], [bmsql_order_line.ol_d_id = 1], [bmsql_order_line.ol_o_id = 2110]),
access([bmsql_order_line.ol_delivery_d], [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_amount], [bmsql_order_line.ol_supply_w_id], [bmsql_order_line.ol_quantity], [bmsql_order_line.ol_dist_info]), partitions(p0)
1 row in set (0.036 sec)
MySQL [tpcctest]>
复制实际执行计划
MySQL [oceanbase]> SELECT * FROM v$plan_cache_plan_explain WHERE tenant_id = 1002 AND plan_id = 51;
+-----------+--------------+------+---------+------------+--------------+-----------------+------------------+------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TENANT_ID | IP | PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR | NAME | ROWS | COST | PROPERTY |
+-----------+--------------+------+---------+------------+--------------+-----------------+------------------+------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1002 | 10.10.10.112 | 2882 | 51 | 0 | 0 | PHY_UPDATE | NULL | 1 | 795430 | NULL |
| 1002 | 10.10.10.112 | 2882 | 51 | 1 | 1 | PHY_TABLE_SCAN | bmsql_order_line | 1 | 795429 | table_rows:162972, physical_range_rows:602869, logical_range_rows:162972, index_back_rows:0, output_rows:0, est_method:local_storage, avaiable_index_name[bmsql_order_line], estimation info[table_id:1101710651081627, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1646467767296202-1646467767296202, logical_rc:0, physical_rc:276925), (table_type:0, version:1646467767296202-1646467767296202-9223372036854775807, logical_rc:162972, physical_rc:325944)] |
+-----------+--------------+------+---------+------------+--------------+-----------------+------------------+------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.085 sec)
复制最后修改时间：2022-03-05 21:01:21
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
墨天轮编辑部
3年前
评论
0谢谢您投稿第二期 OceanBase 技术征文大赛🌹距离本次活动截止征稿只有5天（3月11日截止），欢迎您多多投稿！
3年前
点赞 评论
相关阅读
2025年4月国产数据库大事记：4个千万级中标项目诞生！2024年达梦净利3.6亿、金仓净利8006.6万……
墨天轮编辑部
2293次阅读
2025-04-30 17:39:54
2025年5月中国数据库流行度排行榜：OB一枝独秀破 800，金仓奋起直追跻四强
墨天轮编辑部
1135次阅读
2025-05-13 10:55:54
2025年4月国产数据库中标情况一览：4个千万元级项目，GaussDB与OceanBase大放异彩！
通讯员
803次阅读
2025-04-30 15:24:06
919万！南航信息中心采购OceanBase数据库软件许可及服务
通讯员
294次阅读
2025-04-29 16:13:34
AI关键场景得到全面支持！OceanBase入选Forrester报告三大领域代表厂商
OceanBase数据库
265次阅读
2025-04-19 22:27:54
QPlus V6.3 更新，新增PostgreSQL与PolarDB PG支持，OceanBase 容灾管理重磅上线
沃趣科技
264次阅读
2025-05-13 09:39:27
1364万！2024年中国联通软研院OceanBase扩容单一来源采购公示
通讯员
186次阅读
2025-04-21 15:55:59
Oceanbase单机版上手示例
潇湘秦
186次阅读
2025-04-18 13:40:24
OceanBase创始人阳振坤荣誉退休！曾从0到1发起自研国产分布式数据库，攻克了分布式数据库的核心技术壁垒
通讯员
165次阅读
2025-05-13 14:10:49
CloudDM v2.3.0.0 全新发布，支持 OceanBase For Oracle 和 Oracle
ClouGence
139次阅读
2025-04-27 11:04:16
