实践练习六

最新推荐文章于 2024-01-17 20:21:19 发布
最新推荐文章于 2024-01-17 20:21:19 发布
阅读量171 收藏
点赞数
文章标签： 数据库 linux sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u011355991/article/details/130214881
版权

一、环境准备

obclient [test]> create database tpcc;
Query OK, 1 row affected (0.131 sec)

obclient [test]> create user tpcc identified by ‘royce’;
Query OK, 0 rows affected (0.048 sec)

obclient [test]> grant all on tpcc.* to tpcc;
Query OK, 0 rows affected (0.058 sec)

二、准备BenchmarkSQL
下载并安装BenchmarkSQL

[root@localhost ~]# git clone https://github.com/obpilot/benchmarksql-5.0.git
Cloning into ‘benchmarksql-5.0’…
remote: Enumerating objects: 110, done.
remote: Counting objects: 100% (110/110), done.
remote: Compressing objects: 100% (90/90), done.
remote: Total 110 (delta 14), reused 105 (delta 12), pack-reused 0
Receiving objects: 100% (110/110), 5.58 MiB | 5.11 MiB/s, done.
Resolving deltas: 100% (14/14), done.

编辑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=tpcc@obmysql#obdemo
password=royce

warehouses=2
loadWorkers=2

terminals=2
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=1
//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]# sh runSQL.sh props.ob sql.common/tableCreates.sql
runSQL.sh: line 14: source: funcs.sh: file not found

配置环境或者修改脚本为绝对路径

[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)
);
create table bmsql_district (
d_w_id integer not null,
d_id integer not null,
d_ytd decimal(12,2),
d_tax decimal(4,4),
d_next_o_id integer,
d_name varchar(10),
d_street_1 varchar(20),
d_street_2 varchar(20),
d_city varchar(20),
d_state char(2),
d_zip char(9)
);
create table bmsql_customer (
c_w_id integer not null,
c_d_id integer not null,
c_id integer not null,
c_discount decimal(4,4),
c_credit char(2),
c_last varchar(16),
c_first varchar(16),
c_credit_lim decimal(12,2),
c_balance decimal(12,2),
c_ytd_payment decimal(12,2),
c_payment_cnt integer,
c_delivery_cnt integer,
c_street_1 varchar(20),
c_street_2 varchar(20),
c_city varchar(20),
c_state char(2),
c_zip char(9),
c_phone char(16),
c_since timestamp,
c_middle char(2),
c_data varchar(500)
);
create sequence bmsql_hist_id_seq;
You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near ‘sequence bmsql_hist_id_seq’ at line 1
create table bmsql_history (
hist_id integer,
h_c_id integer,
h_c_d_id integer,
h_c_w_id integer,
h_d_id integer,
h_w_id integer,
h_date timestamp,
h_amount decimal(6,2),
h_data varchar(24)
);
create table bmsql_new_order (
no_w_id integer not null,
no_d_id integer not null,
no_o_id integer not null
);
create table bmsql_oorder (
o_w_id integer not null,
o_d_id integer not null,
o_id integer not null,
o_c_id integer,
o_carrier_id integer,
o_ol_cnt integer,
o_all_local integer,
o_entry_d timestamp
);
create table bmsql_order_line (
ol_w_id integer not null,
ol_d_id integer not null,
ol_o_id integer not null,
ol_number integer not null,
ol_i_id integer not null,
ol_delivery_d timestamp,
ol_amount decimal(6,2),
ol_supply_w_id integer,
ol_quantity integer,
ol_dist_info char(24)
);
create table bmsql_item (
i_id integer not null,
i_name varchar(24),
i_price decimal(5,2),
i_data varchar(50),
i_im_id integer
);
create table bmsql_stock (
s_w_id integer not null,
s_i_id integer not null,
s_quantity integer,
s_ytd integer,
s_order_cnt integer,
s_remote_cnt integer,
s_data varchar(50),
s_dist_01 char(24),
s_dist_02 char(24),
s_dist_03 char(24),
s_dist_04 char(24),
s_dist_05 char(24),
s_dist_06 char(24),
s_dist_07 char(24),
s_dist_08 char(24),
s_dist_09 char(24),
s_dist_10 char(24)
);

测试前先设置参数，防止超时

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/tpcc?useUnicode=true&characterEncoding=utf-8
user=tpcc@obmysql#obdemo
password=***********
warehouses=2
loadWorkers=2
fileLocation (not defined)
csvNullValue (not defined - using default ‘NULL’)

