实验六 查看oceanbase执行计划

下载benmarksql

https://github.com/meiq4096/benchmarksql-5.0

编辑配置文件

创建表结构

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

运行脚本

sh runLoader.sh props.ob

修改如下参数

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;

再次执行

登录到数据库创建索引

create index bmsql_customer_idx1

on bmsql_customer (c_w_id, c_d_id, c_last, c_first) local;

create index bmsql_oorder_idx1 on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local;

压力测试

sh runBenchmark.sh props.ob

topsql分析

查询top sql

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

GROUP BY sql_id

order by avg_elapsed_time desc limit 30;

获取sql

select distinct query_sql from gv$sql_audit where sql_id='7229213613983BC5FDA15AD11EC70D01';

查询实际执行计划

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.56.210' AND port=2882 AND plan_id=24;

第二条sql

第三条sql