10.2.0.5.6的t1trsn测试库。
用户报前台界面操作很慢，30s-120s之间才能完成，通过看日志，找出来是以下sql。
select n.id_loan_notice_info as noticeId,
nvl(n.id_len der_recover, ‘‘) as idLenderRecover,
decode(r.id_lender_recover, null, n.lenderno, b.lenderno) as lenderno,
decode(r.id_lender_rec over, null, n.lendername, cl.chinese_name) as lendername,
decode(r.id_lender_recover,
null,
n.client_type,
cl.IS_NATURAL_PERSON) as clientType,
n.main_contractno as mainContractNo,
n.contractno as contractNo,
decode(r.id_lender_recover,
null,
nvl(n.contract_ sum, 0),
nvl(c.contract_sum, 0)) as contractSum,
decode(r.id_lender_recover, null, n.date_start_recover, r.date_start) as dateStartRecover,
decode(r.id_lender_recover, null, n.date_end_recover, r.date_end) as dateEndRecover,
decode(r.id_lender_recover, null, n.date_recover, r.date_recover) as dateRecover,
decode(r.id_lender_recover, null, n.cmbno, b.cmbno) as cmbno,
decode(r.id_lender_recover, null, n.cmbname, vc.CMB_NAME) as cmbname,
n.notice_type as noticeType,
n.notice_state as noticeState,
nvl(n.notice_no,
n.ma in_contractno || ‘-‘ || to_char(sysdate, ‘yyyyMMdd‘) || ‘-‘ ||
decode(n.notice_type, ‘1‘, ‘LX‘, ‘2‘, ‘BJ‘, ‘3‘, ‘FX‘, ‘‘)) as noticeNo
from tlms_loa n_notice_info n
left join tlms_lender_recover r
on n.id_lender_recover = r.id_lender_recover
inner join tlms_lender_business_info b
on n.co ntractno = b.contractno
and b.data_state = ‘2‘
and b.delete_flag = ‘N‘
inner join tlms_contract_info c
on b.main_contractno = c.contractno
and c.dat a_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.cmb no = 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_len der_recover = n.id_lender_recover) > 0
and n.notice_type = ?
and v.account_type = ‘01‘
and r.date_en d >= trunc(?)
and r.date_end <= trunc(?)
绑定变量后：
select n.id_loan_notice_info as noticeId,
nvl(n.id_lender_recover, ‘‘) as idLenderRecover,
decode(r.id_lender_recover, null, n.lenderno, b.lenderno) as lenderno,
decode(r.id_lender_recover, null, n.lendername, cl.chinese_name) as lendername,
decode(r.id_lender_recover,
null,
n.client_type,
cl.IS_NATURAL_PERSON) as clientType,
n.main_contractno as mainContractNo,
n.contractno as contractNo,
decode(r.id_lender_recover,
null,
nvl(n.contract_sum, 0),
nvl(c.contract_sum, 0)) as contractSum,
decode(r.id_lender_recover, null, n.date_start_recover, r.date_start) as dateStartRecover,
decode(r.id_lender_recover, null, n.date_end_recover, r.date_end) as dateEndRecover,
decode(r.id_lender_recover, null, n.date_recover, r.date_recover) as dateRecover,
decode(r.id_lender_recover, null, n.cmbno, b.cmbno) as cmbno,
decode(r.id_lender_recover, null, n.cmbname, vc.CMB_NAME) as cmbname,
n.notice_type as noticeType,
n.notice_state as noticeState,
nvl(n.notice_no,
n.main_contractno || ‘-‘ || to_char(sysdate, ‘yyyyMMdd‘) || ‘-‘ ||
decode(n.notice_type, ‘1‘, ‘LX‘, ‘2‘, ‘BJ‘, ‘3‘, ‘FX‘, ‘‘)) as noticeNo
from tlms_loan_notice_info n
left join tlms_lender_recover r
on n.id_lender_recover = r.id_lender_recover
inner join tlms_lender_business_info b
on n.contractno = b.contractno
and b.data_state = ‘2‘
and b.delete_flag = ‘N‘
inner join tlms_contract_info c
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优化到此完毕。