Worker 000: Loading ITEM
Worker 001: Loading Warehouse 1
Worker 000: Loading ITEM done
Worker 000: Loading Warehouse 2
Worker 001: Loading Warehouse 1 done
Worker 000: Loading Warehouse 2 done

创建索引

obclient [tpcc]> 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.170 sec)

obclient [tpcc]> 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 (0.778 sec)

运行测试

[root@localhost run]# sh runBenchmark.sh props.ob
04:01:43,890 [main] INFO jTPCC : Term-00,
04:01:43,898 [main] INFO jTPCC : Term-00, ±------------------------------------------------------------+
04:01:43,898 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
04:01:43,898 [main] INFO jTPCC : Term-00, ±------------------------------------------------------------+
04:01:43,898 [main] INFO jTPCC : Term-00, © 2003, Raul Barbosa
04:01:43,898 [main] INFO jTPCC : Term-00, © 2004-2016, Denis Lussier
04:01:43,907 [main] INFO jTPCC : Term-00, © 2016, Jan Wieck
04:01:43,908 [main] INFO jTPCC : Term-00, ±------------------------------------------------------------+
04:01:43,908 [main] INFO jTPCC : Term-00,
04:01:43,908 [main] INFO jTPCC : Term-00, db=oracle
04:01:43,908 [main] INFO jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
04:01:43,908 [main] INFO jTPCC : Term-00, conn=jdbc:oceanbase://127.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8
04:01:43,909 [main] INFO jTPCC : Term-00, user=tpcc@obmysql#obdemo
04:01:43,909 [main] INFO jTPCC : Term-00,
04:01:43,909 [main] INFO jTPCC : Term-00, warehouses=2
04:01:43,909 [main] INFO jTPCC : Term-00, terminals=2
04:01:43,912 [main] INFO jTPCC : Term-00, runMins=1
04:01:43,913 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0
04:01:43,913 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
04:01:43,913 [main] INFO jTPCC : Term-00,
04:01:43,913 [main] INFO jTPCC : Term-00, newOrderWeight=45
04:01:43,913 [main] INFO jTPCC : Term-00, paymentWeight=43
04:01:43,913 [main] INFO jTPCC : Term-00, orderStatusWeight=4
04:01:43,913 [main] INFO jTPCC : Term-00, deliveryWeight=4
04:01:43,913 [main] INFO jTPCC : Term-00, stockLevelWeight=4
04:01:43,913 [main] INFO jTPCC : Term-00,
04:01:43,914 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
04:01:43,914 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
04:01:43,914 [main] INFO jTPCC : Term-00,
04:01:44,049 [main] INFO jTPCC : Term-00, copied props.ob to my_result_2022-10-24_040144/run.properties
04:01:44,049 [main] INFO jTPCC : Term-00, created my_result_2022-10-24_040144/data/runInfo.csv for runID 6
04:01:44,050 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2022-10-24_040144/data/result.csv
04:01:44,053 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
04:01:44,053 [main] INFO jTPCC : Term-00, osCollectorInterval=1
04:01:44,053 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null
04:01:44,053 [main] INFO jTPCC : Term-00, osCollectorDevices=null
04:01:44,298 [main] INFO jTPCC : Term-00,
04:01:45,063 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 162
04:01:45,063 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 97
04:01:45,063 [main] INFO jTPCC : Term-00, Term-04:03:23,156 [Thread-1] INFO jTPCC : Term-00, mTOTAL: 48 Memory Usage: 18MB / 180MB
04:03:23,157 [Thread-1] INFO jTPCC : Term-00,
04:03:23,157 [Thread-1] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 1.83
04:03:23,157 [Thread-1] INFO jTPCC : Term-00, Measured tpmTOTAL = 3.67
04:03:23,157 [Thread-1] INFO jTPCC : Term-00, Session Start = 2022-10-24 04:01:45
04:03:23,157 [Thread-1] INFO jTPCC : Term-00, Session End = 2022-10-24 04:03:23
04:03:23,157 [Thread-1] INFO jTPCC : Term-00, Transaction Count = 5

三、TOP SQL分析

