高效的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/，如需轉載，請註明出處，否則將追究法律責任。

高效的SQL（清晰的邏輯重構業務SQL）

相關文章