参考：https://www.modb.pro/db/1698342524062617600
一、 数据库环境
#操作系统及配置
[root@localhost ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
[root@localhost ~]# free -g
total used free shared
buff/cache available
Mem: 15 3 7 0 3 11
Swap: 0 0 0
[root@localhost ~]# cat /proc/cpuinfo |
grep processor
processor : 0
processor : 1
processor : 2
processor : 3
processor : 4
processor : 5
processor : 6
processor : 7
#oceanbase环境
obclient [test]> select version();
+------------------------------+
| version() |
+------------------------------+
| 5.7.25-OceanBase_CE-v4.2.1.2 |
+------------------------------+
1 row in set (0.002 sec)
obclient [test]> show variables like
'%chara%';
+--------------------------+---------+
| Variable_name | Value |
+--------------------------+---------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb4 |
+--------------------------+---------+
7 rows in set (0.005 sec)
二、 Tpcc环境准备及压力测试
BenchmarkSQL 的官方下载地址为：https://sourceforge.net/projects/benchmarksql/
但是由于 Benchmark SQL5 不支持 OceanBase 数据库的 TPC-C 测试，需要通过修改 BenchMarkSQL5 部分源码支持 OceanBase 数据库
下面是ob官方文档中关于修改源码的介绍
https://www.oceanbase.com/docs/community-observer-cn-10000000000901541
从官网下载的修改比较麻烦选择了git上下载
git clone https://github.com/obpilot/benchmarksql-5.0.git
#遇到问题：
1）、##修改数据类型varchar2-->varchar
脚本里的建表语句字符类型为varchar2修改为varchar
cd ~/benchmarksql-5.0/run/sql.oceanbase
sed -i 's/varchar2/varchar/g'
tableCreates.sql
2）、oceanbase的默认脚本里会有表组，需要提前创建；
#创建表组
MySQL [(none)]> create tablegroup tpcc_group;
#查看表组
show tablegroups;
1、修改props.ob文件
/root/benchmarksql-5.0/run/props.ob
db=oracle
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://127.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8
user=root@obmysql ###主要修改用户名密码
password=oracle123
warehouses=2
loadWorkers=2
terminals=2
…
2、创建测试库
Create database tpcc;
3、创建表
./runSQL.sh props.ob
./sql.oceanbase/tableCreates.sql
[root@localhost run]# ./runSQL.sh props.ob
./sql.oceanbase/tableCreates.sql
#
------------------------------------------------------------
# Loading SQL file
./sql.oceanbase/tableCreates.sql
#
------------------------------------------------------------
create table bmsql_config (
cfg_name
varchar(30) primary key,
cfg_value
varchar(50)
);
create tablegroup "tpcc_group" ;
---报错忽略已手工创建
You have an error in your SQL syntax; check
the manual that corresponds to your OceanBase version for the right syntax to
use near '"tpcc_group"' at line 1
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' ;
…..
MySQL [tpcc]> show tables;
+------------------+
| Tables_in_tpcc |
+------------------+
| bmsql_config |
| bmsql_customer |
| bmsql_district |
| bmsql_history |
| bmsql_item |
| bmsql_new_order |
| bmsql_oorder |
| bmsql_order_line |
| bmsql_stock |
| bmsql_warehouse |
+------------------+
4、创建索引
[root@localhost run]# ./runSQL.sh props.ob
sql.oceanbase/indexCreates.sql
#
------------------------------------------------------------
# Loading SQL file
sql.oceanbase/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;
5、加载数据
[root@localhost run]# ./runLoader.sh
props.ob
Starting BenchmarkSQL LoadData
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://127.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8
user=root@obmysql
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
+----------+------------------+
| count(1) | bmsql_config |
+----------+------------------+
|
4 | bmsql_config |
|
60000 | bmsql_customer |
|
20 | bmsql_district |
|
60000 | bmsql_history |
|
100000 | bmsql_item |
|
18000 | bmsql_new_order |
|
60000 | bmsql_oorder |
|
599420 | bmsql_order_line |
|
200000 | bmsql_stock |
|
2 | bmsql_warehouse |
+----------+------------------+
6、开始压测
[root@localhost run]# ./runBenchmark.sh
props.ob
04:51:52,817 [main] INFO jTPCC : Term-00,
Term-00, Running Average tpmTOTAL:
0.00 Current tpmTOTAL: 0 Memory Usage: 17MB / 238MB
Term-00, Running Average tpmTOTAL:
5952.90 Current tpmTOTAL: 39276 Memory Usage: 37MB / 215MB
04:52:53,342 [Thread-9] INFO jTPCC : Term-00,
04:52:53,342 [Thread-9] INFO jTPCC : Term-00,
04:52:53,342 [Thread-9] INFO jTPCC : Term-00, Measured tpmC (NewOrders) =
2723.82
04:52:53,342 [Thread-9] INFO jTPCC : Term-00, Measured tpmTOTAL = 5952.87
04:52:53,342 [Thread-9] INFO jTPCC : Term-00, Session Start = 2023-12-22 04:51:53
04:52:53,342 [Thread-9] INFO jTPCC : Term-00, Session End = 2023-12-22 04:52:53
04:52:53,342 [Thread-9] INFO jTPCC : Term-00, Transaction Count = 5961
三、 获取数据库TOP
SQL及分析执行计划
1、查询前三的topsql
MySQL [tpcc]> SELECT
avg_exe_usec,tenant_id, svr_ip, svr_port, sql_id, plan_id FROM
oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT
ORDER BY avg_exe_usec DESC LIMIT 3 ;
+--------------+-----------+-----------------+----------+----------------------------------+---------+
| avg_exe_usec | tenant_id | svr_ip | svr_port | sql_id | plan_id |
+--------------+-----------+-----------------+----------+----------------------------------+---------+
|
1121729 | 1002 |
192.168.150.116 | 2882 |
BBC7374E8DB6F844AB0A13451D3195E8 |
1040 |
|
294941 | 1002 |
192.168.150.116 | 2882 |
F59A700FA168324279B0DBC25E19760F |
1045 |
|
164315 | 1002 |
192.168.150.116 | 2882 |
3BF9D8DD2E998A807574D24C2F5BDAEC |
1052 |
+--------------+-----------+-----------------+----------+----------------------------------+---------+
2、分析第一条sql
#查看sql内容
MySQL [tpcc]> select query_sql from
oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT where
sql_id='BBC7374E8DB6F844AB0A13451D3195E8'\G
*************************** 1. row
***************************
query_sql: select count(1) , 'bmsql_config' from bmsql_config union
select count(1) ,
'bmsql_customer' from bmsql_customer union select count(1) , 'bmsql_district' from
bmsql_district union select count(1) , 'bmsql_history' from
bmsql_history union select count(1) , 'bmsql_item' from
bmsql_item union select count(1) , 'bmsql_new_order' from
bmsql_new_order union select count(1) , 'bmsql_oorder' from
bmsql_oorder union select count(1) , 'bmsql_order_line' from bmsql_order_line union
select count(1) ,
'bmsql_stock' from bmsql_stock
union select count(1) , 'bmsql_warehouse' from
bmsql_warehouse
1 row in set (0.12 sec)
#查看解析执行计划
MySQL [tpcc]> explain select count(1)
, 'bmsql_config' from bmsql_config union
select count(1) ,
'bmsql_customer' from bmsql_customer union select count(1) , 'bmsql_district' from
bmsql_district union select count(1) , 'bmsql_history' from
bmsql_history union select count(1) , 'bmsql_item' from
bmsql_item union select count(1) , 'bmsql_new_order' from
bmsql_new_order union select count(1) , 'bmsql_oorder' from
bmsql_oorder union select count(1) , 'bmsql_order_line' from bmsql_order_line union
select count(1) ,
'bmsql_stock' from bmsql_stock
union select count(1) , 'bmsql_warehouse' from
bmsql_warehouse;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
====================================================================================
|
| |ID|OPERATOR |NAME
|EST.ROWS|EST.TIME(us)|
|
|
------------------------------------------------------------------------------------
|
| |0 |HASH DISTINCT | |1 |61030 |
|
| |1 |└─UNION ALL | |10 |61029 |
|
| |2 |
├─SCALAR GROUP BY | |1 |4 |
|
| |3 |
│ └─TABLE FULL SCAN|bmsql_config |4 |4 |
|
| |4 |
├─SCALAR GROUP BY | |1 |3477 |
|
| |5 |
│ └─TABLE FULL SCAN|bmsql_customer(bmsql_customer_idx1)|120000 |1302
|
|
| |6 |
├─SCALAR GROUP BY | |1 |59 |
|
| |7 |
│ └─TABLE FULL SCAN|bmsql_district |13 |59 |
|
| |8 |
├─SCALAR GROUP BY | |1 |1893 |
|
| |9 |
│ └─TABLE FULL SCAN|bmsql_history |65277 |710
|
|
| |10|
├─SCALAR GROUP BY | |1 |5792 |
|
| |11|
│ └─TABLE FULL SCAN|bmsql_item |200000 |2167
|
|
| |12|
├─SCALAR GROUP BY | |1 |1123 |
|
| |13|
│ └─TABLE FULL SCAN|bmsql_new_order |31922 |544
|
|
| |14|
├─SCALAR GROUP BY | |1 |3588 |
|
| |15|
│ └─TABLE FULL SCAN|bmsql_oorder |120855 |1398
|
|
| |16|
├─SCALAR GROUP BY | |1 |38687 |
|
| |17|
│ └─TABLE FULL SCAN|bmsql_order_line |1305635 |15024 |
|
| |18|
├─SCALAR GROUP BY | |1 |6380 |
|
| |19|
│ └─TABLE FULL SCAN|bmsql_stock |200000 |2755
|
|
| |20|
└─SCALAR GROUP BY | |1 |31 |
|
| |21|
└─TABLE FULL SCAN|bmsql_warehouse |1 |30 |
|
|
====================================================================================
#查看真实执行计划
MySQL [tpcc]> SELECT SVR_IP, plan_depth,
plan_line_id,operator,name,rows,cost,property
-> from oceanbase.`GV$OB_PLAN_CACHE_PLAN_EXPLAIN`
-> where tenant_id=1002 AND SVR_IP = '192.168.150.116' AND
SVR_PORT=2882 AND plan_id=1040;
+-----------------+------------+--------------+------------------------+-------------------------------------+--------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SVR_IP | plan_depth | plan_line_id |
operator | name | rows | cost
| property
|
+-----------------+------------+--------------+------------------------+-------------------------------------+--------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 192.168.150.116 | 0 | 0 | PHY_HASH_DISTINCT | NULL | 1 | 31796 | NULL
|
| 192.168.150.116 | 1 | 1 |
PHY_MERGE_UNION | NULL | 10 | 31795 | NULL
|
| 192.168.150.116 | 2 | 2 | PHY_SCALAR_AGGREGATE | NULL | 1 |
3 | NULL
|
| 192.168.150.116 | 3 | 3 | PHY_TABLE_SCAN | bmsql_config | 4 |
3 | table_rows:4, physical_range_rows:4, logical_range_rows:4,
index_back_rows:0, output_rows:4, avaiable_index_name[bmsql_config], estimation
info[table_id:500089, (table_type:12, version:-1--1--1, logical_rc:4,
physical_rc:4)]
|
| 192.168.150.116 | 2 | 4 | PHY_SCALAR_AGGREGATE | NULL | 1 |
1740 | NULL
|
| 192.168.150.116 | 3 | 5 | PHY_TABLE_SCAN | bmsql_customer(bmsql_customer_idx1)
| 60000 | 652 | table_rows:60000,
physical_range_rows:60000, logical_range_rows:60000, index_back_rows:0,
output_rows:60000, avaiable_index_name[bmsql_customer_idx1,bmsql_customer],
estimation info[table_id:500099, (table_type:11, version:-1--1--1,
logical_rc:57000, physical_rc:57000), (table_type:12, version:-1--1--1,
logical_rc:3000, physical_rc:3000)] |
| 192.168.150.116 | 2 | 6 | PHY_SCALAR_AGGREGATE | NULL | 1 |
4 | NULL
|
| 192.168.150.116 | 3 | 7 | PHY_TABLE_SCAN | bmsql_district | 20 |
3 | table_rows:20, physical_range_rows:20, logical_range_rows:20,
index_back_rows:0, output_rows:20, avaiable_index_name[bmsql_district],
estimation info[table_id:500091, (table_type:11, version:-1--1--1,
logical_rc:19, physical_rc:19), (table_type:12, version:-1--1--1, logical_rc:1,
physical_rc:1)]
|
| 192.168.150.116 | 2 | 8 | PHY_SCALAR_AGGREGATE | NULL | 1 |
1740 | NULL
|
| 192.168.150.116 | 3 | 9 | PHY_TABLE_SCAN | bmsql_history | 60000 |
652 | table_rows:60000, physical_range_rows:60000,
logical_range_rows:60000, index_back_rows:0, output_rows:60000,
avaiable_index_name[bmsql_history], estimation info[table_id:500093,
(table_type:11, version:-1--1--1, logical_rc:57000, physical_rc:57000),
(table_type:12, version:-1--1--1, logical_rc:3000, physical_rc:3000)] |
| 192.168.150.116 | 2 | 10 | PHY_SCALAR_AGGREGATE | NULL | 1 |
2897 | NULL
|
| 192.168.150.116 | 3 | 11 | PHY_TABLE_SCAN | bmsql_item | 100000 | 1085 | table_rows:100000,
physical_range_rows:100000, logical_range_rows:100000, index_back_rows:0,
output_rows:100000, avaiable_index_name[bmsql_item], estimation
info[table_id:500097, (table_type:12, version:-1--1--1, logical_rc:63198,
physical_rc:63198), (table_type:12, version:-1--1--1, logical_rc:36802,
physical_rc:36802)] |
| 192.168.150.116 | 2 | 12 | PHY_SCALAR_AGGREGATE | NULL | 1 |
524 | NULL
|
| 192.168.150.116 | 3 | 13 | PHY_TABLE_SCAN | bmsql_new_order | 18000 |
198 | table_rows:18000, physical_range_rows:18000,
logical_range_rows:18000, index_back_rows:0, output_rows:18000,
avaiable_index_name[bmsql_new_order], estimation info[table_id:500094,
(table_type:11, version:-1--1--1, logical_rc:15774, physical_rc:15774),
(table_type:12, version:-1--1--1, logical_rc:2226, physical_rc:2226)] |
| 192.168.150.116 | 2 | 14 | PHY_SCALAR_AGGREGATE | NULL | 1 |
1740 | NULL
|
| 192.168.150.116 | 3 | 15 | PHY_TABLE_SCAN | bmsql_oorder(bmsql_oorder_idx1) |
60000 | 652 | table_rows:60000,
physical_range_rows:60000, logical_range_rows:60000, index_back_rows:0,
output_rows:60000, avaiable_index_name[bmsql_oorder_idx1,bmsql_oorder],
estimation info[table_id:500100, (table_type:11, version:-1--1--1,
logical_rc:57000, physical_rc:57000), (table_type:12, version:-1--1--1,
logical_rc:3000, physical_rc:3000)] |
| 192.168.150.116 | 2 | 16 | PHY_SCALAR_AGGREGATE | NULL | 1 | 17349 | NULL
|
| 192.168.150.116 | 3 | 17 | PHY_TABLE_SCAN | bmsql_order_line | 599420 | 6485 | table_rows:599420,
physical_range_rows:599420, logical_range_rows:599420, index_back_rows:0,
output_rows:599420, avaiable_index_name[bmsql_order_line], estimation
info[table_id:500096, (table_type:11, version:-1--1--1, logical_rc:547387,
physical_rc:547387), (table_type:12, version:-1--1--1, logical_rc:52033,
physical_rc:52033)] |
| 192.168.150.116 | 2 | 18 | PHY_SCALAR_AGGREGATE | NULL | 1 |
5791 | NULL
|
| 192.168.150.116 | 3 | 19 | PHY_TABLE_SCAN | bmsql_stock | 200000 | 2166 | table_rows:200000,
physical_range_rows:200000, logical_range_rows:200000, index_back_rows:0,
output_rows:200000, avaiable_index_name[bmsql_stock], estimation
info[table_id:500098, (table_type:11, version:-1--1--1, logical_rc:200000,
physical_rc:200000)]
|
| 192.168.150.116 | 2 | 20 | PHY_SCALAR_AGGREGATE | NULL | 1 |
3 | NULL
|
| 192.168.150.116 | 3 | 21 | PHY_TABLE_SCAN | bmsql_warehouse | 2 |
3 | table_rows:2, physical_range_rows:2, logical_range_rows:2,
index_back_rows:0, output_rows:2, avaiable_index_name[bmsql_warehouse],
estimation info[table_id:500090, (table_type:12, version:-1--1--1,
logical_rc:1, physical_rc:1), (table_type:12, version:-1--1--1, logical_rc:1,
physical_rc:1)]
|
+-----------------+------------+--------------+------------------------+-------------------------------------+--------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3、分析第二条sql
#查看sql内容
MySQL [tpcc]> select query_sql from
oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT 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 <
17 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 = 2 ) )
#查看解析执行计划
MySQL [tpcc]> 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 < 17 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 = 2 ) );
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
|
================================================================================
|
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
|
|
--------------------------------------------------------------------------------
|
| |0 |SCALAR GROUP BY | |1 |11345 |
|
| |1 |└─HASH RIGHT SEMI JOIN | |2338 |11303
|
|
| |2 |
├─SUBPLAN SCAN
|VIEW1 |2386 |2922
|
|
| |3 |
│ └─NESTED-LOOP JOIN
| |2386 |2916
|
|
| |4 |
│ ├─TABLE GET |bmsql_district |1
|3 |
|
| |5 |
│ └─DISTRIBUTED TABLE RANGE
SCAN|bmsql_order_line|21474 |2377 |
|
| |6 |
└─TABLE RANGE SCAN
|bmsql_stock |10000 |7074
|
|
|
================================================================================
#查看真实执行计划
MySQL [tpcc]> SELECT SVR_IP, plan_depth,
plan_line_id,operator,name,rows,cost,property
-> from oceanbase.`GV$OB_PLAN_CACHE_PLAN_EXPLAIN`
-> where tenant_id=1002 AND SVR_IP = '192.168.150.116' AND
SVR_PORT=2882 AND plan_id=1045;
+-----------------+------------+--------------+-------------------------+------------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SVR_IP | plan_depth | plan_line_id |
operator | name | rows | cost | property
|
+-----------------+------------+--------------+-------------------------+------------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 192.168.150.116 | 0 | 0 | PHY_SCALAR_AGGREGATE | NULL | 1 | 9435 | NULL
|
| 192.168.150.116 | 1 | 1 |
PHY_HASH_JOIN | NULL |
3111 | 9378 | NULL
|
| 192.168.150.116 | 2 | 2 | PHY_SUBPLAN_SCAN | NULL |
2183 | 2546 | NULL
|
| 192.168.150.116 | 3 | 3 | PHY_NESTED_LOOP_JOIN | NULL |
2183 | 2540 | NULL
|
| 192.168.150.116 | 4 | 4 | PHY_TABLE_SCAN | bmsql_district |
1 | 2 | table_rows:20,
physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:0,
avaiable_index_name[bmsql_district]
|
| 192.168.150.116 | 4 | 5 | PHY_TABLE_SCAN | bmsql_order_line | 19639 | 2047 |
table_rows:292766, physical_range_rows:19638, logical_range_rows:19638,
index_back_rows:0, output_rows:19638, avaiable_index_name[bmsql_order_line],
estimation info[table_id:500096, (table_type:10, version:-1--1--1,
logical_rc:29458, physical_rc:29458), (table_type:11, version:-1--1--1,
logical_rc:29458, physical_rc:29458), (table_type:12, version:-1--1--1,
logical_rc:0, physical_rc:0), (table_type:0, version:-1--1--1, logical_rc:0,
physical_rc:0)] |
| 192.168.150.116 | 2 | 6 | PHY_TABLE_SCAN | bmsql_stock | 10000 | 5535 | table_rows:200000,
physical_range_rows:100025, logical_range_rows:100000, index_back_rows:0,
output_rows:10000, avaiable_index_name[bmsql_stock], estimation
info[table_id:500098, (table_type:11, version:-1--1--1, logical_rc:100000,
physical_rc:100000), (table_type:0, version:-1--1--1, logical_rc:0,
physical_rc:25)]
|
4、分析第三条sql
#查看sql内容
MySQL [tpcc]> select query_sql from
oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT where
sql_id='3BF9D8DD2E998A807574D24C2F5BDAEC'\G
*************************** 1. row
***************************
query_sql: SELECT /*+ NO_REWRITE
NO_PARALLEL DYNAMIC_SAMPLING(0) QUERY_TIMEOUT(984642) OPT_PARAM('USE_DEFAULT_OPT_STAT','TRUE') */
COUNT(*), APPROX_COUNT_DISTINCT(`ol_o_id`), SUM(CASE WHEN `ol_o_id` IS NULL
THEN 1 ELSE 0 END), APPROX_COUNT_DISTINCT(`ol_i_id`), SUM(CASE WHEN `ol_i_id`
IS NULL THEN 1 ELSE 0 END) FROM `tpcc`.`bmsql_order_line` SAMPLE BLOCK(1.203008) SEED(1)
1 row in set (0.00 sec)
#查看解析执行计划
MySQL [tpcc]> explain SELECT /*+
NO_REWRITE NO_PARALLEL
DYNAMIC_SAMPLING(0)
QUERY_TIMEOUT(984642)
OPT_PARAM('USE_DEFAULT_OPT_STAT','TRUE') */ COUNT(*),
APPROX_COUNT_DISTINCT(`ol_o_id`), SUM(CASE WHEN `ol_o_id` IS NULL THEN 1 ELSE 0
END), APPROX_COUNT_DISTINCT(`ol_i_id`), SUM(CASE WHEN `ol_i_id` IS NULL THEN 1
ELSE 0 END) FROM `tpcc`.`bmsql_order_line`
SAMPLE BLOCK(1.203008) SEED(1);
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
=====================================================================
|
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
|
|
---------------------------------------------------------------------
|
| |0 |SCALAR GROUP BY | |1 |2215 |
|
| |1 |└─TABLE BLOCK SAMPLE
SCAN|bmsql_order_line|15707 |958 |
|
|
=====================================================================
|
| Outputs & filters:
|
|
-------------------------------------
|
| 0
- output([T_FUN_COUNT(*)],
[ESTIMATE_NDV(T_FUN_APPROX_COUNT_DISTINCT_SYNOPSIS(bmsql_order_line.ol_o_id))],
[T_FUN_SUM(CASE WHEN bmsql_order_line.ol_o_id |
|
IS NULL THEN 1 ELSE 0 END)],
[ESTIMATE_NDV(T_FUN_APPROX_COUNT_DISTINCT_SYNOPSIS(bmsql_order_line.ol_i_id))],
[T_FUN_SUM(CASE WHEN bmsql_order_line.ol_i_id |
|
IS NULL THEN 1 ELSE 0 END)]), filter(nil), rowset=256
|
|
group(nil), agg_func([T_FUN_COUNT(*)], [T_FUN_SUM(CASE WHEN
bmsql_order_line.ol_o_id IS NULL THEN 1 ELSE 0 END)], [T_FUN_SUM(CASE WHEN
bmsql_order_line.ol_i_id |
|
IS NULL THEN 1 ELSE 0 END)],
[T_FUN_APPROX_COUNT_DISTINCT_SYNOPSIS(bmsql_order_line.ol_o_id)],
[T_FUN_APPROX_COUNT_DISTINCT_SYNOPSIS(bmsql_order_line.ol_i_id)]) |
| 1
- output([bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), filter(nil),
rowset=256
|
|
access([bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]),
partitions(p0)
|
|
is_index_back=false, is_global_index=false,
|
|
range_key([bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id],
[bmsql_order_line.ol_o_id], [bmsql_order_line.ol_number]),
range(MIN,MIN,MIN,MIN |
|
; MAX,MAX,MAX,MAX)always true
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
#查看真实执行计划
MySQL [tpcc]> SELECT SVR_IP, plan_depth,
plan_line_id,operator,name,rows,cost,property
-> from oceanbase.`GV$OB_PLAN_CACHE_PLAN_EXPLAIN`
-> where tenant_id=1002 AND SVR_IP = '192.168.150.116' AND
SVR_PORT=2882 AND plan_id=1052;
+-----------------+------------+--------------+------------------------+------+------+------+----------+
| SVR_IP | plan_depth | plan_line_id |
operator | name | rows |
cost | property |
+-----------------+------------+--------------+------------------------+------+------+------+----------+
| 192.168.150.116 | 0 | 0 | PHY_SCALAR_AGGREGATE | NULL |
1 | 3 | NULL |
| 192.168.150.116 | 1 | 1 |
PHY_BLOCK_SAMPLE_SCAN | NULL |
1 | 3 | NULL |
+-----------------+------------+--------------+------------------------+------+------+------+----------+
2 rows in set (0.00 sec)
实验完成了，但是要学习的东西还有很多，等把oceanbase系统学习一遍后，要回头再重新做一边，增加印象
