实践练习六：查看OceanBase执行计划

实践练习六（必选）：查看 OceanBase 执行计划 - 培训中心 - 社区问答- OceanBase社区-分布式数据库本次练习是必选练习之一。 练习目的本次练习目的掌握 OceanBase 的执行计划查看方法，包括 explain 命令和查看实际执行计划。 练习条件有 服务器，内存资源至少 12G*1 台，部署有 OceanBase 集群…https://ask.oceanbase.com/t/topic/20400276 本篇文章记录OB实践第六题，使用 BenmarkSQL 运行 TPC-C 测试，并查看 3条 TOP SQL 的 解析执行计划 和 实际执行计划。

一、使用 BenmarkSQL 运行 TPC-C测试

环境准备

 OceanBase环境沿用之前的手动部署环境，详情见实践练习二  

1.benchmarksql压测工具部署

 工具下载地址：Download BenchmarkSQL

#安装JAVA环境(建议使用 1.8u131 及以上版本)
java -version
#下载Ant(建议使用 apache-ant-1.10 及以上版本)
wget "http://archive.apache.org/dist/ant/binaries/apache-ant-1.10.6-bin.zip"
#安装Ant
yum install -y zip unzip
unzip apache-ant-1.10.6-bin.zip
#设置环境变量
sudo vim /etc/profile
添加以下内容：
#ant
export ANT_HOME=xx/apache-ant-1.10.6
export PATH=xx/apache-ant-1.10.6/bin:$PATH

source /etc/profile
#检查是否安装成功
ant -version
Apache Ant(TM) version 1.10.6 compiled on May 2 2019  --表示安装成功
#上传并解压benchmarksql安装包
unzip benchmarksql-5.0.zip

#编译Benchmark SQL
进入 Benchmark SQL 解压后的目录，使用 Ant 编译 Benchmark SQL
cd /root/benchmarksql-5.0
ant

 输出如下图所示，表示成功

2.适配Benchmark SQL5

 由于 Benchmark SQL5 不支持 OceanBase 数据库的 TPC-C 测试，需要修改 BenchMarkSQL5 部分源码支持 OceanBase 数据库

#修改 benchmarksql-5.0/src/client/jTPCC.java 文件，增加 OceanBase 数据库相关内容
vim /root/benchmarksql-5.0/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("oceanbase"))   //增加 OceanBase 数据库相关内容
            dbType = DB_OCEANBASE;
        else
        {
            log.error("unknown database type '" + iDB + "'");
            return;
        }

#修改 benchmarksql-5.0/src/client/jTPCCConfig.java 文件，增加 OceanBase 数据库类型
vim /root/benchmarksql-5.0/src/client/jTPCCConfig.java
修改如下内容：
    public final static int     DB_UNKNOWN = 0,
                                DB_FIREBIRD = 1,
                                DB_ORACLE = 2,
                                DB_POSTGRES = 3,           //将";"改为","
                                DB_OCEANBASE = 4;          //添加 OceanBase数据库类型

#修改 benchmarksql-5.0/src/client/jTPCCConnection.java 文件，在 SQL 子查询增加 "AS L" 别名
vim /root/benchmarksql-5.0/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");    //增加 "AS L" 别名
                break;

#重新编译修改后的源码
cd /root/benchmarksql-5.0
ant

#在 benchmarksql-5.0/run 目录下，创建文件 prop.oceanbase
vim /root/benchmarksql-5.0/run/prop.oceanbase
修改如下内容：
db=oceanbase
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://$host_ip:$port/$db_name?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=30000000
//请填写完整user信息(按照实际情况填写信息)
user=$user@$tenant 
password=*****
warehouses=1000             #设置压测数据量
loadWorkers=40              #设置压测数据量
terminals=600               #并发数量，MySQL 租户配置下并发需要结合具体配置动态调整
database=$db_name
//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

#修改文件：benchmarksql-5.0/run/funcs.sh，添加 OceanBase 数据库类型
vim /root/benchmarksql-5.0/run/funcs.sh
修改如下内容：
function setCP()
{
    case "$(getProp db)" in
firebird)
    cp="../lib/firebird/*:../lib/*"
    ;;
oracle)
    cp="../lib/oracle/*"
    if [ ! -z "${ORACLE_HOME}" -a -d ${ORACLE_HOME}/lib ] ; then
  cp="${cp}:${ORACLE_HOME}/lib/*"
    fi
    cp="${cp}:../lib/*"
    ;;
postgres)
    cp="../lib/postgres/*:../lib/*"
    ;;
