存储过程运行得很慢！！！

ShineCQY

存储过程很慢（总共需要2个多小时），用10046事件跟踪了下，发现有如下信息：
Optimizer mode: ALL_ROWS
Parsing user id: 39     (recursive depth: 1)
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=31983 pr=31852 pw=0 time=0 us)
   4389   HASH JOIN RIGHT OUTER (cr=31815 pr=31795 pw=0 time=1043 us cost=7209 size=314964 card=4038)
     26    TABLE ACCESS FULL PARAM_CCY_CCYCODE (cr=7 pr=6 pw=0 time=0 us cost=3 size=182 card=26)
   4389    TABLE ACCESS FULL V_BRZ_IDMS_ACC_DTL (cr=31808 pr=31789 pw=0 time=951 us cost=7205 size=286698 card=4038)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                        58        0.01          0.01
  db file scattered read                       2010        0.05          4.05
********************************************************************************
从执行计划中看出全表扫描比较多，这些表我看了下，基本上都没有索引，不知道该建立哪种类型的索引？另，存储过程代码中用in 比较多，是否改为 exists呢？希望大家能提点建议。

[ 本帖最后由 ShineCQY 于 2012-8-16 15:15 编辑 ]

ShineCQY

在朋友的帮助下，最终使存储过程由98分钟缩短到了3.5分钟，好开心，感谢热心网友的帮助，第一次优化 感触良多...
优化结果
存储过程sp_idms_day_accntab 在测试库上由原来的98分钟左右缩短到现在的4.5分钟左右(测试时间2012-04-30）。
建立索引的表
DM_ACCNTAB
create index idx_dm_accntab on dm_accntab (acct_code,data_date) online;
V_BRZ_IDMS_ACC_DTL
create index idx_v_brz_idms_acc_dtl on v_brz_idms_acc_dtl (data_date) online;
V_BRZ_IDMS_AC_GEN_CONDITION
create index idx_v_brz_idms_ac_gen on v_brz_idms_ac_gen_condition (data_date) online;
V_BRZ_IDMS_AML
create index idx_V_BRZ_IDMS_AML on V_BRZ_IDMS_AML (data_date) online;
存储过程代码 未改动

[ 本帖最后由 ShineCQY 于 2012-8-17 17:20 编辑 ]

ShineCQY

经过一周的测试，发现还是修改索引对存储存储过程运行性能影响最大，修改SQL代码效果甚微，何况自己SQL也不咋地... 修改代码（用instr函数代替like，把distinct改为group by 都是过了，几乎没有效果）。现在我想知道的是我现在建立的索引是不是最优的？索引方面还有没有优化的余地？ 求教高手，并向高手学习...
表和索引信息、新的trace文件在附件中。

钱多多

分析并学习了，多谢~

Ling.QIu

原帖由 钱多多 于 2012-8-15 08:21 发表


我想问下，为什么distinct要改成group by？我的疑问是，distinct在写法上比group by要简便一些，比如我可以按照某些列分组，然后对另外的一些列去重取值。又或者您的意思是仅仅在这个pro里需要这么做？ ...

老杨的关于这两者的区别
http://space.itpub.net/4227/viewspace-69053

钱多多

原帖由 Ling.QIu 于 2012-8-13 18:33 发表
in是需要改掉的

还有distinct 请改成group by

尽量不要 select 套select  将一个复杂的语句拆成小的几个部分 逐步调整

个人意见

我想问下，为什么distinct要改成group by？我的疑问是，distinct在写法上比group by要简便一些，比如我可以按照某些列分组，然后对另外的一些列去重取值。又或者您的意思是仅仅在这个pro里需要这么做？

Ling.QIu

in是需要改掉的

还有distinct 请改成group by

尽量不要 select 套select  将一个复杂的语句拆成小的几个部分 逐步调整

个人意见

ShineCQY

今天测试了下，对两张表建立了复合索引。建立索引的原则是看where 选择条件那些列使用的比较频繁。表v_brz_idms_acc_dtl 经常选择的列 （ccy_code,record_type,data_date)
表dm_accntab  经常选择的列  (cust_num,acct_code,data_date)
然后建立索引 create index idx_v_brz_idms_acc_dtl on v_brz_idms_acc_dtl (ccy_code,data_date) online;
create index idx_dm_accntab on dm_accntab (acct_code,data_date) online;
不知道这样建立索引合理吗？希望刘大给个建议。
改动结果：原本执行存储过程需要98分钟，现在需要60分钟。
由于 没有权限查看跟踪文件，所以暂时没能得到改动后的执行计划。

ShineCQY

问题补充：发现表DM_ACCNTAB 、V_BRZ_IDMS_ACC_DTL 不仅没有索引而且连主键也没有。 这两张表的数据行都超过90W行。这样的表该怎样操作呢？