-
安装 obloader 的 jdk 环境，安装文件 jdk-8u333-linux-x64.rpm 已预先放置在 /root/t-oceanbase-antman 目录下
rpm -ivh jdk-8u333-linux-x64.rpm -
设置环境变量，JAVA_HOME
export JAVA_HOME=/usr/java/default -
生成实验用表
cd /root/ob-loader-dumper-3.1.0-SNAPSHOT/bin/
./obloader -h 172.16.1.15 -P 2883 -c obce_test -utpcc -pobce_test -t ob_pay -D TPCC --table TABLE_SMALL --thread 2 --ddl --csv --external-data -f /root/table --truncate-table --skip-header
./obloader -h 172.16.1.15 -P 2883 -c obce_test -utpcc -pobce_test -t ob_pay -D TPCC --table TABLE_BIG --thread 2 --ddl --csv --external-data -f /root/table --truncate-table --skip-header
- 执行查询脚本
obclient -h172.16.1.15 -P 2883 -utpcc@ob_pay -pobce_test
source /root/obce_sqltune/run1.sql
观察到第一条查询语句执行速度很快，但是第二条查询语句在 1 分钟后，超时退出执行。
- 查找超时 sql 所使用的执行计划
从 gv$sql_audit 中找到该条 sql 执行时 SVR_IP，SVR_PORT，PLAN_ID 等信息，IS_HIT_PLAN=0 表示该条 sql 执行重用了第一条 sql 的执行计划
select * from gv$sql_audit where query_sql like ‘select%rownum%table_big%table_small%’ order by request_time \G
#从 gv$plan_cache_plan_explain 中查找该执行计划
select * from gv$plan_cache_plan_explain where ip=‘172.16.1.16’ and port=‘2882’ and plan_id=70 ;
select * from gv$plan_cache_plan_explain where svr_ip=‘172.16.1.53’ and svr_port=‘2882’ and plan_id=100 \G; ;
结论1 .比较2条查询语句，区别主要在于分区的不同，第一条查询语句query1的trn_date扫描 table_big 一天的数据（2022/12/31 日），
第二条查询语句 query2 遍历一个月（2022/6 月）的分区数据， Ocenabase 优化器在执行第一条语句时，发现 table_big 中没有 2022/12/31 日的数据,
生成了一个针对此语句的执行计划，然后在执行第二条类似的查询语句时，优化复用了第一条语句执行后存放在 sql plan cache 的执行计划，造成第二
条语句没有生成一个更好的执行计划而造成性能问题。
#显示第一条 SQL 的执行计划， 与后面的练习比较
explain extended
select *
from ( select row_.*, rownum rownum_
from ( select *
from table_big m
join ( select a.acct_num, a.txn_date
from table_big a
join table_small b
on (a.acct_num = b.acct_num)
where a.cash_flag = ‘U’
and a.txn_amt != 0
and a.txn_stat = ‘0’
and a.txn_date>= ‘20220601’
and a.txn_date<= ‘20220630’
group by a.acct_num, a.txn_date
) n
on ( m.acct_num = n.acct_num
and m.txn_date= n.txn_date)
where m.cash_flag = ‘C’
and m.txn_stat = ‘1’
and m.txn_amt != 0
) row_
where rownum <= 100
)
where rownum_ > 0
;
| =========================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
|0 |COUNT | |25 |391576|
|1 | SUBPLAN SCAN |VIEW1 |25 |391576|
|2 | LIMIT | |25 |391576|
|3 | NESTED-LOOP JOIN | |25 |391575|
|4 | PX COORDINATOR | |25 |361165|
|5 | EXCHANGE OUT DISTR |:EX10000 |25 |361141|
|6 | PX PARTITION ITERATOR | |25 |361141|
|7 | TABLE SCAN |M |25 |361141|
|8 | SUBPLAN SCAN |N |1 |1234 |
|9 | MERGE GROUP BY | |1 |1234 |
|10| PX COORDINATOR | |2 |1234 |
|11| EXCHANGE OUT DISTR |:EX20001 |2 |1233 |
|12| NESTED-LOOP JOIN | |2 |1233 |
|13| EXCHANGE IN DISTR | |1 |1232 |
|14| EXCHANGE OUT DISTR (PKEY)|:EX20000 |1 |1232 |
|15| PX PARTITION ITERATOR | |1 |1232 |
|16| TABLE SCAN |A(TABLE_BIG_I1)|1 |1232 |
|17| PX PARTITION ITERATOR | |755 |292 |
|18| TABLE SCAN |B |755 |292 |
7 - output([M.ACCT_NUM(0x7fe46a450350)], [M.TXN_DATE(0x7fe46a450930)], [M.CASH_FLAG(0x7fe46a450f10)], [M.TXN_STAT(0x7fe46a451200)], [M.TXN_AMT(0x7fe46a4557c0)], [M.ACCT_CODE(0x7fe46a455ab0)], [M.ACCT_OPEN_ORG(0x7fe46a455da0)], [M.TXN_DETAIL(0x7fe46a456090)]), filter([M.TXN_STAT(0x7fe46a451200) = ?(0x7fe46a45bca0)], [M.CASH_FLAG(0x7fe46a450f10) = ‘C’(0x7fe46a45e680)], [M.TXN_DATE(0x7fe46a450930) <= ?(0x7fe46a45d5c0)], [M.TXN_DATE(0x7fe46a450930) >= ?(0x7fe46a45de20)], [M.TXN_AMT(0x7fe46a4557c0) != 0(0x7fe46a45f170)]),
access([M.ACCT_NUM(0x7fe46a450350)], [M.TXN_DATE(0x7fe46a450930)], [M.CASH_FLAG(0x7fe46a450f10)], [M.TXN_STAT(0x7fe46a451200)], [M.TXN_AMT(0x7fe46a4557c0)], [M.ACCT_CODE(0x7fe46a455ab0)], [M.ACCT_OPEN_ORG(0x7fe46a455da0)], [M.TXN_DETAIL(0x7fe46a456090)]), partitions(p[0-2]),
is_index_back=false, filter_before_indexback[false,false,false,false,false],
range_key([M.ACCT_NUM(0x7fe46a450350)], [M.ACCT_CODE(0x7fe46a455ab0)], [M.ACCT_OPEN_ORG(0x7fe46a455da0)]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
结论2
虽然查询范围有了较大变化，可以 select count（*）方式看一下实际的匹配记录数，但在 M 表（table_big）中估算匹配记录数只有 25 条，并不足以改变执行计划选择。
- 手工收集统计信息，让优化器可以更准确的估算，查看执行计划是否有变化
#call dbms_stats.gather_table_stats(‘tpcc’,‘table_big’, method_opt=>‘for all columns size 32’);
call dbms_stats.gather_table_stats(‘tpcc’,‘table_big’, method_opt=>‘for all columns size auto’);
explain extended
select *
from ( select row_.*, rownum rownum_
from ( select *
from table_big m
join ( select a.acct_num, a.txn_date
from table_big a
join table_small b
on (a.acct_num = b.acct_num)
where a.cash_flag = ‘U’
and a.txn_amt != 0
and a.txn_stat = ‘0’
and a.txn_date>= ‘20220601’
and a.txn_date<= ‘20220630’
group by a.acct_num, a.txn_date
) n
on ( m.acct_num = n.acct_num
and m.txn_date= n.txn_date)
where m.cash_flag = ‘C’
and m.txn_stat = ‘1’
and m.txn_amt != 0
) row_
where rownum <= 100
)
where rownum_ > 0
;
| ===========================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
|0 |COUNT | |100 |3416|
|1 | SUBPLAN SCAN |VIEW1 |100 |3414|
|2 | LIMIT | |100 |3413|
|3 | PX COORDINATOR | |100 |3411|
|4 | EXCHANGE OUT DISTR |:EX10002 |100 |3292|
|5 | LIMIT | |100 |3292|
|6 | NESTED-LOOP JOIN | |100 |3291|
|7 | EXCHANGE IN DISTR | |50 |3036|
|8 | EXCHANGE OUT DISTR (PKEY) |:EX10001 |50 |3025|
|9 | SUBPLAN SCAN |N |50 |3025|
|10| MERGE GROUP BY | |50 |3025|
|11| SORT | |292 |3013|
|12| NESTED-LOOP JOIN | |292 |2613|
|13| EXCHANGE IN DISTR | |4 |1192|
|14| EXCHANGE OUT DISTR (PKEY)|:EX10000 |4 |1191|
|15| PX PARTITION ITERATOR | |4 |1191|
|16| TABLE SCAN |A(TABLE_BIG_I1)|4 |1191|
|17| PX PARTITION ITERATOR | |755 |292 |
|18| TABLE SCAN |B |755 |292 |
|19| PX PARTITION ITERATOR | |1 |4 |
|20| TABLE SCAN |M(TABLE_BIG_I1)|1 |4 |
思考 1：为什么 M 表（table_big）上估算的匹配记录数更少了？
提示 1：join 顺序发生了变化，join 谓词提供了额外的过滤。
思考 2：观察该执行计划，考察数据重分布的情况，是否还有进一步的优化空间？
提示 2：可以使用表组来实现分区间并行 join
create tablegroup t_bs partition by hash partitions 3;
alter table table_big tablegroup t_bs;
alter table table_small tablegroup t_bs;
explain extended
select *
from ( select row_.*, rownum rownum_
from ( select *
from table_big m
join ( select a.acct_num, a.txn_date
from table_big a
join table_small b
on (a.acct_num = b.acct_num)
where a.cash_flag = ‘U’
and a.txn_amt != 0
and a.txn_stat = ‘0’
and a.txn_date>= ‘20220601’
and a.txn_date<= ‘20220630’
group by a.acct_num, a.txn_date
) n
on ( m.acct_num = n.acct_num
and m.txn_date= n.txn_date)
where m.cash_flag = ‘C’
and m.txn_stat = ‘1’
and m.txn_amt != 0
) row_
where rownum <= 100
)
where rownum_ > 0
;
| ===============================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
|0 |COUNT | |100 |3006|
|1 | SUBPLAN SCAN |VIEW1 |100 |3004|
|2 | LIMIT | |100 |3003|
|3 | PX COORDINATOR | |100 |3001|
|4 | EXCHANGE OUT DISTR |:EX10000 |100 |2882|
|5 | LIMIT | |100 |2882|
|6 | PX PARTITION ITERATOR| |100 |2881|
|7 | LIMIT | |100 |2881|
|8 | NESTED-LOOP JOIN | |100 |2879|
|9 | SUBPLAN SCAN |N |50 |2624|
|10| MERGE GROUP BY | |50 |2624|
|11| NESTED-LOOP JOIN| |292 |2612|
|12| TABLE SCAN |A(TABLE_BIG_I1)|4 |1191|
|13| TABLE SCAN |B |755 |292 |
|14| TABLE SCAN |M(TABLE_BIG_I1)|1 |4 |
思考：比较两张表在没有表组和有表组时，执行计划有啥区别？
提示：使用表组以后，减少了跨机器的数据重分布。
再次执行 run1 脚本，查看两条 query 的执行时间
source /root/obce_sqltune/run1.sql
select * from gv$sql_audit where query_sql like ‘select%rownum%table_big%table_small%’ order by request_time \G
思考：为什么统计信息收集、表组添加以后，第二条 query 依然超时？
提示：脚本中有 flush plan cache 操作，第一条 query 执行时重选执行计划，选择的执行计划不适合第二条 query
运行 run2 脚本，查看连续执行第二条 query 的变化
source /root/obce_sqltune/run2.sql
查看 query2 和 query3 的执行计划
select * from gv$sql_audit where query_sql like ‘select%rownum%table_big%table_small%’ order by request_time \G
select * from gv$plan_cache_plan_explain where svr_ip=‘172.16.1.16’ and svr_port=‘2882’ and plan_id=122 ;
select * from gv$plan_cache_plan_explain where svr_ip=‘172.16.1.16’ and svr_port=‘2882’ and plan_id=124 ;
思考：为什么 query2 的执行计划不见了？
提示：OceanBase 优化器的智能反馈功能，根据实际 sql 执行时间与估算时间偏差来重新
选择执行计划（淘汰掉原来计划）
source /root/obce_sqltune/run3.sql
通过 gv$sql_audit 查找第二条 query 的 sql_id
select * from gv$sql_audit where query_sql like ‘select%rownum%table_big%table_small%’ order by request_time \G
663BB560FC7FD324409125995F3C979A
663BB560FC7FD324409125995F3C979A
从第二条 query 语句的 explain 结果中找到 outline data 信息。
Outline Data:
/*+
BEGIN_OUTLINE_DATA
LEADING(@“SEL$5” (“N”@“SEL$5” “TPCC.M”@“SEL$5” ))
USE_NL(@“SEL$5” (“TPCC.M”@“SEL$5” ))
PQ_DISTRIBUTE(@“SEL$5” (“TPCC.M”@“SEL$5” ) NONE NONE)
NO_USE_NL_MATERIALIZATION(@“SEL$5” (“TPCC.M”@“SEL$5” ))
NO_USE_HASH_AGGREGATION(@“SEL$4”)
LEADING(@“SEL$4” (“TPCC.A”@“SEL$4” “TPCC.B”@“SEL$4” ))
USE_NL(@“SEL$4” (“TPCC.B”@“SEL$4” ))
PQ_DISTRIBUTE(@“SEL$4” (“TPCC.B”@“SEL$4” ) NONE NONE)
NO_USE_NL_MATERIALIZATION(@“SEL$4” (“TPCC.B”@“SEL$4” ))
INDEX(@“SEL$4” “TPCC.A”@“SEL$4” “TABLE_BIG_I1”)
FULL(@“SEL$4” “TPCC.B”@“SEL$4”)
INDEX(@“SEL$5” “TPCC.M”@“SEL$5” “TABLE_BIG_I1”)
END_OUTLINE_DATA
*/
create outline big_sql on ‘663BB560FC7FD324409125995F3C979A’ USING HINT /+LEADING(@“SEL$5” (“N”@“SEL$5” “TPCC.M”@“SEL$5” )) USE_NL(@“SEL$5” (“TPCC.M”@“SEL$5” ))/;
select * from gv$outline \G
*************************** 1. row ***************************
TENANT_ID: 1001
DATABASE_ID: 1100611139404827
OUTLINE_ID: 1100611139404777
DATABASE_NAME: TPCC
OUTLINE_NAME: BIG_SQL
VISIBLE_SIGNATURE:
SQL_TEXT:
OUTLINE_TARGET:
OUTLINE_SQL:
SQL_ID: 663BB560FC7FD324409125995F3C979A
OUTLINE_CONTENT: /+LEADING(@“SEL$5” (“N”@“SEL$5” “TPCC.M”@“SEL$5” )) USE_NL(@“SEL$5” (“TPCC.M”@“SEL$5” ))/
1 row in set (0.055 sec)
source /root/obce_sqltune/run1.sql
select * from gv$sql_audit where query_sql like ‘select%rownum%table_big%table_small%’ order by request_time \G
思考：如何查看 outline 已经生效？
提示：查询 gv$plan_cache_plan_stat。
select * from gv$plan_cache_plan_stat where plan_id=167\G
结论：SQL 执行计划调优中，统计信息收集至关重要，缺乏有效统计信息优化器无法选择最优计划；同时，使用 outline 绑定执行计划在某些场景下是快速纠正执行计划跑偏的有效手段。
使用 hit 优化
set ob_query_timeout=60000000;
explain extended
select /*+ USE_NL(a,b) / *
from ( select row_., rownum rownum_
from ( select *
from table_big m
join ( select a.acct_num, a.txn_date
from table_big a
join table_small b
on (a.acct_num = b.acct_num)
where a.cash_flag = ‘U’
and a.txn_amt != 0
and a.txn_stat = ‘0’
and a.txn_date>= ‘20220601’
and a.txn_date<= ‘20220630’
group by a.acct_num, a.txn_date
) n
on ( m.acct_num = n.acct_num
and m.txn_date= n.txn_date)
where m.cash_flag = ‘C’
and m.txn_stat = ‘1’
and m.txn_amt != 0
) row_
where rownum <= 100
)
where rownum_ > 0
;