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

1.安装benchmarksql

unzip benchmarksql-5.0.zip

vi /export/benchmarksql-5.0/src/client/jTPCC.java
修改代码
添加

else if (iDB.equals("mysql"))
    dbType = DB_UNKNOWN;

vi /export/benchmarksql-5.0/src/client/jTPCCConnection.java
修改代码

编译安装

cd /export/benchmarksql-5.0
ant

创建配置文件

cd /export/benchmarksql-5.0/run
cat props.ob
db=oracle

driver=com.alipay.oceanbase.jdbc.Driver

conn=jdbc:mysql://127.0.0.1:13881/test

user=root@obmysql

password=test
warehouses=4
loadWorkers=10
terminals=10
//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

2.脚本适配
vi /export/benchmarksql-5.0/run/funcs.sh
添加

mysql)
    cp="../lib/mysql/*:../lib/*"
    ;;

vi /export/benchmarksql-5.0/run/runDatabaseBuild.sh

AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"

改为

AFTER_LOAD="indexCreates foreignKeys buildFinish"

添加驱动

mkdir -p /export/benchmarksql-5.0/lib/mysql
cd /export/benchmarksql-5.0/lib/mysql
cp /export/datax/plugin/reader/mysqlreader/libs/mysql-connector-java-5.1.34.jar ./

3.初始化

cd /export/benchmarksql-5.0/run
./runDatabaseBuild.sh props.ob

4.进行压测

cd /export/benchmarksql-5.0/run
./runBenchmark.sh props.ob

5.结果分析

SELECT sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time, round(avg(execute_time)) avg_exec_time FROM gv$sql_audit s GROUP BY sql_id order by avg_elapsed_time desc limit 10;

测试慢SQL几乎都为ddl和insert
手动执行一条慢SQL演示一下如何查看解析执行计划和实际的执行计划

eDatabase changed
MySQL [test]> explain select sum(hist_id) from bmsql_history where h_data not in("ss")\G
*************************** 1. row ***************************
Query Plan: ===================================================
|ID|OPERATOR       |NAME         |EST. ROWS|COST  |
---------------------------------------------------
|0 |SCALAR GROUP BY|             |1        |327791|
|1 | TABLE SCAN    |bmsql_history|8908     |326090|
===================================================

Outputs & filters: 
-------------------------------------
  0 - output([T_FUN_SUM(bmsql_history.hist_id)]), filter(nil), 
      group(nil), agg_func([T_FUN_SUM(bmsql_history.hist_id)])
  1 - output([bmsql_history.hist_id]), filter([bmsql_history.h_data != 'ss']), 
      access([bmsql_history.h_data], [bmsql_history.hist_id]), partitions(p0)

1 row in set (0.002 sec)

MySQL [oceanbase]> SELECT * FROM v$plan_cache_plan_stat WHERE tenant_id= 1001 AND STATEMENT LIKE '%sum(hist_id)%'\G
*************************** 1. row ***************************
            tenant_id: 1001
               svr_ip: 127.0.0.1
             svr_port: 13882
              plan_id: 2162
               sql_id: 56E41DA1B35A711E0CC2108BFF582E30
                 type: 1
    is_bind_sensitive: 0
        is_bind_aware: 0
                db_id: 18446744073709551615
            statement: SELECT * FROM v$plan_cache_plan_stat WHERE tenant_id= ? AND STATEMENT LIKE '%sum(hist_id)%'
            query_sql: SELECT * FROM v$plan_cache_plan_stat WHERE tenant_id= 1001 AND STATEMENT LIKE '%sum(hist_id)%'
       special_params: '%sum(hist_id)%'
          param_infos: {1,0,0,0,5},{1,0,0,-1,22},{1,0,0,-1,4},{1,0,0,-1,10},{1,0,0,-1,10},{1,1,0,-1,4}
             sys_vars: 45,4194304,2,4,1,0,0,32,3,1,0,1,1,0,10485760,1,1,0,1,BINARY,BINARY,AL32UTF8,AL32UTF8,BYTE,FALSE,1,100,64,200,0,13,NULL,1,1,1
            plan_hash: 16749516816941279108
      first_load_time: 2022-05-03 01:31:12.420262
       schema_version: 1651510882539248
       merged_version: 11
     last_active_time: 2022-05-03 01:31:12.427506
         avg_exe_usec: 13093
     slowest_exe_time: 2022-05-03 01:31:12.427506
     slowest_exe_usec: 13093
           slow_count: 0
            hit_count: 0
            plan_size: 192256
           executions: 1
           disk_reads: 0
        direct_writes: 0
          buffer_gets: 4
application_wait_time: 0
concurrency_wait_time: 0
    user_io_wait_time: 0
       rows_processed: 1
         elapsed_time: 13093
             cpu_time: 12898
         large_querys: 0
 delayed_large_querys: 0
    delayed_px_querys: 0
      outline_version: 0
           outline_id: -1
         outline_data: /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "oceanbase.__all_virtual_plan_stat"@"SEL$1") END_OUTLINE_DATA*/
         acs_sel_info: 
           table_scan: 1
            evolution: 0
       evo_executions: 0
         evo_cpu_time: 0
        timeout_count: 0
           ps_stmt_id: -1
               sessid: 0
          temp_tables: 
           is_use_jit: 0
          object_type: SQL_PLAN
           hints_info: 
     hints_all_worked: 1
         pl_schema_id: NULL
is_batched_multi_stmt: 0