oceanbase)      #添加 OceanBase 数据库类型
    cp="../lib/oceanbase/*:../lib/*"
    ;;
    esac
    myCP=".:${cp}:../dist/*"
    export myCP
}

...省略

case "$(getProp db)" in
    firebird|oracle|postgres|oceanbase)  #添加 OceanBase 数据库类型
    ;;
    "") echo "ERROR: missing db= config option in ${PROPS}" >&2
    exit 1
    ;;
    *)  echo "ERROR: unsupported database type 'db=$(getProp db)' in ${PROPS}" >&2
    exit 1
    ;;
esac

#添加 mysql java connector 驱动，推荐 mysql-connector-java-5.1.47.jar
tar -zxvf mysql-connector-java-5.1.47.tar.gz
cd /root/benchmarksql-5.0
mkdir lib/oceanbase/
cp /root/mysql-connector-java-5.1.47/mysql-connector-java-5.1.47.jar lib/oceanbase/

#修改 benchmarksql-5.0/run/runDatabaseBuild.sh
vim /root/benchmarksql-5.0/run/runDatabaseBuild.sh
AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"
修改为：
AFTER_LOAD="indexCreates buildFinish"

 至此benchmarksql适配完毕，接下来需要改造benchmarksql5中的SQL

3.改造 BenchMarkSQL5 中的 SQL

#备份并重写 benchmarksql-5.0/run/sql.common/tableCreates.sql
cp /root/benchmarksql-5.0/run/sql.common/tableCreates.sql /root/benchmarksql-5.0/run/sql.common/tableCreates.sql.bak
vim /root/benchmarksql-5.0/run/sql.common/tableCreates.sql

CREATE TABLE bmsql_config (
  cfg_name    varchar(30) PRIMARY KEY,
  cfg_value   varchar(50)
);

CREATE TABLEGROUP IF NOT EXISTS tpcc_group binding true partition by hash partitions 96;

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)
)tablegroup='tpcc_group' partition by hash(w_id) partitions 96;

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),
  PRIMARY KEY (d_w_id, d_id)
)tablegroup='tpcc_group' partition by hash(d_w_id) partitions 96;

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),
  PRIMARY KEY (c_w_id, c_d_id, c_id)
)tablegroup='tpcc_group' partition by hash(c_w_id) partitions 96;


CREATE TABLE bmsql_history (
  hist_id  integer AUTO_INCREMENT,
  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)
)tablegroup='tpcc_group' partition by hash(h_w_id) partitions 96;

CREATE TABLE bmsql_new_order (
  no_w_id  integer   not null ,
  no_d_id  integer   not null,
  no_o_id  integer   not null,
  PRIMARY KEY (no_w_id, no_d_id, no_o_id)
)tablegroup='tpcc_group' partition by hash(no_w_id) partitions 96;

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,
  PRIMARY KEY (o_w_id, o_d_id, o_id)
)tablegroup='tpcc_group' partition by hash(o_w_id) partitions 96;

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),
  PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number)
)tablegroup='tpcc_group' partition by hash(ol_w_id) partitions 96;

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,
  PRIMARY KEY (i_id)
);

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),
  PRIMARY KEY (s_w_id, s_i_id)
)tablegroup='tpcc_group' partition by hash(s_w_id) partitions 96;

#备份并重写 benchmarksql-5.0/run/sql.common/tableDrops.sql
cp /root/benchmarksql-5.0/run/sql.common/tableDrops.sql /root/benchmarksql-5.0/run/sql.common/tableDrops.sql.bak
vim /root/benchmarksql-5.0/run/sql.common/tableDrops.sql

DROP TABLE bmsql_config;
DROP TABLE bmsql_new_order;
DROP TABLE bmsql_order_line;
DROP TABLE bmsql_oorder;
DROP TABLE bmsql_history;
DROP TABLE bmsql_customer;
DROP TABLE bmsql_stock;
DROP TABLE bmsql_item;
DROP TABLE bmsql_district;
DROP TABLE bmsql_warehouse;
DROP TABLEGROUP tpcc_group;

#备份并重写 benchmarksql-5.0/run/sql.common/indexCreates.sql
cp /root/benchmarksql-5.0/run/sql.common/indexCreates.sql /root/benchmarksql-5.0/run/sql.common/indexCreates.sql.bak
vim /root/benchmarksql-5.0/run/sql.common/indexCreates.sql

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;

#备份并重写 benchmarksql-5.0/run/sql.common/indexDrops.sql
cp /root/benchmarksql-5.0/run/sql.common/indexDrops.sql /root/benchmarksql-5.0/run/sql.common/indexDrops.sql.bak
vim /root/benchmarksql-5.0/run/sql.common/indexDrops.sql