查看TOP sql
obclient [tpcc]> 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;
±-------------±---------±---------±---------------------------------±--------+
| avg_exe_usec | svr_ip | svr_port | sql_id | plan_id |
±-------------±---------±---------±---------------------------------±--------+
| 4146400 | 10.0.2.4 | 2882 | 7229213613983BC5FDA15AD11EC70D01 | 73 |
| 2120593 | 10.0.2.4 | 2882 | E1F2BDA1D7391B757859ED3704E5AFB7 | 75 |
| 630255 | 10.0.2.4 | 2882 | AE32C84F890055A535A28B262C649D41 | 64 |
±-------------±---------±---------±---------------------------------±--------+
3 rows in set (0.116 sec)

查看sql语句及执行计划

第一条：

obclient [tpcc]> select query_sql from oceanbase.gv$plan_cache_plan_stat 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 = 2 AND s_i_id = 4553 FOR UPDATE |
±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.022 sec)

查看执行计划
obclient [tpcc]> explain SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 FROM bmsql_stock WHERE s_w_id = 2 AND s_i_id = 4553 FOR UPDATE
-> ;
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================
|ID|OPERATOR |NAME |EST. ROWS|COST |

|0 |TABLE SCAN|bmsql_stock|10 |124026|

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 = 4553]),
access([bmsql_stock.__pk_increment], [bmsql_stock.s_w_id], [bmsql_stock.s_i_id], [bmsql_stock.s_quantity], [bmsql_stock.s_data], [bmsql_stock.s_dist_01], [bmsql_stock.s_dist_02], [bmsql_stock.s_dist_03], [bmsql_stock.s_dist_04], [bmsql_stock.s_dist_05], [bmsql_stock.s_dist_06], [bmsql_stock.s_dist_07], [bmsql_stock.s_dist_08], [bmsql_stock.s_dist_09], [bmsql_stock.s_dist_10]), partitions(p0)
|
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.004 sec)

实际执行计划：
obclient [tpcc]> 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.0.2.4’ AND port=2882 AND plan_id=73;
±---------±-----------±-------------±---------------±------------±-----±-------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ip | plan_depth | plan_line_id | operator | name | rows | cost | property |
±---------±-----------±-------------±---------------±------------±-----±-------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10.0.2.4 | 0 | 0 | PHY_TABLE_SCAN | bmsql_stock | 10 | 124025 | table_rows:100000, physical_range_rows:100000, logical_range_rows:100000, index_back_rows:0, output_rows:9, est_method:basic_stat, avaiable_index_name[bmsql_stock] |
±---------±-----------±-------------±---------------±------------±-----±-------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.024 sec)

执行计划一致

第二条：

obclient [tpcc]> select query_sql from oceanbase.gv$plan_cache_plan_stat where sql_id=‘E1F2BDA1D7391B757859ED3704E5AFB7’;
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| query_sql |
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| UPDATE bmsql_stock SET s_quantity = 102, s_ytd = s_ytd + 3, s_order_cnt = s_order_cnt + 1, s_remote_cnt = s_remote_cnt + 0 WHERE s_w_id = 1 AND s_i_id = 2947 |
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.013 sec)

查看执行计划：
obclient [tpcc]> explain UPDATE bmsql_stock SET s_quantity = 102, s_ytd = s_ytd + 3, s_order_cnt = s_order_cnt + 1, s_remote_cnt = s_remote_cnt + 0 WHERE s_w_id = 1 AND s_i_id = 2947;
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =============================================
|ID|OPERATOR |NAME |EST. ROWS|COST |

|0 |UPDATE | |10 |130653|
|1 | TABLE SCAN|bmsql_stock|10 |130643|

Outputs & filters:

