查看 OceanBase 执行计划

1. 1. 使用 BenmarkSQL 运行 TPC-C ，并发数不用很高，5~10 并发即可（根据机器资源）
      1. 安装BenmarkSQL
2. 下载

wget https://jaist.dl.sourceforge.net/project/benchmarksql/benchmarksql-5.0.zip

1. 配置

Vi /root/benchmarksql-5.0/run/props.ob目录下,编辑后的内容如下:

db=oracle

driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver

conn=jdbc:oceanbase://127.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8

user=test2@test2#admin

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

1. 1. 1. BenmarkSQL测试
         1. 测试前环境变量

obclient -h127.0.0.1 -P 2883 -u root@test#admin -p'123456qwe' -A -c

##设置以下租户全局变量:

//show variables like '%ob_timestamp_service%'; 

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;

1. 1. 1. 1. 普通租户登陆

obclient -h192.168.0.28 -P2883 -uTPCC@test2#admin:1673835228 -p'' -A

cd /root/benchmarksql-5.0/run

1. 1. 1. 1. 运行创建表语句

sh runSQL.sh props.ob sql.common/tableCreates.sql

注意：报找不到funcs.sh文件,将funcs.sh设为绝对路径:

编辑一下runSQL.sh文件,更改内容如下:

#!/usr/bin/env bash

...

source /root/benchmarksql-5.0/run/funcs.sh $1

1. 1. 1. 1. 装载数据

sh runLoader.sh props.ob

注意：runLoader.sh: line 8: source: funcs.sh: file not found

Starting BenchmarkSQL LoadData

driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver

conn=jdbc:oceanbase://192.168.0.28:2883/TPCC?useUnicode=true&characterEncoding=utf-8

user=TPCC@test2#admin:1673835228

password=***********

warehouses=2

loadWorkers=1

fileLocation (not defined)

csvNullValue (not defined - using default 'NULL')

Worker 000: Loading ITEM

Worker 001: Loading Warehouse      1

Worker 002: Loading Warehouse      2

Worker 003: Loading Warehouse      3

Worker 000: Loading ITEM done

Worker 000: Loading Warehouse      4

Worker 001: Loading Warehouse      5 done

1. 1. 1. 1. 运行TPCC测试

sh runBenchmark.sh props.ob

注意：runBenchmark.sh: line 15: source: funcs.sh: file not found

添加绝对路径

##'2023-09-22 14:57:13' - '2023-09-22 14:59:31' 共 112个事务

1. 1. 分析 TPC-C TOP SQL，并查看 3条 SQL 的 解析执行计划 和 实际执行计划
      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    request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) ) GROUP BY sql_id order by avg_elapsed_time desc limit 10;

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 sql_id in  (' EC179230CBDD37CAC3BADEA5713AC139', '7B47FAE6C6C70D16D32B3E912D6E3181', 'D6235A02E65BE670CE68B91F7A5C959A','CB488C587A02856883A6EDDE6C927CC2', '03F777AFD4ECAA7C533613F17CAFBFA0', 'C9DA44EF8FBAFC40450B9FFB3FB9728B','7A51C5E6187094C97E3A40039ADD5B18','5E7BB584DF5C05299DA510C9E4BF0DCC','0C70BC079EF2DA1C30DD72C485CB287D','697000E8BE07C49E601175E1850D9398' ) GROUP BY sql_id order by avg_elapsed_time desc limit 10;

1. 1. 1. 第一个 sql案例
         1. 解析执行计划

select  query_sql from gv$sql_audit where sql_id='130658322FDC78779AF90493CA8E5187';

1. 1. 1. 1. 实际执行计划

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 = '192.168.0.28' AND port=2882 AND plan_id=1071

;

1. 1. 1. 第二个 sql案例
         1. 解析执行计划

SELECT sql_id,query_sql,elapsed_time,execute_time, s.svr_ip, s.svr_port, s.tenant_id, s.plan_id FROM gv$sql_audit s order by request_time desc limit 2；

select  query_sql from gv$sql_audit where sql_id='740A337A45DF23CEEB4BE9BE718D2511';

1. 1. 1. 1. 实际执行计划

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 = '192.168.0.28' AND port=2882 AND plan_id=1070;

1. 1. 1. 第三个 sql案例
         1. 解析执行计划

select  query_sql from gv$sql_audit where sql_id='935A382CCE70D4D7E03EEB5F2EC48C76';

1. 1. 1. 1. 实际执行计划

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 = '192.168.0.28' AND port=2882 AND plan_id=1071;