高效的SQL（清晰的逻辑重构业务SQL）

高效的SQL（清晰的逻辑重构业务SQL） 


业务SQL如下
select to_char(t.entertime, 'yyyymmdd') AS time,
       t.userid,
       --振铃通话时间
       sum(ROUND(TO_NUMBER(t.turnontime - t.ringtime) * 24 * 60 * 60)) AS t_ring,
       --进线量
       count(case
               when (((select ceil((t1.custhanguptime - t1.ringtime) * 24 * 60 * 60) sh
                         FROM doudou t1
                        where t1.custhanguptime is not null and t1.callid=t.callid
                          and t1.ringtime is not null) > 5 or
                    (t.custhanguptime is null))) then
                1
               else
                null
             end) as n_enter,
       --呼入通话时长
       sum(ROUND(TO_NUMBER(t.hanguptime - t.turnontime) * 24 * 60 * 60)) AS t_inbound
  from doudou t
 where  t.ringtime is not null
 Group by to_char(t.entertime, 'yyyymmdd'), t.userid;


索引如下 
alter table DOUDOU
  add constraint DOUDOU_PK_CALLID primary key (CALLID)
  using index 
  tablespace CMSDATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
  


Elapsed: 00:00:13.96


Execution Plan
----------------------------------------------------------
Plan hash value: 2637126917


------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |   691K|    45M|  5971   (3)| 00:01:12 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| DOUDOU           |     1 |    30 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DOUDOU_PK_CALLID |     1 |       |     2   (0)| 00:00:01 |
|   3 |  HASH GROUP BY              |                  |   691K|    45M|  5971   (3)| 00:01:12 |
|*  4 |   TABLE ACCESS FULL         | DOUDOU           |   691K|    45M|  5921   (2)| 00:01:12 |
------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("T1"."CUSTHANGUPTIME" IS NOT NULL AND "T1"."RINGTIME" IS NOT NULL)
   2 - access("T1"."CALLID"=:B1)
   4 - filter("T"."RINGTIME" IS NOT NULL)


Note
-----
   - dynamic sampling used for this statement (level=2)




Statistics
----------------------------------------------------------
         78  recursive calls
          0  db block gets
    1191076  consistent gets  <==逻辑读过多
       4080  physical reads
          0  redo size
     542664  bytes sent via SQL*Net to client
      10904  bytes received via SQL*Net from client
        946  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      14169  rows processed
      
优化思想
1.解决TAF问题
2.如果解决不了TAF，瘦身DOUDOU这张表的有效数据，这样可以减少consistent gets


1.解决TAF问题
1252852 DOUDOU
563939 ringtime is not null  
1901 ringtime is not null AND custhanguptime is not null 


create index idx_zuhe on doudou (custhanguptime,ringtime,CALLID); 
exec dbms_stats.gather_table_stats(user,'DOUDOU',cascade=>true);
##经过复合索引，效果并不明显。所以选择瘦身DOUDOU表。
##使用分区表进行瘦身


2.如果解决不了TAF，瘦身DOUDOU这张表的有效数据，这样可以减少consistent gets
select to_char(t.entertime, 'yyyymmdd') AS time,
       t.userid,
       --振铃通话时间
       sum(ROUND(TO_NUMBER(t.turnontime - t.ringtime) * 24 * 60 * 60)) AS t_ring,
       --进线量
       count(case
               when (((select ceil((t1.custhanguptime - t1.ringtime) * 24 * 60 * 60) sh
                         FROM doudou t1
                        where t1.custhanguptime is not null and t1.callid=t.callid
                          and t1.ringtime is not null) > 5 or
                    (t.custhanguptime is null))) then
                1
               else
                null
             end) as n_enter,
       --呼入通话时长
       sum(ROUND(TO_NUMBER(t.hanguptime - t.turnontime) * 24 * 60 * 60)) AS t_inbound
  from  doudou t
 where 
   to_char(t.entertime, 'yyyymmdd') like '201607%' and  --重构SQL，瘦身DOUDOU表的有效数据。
 t.ringtime is not null
 Group by to_char(t.entertime, 'yyyymmdd'), t.userid ;
 
194 rows selected.


Elapsed: 00:00:01.76


Execution Plan
----------------------------------------------------------
Plan hash value: 2637126917


--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  | 28374 |  1246K|       |  6240   (2)| 00:01:15 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| DOUDOU           |     1 |    24 |       |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DOUDOU_PK_CALLID |     1 |       |       |     2   (0)| 00:00:01 |
|   3 |  HASH GROUP BY              |                  | 28374 |  1246K|  1800K|  6240   (2)| 00:01:15 |
|*  4 |   TABLE ACCESS FULL         | DOUDOU           | 28374 |  1246K|       |  5914   (2)| 00:01:11 |
--------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("T1"."CUSTHANGUPTIME" IS NOT NULL AND "T1"."RINGTIME" IS NOT NULL)
   2 - access("T1"."CALLID"=:B1)
   4 - filter("T"."RINGTIME" IS NOT NULL AND TO_CHAR(INTERNAL_FUNCTION("T"."ENTERTIME"),'yyyymmd
              d') LIKE '201607%')




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      44513  consistent gets  <==业务SQL重构后，基本满足需求
          0  physical reads
          0  redo size
       8099  bytes sent via SQL*Net to client
        652  bytes received via SQL*Net from client
         14  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        194  rows processed 
##根据业务逻辑，瘦身DOUDOU之后逻辑读从1191076优化至44513。优化了26倍。
##此业务SQL经跟业务人员沟通，当月的数据就可以满足业务需求。所以重构了业务SQL减少逻辑读。


总结：
优化往往是优化写SQL的一种思想，这种思想的核心就是用最小的数据来完成需求。无关的数据都是"砍!砍!砍!"掉

########################################################################################
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!【QQ交流群：53993419】
QQ：14040928 E-mail：dbadoudou@163.com
本文链接： http://blog.itpub.net/26442936/viewspace-2121889/
########################################################################################

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

转载于:http://blog.itpub.net/26442936/viewspace-2121889/