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优化到此完毕。