0 - output(nil), filter(nil), table_columns([{bmsql_stock: ({bmsql_stock: (bmsql_stock.__pk_increment, bmsql_stock.s_w_id, bmsql_stock.s_i_id, bmsql_stock.s_quantity, bmsql_stock.s_ytd, bmsql_stock.s_order_cnt, bmsql_stock.s_remote_cnt, 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)})}]),
update([bmsql_stock.s_quantity=?], [bmsql_stock.s_ytd=column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_ytd + 3, INT(-1, 0)))], [bmsql_stock.s_order_cnt=column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_order_cnt + 1, INT(-1, 0)))], [bmsql_stock.s_remote_cnt=column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_remote_cnt + 0, INT(-1, 0)))])
1 - output([bmsql_stock.__pk_increment], [bmsql_stock.s_w_id], [bmsql_stock.s_i_id], [bmsql_stock.s_quantity], [bmsql_stock.s_ytd], [bmsql_stock.s_order_cnt], [bmsql_stock.s_remote_cnt], [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], [?], [column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_ytd + 3, INT(-1, 0)))], [column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_order_cnt + 1, INT(-1, 0)))], [column_conv(INT,PS:(11,0),NULL,cast(bmsql_stock.s_remote_cnt + 0, INT(-1, 0)))]), filter([bmsql_stock.s_w_id = 1], [bmsql_stock.s_i_id = 2947]),
access([bmsql_stock.s_quantity], [bmsql_stock.s_ytd], [bmsql_stock.s_order_cnt], [bmsql_stock.s_remote_cnt], [bmsql_stock.__pk_increment], [bmsql_stock.s_w_id], [bmsql_stock.s_i_id], [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.009 sec)

实际执行计划：
obclient [tpcc]> 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.0.2.4’ AND port=2882 AND plan_id=75;
±---------±-----------±-------------±----------------±------------±-----±-------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ip | plan_depth | plan_line_id | operator | name | rows | cost | property |
±---------±-----------±-------------±----------------±------------±-----±-------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10.0.2.4 | 0 | 0 | PHY_UPDATE | NULL | 10 | 130652 | NULL |
| 10.0.2.4 | 1 | 1 | PHY_TABLE_SCAN | bmsql_stock | 10 | 130642 | table_rows:100000, physical_range_rows:100000, logical_range_rows:100000, index_back_rows:0, output_rows:9, est_method:basic_stat, avaiable_index_name[bmsql_stock] |
±---------±-----------±-------------±----------------±------------±-----±-------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.004 sec)

第三条：

obclient [tpcc]> select query_sql from oceanbase.gv$plan_cache_plan_stat where sql_id=‘AE32C84F890055A535A28B262C649D41’;
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| query_sql |
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 4 AND c_id = 2692 FOR UPDATE |
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.015 sec)

查看执行计划：
obclient [tpcc]> explain SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 4 AND c_id = 2692 FOR UPDATE;
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ===================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |

|0 |TABLE SCAN|bmsql_customer(bmsql_customer_idx1)|30 |20867|

Outputs & filters:

0 - output([bmsql_customer.c_first], [bmsql_customer.c_middle], [bmsql_customer.c_last], [bmsql_customer.c_street_1], [bmsql_customer.c_street_2], [bmsql_customer.c_city], [bmsql_customer.c_state], [bmsql_customer.c_zip], [bmsql_customer.c_phone], [bmsql_customer.c_since], [bmsql_customer.c_credit], [bmsql_customer.c_credit_lim], [bmsql_customer.c_discount], [bmsql_customer.c_balance]), filter([bmsql_customer.c_id = 2692]),
access([bmsql_customer.__pk_increment], [bmsql_customer.c_w_id], [bmsql_customer.c_d_id], [bmsql_customer.c_id], [bmsql_customer.c_first], [bmsql_customer.c_middle], [bmsql_customer.c_last], [bmsql_customer.c_street_1], [bmsql_customer.c_street_2], [bmsql_customer.c_city], [bmsql_customer.c_state], [bmsql_customer.c_zip], [bmsql_customer.c_phone], [bmsql_customer.c_since], [bmsql_customer.c_credit], [bmsql_customer.c_credit_lim], [bmsql_customer.c_discount], [bmsql_customer.c_balance]), partitions(p0)
|
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.019 sec)

实际执行计划；
obclient [tpcc]> 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.0.2.4’ AND port=2882 AND plan_id=64;
±---------±-----------±-------------±---------------±------------------------------------±-----±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ip | plan_depth | plan_line_id | operator | name | rows | cost | property |
±---------±-----------±-------------±---------------±------------------------------------±-----±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10.0.2.4 | 0 | 0 | PHY_TABLE_SCAN | bmsql_customer(bmsql_customer_idx1) | 30 | 20866 | table_rows:9000, physical_range_rows:2947, logical_range_rows:2947, index_back_rows:2947, output_rows:29, est_method:local_storage, avaiable_index_name[bmsql_customer,bmsql_customer_idx1], estimation info[table_id:1100611139453789, (table_type:1, version:0-1666555187402389-1666555187402389, logical_rc:2947, physical_rc:2947), (table_type:0, version:1666554989549235-1666554989549235-9223372036854775807, logical_rc:0, physical_rc:0)] |
±---------±-----------±-------------±---------------±------------------------------------±-----±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.015 sec)

