目录
1 使用 BenmarkSQL 运行 TPC-C
1.1 下载BenmarkSQL
git clone https://github.com/obpilot/benchmarksql-5.0.git
1.2 编辑props.ob配置文件
这个配置文件在/root/benchmarksql-5.0/run/目录下,编辑后的内容如下:
db=oracle
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://127.1:2883/tpccdb?useUnicode=true&characterEncoding=utf-8
user=tpcc@my_tenant#obcluster
password=123456
warehouses=2
loadWorkers=1
terminals=5
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=5
//Number of total transactions per minute
limitTxnsPerMin=0
//Set to true to run in 4.x compatible mode. Set to false to use the
//entire configured database evenly.
terminalWarehouseFixed=true
//The following five values must add up to 100
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
[root@localhost run]# pwd
/root/benchmarksql-5.0/run
1.3 运行创建表语句
[root@localhost run]# sh runSQL.sh props.ob sql.common/tableCreates.sql
这里出了点小问题,这个脚本报找不到funcs.sh文件,应该是环境变量的问题,不过通过更改脚本中funcs.sh路径为绝对路径也可以解决这个问题:
编辑一下runSQL.sh文件,更改内容如下:
#!/usr/bin/env bash
# ----
# Check command line usage
# ----
if [ $# -ne 2 ] ; then
echo "usage: $(basename $0) PROPS_FILE SQL_FILE" >&2
exit 2
fi
# ----
# Load common functions
# ----
source /root/benchmarksql-5.0/run/funcs.sh $1
再运行创建表的命令,表创建正常了,下面的脚本都需要做这样的更改
[root@localhost run]# sh 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)
---------
---------
1.4 数据装载
装载前先优化一下测试租户的设置,避免大事务超时,先登陆到租户
obclient -h127.0.0.1 -P 2883 -u root@my_tenant -p -A -c
设置以下租户全局变量:
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;
运行脚本,装载数据,输出显示装载成功,由于是笔记本电脑,仓库数选的小一些
[root@localhost run]# sh runLoader.sh props.ob
Starting BenchmarkSQL LoadData
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://127.1:2883/tpccdb?useUnicode=true&characterEncoding=utf-8
user=tpcc@my_tenant#obcluster
password=***********
warehouses=2
loadWorkers=1
fileLocation (not defined)
csvNullValue (not defined - using default 'NULL')
Worker 000: Loading ITEM
Worker 000: Loading ITEM done
Worker 000: Loading Warehouse 1
Worker 000: Loading Warehouse 1 done
Worker 000: Loading Warehouse 2
Worker 000: Loading Warehouse 2 done
1.5 登陆到数据库,创建两个索引
MySQL [(none)]> use tpccdb
Database changed
MySQL [tpccdb]> create index bmsql_customer_idx1
-> on bmsql_customer (c_w_id, c_d_id, c_last, c_first) local;
Query OK, 0 rows affected (1.506 sec)
MySQL [tpccdb]> create index bmsql_oorder_idx1
-> on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local;
Query OK, 0 rows affected (1.181 sec)
1.6 运行TPCC测试
[root@localhost run]# sh runBenchmark.sh props.ob
20:20:46,632 [main] INFO jTPCC : Term-00,
20:20:46,636 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
20:20:46,636 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
20:20:46,636 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
20:20:46,636 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
20:20:46,637 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
20:20:46,639 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
20:20:46,639 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
20:20:46,639 [main] INFO jTPCC : Term-00,
20:20:46,639 [main] INFO jTPCC : Term-00, db=oracle
20:20:46,639 [main] INFO jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
20:20:46,640 [main] INFO jTPCC : Term-00, conn=jdbc:oceanbase://127.1:2883/tpccdb?useUnicode=true&characterEncoding=ut f-8
20:20:46,640 [main] INFO jTPCC : Term-00, user=tpcc@my_tenant#obcluster
20:20:46,640 [main] INFO jTPCC : Term-00,
20:20:46,640 [main] INFO jTPCC : Term-00, warehouses=2
20:20:46,640 [main] INFO jTPCC : Term-00, terminals=5
20:20:46,645 [main] INFO jTPCC : Term-00, runMins=5
20:20:46,645 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0
20:20:46,645 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
20:20:46,645 [main] INFO jTPCC : Term-00,
20:20:46,645 [main] INFO jTPCC : Term-00, newOrderWeight=45
20:20:46,645 [main] INFO jTPCC : Term-00, paymentWeight=43
20:20:46,645 [main] INFO jTPCC : Term-00, orderStatusWeight=4
20:20:46,645 [main] INFO jTPCC : Term-00, deliveryWeight=4
20:20:46,645 [main] INFO jTPCC : Term-00, stockLevelWeight=4
20:20:46,645 [main] INFO jTPCC : Term-00,
20:20:46,646 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
20:20:46,646 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
20:20:46,646 [main] INFO jTPCC : Term-00,
20:20:46,713 [main] INFO jTPCC : Term-00, copied props.ob to my_result_2022-01-26_202046/run.properties
20:20:46,722 [main] INFO jTPCC : Term-00, created my_result_2022-01-26_202046/data/runInfo.csv for runID 7
20:20:46,724 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2022-01-26_202046/data/result.c sv
20:20:46,725 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
20:20:46,726 [main] INFO jTPCC : Term-00, osCollectorInterval=1
20:20:46,726 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null
20:20:46,726 [main] INFO jTPCC : Term-00, osCollectorDevices=null
20:20:46,905 [main] INFO jTPCC : Term-00,
20:20:47,417 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 14
20:20:47,417 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 90
20:20:47,417 [main] INFO jTPCC : Term-00, Term-00, Running Average tpmTOTAL: 5.81 Curren20:26:59,279 [Thread-3] INFO jTPCC : Term-00, 186MB
20:26:59,279 [Thread-3] INFO jTPCC : Term-00,
20:26:59,279 [Thread-3] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 3.39
20:26:59,280 [Thread-3] INFO jTPCC : Term-00, Measured tpmTOTAL = 5.81
20:26:59,280 [Thread-3] INFO jTPCC : Term-00, Session Start = 2022-01-26 20:20:47
20:26:59,280 [Thread-3] INFO jTPCC : Term-00, Session End = 2022-01-26 20:26:59
20:26:59,280 [Thread-3] INFO jTPCC : Term-00, Transaction Count = 35
毕竟是笔记本电脑,内存仅有16G, tpmC才3.39, 五分钟一共运行了35个事务,指标低了点。
2 TPC-C TOP SQL分析
2.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 user_name='tpcc'
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 |
+----------------------------------+----------+------------------+---------------+
| 7229213613983BC5FDA15AD11EC70D01 | 2 | 4489936 | 4283007 |
| F59A700FA168324279B0DBC25E19760F | 1 | 4356147 | 4162021 |
| 5984364296F35BE1B71CD5622426385A | 1 | 2518614 | 2462524 |
| 482BA7822AE7BE644CEBEB55213E7284 | 1 | 1977823 | 1973265 |
| EC66B09D06D688727D0F999BFCFF5348 | 1 | 1857203 | 1851199 |
| E1F2BDA1D7391B757859ED3704E5AFB7 | 1 | 1825043 | 1821800 |
| | 1172 | 1454010 | 1183139 |
| E86A0CA8BE3F21A2FBC9F1F9855075A1 | 1 | 1306776 | 1173832 |
| A460265EC2F0763A15DD27CE9E4E2200 | 1 | 835242 | 590782 |
| F0EFFFCD85E71C241661E66EEA047C58 | 2 | 707358 | 678918 |
+----------------------------------+----------+------------------+---------------+
10 rows in set (0.119 sec)
2.2 对elapsed时间最长的前三条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 3;
+----------------------------------+----------+------------------+---------------+-----------+----------+-----------+---------+
| sql_id | count(*) | avg_elapsed_time | avg_exec_time | svr_ip | svr_port | tenant_id | plan_id |
+----------------------------------+----------+------------------+---------------+-----------+----------+-----------+---------+
| 7229213613983BC5FDA15AD11EC70D01 | 2 | 4489936 | 4283007 | 127.0.0.1 | 2882 | 1001 | 40 |
| F59A700FA168324279B0DBC25E19760F | 1 | 4356147 | 4162021 | 127.0.0.1 | 2882 | 1001 | 42 |
| 5984364296F35BE1B71CD5622426385A | 1 | 2518614 | 2462524 | 127.0.0.1 | 2882 | 1001 | 27 |
+----------------------------------+----------+------------------+---------------+-----------+----------+-----------+---------+
3 rows in set (0.088 sec)
获取第一条sql的文本
select distinct query_sql from gv$sql_audit where sql_id='7229213613983BC5FDA15AD11EC70D01';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 = 97744 FOR UPDATE |
| 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 = 10652 FOR UPDATE |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.068 sec)
第一个sql_id 有两个不同的文本,这两个文本的不同仅仅是传入参数不同,可以共享执行计划,这个计划的plan_id 为40,运行下面sql查询这条语句的实际执行计划
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 = '127.0.0.1' AND port=2882 AND plan_id=40;
查询结果为空集,看来这条语句的实际执行计划已经被从缓存里刷出去了,切换到tpcc db运行以下这条语句后,再进行查询,得到下面的执行计划
*************************** 1. row ***************************
ip: 127.0.0.1
plan_depth: 0
plan_line_id: 0
operator: PHY_TABLE_SCAN
name: bmsql_stock
rows: 9
cost: 248249
property: table_rows:86530, physical_range_rows:200159, logical_range_rows:86530, index_back_rows:0, output_rows:8, est_method:local_storage, avaiable_index_name[bmsql_stock], estimation info[table_id:1100611139453798, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643245354224215-1643245354224215, logical_rc:0, physical_rc:96654), (table_type:0, version:1643245354224215-1643245354224215-9223372036854775807, logical_rc:86530, physical_rc:103505)]
1 row in set (0.013 sec)
ERROR: No query specified
这条语句的解释执行计划如下:
*************************** 1. row ***************************
Query Plan: ============================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
--------------------------------------------
|0 |TABLE SCAN|bmsql_stock|9 |248250|
============================================
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 = 10652]),
access([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.016 sec)
ERROR: No query specified
可以看到,这条语句实际的执行计划和解释执行计划是相同的,都做了全表扫描,成本显示也相同。
获取第二条语句的文本
MySQL [oceanbase]> select query_sql from gv$sql_audit where sql_id='F59A700FA168324279B0DBC25E19760F';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| query_sql |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 2 AND s_quantity < 13 AND s_i_id IN (SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 2 AND d_id = 8 ) ) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.069 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 = '127.0.0.1' AND port=2882 AND plan_id=42\G;
*************************** 1. row ***************************
ip: 127.0.0.1
plan_depth: 0
plan_line_id: 0
operator: PHY_SCALAR_AGGREGATE
name: NULL
rows: 1
cost: 811804
property: NULL
*************************** 2. row ***************************
ip: 127.0.0.1
plan_depth: 1
plan_line_id: 1
operator: PHY_HASH_JOIN
name: NULL
rows: 2
cost: 811804
property: NULL
*************************** 3. row ***************************
ip: 127.0.0.1
plan_depth: 2
plan_line_id: 2
operator: PHY_SUBPLAN_SCAN
name: NULL
rows: 1
cost: 612151
property: NULL
*************************** 4. row ***************************
ip: 127.0.0.1
plan_depth: 3
plan_line_id: 3
operator: PHY_NESTED_LOOP_JOIN
name: NULL
rows: 1
cost: 612151
property: NULL
*************************** 5. row ***************************
ip: 127.0.0.1
plan_depth: 4
plan_line_id: 4
operator: PHY_TABLE_SCAN
name: bmsql_order_line
rows: 40
cost: 612101
property: table_rows:404006, physical_range_rows:600330, logical_range_rows:404006, index_back_rows:0, output_rows:39, est_method:local_storage, avaiable_index_name[bmsql_order_line], estimation info[table_id:1100611139453797, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643245354224215-1643245354224215, logical_rc:0, physical_rc:173262), (table_type:0, version:1643245354224215-1643245354224215-9223372036854775807, logical_rc:404006, physical_rc:427068)]
*************************** 6. row ***************************
ip: 127.0.0.1
plan_depth: 4
plan_line_id: 5
operator: PHY_MATERIAL
name: NULL
rows: 1
cost: 50
property: NULL
*************************** 7. row ***************************
ip: 127.0.0.1
plan_depth: 5
plan_line_id: 6
operator: PHY_TABLE_SCAN
name: bmsql_district
rows: 1
cost: 50
property: 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:1100611139453792, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643245354224215-1643245354224215, logical_rc:0, physical_rc:0), (table_type:0, version:1643245354224215-1643245354224215-9223372036854775807, logical_rc:20, physical_rc:20)]
*************************** 8. row ***************************
ip: 127.0.0.1
plan_depth: 2
plan_line_id: 7
operator: PHY_TABLE_SCAN
name: bmsql_stock
rows: 91
cost: 199621
property: table_rows:91148, physical_range_rows:200114, logical_range_rows:91148, index_back_rows:0, output_rows:90, est_method:local_storage, avaiable_index_name[bmsql_stock], estimation info[table_id:1100611139453798, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643245354224215-1643245354224215, logical_rc:0, physical_rc:96654), (table_type:0, version:1643245354224215-1643245354224215-9223372036854775807, logical_rc:91148, physical_rc:103460)]
8 rows in set (0.005 sec)
解释执行计划
explain SELECT count(*) AS low_stock FROM (SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock
WHERE s_w_id = 2 AND s_quantity < 13 AND s_i_id IN (
SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id
AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 2 AND d_id = 8))\G;
MySQL [tpccdb]> explain SELECT count(*) AS low_stock FROM (SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock
-> WHERE s_w_id = 2 AND s_quantity < 13 AND s_i_id IN (
-> SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id
-> AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 2 AND d_id = 8))\G;
*************************** 1. row ***************************
Query Plan: ============================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
------------------------------------------------------------
|0 |SCALAR GROUP BY | |1 |812132|
|1 | HASH RIGHT SEMI JOIN| |2 |812131|
|2 | SUBPLAN SCAN |VIEW1 |1 |612420|
|3 | NESTED-LOOP JOIN | |1 |612419|
|4 | TABLE SCAN |bmsql_order_line|37 |612369|
|5 | MATERIAL | |1 |51 |
|6 | TABLE SCAN |bmsql_district |1 |51 |
|7 | TABLE SCAN |bmsql_stock |86 |199683|
============================================================
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 = 2], [bmsql_order_line.ol_d_id = 8]),
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 = 2], [bmsql_district.d_id = 8], [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 = 2], [bmsql_stock.s_quantity < 13]),
access([bmsql_stock.s_w_id], [bmsql_stock.s_quantity], [bmsql_stock.s_i_id]), partitions(p0)
1 row in set (0.016 sec)
ERROR: No query specified
这条语句的解释执行计划和实际执行计划也是相同的。
获取第三条语句的文本
MySQL [oceanbase]> select query_sql from gv$sql_audit where sql_id='5984364296F35BE1B71CD5622426385A';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| query_sql |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_delivery_d FROM bmsql_order_line WHERE ol_w_id = 2 AND ol_d_id = 4 AND ol_o_id = 1182 ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.060 sec)
实际执行计划
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 = '127.0.0.1' AND port=2882 AND plan_id=27;
*************************** 1. row ***************************
ip: 127.0.0.1
plan_depth: 0
plan_line_id: 0
operator: PHY_SORT
name: NULL
rows: 1
cost: 778838
property: NULL
*************************** 2. row ***************************
ip: 127.0.0.1
plan_depth: 1
plan_line_id: 1
operator: PHY_TABLE_SCAN
name: bmsql_order_line
rows: 1
cost: 778837
property: table_rows:404006, physical_range_rows:600330, logical_range_rows:404006, index_back_rows:0, output_rows:0, est_method:local_storage, avaiable_index_name[bmsql_order_line], estimation info[table_id:1100611139453797, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643245354224215-1643245354224215, logical_rc:0, physical_rc:173262), (table_type:0, version:1643245354224215-1643245354224215-9223372036854775807, logical_rc:404006, physical_rc:427068)]
2 rows in set (0.014 sec)
解释执行计划
explain SELECT ol_i_id, ol_supply_w_id, ol_quantity,
ol_amount, ol_delivery_d
FROM bmsql_order_line WHERE ol_w_id = 2 AND ol_d_id = 4 AND ol_o_id = 1182
ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number\G;
MySQL [tpccdb]> explain SELECT ol_i_id, ol_supply_w_id, ol_quantity,
-> ol_amount, ol_delivery_d
-> FROM bmsql_order_line WHERE ol_w_id = 2 AND ol_d_id = 4 AND ol_o_id = 1182
-> ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number\G;
*************************** 1. row ***************************
Query Plan: ==================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
--------------------------------------------------
|0 |SORT | |1 |779182|
|1 | TABLE SCAN|bmsql_order_line|1 |779181|
==================================================
Outputs & filters:
-------------------------------------
0 - output([bmsql_order_line.ol_i_id], [bmsql_order_line.ol_supply_w_id], [bmsql_order_line.ol_quantity], [bmsql_order_line.ol_amount], [bmsql_order_line.ol_delivery_d]), filter(nil), sort_keys([bmsql_order_line.ol_number, ASC])
1 - output([bmsql_order_line.ol_i_id], [bmsql_order_line.ol_supply_w_id], [bmsql_order_line.ol_quantity], [bmsql_order_line.ol_amount], [bmsql_order_line.ol_delivery_d], [bmsql_order_line.ol_number]), filter([bmsql_order_line.ol_w_id = 2], [bmsql_order_line.ol_d_id = 4], [bmsql_order_line.ol_o_id = 1182]),
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], [bmsql_order_line.ol_supply_w_id], [bmsql_order_line.ol_quantity], [bmsql_order_line.ol_amount], [bmsql_order_line.ol_delivery_d], [bmsql_order_line.ol_number]), partitions(p0)
1 row in set (0.007 sec)
ERROR: No query specified
这条语句的解释执行计划和实际执行计划也是相同的,都是全表扫描之后进行排序。