查看OB执行计划

目标：

1.使用benmarkSQL运行tpc-c，并发数不用很高，5-10并发即可

2.分析tpc-c top sql，并查看3条sql的解析执行计划和实际执行计划

3.对其中一条查询sql创建索引并查看解释执行计划和实际执行计划变更情况

一、服务器规划：

二、使用benmarkSQL运行tpc-c

TPC-C是一个对OLTP（联机交易处理）系统进行测试的规范。

1.下载benchmarkSQL

地址： https://github.com/obpilot/benchmarksql-5.0.git

[root@obproxy ~]# unzip benchmarksql-5.0-master.zip

[root@obproxy ~]# cd benchmarksql-5.0-master/

2.配置benchmarksql

[root@obproxy ~]# vim benchmarksql-5.0-master/run/props.ob

db=oracle

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

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

user=root@myoadb#myob

password=

3.创建数据库表

#登录到租户myoadb

[root@obproxy ~]# obclient -h192.168.10.101 -uroot@myoadb#myob -P2883 -p -c -A oceanbase

#避免大事务超时，设置以下租户全局变量

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_servers_target=800;

#运行建表脚本

 [root@obproxy run]# sh runSQL.sh props.ob sql.common/tableCreates.sql

runSQL.sh: line 14: source: funcs.sh: file not found

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

[root@obproxy run]# vim runSQL.sh

#!/usr/bin/env bash

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

#运行建表脚本

[root@obproxy run]# sh runSQL.sh props.ob sql.common/tableCreates.sql

#查看创建的表

obclient [oadb]> show tables;

+------------------+

| Tables_in_oadb   |

+------------------+

| bmsql_config     |

| bmsql_customer   |

| bmsql_district   |

| bmsql_history    |

| bmsql_item       |

| bmsql_new_order  |

| bmsql_oorder     |

| bmsql_order_line |

| bmsql_stock      |

| bmsql_warehouse  |

+------------------+

10 rows in set (0.012 sec)

4.加载测试数据和创建索引

[root@obproxy run]# vim runLoader.sh

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

[root@obproxy run]# sh runLoader.sh props.ob

[root@obproxy run]# sh runLoader.sh props.ob

Starting BenchmarkSQL LoadData

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

conn=jdbc:oceanbase://127.0.0.1:2883/oadb?useUnicode=true&characterEncoding=utf-8

user=root@myoadb#myob

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

#登录到数据库，创建两个索引

[root@obproxy run]# obclient -h192.168.10.101 -uroot@myoadb#myob -P2883 -p -c -A oadb

obclient [oadb]> show tables;

+------------------+

| Tables_in_oadb   |

+------------------+

| bmsql_config     |

| bmsql_customer   |

| bmsql_district   |

| bmsql_history    |

| bmsql_item       |

| bmsql_new_order  |

| bmsql_oorder     |

| bmsql_order_line |

| bmsql_stock      |

| bmsql_warehouse  |

+------------------+

10 rows in set (0.004 sec)

obclient [oadb]> create index bmsql_customer_idx1 on  bmsql_customer (c_w_id, c_d_id, c_last, c_first) local;

obclient [oadb]> create index bmsql_oorder_idx1  on  bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local;

5.运行TPC-C测试

#测试前发起一次集群合并，以root登录到sys租户执行：

[root@obproxy run]# obclient -h192.168.10.101 -uroot@sys#myob -P2883 -p -c -A oceanbase

obclient [oceanbase]> alter system major freeze;

#运行TPCC测试

[root@obproxy run]# vim runBenchmark.sh

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

[root@obproxy run]# sh runBenchmark.sh props.ob

三、查看执行计划

1.查询top sql

#登录系统租户查询：

[root@obproxy run]# obclient -h192.168.10.101 -uroot@sys#myob -P2883 -p -c -A oceanbase

#找出3条执行时间最长的SQL

#查看第一条SQL执行计划

obclient [oceanbase]> select query_sql from oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT where sql_id='6655242CB5E5EE109296FC00EA9AB1D6';

#查看第二条执行计划

obclient [oceanbase]> select query_sql from oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT where sql_id='E34528D6A44F5E88EDC786ED0F6810D3';

#查看第三条执行计划

obclient [oceanbase]> select query_sql from oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT where sql_id='D6277E097ACB6F2AE5B11428AFF82622 ';