查看 OceanBase 执行计划
SQL审计设置
查看SQL审计开关
[admin@ocp ~]$ mysql -h10.144.2.106 -ubenchmark@tpcc -P2883 -pbenchmark -c -A oceanbase
ERROR 1045 (42000): Access denied for user 'benchmark'@'xxx.xxx.xxx.xxx' (using password: YES)
[admin@ocp ~]$ mysql -h10.144.2.106 -ubenchmarksql@tpcc -P2883 -pbenchmarksql -c -A oceanbase
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)
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]> show variables like 'ob_enable_sql_audit';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| ob_enable_sql_audit | ON |
+---------------------+-------+
1 row in set (0.05 sec)
开启SQL审计
在租户里开启或关闭 SQL 审计功能:
MySQL [oceanbase]> set global ob_enable_sql_audit = on;
Query OK, 0 rows affected (0.01 sec)
执行低压力Benchmark测试
测试方案
- 使用 OBD 部署OceanBase 数据库集群。TPC-C 单独部署在一台机器上, 作为客户端的压力机器。
- OceanBase 集群规模为 2：2：2。部署成功后，新建执行 TPC-C 测试的租户及用户：租户tpcc，用户benchmarksql。将租户的
primary_zone设置为RANDOM。RANDOM表示新建表分区的 Leader 随机到这 6 台机器。
测试规格
warehouses=2000
loadWorkers=100
terminals=20
runMins=10
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
安装 Benchmark SQL
按照以下步骤安装 Benchmark SQL：
-
下载 Benchmark SQL。
本次使用的是开源社区进行适配了mysql的benchmarksql。所以不包含修改源代码适配过程。
-
解压 Benchmark SQL。
unzip ./benchmarksql-5.0.zip
适配OceanBase
这里测试实验的是已经适配了mysql数据库的benchmarksql.
修改建表语句
修改benchmarksql/run/sql.mysql/tableCreates.sql
create table bmsql_config (
cfg_name varchar(30) primary key,
cfg_value varchar(50)
);
-- drop tablegroup tpcc_group;
create tablegroup tpcc_group partition by hash partitions 128;
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 128;
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 128;
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 128;
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)
)tablegroup='tpcc_group' partition by hash(h_w_id) partitions 128;
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 128;
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 128;
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 128;
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)
) duplicate_scope='cluster';
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' use_bloom_filter=true partition by hash(s_w_id) partitions 128;
修改索引创建语句
修改benchmarksql/run/sql.mysql/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/run/sql.mysql/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;
purge recyclebin;
-- tpcc_group
drop tablegroup tpcc_group
导数执行
-
运行以下命令，初始化环境：
benchmarksql/run 目录下执行
./runDatabaseDestroy.sh prop.ob -
运行以下命令，创建表并导入数据：
benchmarksql/run 目录下执行
./runDatabaseBuild.sh prop.ob
TPC-C 测试执行
在 benchmarksql/run 目录下执行。
按照以下命令进行 TPC-C 测试：
./runBenchmark.sh prop.ob
执行结果
[root@ocp run]# ./runBenchmark.sh props.ob
05:29:15,617 [main] INFO jTPCC : Term-00,
05:29:15,619 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
05:29:15,619 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
05:29:15,619 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
05:29:15,619 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
05:29:15,620 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
05:29:15,621 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
05:29:15,621 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
05:29:15,621 [main] INFO jTPCC : Term-00,
05:29:15,621 [main] INFO jTPCC : Term-00, db=mysql
05:29:15,622 [main] INFO jTPCC : Term-00, driver=com.mysql.jdbc.Driver
05:29:15,622 [main] INFO jTPCC : Term-00, conn=jdbc:mysql:loadbalance://10.144.2.106:2883,10.144.2.107:2883,10.144.2.108:2883,10.144.2.109:2883,10.144.2.110:2883,10.144.2.111:2883/benchmark?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=3000000&useCursorFetch=true&cachePrepStmts=true&prepStmtCacheSqlLimit=10240&prepStmtCacheSize=100000&useServerPrepStmts=true
05:29:15,622 [main] INFO jTPCC : Term-00, user=benchmarksql@tpcc
05:29:15,622 [main] INFO jTPCC : Term-00,
05:29:15,622 [main] INFO jTPCC : Term-00, warehouses=2000
05:29:15,622 [main] INFO jTPCC : Term-00, terminals=5
05:29:15,623 [main] INFO jTPCC : Term-00, runMins=10
05:29:15,623 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0
05:29:15,623 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
05:29:15,624 [main] INFO jTPCC : Term-00,
05:29:15,624 [main] INFO jTPCC : Term-00, newOrderWeight=45
05:29:15,624 [main] INFO jTPCC : Term-00, paymentWeight=43
05:29:15,624 [main] INFO jTPCC : Term-00, orderStatusWeight=4
05:29:15,624 [main] INFO jTPCC : Term-00, deliveryWeight=4
05:29:15,624 [main] INFO jTPCC : Term-00, stockLevelWeight=4
05:29:15,624 [main] INFO jTPCC : Term-00,
05:29:15,624 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
05:29:15,624 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
05:29:15,624 [main] INFO jTPCC : Term-00,
05:29:15,649 [main] INFO jTPCC : Term-00, copied props.ob to my_result_2022-02-25_052915/run.properties
05:29:15,649 [main] INFO jTPCC : Term-00, created my_result_2022-02-25_052915/data/runInfo.csv for runID 1587
05:29:15,649 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2022-02-25_052915/data/result.csv
05:29:15,650 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
05:29:15,650 [main] INFO jTPCC : Term-00, osCollectorInterval=1
05:29:15,650 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null
05:29:15,650 [main] INFO jTPCC : Term-00, osCollectorDevices=net_em1 blk_nvme0n1
05:29:15,703 [main] INFO jTPCC : Term-00,
Traceback (most recent call last):
File "<stdin>", line 299, in <module>
File "<stdin>", line 90, in main
File "<stdin>", line 269, in initNetDevice
IOError: [Errno 2] No such file or directory: '/sys/class/net/em1/statistics/rx_packets'
05:29:15,756 [Thread-0] ERROR OSCollector$CollectData : OSCollector, unexpected EOF while reading from external helper process
05:29:16,198 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 208
05:29:16,198 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 118
05:29:16,198 [main] INFO jTPCC : Term-00,
Term-00, Running Average tpmTOTAL: 32033.04 Current tpmTOTAL: 2109240 Memory Usage: 112MB / 1442MB
05:39:16,861 [Thread-2] INFO jTPCC : Term-00,
05:39:16,862 [Thread-2] INFO jTPCC : Term-00,
05:39:16,862 [Thread-2] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 14479.55
05:39:16,862 [Thread-2] INFO jTPCC : Term-00, Measured tpmTOTAL = 32030.29
05:39:16,862 [Thread-2] INFO jTPCC : Term-00, Session Start = 2022-02-25 05:29:16
05:39:16,862 [Thread-2] INFO jTPCC : Term-00, Session End = 2022-02-25 05:39:16
05:39:16,862 [Thread-2] INFO jTPCC : Term-00, Transaction Count = 320334
分析SQL
分析 TPC-C TOP SQL，并查看3条 SQL 的解析执行计划和实际执行计划
查看TOP 10的SQL
MySQL [oceanbase]> SELECT/*+ PARALLEL(15)*/ SQL_ID, COUNT(*) AS QPS, AVG(t1.elapsed_time) RT
-> FROM oceanbase.gv$sql_audit t1
-> WHERE tenant_id = 1001
-> GROUP BY t1.sql_id ORDER BY RT DESC LIMIT 10;
+----------------------------------+------+--------------+
| SQL_ID | QPS | RT |
+----------------------------------+------+--------------+
| 1C8BA8E2A6D2F4900B237CDB9B276371 | 1 | 1778149.0000 |
| 7DC10E3547C663FE0E9350A0DD23C052 | 1 | 1583977.0000 |
| 234A5447A85B4DB0110569C117986CB0 | 1 | 1541224.0000 |
| 59E2137ECD5DDEE462DED984FEDDA585 | 1 | 1487768.0000 |
| 283AC0A3A8A43DD1A2ECFBE07E4A99BC | 1 | 1474334.0000 |
| C3AA55B8ACDED0282166B052AFD51F9C | 1 | 1458817.0000 |
| D383E549D344CBC46BFF590690187D19 | 1 | 1455760.0000 |
| 3F9993A8A1FC23C40B9980B45F713D42 | 1 | 1439289.0000 |
| D5A377BF7288EBCF6DDC7988E77A4380 | 1 | 1438694.0000 |
| D881F7AE87CD18A69A9329D6096C601B | 1 | 1428771.0000 |
+----------------------------------+------+--------------+
10 rows in set (0.85 sec)
查询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='benchmarksql' 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 |
+----------------------------------+----------+------------------+---------------+
| 3E76C52BE13F7D48388D2476D9596C7F | 1 | 843471 | 766414 |
| E0D27337BEF665886EB694E0F68A6A4B | 1 | 43027 | 3116 |
| 79C127C79275FCB830CB02CBCE28948F | 1 | 36203 | 3803 |
| 98081DF291BAB86943C93DE34D37083D | 2 | 31827 | 6557 |
| 9C20469C2AD80467EB8EBD79AFF2EE0F | 3 | 30049 | 15127 |
| E3750B4E0E13AB3254D5D93E2DD6F6EA | 1 | 23293 | 3141 |
| FC49A0648E312569A29359AC00C38B4E | 1 | 21869 | 3137 |
| C9CECD6B36750DEEFFB586465A4A8B1C | 1 | 21641 | 2494 |
| CFF3129A7C53F9DCA11DD32585BBDC27 | 1 | 21180 | 2489 |
| 4E0FBC5C7AFD78A1F7FB8A1D9FBAADF5 | 2 | 20927 | 2931 |
+----------------------------------+----------+------------------+---------------+
对elapsed时间最长的前10条sql进行分析
查询获取实际执行计划需要的信息
MySQL [oceanbase]> SELECT sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time, round(avg(execute_time)) avg_exec_time, s.svr_ip, s.svr_port, s.tenant_id, s.plan_id FROM gv$sql_audit s WHERE 1=1 and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) ) GROUP BY sql_id order by avg_elapsed_time desc limit 10;
+----------------------------------+----------+------------------+---------------+--------------+----------+-----------+---------+
| sql_id | count(*) | avg_elapsed_time | avg_exec_time | svr_ip | svr_port | tenant_id | plan_id |
+----------------------------------+----------+------------------+---------------+--------------+----------+-----------+---------+
| 5E8C83BFB1A6FF16645493E272039D84 | 1 | 1114249 | 1103850 | 10.144.2.107 | 2882 | 1001 | 94 |
| BBD45D535E7E601AB57DF2F8FA752692 | 1 | 915440 | 907599 | 10.144.2.107 | 2882 | 1001 | 95 |
| 3E76C52BE13F7D48388D2476D9596C7F | 1 | 843471 | 766414 | 10.144.2.107 | 2882 | 1001 | 90 |
| DE87906DB54423565A43CEAA58618D72 | 1 | 344768 | 336313 | 10.144.2.107 | 2882 | 1001 | 96 |
| E0D27337BEF665886EB694E0F68A6A4B | 1 | 43027 | 3116 | 10.144.2.107 | 2882 | 1001 | 64 |
| 79C127C79275FCB830CB02CBCE28948F | 1 | 36203 | 3803 | 10.144.2.110 | 2882 | 1001 | 13565 |
| 98081DF291BAB86943C93DE34D37083D | 2 | 31827 | 6557 | 10.144.2.109 | 2882 | 1001 | 81 |
| 9C20469C2AD80467EB8EBD79AFF2EE0F | 3 | 30049 | 15127 | 10.144.2.107 | 2882 | 1001 | 61 |
| E3750B4E0E13AB3254D5D93E2DD6F6EA | 1 | 23293 | 3141 | 10.144.2.109 | 2882 | 1001 | 89 |
| FC49A0648E312569A29359AC00C38B4E | 1 | 21869 | 3137 | 10.144.2.107 | 2882 | 1001 | 73 |
+----------------------------------+----------+------------------+---------------+--------------+----------+-----------+---------+
10 rows in set (0.98 sec)
查看执行计划
获取第一条sql的文本
前4条都是查询审计日志的SQL忽略。从第5条开始，查询看到plan_id是94，sql_id是E0D27337BEF665886EB694E0F68A6A4B
MySQL [oceanbase]> select distinct query_sql from gv$sql_audit where sql_id='E0D27337BEF665886EB694E0F68A6A4B' \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 = 202 AND s_quantity < 19 AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 202 AND d_id = 8 ) ) tmp
1 row in set (0.18 sec)
查看实际执行计划
运行下面sql查询这条语句的实际执行计划
MySQL [oceanbase]> SELECT ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`gv$plan_cache_plan_explain` where tenant_id=1001 AND ip = '10.144.2.107' AND port=2882 AND plan_id=94;
+--------------+------------+--------------+------------------------------+-------------------------+------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ip | plan_depth | plan_line_id | operator | name | rows | cost | property |
+--------------+------------+--------------+------------------------------+-------------------------+------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10.144.2.107 | 0 | 0 | PHY_LIMIT | NULL | 10 | 2422 | NULL |
| 10.144.2.107 | 1 | 1 | PHY_PX_MERGE_SORT_COORD | NULL | 10 | 2421 | NULL |
| 10.144.2.107 | 2 | 2 | PHY_PX_REDUCE_TRANSMIT | NULL | 10 | 2410 | NULL |
| 10.144.2.107 | 3 | 3 | PHY_LIMIT | NULL | 10 | 2410 | NULL |
| 10.144.2.107 | 4 | 4 | PHY_SORT | NULL | 10 | 2409 | NULL |
| 10.144.2.107 | 5 | 5 | PHY_HASH_GROUP_BY | NULL | 64 | 2212 | NULL |
| 10.144.2.107 | 6 | 6 | PHY_PX_FIFO_RECEIVE | NULL | 100 | 2107 | NULL |
| 10.144.2.107 | 7 | 7 | PHY_PX_DIST_TRANSMIT | NULL | 100 | 2000 | NULL |
| 10.144.2.107 | 8 | 8 | PHY_GRANULE_ITERATOR | NULL | 100 | 2000 | NULL |
| 10.144.2.107 | 9 | 9 | PHY_TABLE_SCAN | __all_virtual_sql_audit | 100 | 2000 | table_rows:600000, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:basic_stat, avaiable_index_name[__all_virtual_sql_audit], pruned_index_name[all_virtual_sql_audit_i1] |
+--------------+------------+--------------+------------------------------+-------------------------+------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.03 sec)
分析执行计划
MySQL [benchmark]> explain SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 202 AND s_quantity < 19 AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 202 AND d_id = 8 ) ) tmp;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
------------------------------------------------------------
|0 |EXCHANGE IN REMOTE | |1 |70176|
|1 | EXCHANGE OUT REMOTE | |1 |70176|
|2 | SCALAR GROUP BY | |1 |70176|
|3 | NESTED-LOOP JOIN | |2531 |69693|
|4 | SUBPLAN SCAN |VIEW2 |1804 |4202 |
|5 | HASH DISTINCT | |1804 |3953 |
|6 | NESTED-LOOP JOIN| |1822 |2064 |
|7 | TABLE GET |bmsql_district |1 |53 |
|8 | TABLE SCAN |bmsql_order_line|5465 |2498 |
|9 | TABLE GET |bmsql_stock |1 |37 |
============================================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_COUNT(*)]), filter(nil)
1 - output([T_FUN_COUNT(*)]), filter(nil)
2 - output([T_FUN_COUNT(*)]), filter(nil),
group(nil), agg_func([T_FUN_COUNT(*)])
3 - output([1]), filter(nil),
conds(nil), nl_params_([VIEW2.VIEW1.ol_i_id])
4 - output([VIEW2.VIEW1.ol_i_id]), filter(nil),
access([VIEW2.VIEW1.ol_i_id])
5 - output([bmsql_order_line.ol_i_id]), filter(nil),
distinct([bmsql_order_line.ol_i_id])
6 - output([bmsql_order_line.ol_i_id]), filter(nil),
conds(nil), nl_params_([bmsql_district.d_next_o_id - 20], [bmsql_district.d_next_o_id])
7 - output([bmsql_district.d_next_o_id]), filter([bmsql_district.d_next_o_id > bmsql_district.d_next_o_id - 20]),
access([bmsql_district.d_next_o_id]), partitions(p74)
8 - output([bmsql_order_line.ol_i_id]), filter(nil),
access([bmsql_order_line.ol_i_id]), partitions(p74)
9 - output([1]), filter([bmsql_stock.s_quantity < 19]),
access([bmsql_stock.s_quantity]), partitions(p74)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
获取第二条sql的文本
前4条都是查询审计日志的SQL忽略。从第7条开始，查询看到plan_id是61，sql_id是9C20469C2AD80467EB8EBD79AFF2EE0F
MySQL [oceanbase]> select distinct query_sql from gv$sql_audit where sql_id='98081DF291BAB86943C93DE34D37083D ' \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 = 818 AND s_quantity < 16 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 = 818 AND d_id = 5 ) ) tmp
1 row in set (0.45 sec)
查看实际执行计划
MySQL [oceanbase]> SELECT ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`gv$plan_cache_plan_explain` where tenant_id=1001 AND ip = '10.144.2.107' AND port=2882 AND plan_id=61;
+--------------+------------+--------------+----------------------------+------------------+------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ip | plan_depth | plan_line_id | operator | name | rows | cost | property |
+--------------+------------+--------------+----------------------------+------------------+------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10.144.2.107 | 0 | 0 | PHY_DIRECT_RECEIVE | NULL | 1 | 42834 | NULL |
| 10.144.2.107 | 1 | 1 | PHY_DIRECT_TRANSMIT | NULL | 1 | 42833 | NULL |
| 10.144.2.107 | 2 | 2 | PHY_SCALAR_AGGREGATE | NULL | 1 | 42833 | NULL |
| 10.144.2.107 | 3 | 3 | PHY_NESTED_LOOP_JOIN | NULL | 1630 | 42522 | NULL |
| 10.144.2.107 | 4 | 4 | PHY_SUBPLAN_SCAN | NULL | 1104 | 2449 | NULL |
| 10.144.2.107 | 5 | 5 | PHY_HASH_DISTINCT | NULL | 1104 | 2297 | NULL |
| 10.144.2.107 | 6 | 6 | PHY_NESTED_LOOP_JOIN | NULL | 1112 | 1142 | NULL |
| 10.144.2.107 | 7 | 7 | PHY_TABLE_SCAN | bmsql_district | 1 | 52 | table_rows:160, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:0, est_method:local_storage, avaiable_index_name[bmsql_district] |
| 10.144.2.107 | 7 | 8 | PHY_TABLE_SCAN | bmsql_order_line | 3334 | 1113 | table_rows:5578531, physical_range_rows:3334, logical_range_rows:3333, index_back_rows:0, output_rows:3333, est_method:local_storage, avaiable_index_name[bmsql_order_line], estimation info[table_id:1100611139453897, (table_type:1, version:0-1645725610481717-1645725610481717, logical_rc:29989, physical_rc:29989), (table_type:7, version:1645725600395743-1645725610481717-1645725630203780, logical_rc:0, physical_rc:0), (table_type:5, version:1645725600395743-1645725610481717-1645725630203780, logical_rc:0, physical_rc:0), (table_type:0, version:1645725630203780-1645725630203780-9223372036854775807, logical_rc:9, physical_rc:17)] |
| 10.144.2.107 | 4 | 9 | PHY_TABLE_SCAN | bmsql_stock | 1 | 36 | table_rows:1600000, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:0, est_method:local_storage, avaiable_index_name[bmsql_stock], estimation info[table_id:1100611139453899, (table_type:1, version:0-1645725610481717-1645725610481717, logical_rc:1600000, physical_rc:1600000), (table_type:7, version:1645725600395743-1645725610481717-1645725630002982, logical_rc:0, physical_rc:0), (table_type:5, version:1645725600395743-1645725610481717-1645725630002982, logical_rc:0, physical_rc:0), (table_type:0, version:1645725630002982-1645725630002982-9223372036854775807, logical_rc:0, physical_rc:153)] |
+--------------+------------+--------------+----------------------------+------------------+------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)
分析执行计划
MySQL [benchmark]> explain SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 818 AND s_quantity < 16 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 = 818 AND d_id = 5 ) ) tmp;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ==========================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
----------------------------------------------------------
|0 |SCALAR GROUP BY | |1 |77893|
|1 | NESTED-LOOP JOIN | |2519 |77412|
|2 | SUBPLAN SCAN |VIEW2 |2005 |4640 |
|3 | HASH DISTINCT | |2005 |4363 |
|4 | NESTED-LOOP JOIN| |2030 |2262 |
|5 | TABLE GET |bmsql_district |1 |53 |
|6 | TABLE SCAN |bmsql_order_line|6088 |2688 |
|7 | TABLE GET |bmsql_stock |1 |37 |
==========================================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_COUNT(*)]), filter(nil),
group(nil), agg_func([T_FUN_COUNT(*)])
1 - output([1]), filter(nil),
conds(nil), nl_params_([VIEW2.VIEW1.ol_i_id])
2 - output([VIEW2.VIEW1.ol_i_id]), filter(nil),
access([VIEW2.VIEW1.ol_i_id])
3 - output([bmsql_order_line.ol_i_id]), filter(nil),
distinct([bmsql_order_line.ol_i_id])
4 - output([bmsql_order_line.ol_i_id]), filter(nil),
conds(nil), nl_params_([bmsql_district.d_next_o_id - 20], [bmsql_district.d_next_o_id])
5 - output([bmsql_district.d_next_o_id]), filter([bmsql_district.d_next_o_id > bmsql_district.d_next_o_id - 20]),
access([bmsql_district.d_next_o_id]), partitions(p50)
6 - output([bmsql_order_line.ol_i_id]), filter(nil),
access([bmsql_order_line.ol_i_id]), partitions(p50)
7 - output([1]), filter([bmsql_stock.s_quantity < 16]),
access([bmsql_stock.s_quantity]), partitions(p50)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
获取第三条SQL文本
前4条都是查询审计日志的SQL忽略。第10条，查询看到plan_id是73，sql_id是 FC49A0648E312569A29359AC00C38B4E
MySQL [oceanbase]> select distinct query_sql from gv$sql_audit where sql_id='FC49A0648E312569A29359AC00C38B4E ' \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 = 818 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 = 818 AND d_id = 5 ) ) tmp
1 row in set (0.37 sec)
分析实际执行计划
MySQL [benchmark]> explain SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 818 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 = 818 AND d_id = 5 ) ) tmp;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
------------------------------------------------------------
|0 |EXCHANGE IN REMOTE | |1 |77951|
|1 | EXCHANGE OUT REMOTE | |1 |77951|
|2 | SCALAR GROUP BY | |1 |77951|
|3 | NESTED-LOOP JOIN | |2686 |77438|
|4 | SUBPLAN SCAN |VIEW2 |2005 |4640 |
|5 | HASH DISTINCT | |2005 |4363 |
|6 | NESTED-LOOP JOIN| |2030 |2262 |
|7 | TABLE GET |bmsql_district |1 |53 |
|8 | TABLE SCAN |bmsql_order_line|6088 |2688 |
|9 | TABLE GET |bmsql_stock |1 |37 |
============================================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_COUNT(*)]), filter(nil)
1 - output([T_FUN_COUNT(*)]), filter(nil)
2 - output([T_FUN_COUNT(*)]), filter(nil),
group(nil), agg_func([T_FUN_COUNT(*)])
3 - output([1]), filter(nil),
conds(nil), nl_params_([VIEW2.VIEW1.ol_i_id])
4 - output([VIEW2.VIEW1.ol_i_id]), filter(nil),
access([VIEW2.VIEW1.ol_i_id])
5 - output([bmsql_order_line.ol_i_id]), filter(nil),
distinct([bmsql_order_line.ol_i_id])
6 - output([bmsql_order_line.ol_i_id]), filter(nil),
conds(nil), nl_params_([bmsql_district.d_next_o_id - 20], [bmsql_district.d_next_o_id])
7 - output([bmsql_district.d_next_o_id]), filter([bmsql_district.d_next_o_id > bmsql_district.d_next_o_id - 20]),
access([bmsql_district.d_next_o_id]), partitions(p50)
8 - output([bmsql_order_line.ol_i_id]), filter(nil),
access([bmsql_order_line.ol_i_id]), partitions(p50)
9 - output([1]), filter([bmsql_stock.s_quantity < 18]),
access([bmsql_stock.s_quantity]), partitions(p50)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
扫一扫
1692
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
