一、环境
kc1.4xlarge.4 16c 64G 极速型SSD # cat /etc/kylin-release Kylin Linux Advanced Server release V10 (Tercel)ob 单zone 3副本
二、安装benchmarksql 5.1
2.1 获取源码
wget <https://github.com/pgsql-io/benchmarksql/archive/refs/tags/REL5_1.tar.gz>
2.2 修改src/client/jTPCC.java
if (iDB.equals("firebird"))
dbType = DB_FIREBIRD;
else if (iDB.equals("oracle"))
dbType = DB_ORACLE;
else if (iDB.equals("postgres"))
dbType = DB_POSTGRES;
**else if (iDB.equals("mysql"))
dbType = DB_UNKNOWN;**
else
{
log.error("unknown database type '" + iDB + "'");
return;
}
2.3 src/client/jTPCCConnection.java
default:
stmtStockLevelSelectLow = dbConn.prepareStatement(
"SELECT count(*) AS low_stock FROM (" +
" SELECT s_w_id, s_i_id, s_quantity " +
" FROM bmsql_stock " +
" WHERE s_w_id = ? AND s_quantity < ? 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 = ? AND d_id = ? " +
" ) " +
" )**AS L**");
break;
2.4 编译
cd benchmarksql_5_1
ant
三、装载数据
3.1 benchmarksql-5.0/run目录下创建prop.mysql
db=mysql
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://192.168.0.162:2881/tpch
user=tpch@tpch_tenant
password=tpch
warehouses=10
loadWorkers=4
terminals=100
//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
//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec
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
//osCollectorSSHAddr=user@dbhost
//osCollectorDevices=net_eth0 blk_sda
3.2 装载数据
./runDatabaseBuild.sh prop.mysql
# ------------------------------------------------------------
# Loading SQL file ./sql.common/tableCreates.sql
# ------------------------------------------------------------
Mon Feb 07 15:13:07 CST 2022 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
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),
primary key(w_id)
);
.
.
.
Worker 002: Loading Warehouse 5 done
Worker 002: Loading Warehouse 9
Worker 003: Loading Warehouse 6 done
Worker 003: Loading Warehouse 10
Worker 001: Loading Warehouse 7 done
Worker 000: Loading Warehouse 8 done
Worker 002: Loading Warehouse 9 done
Worker 003: Loading Warehouse 10 done
# ------------------------------------------------------------
# Loading SQL file ./sql.common/indexCreates.sql
...
注：
当前ob版本暂不支持通过 ALTER TABLE 追加、删除、修改主键，报错：ERROR 1235 (0A000): Not supported feature or function，需提前将主键写在建表语句中）
四、调整参数
sys:
alter system set writing_throttling_trigger_percentage=100 tenant=xxx;
alter system set writing_throttling_maximum_duration='1h';
alter system set memstore_limit_percentage = 80;
alter system set freeze_trigger_percentage = 30;
alter system set large_query_threshold = '200s';
alter system set trx_try_wait_lock_timeout = '0ms';
alter system set cpu_quota_concurrency = 4;
alter system set minor_warm_up_duration_time = 0;
alter system set minor_freeze_times=500;
alter system set minor_compact_trigger=3;
alter system set sys_bkgd_io_high_percentage = 90;
alter system set sys_bkgd_io_low_percentage = 70;
alter system set minor_merge_concurrency =20;
alter system set builtin_db_data_verify_cycle = 0;
alter system set trace_log_slow_query_watermark = '10s';
alter system set gts_refresh_interval='500us';
alter system set server_permanent_offline_time='36000s';
alter system set weak_read_version_refresh_interval=0;
alter system set _ob_get_gts_ahead_interval = '5ms';
alter system set bf_cache_priority = 10;
alter system set user_block_cache_priority=5;
alter system set merge_stat_sampling_ratio = 0;
alter system set enable_sql_audit=false;
alter system set _cache_wash_interval = '1m';
tpch租户：
set global ob_sql_work_area_percentage=80;
set global optimizer_use_sql_plan_baselines = true;
set global optimizer_capture_sql_plan_baselines = true;
alter system set ob_enable_batched_multi_statement='true';
set global ob_query_timeout=36000000000;
set global ob_trx_timeout=36000000000;
set global max_allowed_packet=67108864;
set global secure_file_priv="";
set global parallel_max_servers=260;
set global parallel_servers_target=624;
五、测试
[root@ecs0001 run]# ./runBenchmark.sh prop.mysql
16:48:40,608 [main] INFO jTPCC : Term-00,
16:48:40,610 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
16:48:40,611 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.1devel
16:48:40,611 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
16:48:40,611 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
16:48:40,611 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
16:48:40,613 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
16:48:40,613 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
16:48:40,613 [main] INFO jTPCC : Term-00,
16:48:40,613 [main] INFO jTPCC : Term-00, db=mysql
16:48:40,613 [main] INFO jTPCC : Term-00, driver=com.mysql.jdbc.Driver
16:48:40,613 [main] INFO jTPCC : Term-00, conn=jdbc:mysql://192.168.0.162:2881/tpch?useSSL=true&&rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=3000000
16:48:40,614 [main] INFO jTPCC : Term-00, user=tpch@tpch_tenant
16:48:40,614 [main] INFO jTPCC : Term-00,
16:48:40,614 [main] INFO jTPCC : Term-00, warehouses=10
16:48:40,614 [main] INFO jTPCC : Term-00, terminals=100
16:48:40,615 [main] INFO jTPCC : Term-00, runMins=5
16:48:40,615 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0
16:48:40,616 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
16:48:40,616 [main] INFO jTPCC : Term-00, useStoredProcedures=null
16:48:40,616 [main] INFO jTPCC : Term-00,
16:48:40,616 [main] INFO jTPCC : Term-00, newOrderWeight=45
16:48:40,616 [main] INFO jTPCC : Term-00, paymentWeight=43
16:48:40,616 [main] INFO jTPCC : Term-00, orderStatusWeight=4
16:48:40,616 [main] INFO jTPCC : Term-00, deliveryWeight=4
16:48:40,616 [main] INFO jTPCC : Term-00, stockLevelWeight=4
16:48:40,616 [main] INFO jTPCC : Term-00,
16:48:40,616 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
16:48:40,616 [main] INFO jTPCC : Term-00, osCollectorScript=null
16:48:40,616 [main] INFO jTPCC : Term-00,
16:48:40,630 [main] INFO jTPCC : Term-00, copied prop.mysql to my_result_2022-02-07_164840/run.properties
16:48:40,630 [main] INFO jTPCC : Term-00, created my_result_2022-02-07_164840/data/runInfo.csv for runID 5
16:48:40,631 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2022-02-07_164840/data/result.csv
16:48:40,631 [main] INFO jTPCC : Term-00,
16:48:40,907 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 181
16:48:40,907 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 112
16:48:40,907 [main] INFO jTPCC : Term-00,
Term-00, Running Average tpmTOTAL: 32009.98 Current tpmTOTAL: 1056792 Memory Usage: 1711MB / 4408MB
16:53:45,588 [Thread-65] INFO jTPCC : Term-00,
16:53:45,589 [Thread-65] INFO jTPCC : Term-00,
16:53:45,589 [Thread-65] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 14424.82
16:53:45,589 [Thread-65] INFO jTPCC : Term-00, Measured tpmTOTAL = 31975.53
16:53:45,589 [Thread-65] INFO jTPCC : Term-00, Session Start = 2022-02-07 16:48:45
16:53:45,589 [Thread-65] INFO jTPCC : Term-00, Session End = 2022-02-07 16:53:45
16:53:45,589 [Thread-65] INFO jTPCC : Term-00, Transaction Count = 160149
六、查看Top 3执行计划
mysql> SELECT/*+ PARALLEL(15)*/avg_exe_usec, svr_ip, svr_port, sql_id, plan_id -> FROM oceanbase.gv$plan_cache_plan_stat -> WHERE tenant_id = 1001 -> ORDER BY avg_exe_usec DESC LIMIT 3\\G; *************************** 1. row *************************** avg_exe_usec: 654925 svr_ip: 192.168.0.162 svr_port: 2882 sql_id: ED649AA45C2C9B669561C857148ECEA7 plan_id: 160 *************************** 2. row *************************** avg_exe_usec: 484258 svr_ip: 192.168.0.162 svr_port: 2882 sql_id: ED649AA45C2C9B669561C857148ECEA7 plan_id: 159 *************************** 3. row *************************** avg_exe_usec: 80107 svr_ip: 192.168.0.162 svr_port: 2882 sql_id: F4585305C4CB9B091C750826A7DEDD13 plan_id: 123 3 rows in set (0.08 sec) //获取sql语句 mysql> select query_sql from gv$plan_cache_plan_stat where sql_id='ED649AA45C2C9B669561C857148ECEA7'; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | query_sql | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 4 AND s_quantity < 12 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 = 4 AND d_id = 5 ) )AS L | | 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 < 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 = 2 AND d_id = 10 ) )AS L | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) //查看执行计划 mysql> explain SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 4 AND s_quantity < 12 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 = 4 AND d_id = 5 ) )AS L\\G *************************** 1. row *************************** Query Plan: ============================================================= |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------------------- |0 |SCALAR GROUP BY | |1 |5514| |1 | NESTED-LOOP JOIN | |1 |5514| |2 | SUBPLAN SCAN |VIEW2 |100 |1913| |3 | HASH DISTINCT | |100 |1900| |4 | NESTED-LOOP JOIN | |463 |1677| |5 | PX COORDINATOR | |1 |53 | |6 | EXCHANGE OUT DISTR|:EX10000 |1 |53 | |7 | TABLE GET |bmsql_district |1 |53 | |8 | TABLE SCAN |bmsql_order_line|1387 |3975| |9 | PX COORDINATOR | |1 |37 | |10| EXCHANGE OUT DISTR |:EX20000 |1 |37 | |11| 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(nil) 6 - output([bmsql_district.d_next_o_id]), filter(nil), is_single, dop=1 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(p0) 8 - output([bmsql_order_line.ol_i_id]), filter(nil), access([bmsql_order_line.ol_i_id]), partitions(p0) 9 - output([1]), filter(nil) 10 - output([1]), filter(nil), is_single, dop=1 11 - output([1]), filter([bmsql_stock.s_quantity < 12]), access([bmsql_stock.s_quantity]), partitions(p0) 1 row in set (0.01 sec) //查看实际执行计划 mysql> SELECT * FROM gv$plan_cache_plan_explain WHERE tenant_id=1001 AND plan_id=477 and ip='192.168.0.162' and port=2882;\\G +-----------+---------------+------+---------+------------+--------------+------------------------------+------------------+------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | TENANT_ID | IP | PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR | NAME | ROWS | COST | PROPERTY | +-----------+---------------+------+---------+------------+--------------+------------------------------+------------------+------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1001 | 192.168.0.162 | 2882 | 477 | 0 | 0 | PHY_SCALAR_AGGREGATE | NULL | 1 | 5513 | NULL | | 1001 | 192.168.0.162 | 2882 | 477 | 1 | 1 | PHY_NESTED_LOOP_JOIN | NULL | 1 | 5513 | NULL | | 1001 | 192.168.0.162 | 2882 | 477 | 2 | 2 | PHY_SUBPLAN_SCAN | NULL | 100 | 1912 | NULL | | 1001 | 192.168.0.162 | 2882 | 477 | 3 | 3 | PHY_HASH_DISTINCT | NULL | 100 | 1899 | NULL | | 1001 | 192.168.0.162 | 2882 | 477 | 4 | 4 | PHY_NESTED_LOOP_JOIN | NULL | 463 | 1676 | NULL | | 1001 | 192.168.0.162 | 2882 | 477 | 5 | 5 | PHY_PX_FIFO_COORD | NULL | 1 | 52 | NULL | | 1001 | 192.168.0.162 | 2882 | 477 | 6 | 6 | PHY_PX_REDUCE_TRANSMIT | NULL | 1 | 52 | NULL | | 1001 | 192.168.0.162 | 2882 | 477 | 7 | 7 | PHY_TABLE_SCAN | bmsql_district | 1 | 52 | table_rows:100000, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:0, est_method:local_storage, avaiable_index_name[bmsql_district] | | 1001 | 192.168.0.162 | 2882 | 477 | 5 | 8 | PHY_TABLE_SCAN | bmsql_order_line | 1387 | 3974 | table_rows:1400712, physical_range_rows:5786, logical_range_rows:1386, index_back_rows:0, output_rows:1386, est_method:local_storage, avaiable_index_name[bmsql_order_line], estimation info[table_id:1100611139453827, (table_type:1, version:0-1644170410798055-1644170410798055, logical_rc:0, physical_rc:0), (table_type:4, version:1644170410798055-1644224047333418-1644224047333418, logical_rc:12482, physical_rc:52082), (table_type:5, version:1644223987737981-1644224047333418-1644224047333418, logical_rc:0, physical_rc:0), (table_type:0, version:1644224047333418-1644224047333418-9223372036854775807, logical_rc:0, physical_rc:0)] | | 1001 | 192.168.0.162 | 2882 | 477 | 2 | 9 | PHY_PX_FIFO_COORD | NULL | 1 | 36 | NULL | | 1001 | 192.168.0.162 | 2882 | 477 | 3 | 10 | PHY_PX_REDUCE_TRANSMIT | NULL | 1 | 36 | NULL | | 1001 | 192.168.0.162 | 2882 | 477 | 4 | 11 | PHY_TABLE_SCAN | bmsql_stock | 1 | 36 | table_rows:27997, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:0, est_method:remote_storage, avaiable_index_name[bmsql_stock], estimation info[table_id:1100611139453829, (table_type:1, version:0-1644170410798055-1644170410798055, logical_rc:0, physical_rc:0), (table_type:7, version:1644170410798055-1644224040480987-1644224040480987, logical_rc:27997, physical_rc:1169314), (table_type:5, version:1644224001947007-1644224040480987-1644224040480987, logical_rc:0, physical_rc:0), (table_type:0, version:1644224040480987-1644224040480987-9223372036854775807, logical_rc:0, physical_rc:0)] | +-----------+---------------+------+---------+------------+--------------+------------------------------+------------------+------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 12 rows in set (0.00 sec)//依次查看其它sql //#2 mysql> explain SELECT i_price, i_name, i_data FROM bmsql_item WHERE i_id = 15710; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | =================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| --------------------------------------------------- |0 |EXCHANGE IN REMOTE | |1 |53 | |1 | EXCHANGE OUT REMOTE| |1 |53 | |2 | TABLE GET |bmsql_item|1 |53 | =================================================== Outputs & filters: ------------------------------------- 0 - output([bmsql_item.i_price], [bmsql_item.i_name], [bmsql_item.i_data]), filter(nil) 1 - output([bmsql_item.i_price], [bmsql_item.i_name], [bmsql_item.i_data]), filter(nil) 2 - output([bmsql_item.i_price], [bmsql_item.i_name], [bmsql_item.i_data]), filter(nil), access([bmsql_item.i_price], [bmsql_item.i_name], [bmsql_item.i_data]), partitions(p0) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) //#2实际执行计划 mysql> SELECT * FROM gv$plan_cache_plan_explain WHERE tenant_id=1001 AND plan_id=135 and ip='192.168.0.162' and port=2882;\\G +-----------+---------------+------+---------+------------+--------------+----------------------+------------+------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+ | TENANT_ID | IP | PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR | NAME | ROWS | COST | PROPERTY | +-----------+---------------+------+---------+------------+--------------+----------------------+------------+------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1001 | 192.168.0.162 | 2882 | 135 | 0 | 0 | PHY_DIRECT_RECEIVE | NULL | 1 | 52 | NULL | | 1001 | 192.168.0.162 | 2882 | 135 | 1 | 1 | PHY_DIRECT_TRANSMIT | NULL | 1 | 52 | NULL | | 1001 | 192.168.0.162 | 2882 | 135 | 2 | 2 | PHY_TABLE_SCAN | bmsql_item | 1 | 52 | table_rows:100000, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:local_storage, avaiable_index_name[bmsql_item] | +-----------+---------------+------+---------+------------+--------------+----------------------+------------+------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) //#3 mysql> explain SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 FROM bmsql_stock WHERE s_w_id = 7 AND s_i_id = 15710 FOR UPDATE; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ==================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------------- |0 |EXCHANGE IN REMOTE | |1 |55 | |1 | EXCHANGE OUT REMOTE| |1 |54 | |2 | TABLE GET |bmsql_stock|1 |54 | ==================================================== 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(nil) 1 - 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(nil) 2 - 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(nil), 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.00 sec) //#3 实际执行计划 mysql> SELECT * FROM gv$plan_cache_plan_explain WHERE tenant_id=1001 AND plan_id=136 and ip='192.168.0.162' and port=2882;\\G +-----------+---------------+------+---------+------------+--------------+----------------------+-------------+------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | TENANT_ID | IP | PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR | NAME | ROWS | COST | PROPERTY | +-----------+---------------+------+---------+------------+--------------+----------------------+-------------+------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1001 | 192.168.0.162 | 2882 | 136 | 0 | 0 | PHY_DIRECT_RECEIVE | NULL | 1 | 54 | NULL | | 1001 | 192.168.0.162 | 2882 | 136 | 1 | 1 | PHY_DIRECT_TRANSMIT | NULL | 1 | 53 | NULL | | 1001 | 192.168.0.162 | 2882 | 136 | 2 | 2 | PHY_TABLE_SCAN | bmsql_stock | 1 | 53 | table_rows:100000, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:local_storage, avaiable_index_name[bmsql_stock] | +-----------+---------------+------+---------+------------+--------------+----------------------+-------------+------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
