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'