中信信诚资管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 的使用。