ALTER TABLE bmsql_customer DROP INDEX bmsql_customer_idx1;
ALTER TABLE bmsql_oorder DROP INDEX bmsql_oorder_idx1;

 

TPC-C测试步骤

！！以下命令均在 benchmarksql-5.0/run 目录下执行
#初始化环境
./runDatabaseDestroy.sh prop.oceanbase

#创建表并导入数据
./runDatabaseBuild.sh prop.oceanbase

#执行合并（需要使用 sys 租户登录）
Major合并将当前大版本的 SSTable和MemTable 与前一个大版本的全量静态数据进行合并，使存储层统计信息更准确，生成的执行计划更稳定
obclient -h10.186.56.39 -P2883 -uroot@sys#obcluster -p -A
use oceanbase
ALTER SYSTEM major freeze tenant=<your tenant name>;

#查看合并是否成功
所有的 FROZEN_SCN 和 LAST_SCN 的值相等即表示合并完成
SELECT FROZEN_SCN, LAST_SCN FROM oceanbase.CDB_OB_MAJOR_COMPACTION;

#手动收集统计信息
执行 obclient -h$host_ip -P$host_port -u$user@$tenant -p$password -A -D$database 命令进入测试租户
call dbms_stats.gather_schema_stats('$db_name',degree=>96);

#执行压力测试
./runBenchmark.sh prop.oceanbase

 导入数据成功如下图所示：

 合并成功如下图所示：

 压测结果如下：

 

二、分析 TPC-C TOP SQL 执行计划

1.查询TPC-C TOP SQL中avg_elapsed_time时间最长的前三条sql信息

SELECT sql_id, count(*),  round(avg(elapsed_time)) avg_elapsed_time, 
          round(avg(execute_time)) avg_exec_time,
          SVR_IP,
          SVR_PORT,
          TENANT_ID,
          PLAN_ID
        FROM gv$ob_sql_audit s
        WHERE user_name='root' 
         and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) )
        GROUP BY sql_id
        order by avg_elapsed_time desc limit 3;

2.根据sql_id查询sql语句

select distinct query_sql from gv$ob_sql_audit where sql_id='ED649AA45C2C9B669561C857148ECEA7';
select distinct query_sql from gv$ob_sql_audit where sql_id='443BEA6BA8A2798AAA9D9538E51A9049';
select distinct query_sql from gv$ob_sql_audit where sql_id='C9FA41444B84AF63AD4FB24B9252F1A6';

第一条sql

SELECT count(*) AS low_stock FROM (    SELECT s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = 12 AND s_quantity < 14 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 = 12 AND d_id = 9         )     );

 通过查询v$ob_plan_cache_plan_explain视图，查看实际执行计划

select * from v$ob_plan_cache_plan_explain where tenant_id = 1002 and plan_id = 2282;

 查看解析执行计划

explain SELECT count(*) AS low_stock FROM (    SELECT s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = 12 AND s_quantity < 14 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 = 12 AND d_id = 9         )     );

 解释执行计划和实际执行计划是相同的

第二条sql

SELECT count(*) AS low_stock FROM (    SELECT s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = 23 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 = 23 AND d_id = 10         )     );

 通过查询v$ob_plan_cache_plan_explain视图，查看实际执行计划

select * from v$ob_plan_cache_plan_explain where tenant_id = 1002 and plan_id = 1044;

 查看解析执行计划

explain SELECT count(*) AS low_stock FROM (    SELECT s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = 23 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 = 23 AND d_id = 10         )     );

 解释执行计划和实际执行计划也是相同的

第三条sql

SELECT o_id, o_entry_d, o_carrier_id     FROM bmsql_oorder     WHERE o_w_id = 36 AND o_d_id = 3 AND o_c_id = 1851       AND o_id = (          SELECT max(o_id)               FROM bmsql_oorder               WHERE o_w_id = 36 AND o_d_id = 3 AND o_c_id = 1851          );

 通过查询v$ob_plan_cache_plan_explain视图，查看实际执行计划

select * from v$ob_plan_cache_plan_explain where tenant_id = 1002 and plan_id = 1789;

 查看解析执行计划

explain SELECT o_id, o_entry_d, o_carrier_id     FROM bmsql_oorder     WHERE o_w_id = 36 AND o_d_id = 3 AND o_c_id = 1851       AND o_id = (          SELECT max(o_id)               FROM bmsql_oorder               WHERE o_w_id = 36 AND o_d_id = 3 AND o_c_id = 1851          );

 解释执行计划预估为表范围扫描，实际执行计划走全表扫描，成本消耗更低

至此OB实践练习六全部完成，完结撒花！