可以看出，解析执行计划和实际执行计划一致。

wanxxan
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
自己实现一个SQL解析引擎
人既无名的专栏
06-26 3万+
SQL的解析引擎包括查询编译与查询优化和查询的运行，主要包括3个步骤： 查询分析： 制定逻辑查询计划（优化相关） 制定物理查询计划（优化相关） 查询分析： 将SQL语句表示成某种有用的语法树. 制定逻辑查询计划： 把语法树转换成一个关系代数表达式或者类似的结构，这个结构通常称作逻辑计划。 制定物理查询计划：把逻辑计划转换成物理查询计划，要求指定操作执行的顺序，每一步使用的算法，操作之间的传递方式等。
使用Dbeaver连接OceanBase
weixin_43869425的博客
09-13 6162
数据库连不上的特殊情况，自己定义一下
Jmeter连接OceanBase数据库
weixin_38080856的博客
05-18 3396
Jmeter连接OceanBase数据库
【JMeter】JMeter连OceanBase数据库
最新发布
仰望的博客
01-17 862
2、将下载下来的jar包放到jmeter安装目录的 lib 目录下，或者打开JMeter客户端，在测试计划中引入jar包。
实践练习6：查看 OceanBase 执行计划
qinwen740的博客
03-30 2224
环境准备 机器信息 机器类型 虚拟机 IP 10.201.0.170, 10.201.0.171, 10.201.0.172, 10.201.0.173 网卡名 eth0 OS CentOS Linux release 7.9.2009 CPU 4C 内存 总内存 16G，可用内存 15G 磁盘1 磁盘 /dev/vda 200G 磁盘2 磁盘 /dev/vdb 300G 机器划分 角色 机器 备注 性能测
OceanBase实践练习六
03-15
【OceanBase实践练习六】 OceanBase是一个分布式数据库系统，由阿里巴巴集团自主研发，具有高可用、高性能、线性扩展等特性，特别适用于大规模并发事务处理。本实践练习将带你深入理解OceanBase的运行机制，通过...
ES6实践练习代码
08-12
**ES6实践练习代码** 在JavaScript的开发领域，ES6（ECMAScript 2015）是一个重要的里程碑，引入了大量新特性，极大地提升了代码的可读性和编写效率。这个压缩包“ES6_practice-master”显然是一个关于ES6实践的...
编码实践：编码练习
02-17
6. **泛型（Generics）**： - 泛型允许编写可重用的代码，适用于多种类型，提高代码的灵活性和可维护性。 7. **可选类型（Optionals）**： - Swift的可选类型用于处理可能缺失的值，避免空指针异常。 8. **错误...
前端实践：练习基本前端
02-22
本实践项目“前端实践：练习基本前端”旨在帮助初学者熟悉并掌握HTML的基本用法，从而更好地理解和创建静态网页。 首先，HTML由一系列的标签组成，这些标签用于标记和组织网页中的文本、图片、链接等元素。例如，`...
VC.zip_vc6新手练习
09-23
【VC6.0新手练习】：此资源...总之，“VC.zip_vc6新手练习”提供的源码是学习和理解C++编程以及VC++ 6.0 IDE操作的宝贵资源。通过逐步解析和实践这些代码，初学者可以快速掌握编程技能，并逐步成长为熟练的C++开发者。
OceanBase 源码解读（七）：一文读懂数据库索引实现原理
OceanBase分布式数据库的博客
12-10 1664
此前，带你读源码第六篇《戳这里回顾：OceanBase 源码解读（六）：存储引擎详解》为大家详细讲解了OceanBase存储引擎，并为大家回答了关于 OceanBase 数据库的相关提问。 本期“源码解读”将尝试从代码导读的角度，简要介绍 OceanBase 的索引构建流程，带领大家读懂索引构建的相关代码，必须速速收藏！ 1. 什么是索引 首先请思考，在一个一般的数据库中，索引表的语义是什么？ 索引表，其实是在主表（也称数据表）之外，再创建一份冗余且有序的数据，用于加速某些查询流程。...
dbeaver连接oceanbase备忘
m0_37513685的博客
04-19 2587
dbeaver连接oceanbase配置方法
linux使用sh执行脚本中有source命令时提示找不到文件
LiLittleCat的博客
07-06 1万+
文章目录问题原因分析解决方法 问题 shell脚本a.sh如下： #!/bin/bash cd /home/ if [ -f b.sh ];then source b.sh fi 使用sh命令执行a.sh: [root@localhost osc]# sh a.sh a.sh: line 4: source: b.sh: file not found 原因分析 source 命令找filename是在当前Shell环境中从文件名读取并执行命令，如果filename不包含斜杠，则使用PATH中的文件名查
benchmark在postgresql上的安装及使用
weixin_34194359的博客
05-12 1051
BenchmarkSQL是一款经典的开源数据库测试工具，内嵌了TPCC测试脚本，可以对EnterpriseDB、PostgreSQL、MySQL、Oracle以及SQL Server等数据库直接进行测试。二、测试前提1. 安装JDK。因为BenchmarkSQL本身是使用Java语言编写的，所以如果在Linux系统下还没有安装JDK的话，我们首先需要对其进行安装；2. 安装PostgreSQL数...
OceanBase实践练习六：查看 OceanBase 执行计划
lichx123的博客
01-27 2407
目录 1 使用 BenmarkSQL 运行 TPC-C 1.1 下载BenmarkSQL 1.2 编辑props.ob配置文件 1.3 运行创建表语句 1.4 数据装载 1.5 登陆到数据库，创建两个索引 1.6 运行TPCC测试 2TPC-C TOP SQL分析 2.1 查询TOP sql 2.2 对elapsed时间最长的前三条sql进行分析 1 使用 BenmarkSQL 运行 TPC-C 1.1 下载BenmarkSQL git clone https://g...
DM8 TPCC测试
weixin_34938478的博客
12-21 2026
1.使用benchwarksql进行tpcc测试上传所需软件包并解压bms.tar bms5_index.sql bms5_table.sql bms.tar 2.检查驱动版本 ../benchmarksql/lib/dm 下面是否是最新的驱动，如果没有直接到数据库下拷贝/opt/dmdbms/drivers/jdbc/DmJdbcDriver18.jar 3.配置prop.dm文件来规划预创建表的数据量 [root@DM02 run]$ vi props.dm db=dm dri...
实践练习六（必选）：OceanBase TPC-C 测试和查看 执行计划
你的世界是你创造的结果
02-18 1847
实践练习六（必选）：OceanBase TPC-C 测试和查看 执行计划 练习目的 本次练习目的掌握 OceanBase 的执行计划查看方法，包括 explain 命令和查看实际执行计划。 练习条件 有 服务器，内存资源至少 12G*1 台，部署有 OceanBase 集群（单副本或三副本都可以）。 练习内容 请记录并分享下列内容： （必选）使用 BenmarkSQL 运行 TPC-C ，并发数不用很高，5~10 并发即可（根据机器资源）。 （必选）分析 TPC-C TOP SQL，并查看 3条 S
OceanBase快速安装实践
热门推荐
我的博客
03-22 1万+
本文意图帮助快速安装OceanBase这一金融级分布式数据库产品。 但部署设计仅供产品体验目的，非生产级别安装方案。 安装OceanBase主要有两种方式， 一为OCP，另一为脚本方式（即deploy.py）。 因不具备桌面环境，本人选择deploy.py方式安装。 0. 规划: 四台机器： 192.168.1.10 (记为 IP10) 作为安装控制端 192.168.1.50 (记为 IP...
OceanBase 2.2 体验：用JMeter测试OceanBase性能
obpilot的博客
03-22 1682
OceanBase 2.2 体验：用JMeter测试OceanBase性能 本文介绍使用JMeter测试OceanBase性能的方法。 这个方法适用于所有关系数据库(包括分布式的)。 测试方法。 在前文《分布式数据库选型测试(一)——测试需求》里已经就数据库性能对比测试的方法做过总结，这里再补充一些内容。 测试OceanBase集群性能的方法有很多，如sysbench、benchmarksql。不...
数据库结构：实践练习答案
"chapter13 实践习题2 数据存储结构 解决Chapter13实践练习的答案" 在本章的实践中，我们关注的是数据存储结构及其在实际操作中的应用。首先，让我们详细讨论一下练习13.1中涉及的概念。 13.1 练习解答： 这个问题...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值