on b.main_contractno = c.contractno

and c.data_state = '2'

and c.delete_flag = 'N'

left join client_info_vw cl

on b.lenderno = cl.client_no

left join TCMS_TO_TLMS_TRUST_ACCOUNT_VW v

on n.cmbno = v.cmb_no

left join PMS_TO_TLMS_COMBINATION_VW vc

on b.cmbno = vc.cmbno

WHERE (select count(1)

from tlms_lender_recover r

where r.id_lender_recover = n.id_lender_recover) > 0

and n.notice_type = 3

and v.account_type = '01'

and r.date_end >= date '2014-03-01'

and r.date_end < = date '2014-03-31'

其中涉及的一个表，开发测试都认为有问题，因为如下一个语句，也得执行很久，而表只有800多条。

select * from TLMSDATA.TLMS_LOAN_NOTICE_INFO;

统计信息也是显示只有827条，最近一次收集是4天前，3月8日。

SQL> @stattab TLMS_LOAN_NOTICE_INFO

--@stattab tabname

OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED

-------- ------------------------------ ---------- --------------------------------------------------

TLMSDATA TLMS_LOAN_NOTICE_INFO 827 2014/3/8 0:01:57

看统计信息收集历史。

select * from dba_tab_stats_history where owner='TLMSDATA' and table_name='TLMS_LOAN_NOTICE_INFO' order by stats_update_time desc;

这个库按照我们的收集策略，是收集启用的。

SQL> @stats_enable

GATHER_VALID

---------------

STATS_ENABLE

---------------

收集启用

--这个表有4.9G，high water mark很高，曾经有过大量数据。

SQL> @sizebytab tlmsdata TLMS_LOAN_NOTICE_INFO

SIZE_MB

----------

4961

--将high water mark降下来。

SQL> alter table tlmsdata.TLMS_LOAN_NOTICE_INFO enable row movement;

Table altered

SQL> alter table tlmsdata.TLMS_LOAN_NOTICE_INFO SHRINK SPACE cascade;

Table altered

--此时这个表段只占用1M了。

SQL> @sizebytab tlmsdata TLMS_LOAN_NOTICE_INFO

--正确用法 @tabsize owner tab

SIZE_MB

----------

1

然后用户就反映后台查还是app界面查，都变得很快了。

这个SQL的执行计划，出问题的地方就是如下红框，索引必然也被撑得很大，而shrink cascade会把索引也shrink。

SQL优化到此完毕。