High Water Mark导致的SQL效率问题

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