记一次函数优化

最新推荐文章于 2024-10-08 17:39:57 发布

ctest3001

最新推荐文章于 2024-10-08 17:39:57 发布

阅读量81

点赞数

文章标签：数据库

公司某部门需要一张报表，我将写好的sql语句在测试环境中执行，实验一下，结果发现这条语句运行出奇的慢，整个查询完成花了接近8分钟，奇怪。sql语句如下（表名以已修改）：
SQL> select distinct
2 trunc(sysdate) 报表日期,
3 t.lbi_demand_num 申请编号,
4 t.lbi_frist_name || t.lbi_last_name 主贷人姓名,
5 decode(cc.cli_gender,'1','男','0','女' ) 性别,
6 (select org.org_name from oooo org where org.org_id = t.lbi_bba_id) 分行,
7 (select mg.mamager_name from mmmm mg where mg.mamager_id = t.lbi_cha_id ) 渠道,
8 (select lt.lty_loan_type from llll lt where lt.lty_id = t.lbi_lty_id ) 贷款类型,
9 t.lbi_pact_number 合同编号,
10 trunc(t.lbi_fact_loan_limit) 贷款金额,
11 calcprincipal(act.act_id) 剩余本金,
12 trunc(t.lbi_fact_time_limit) 贷款期数,
13 getyetlimit(t.lbi_id) 已付期数,
14 trunc(t.lbi_pact_startend_date) 贷款生效日期,
15 cd.dmi_refund_date 到期日,
16 ps.pm_per_payment_fee 每月还款额,
17 trunc(act.act_next_paydate) 到期还款日期,
18 getnextpaymoeny(act.act_id) 到期还款金额,
19 (select rw.rwa_meaning from rrrr rw where rw.rwa_id = t.lbi_rwa_id) 还款方式
20 from cccc t
21 inner join ccccc cc
22 on cc.lbi_id = t.lbi_id
23 inner join aaaa act on act.act_clbi_id = t.lbi_id
24 and cc.cli_status = 1
25 and (cc.cli_relation is null or cc.cli_relation = '')
26 left join cccccc cd on cd.dmi_loanbill_id = t.lbi_id
27 left join ccccccccccc cp on cp.pba_loan_id=t.lbi_id and cp.pba_dma=1
28 left join ppppppp ps on ps.pm_account_id =act.act_id and ps.pm_period=1
29 where t.lbi_account_code <> 'CN'
30 and calcprincipal(act.act_id)>0;

看了又看，没发语句本身有什么端倪，于是开启sqltrace细看之：

SQL> set autotrace on
SQL> select distinct
2 trunc(sysdate) 报表日期,
3 t.lbi_demand_num 申请编号,
4 t.lbi_frist_name || t.lbi_last_name 主贷人姓名,
5 decode(cc.cli_gender,'1','男','0','女' ) 性别,
6 (select org.org_name from oooo org where org.org_id = t.lbi_bba_id) 分行,
7 (select mg.mamager_name from mmmm mg where mg.mamager_id = t.lbi_cha_id ) 渠道,
8 (select lt.lty_loan_type from llll lt where lt.lty_id = t.lbi_lty_id ) 贷款类型,
9 t.lbi_pact_number 合同编号,
10 trunc(t.lbi_fact_loan_limit) 贷款金额,
11 calcprincipal(act.act_id) 剩余本金,
12 trunc(t.lbi_fact_time_limit) 贷款期数,
13 getyetlimit(t.lbi_id) 已付期数,
14 trunc(t.lbi_pact_startend_date) 贷款生效日期,
15 cd.dmi_refund_date 到期日,
16 ps.pm_per_payment_fee 每月还款额,
17 trunc(act.act_next_paydate) 到期还款日期,
18 getnextpaymoeny(act.act_id) 到期还款金额,
19 (select rw.rwa_meaning from rrrr rw where rw.rwa_id = t.lbi_rwa_id) 还款方式
20 from cccc t
21 inner join ccccc cc
22 on cc.lbi_id = t.lbi_id
23 inner join aaaa act on act.act_clbi_id = t.lbi_id
24 and cc.cli_status = 1
25 and (cc.cli_relation is null or cc.cli_relation = '')
26 left join cccccc cd on cd.dmi_loanbill_id = t.lbi_id
27 left join ccccccccccc cp on cp.pba_loan_id=t.lbi_id and cp.pba_dma=1
28 left join ppppppp ps on ps.pm_account_id =act.act_id and ps.pm_period=1
29 where t.lbi_account_code <> 'CN'
30 and calcprincipal(act.act_id)>0;

执行出来，果然有一个地方不对劲，在Statistics里面
Statistics
----------------------------------------------------------
18910 recursive calls
0 db block gets
317548963 consistent gets
0 physical reads
404 redo size
346164 bytes sent via SQL*Net to client
2951 bytes received via SQL*Net from client
223 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3330 rows processed

consistent gets，3亿，夸张了。这个consistent gets，一致性读，官方文档的解释：

The consistent gets Oracle metric is the number of times a consistent read (a logical RAM buffer I/O) was requested to get data from a data block.

翻译过来就是：为了保证一致性读时，从需要的数据块读取数据的次数（翻译得很烂）。注意这里的consistent gets一致性读，和oracle的特性之一consistent read一致性读不是同一个东西。

简单点说，在trace里面表示处理这个sql的时候需要在一致性读上处理多少个块。这些块产生的主要原因就是由于在查询过程中其他会话对数据块进行操作，并对我所需要的块做了修改，但是由于我的查询是在这些修改之前调用的，所以需要对回滚段中的前映象进行查询，以保证数据的一致性，所以就产生了一致性读的数据块。

有了这个重要的线索，再一次回头分析sql语句，各个表的数据都不大，so大胆猜测问题出在三个函数里面。于是依次屏蔽三个，执行这个sql，果然不出所料，在将 getyetlimit 屏蔽之后，这个sql语句只花了1秒。。。

分析此function（表名已改）

create or replace function GETYETLIMIT(lbiid in varchar2)
  return number is
  Result       number;
  totallimit   number;
  accountstate varchar2(10);
begin
  select LBI_ACCOUNT_CODE, LBI_FACT_TIME_LIMIT
    into accountstate, totallimit
    from cccccc
   where lbi_id = lbiid;
  select case
           when substr(accountstate, 0, 2) = 'SL' then
            totallimit
           else
            round(sum(t.PM_PAID_MANAGEFEE + t.PM_PAID_PRINCIPAL +
                t.PM_PAID_INTEREST) /
            sum(t.PM_MANAGEFEE + t.PM_INTEREST + t.PM_PRINCIPAL) *
            totallimit,2)
         end into Result
    from pppppp t
   where t.pm_account_id in (select act_id from aaaaa where act_clbi_id= lbiid);
  return(Result);
end GETYETLIMIT;

估计就是这个in造成的。这里说下in和exist的区别：

对于in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。

将这个in改成 = ，最终问题解决，查询只花了3秒。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25915379/viewspace-714788/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25915379/viewspace-714788/