中信信诚资管SQL优化

中信信诚资管测试数据库SQL性能优化
【处理时间】2016-11-21 至2016-11-22
【客户名称】中信信诚资管
【主机信息】oracle11g 11.2.0.4 单机
【处理人员】周光晖
【问题说明】现象：
中信信诚方工反应一条晚上跑批处理的查询要8分钟左右，有时候直接报错
ORA-01652: 无法通过 128 (在表空间 TEMP 中) 扩展 temp 段
时间耗时难以接受。

希望HS DBA介入分析是否有进一步优化的余地。

问题分析
1. sql 文本如下，是一段较为简单的多视图联合查询的语句，最外层套了一个sequence
select tax_sequence.nextval,v.* from (
select'personAll'|| '|'|| vs.c_fundcode || '|'|| to_char(max(vs.d_date),'yyyymmdd') tx_id,
'TA' source_sys,
'personAll' fund_acctno,
'personAll' cust_id,
vs.c_fundcode,
max(vs.d_date) bill_date,
sum(vs.f_lastshares) real_shares,
trunc(max(vs.d_date),'mm') bill_date_month,
1,
sysdate,
'WRITED'
from v_vat_staticshares@cust_dblink vs,
v_vat_accoinfo@cust_dblink va,
v_vat_trustfundinfo@cust_dblink vt
where vs.d_date = to_date('20161010','yyyyddmm')
and vs.c_tano = '0M'
and vs.c_fundacco = va.c_fundacco
and va.c_tano = '0M'
and va.c_custtype = '1'
and vs.c_fundcode = vt.c_fundcode
and vt.c_tano = '0M'
group by vs.c_fundcode
) v
where v.real_shares > 0;
2. 客户反映如果直接执行内层的查询，即不带sequence的情况下。

内部查询只要1秒即可出结果。

其执行计划如下：
执行计划中看到，视图V_VAT_TRUSTFUNDINFO，V_VAT_STATICSHARES 直接在执行计划中视图单独执行了。

而sql文本中视图v_vat_accoinfo va 被谓词推进了。

因为在视图的基表中有选择性较好的索引，且视图本身查询很慢。

将谓词条件推入视图使用基表的索引大幅提升了执行的性能。

SQL Plan Monitoring Details (Plan Hash Value=522553774)
========================================================================================== | Id | Operation | Name | Rows | Cost |
| | | | (Estim) | |
========================================================================================== | 0 | SELECT STATEMENT | | | |
| 1 | HASH GROUP BY | | 2954 | 4826 |
| 2 | HASH JOIN | | 48694 | 4824 |
| 3 | VIEW | V_VAT_TRUSTFUNDINFO | 3155 | 101 |
| 4 | SORT UNIQUE | | 3155 | 101 |
| 5 | UNION-ALL | | | |
| 6 | TABLE ACCESS FULL | TSUBPROJECTS | 1 | 3 |
| 7 | HASH JOIN RIGHT OUTER | | 3148 | 89 |
| 8 | TABLE ACCESS FULL | TSUBPROJECTS | 164 | 3 |
| 9 | HASH JOIN | | 3148 | 86 |
| 10 | TABLE ACCESS FULL | TTRUSTPROJECTS | 2026 | 17 |
| 11 | TABLE ACCESS FULL | TFUNDINFO | 3209 | 69 |
| 12 | TABLE ACCESS FULL | TSUBPROJECTS | 1 | 3 |
| 13 | NESTED LOOPS | | 7 | 10 |
| 14 | NESTED LOOPS | | 7 | 10 |
| 15 | TABLE ACCESS FULL | TSUBPROJECTS | 7 | 3 |
| 16 | INDEX UNIQUE SCAN | ITRUSTPROJECTS_PROJECTCODE | 1 | |
| 17 | TABLE ACCESS BY INDEX ROWID | TTRUSTPROJECTS | 1 | 1 |
| 18 | HASH JOIN | | 45592 | 4723 |
| 19 | NESTED LOOPS | | 16552 | 1098 |
| 20 | TABLE ACCESS FULL | TACCOINFO | 16552 | 1097 |
| 21 | INDEX UNIQUE SCAN | ICUSTOMERINFO | 1 | |
| 22 | VIEW | V_VAT_STATICSHARES | 96334 | 3625 |
| 23 | FILTER | | | |
| 24 | HASH GROUP BY | | 96334 | 3625 |
| 25 | HASH JOIN | | 97000 | 1176 |
| 26 | TABLE ACCESS FULL | TFUNDINFO | 3271 | 69 |
| 27 | HASH JOIN | | 97000 | 1106 |
| 28 | TABLE ACCESS FULL | TTRUSTBENEFITCERT | 54636 | 275 |
| 29 | NESTED LOOPS | | 97657 | 831 |
| 30 | INDEX UNIQUE SCAN | IOPENDAY | 1 | 1 |
| 31 | TABLE ACCESS FULL | TSHARECURRENTS | 97657 | 830 |
========================================================================================
3. 既然是11g中执行耗时长达8分钟，那么已经被sql monitor捕获到了。

通过sql monitor分析下这段sql在执行过程中的瓶颈在哪里。

我们看下在外层多了一个sequence的情况下，执行计划是怎么样的。

这段sql语句在执行了114s之后挂掉了，压根没有执行出任何结果。

挂掉的原因是temp被撑爆。

我们在v$session s, v$sort_usage u 视图中的数据也印证了temp被hash jion耗尽的情况。

select dbms_sqltune.report_sql_monitor(sql_id => '2x0zh72gvq6yh',type=> 'text') from dual;
4. 上面的执行计划显示在hash join 处资源消耗最严重，主要的等待事件为直接路径读。

尝试在sql中加 hint
/*+ opt_param('_serial_direct_read', 'never')*/ 禁用 11g 中可能因为直接路径读机制改变的影响。

其执行计划依然没变，最后同样是爆掉temp告终。

也试过在内查询中使用 no_merge push_pred hint 强制走谓词推进，然而并没有效果
5. 考虑禁用 hash join 尝试效果如何,在sql 文本中加入 hint /*+ opt_param('hash_join_enabled', 'false') */执行计划从 hash join 变成了 nest loop 执行时间从8分钟下降到了 10秒钟。

处理建议
1.通过sql monitor 很容易可以定位到执行计划的瓶颈。

方便我们找到问题突破口。

2.谓词推进在合适的时机效果很明显。

如视图本身查询很慢，谓词条件推入能够用到视图基表的索引则可以大幅提升
性能。

3.opt_param hint 的使用。