昨天一位鐵哥們發來一個OLAP的爛SQL，它要跑1分16秒，問一下能否優化，SQL語句和執行計劃如下：
SQL> SELECT sum(t.ysje), sum(t.ssje)
2 FROM SWGL_DDJBXX t, SWGL_DDCPBXX dd
3 WHERE dd.dd_id = t.dd_id
4 AND dd.CPBBB_ID = (SELECT MAX(ddcpb.CPBBB_ID)
5 FROM SWGL_DDCPBXX ddcpb
6 WHERE ddcpb.dd_id = t.dd_id)
7 AND t.fzgs_dm = '001093'
8 AND t.skzt IN ('4')
9 AND t.kpzt IN ('0', '1', '2', '3', '4')
10 AND (t.xjbz = '9999' OR
11 t.xjbz IN
12 (SELECT xj.xjbz
13 FROM SWGL_DDXJXX xj
14 WHERE xj.status IN ('0', '1', '2', '3', '4', '5')))
15 AND t.jfzt IN ('0', '1', '2', '3', '4', '5')
16 AND t.ddzt IN ('01', '11', '12', '13', '14')
17 AND t.ddly IN ('01', '02', '03', '04', '05', '06', '07')
18 AND t.FLOWSTATUS IN ('0', '1', '2', '3', '4')
19 AND t.SKZT = '4'
20 AND EXISTS
21 (select 1
22 from SWGL_DDXJQRXX xj
23 where xj.xjbz = t.xjbz
24 and xj.xjqrlx = '1'
25 and xj.wdqrsj >=
26 to_date('2011-09-01 00:00:00', 'yyyy-mm-dd HH24:MI:ss')
27 and xj.xjqr_id = (select max(xjqr_id)
28 from SWGL_DDXJQRXX qr
29 where qr.xjbz = t.xjbz))
30 AND t.SKZT = '4'
31 AND EXISTS
32 (select 1
33 from SWGL_DDXJQRXX xj
34 where xj.xjbz = t.xjbz
35 and xj.xjqrlx = '1'
36 and xj.wdqrsj <=
37 to_date('2011-09-01 23:59:59', 'yyyy-mm-dd HH24:MI:ss')
38 and xj.xjqr_id = (select max(xjqr_id)
39 from SWGL_DDXJQRXX qr
40 where qr.xjbz = t.xjbz));
SUM(T.YSJE) SUM(T.SSJE)
----------- -----------
84000 84000
已用時間: 00: 01: 16.54
執行計劃
----------------------------------------------------------
Plan hash value: 262682057
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 107 | | 66033 (1)| 00:13:13 |
| 1 | SORT AGGREGATE | | 1 | 107 | | | |
|* 2 | FILTER | | | | | | |
|* 3 | HASH JOIN | | 2628 | 274K| 9456K| 65244 (1)| 00:13:03 |
| 4 | MERGE JOIN | | 98724 | 8291K| | 50450 (1)| 00:10:06 |
| 5 | SORT JOIN | | 3063K| 90M| | 29372 (1)| 00:05:53 |
| 6 | VIEW | VW_SQ_1 | 3063K| 90M| | 29372 (1)| 00:05:53 |
| 7 | HASH GROUP BY | | 3063K| 61M| 189M| 29372 (1)| 00:05:53 |
| 8 | MAT_VIEW ACCESS FULL | SWGL_DDCPBXX | 3111K| 62M| | 9451 (1)| 00:01:54 |
|* 9 | SORT JOIN | | 98724 | 5302K| 15M| 21079 (1)| 00:04:13 |
|* 10 | MAT_VIEW ACCESS BY INDEX ROWID| SWGL_DDJBXX | 98724 | 5302K| | 19748 (1)| 00:03:57 |
|* 11 | INDEX RANGE SCAN | IDX_SWGL_DDJBXX_FZGS | 167K| | | 458 (1)| 00:00:06 |
| 12 | MAT_VIEW ACCESS FULL | SWGL_DDCPBXX | 3111K| 62M| | 9451 (1)| 00:01:54 |
|* 13 | MAT_VIEW ACCESS BY INDEX ROWID | SWGL_DDXJXX | 1 | 24 | | 2 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_SWGL_DDXJXX | 1 | | | 1 (0)| 00:00:01 |
|* 15 | MAT_VIEW ACCESS BY INDEX ROWID | SWGL_DDXJQRXX | 1 | 37 | | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PK_SWGL_DDXJQRXX | 1 | | | 1 (0)| 00:00:01 |
| 17 | SORT AGGREGATE | | 1 | 27 | | | |
|* 18 | MAT_VIEW ACCESS FULL | SWGL_DDXJQRXX | 2 | 54 | | 393 (1)| 00:00:05 |
|* 19 | MAT_VIEW ACCESS BY INDEX ROWID | SWGL_DDXJQRXX | 1 | 37 | | 2 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PK_SWGL_DDXJQRXX | 1 | | | 1 (0)| 00:00:01 |
| 21 | SORT AGGREGATE | | 1 | 27 | | | |
|* 22 | MAT_VIEW ACCESS FULL | SWGL_DDXJQRXX | 2 | 54 | | 393 (1)| 00:00:05 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("T"."XJBZ"='9999' OR EXISTS (SELECT 0 FROM "SWGL_DDXJXX" "XJ" WHERE "XJ"."XJBZ"=:B1 AND
("XJ"."STATUS"='0' OR "XJ"."STATUS"='1' OR "XJ"."STATUS"='2' OR "XJ"."STATUS"='3' OR "XJ"."STATUS"='4' OR
"XJ"."STATUS"='5'))) AND EXISTS (SELECT 0 FROM "SWGL_DDXJQRXX" "XJ" WHERE "XJ"."XJQR_ID"= (SELECT
MAX("XJQR_ID") FROM "SWGL_DDXJQRXX" "QR" WHERE "QR"."XJBZ"=:B2) AND "XJ"."XJBZ"=:B3 AND
"XJ"."WDQRSJ">=TO_DATE('2011-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "XJ"."XJQRLX"='1') AND EXISTS
(SELECT 0 FROM "SWGL_DDXJQRXX" "XJ" WHERE "XJ"."XJQR_ID"= (SELECT MAX("XJQR_ID") FROM "SWGL_DDXJQRXX" "QR"
WHERE "QR"."XJBZ"=:B4) AND "XJ"."XJBZ"=:B5 AND "XJ"."XJQRLX"='1' AND "XJ"."WDQRSJ"<=TO_DATE('2011-09-01
23:59:59', 'yyyy-mm-dd hh24:mi:ss')))
3 - access("DD"."DD_ID"="T"."DD_ID" AND "DD"."CPBBB_ID"="VW_COL_1")
9 - access("DD_ID"="T"."DD_ID")
filter("DD_ID"="T"."DD_ID")
10 - filter("T"."SKZT"='4' AND ("T"."DDLY"='01' OR "T"."DDLY"='02' OR "T"."DDLY"='03' OR "T"."DDLY"='04'
OR "T"."DDLY"='05' OR "T"."DDLY"='06' OR "T"."DDLY"='07') AND ("T"."JFZT"='0' OR "T"."JFZT"='1' OR
"T"."JFZT"='2' OR "T"."JFZT"='3' OR "T"."JFZT"='4' OR "T"."JFZT"='5') AND ("T"."KPZT"='0' OR "T"."KPZT"='1'
OR "T"."KPZT"='2' OR "T"."KPZT"='3' OR "T"."KPZT"='4') AND ("T"."DDZT"='01' OR "T"."DDZT"='11' OR
"T"."DDZT"='12' OR "T"."DDZT"='13' OR "T"."DDZT"='14') AND ("T"."FLOWSTATUS"='0' OR "T"."FLOWSTATUS"='1' OR
"T"."FLOWSTATUS"='2' OR "T"."FLOWSTATUS"='3' OR "T"."FLOWSTATUS"='4'))
11 - access("T"."FZGS_DM"='001093')
13 - filter("XJ"."STATUS"='0' OR "XJ"."STATUS"='1' OR "XJ"."STATUS"='2' OR "XJ"."STATUS"='3' OR
"XJ"."STATUS"='4' OR "XJ"."STATUS"='5')
14 - access("XJ"."XJBZ"=:B1)
15 - filter("XJ"."XJBZ"=:B1 AND "XJ"."WDQRSJ">=TO_DATE('2011-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND "XJ"."XJQRLX"='1')
16 - access("XJ"."XJQR_ID"= (SELECT MAX("XJQR_ID") FROM "SWGL_DDXJQRXX" "QR" WHERE "QR"."XJBZ"=:B1))
18 - filter("QR"."XJBZ"=:B1)
19 - filter("XJ"."XJBZ"=:B1 AND "XJ"."XJQRLX"='1' AND "XJ"."WDQRSJ"<=TO_DATE('2011-09-01 23:59:59',
'yyyy-mm-dd hh24:mi:ss'))
20 - access("XJ"."XJQR_ID"= (SELECT MAX("XJQR_ID") FROM "SWGL_DDXJQRXX" "QR" WHERE "QR"."XJBZ"=:B1))
22 - filter("QR"."XJBZ"=:B1)
統計資訊
----------------------------------------------------------
123 recursive calls
7 db block gets
9720499 consistent gets
15452 physical reads
0 redo size
589 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
1 rows processed
首先，我想罵人了，寫這個SQL的人水準也太菜了吧，請注意看這個SQL，相信你看了它幾秒鐘就會罵人了，于是我對它進行了改寫
SELECT sum(t.ysje), sum(t.ssje)
FROM SWGL_DDJBXX t, SWGL_DDCPBXX dd
WHERE dd.dd_id = t.dd_id
AND dd.CPBBB_ID = (SELECT MAX(ddcpb.CPBBB_ID)
FROM SWGL_DDCPBXX ddcpb
WHERE ddcpb.dd_id = t.dd_id)
AND t.fzgs_dm = '001093'
AND t.skzt IN ('4')
AND t.kpzt IN ('0', '1', '2', '3', '4')
AND (t.xjbz = '9999' OR
t.xjbz IN
(SELECT xj.xjbz
FROM SWGL_DDXJXX xj
WHERE xj.status IN ('0', '1', '2', '3', '4', '5')))
AND t.jfzt IN ('0', '1', '2', '3', '4', '5')
AND t.ddzt IN ('01', '11', '12', '13', '14')
AND t.ddly IN ('01', '02', '03', '04', '05', '06', '07')
AND t.FLOWSTATUS IN ('0', '1', '2', '3', '4')
AND t.SKZT = '4'
AND EXISTS
(select
1
from SWGL_DDXJQRXX xj
where xj.xjbz = t.xjbz
and xj.xjqrlx = '1'
and xj.wdqrsj >=
to_date('2011-09-01 00:00:00', 'yyyy-mm-dd HH24:MI:ss')
and xj.wdqrsj <=
to_date('2011-09-01 23:59:59', 'yyyy-mm-dd HH24:MI:ss')
and xj.xjqr_id = (select
max(xjqr_id)
from SWGL_DDXJQRXX qr
where qr.xjbz = t.xjbz))
AND t.SKZT = '4'
更改了SQL之後，跑了一次SQL，依然要跑1分多，好了現在才開始進入優化的正題，SQL優化做得多了，一眼就能看出這個SQL性能問題出在 and EXISTS 這個地方，于是讓哥們去掉
and EXISTS部分，看SQL需要多久跑完
SELECT sum(t.ysje), sum(t.ssje)
FROM SWGL_DDJBXX t, SWGL_DDCPBXX dd
WHERE dd.dd_id = t.dd_id
AND dd.CPBBB_ID = (SELECT MAX(ddcpb.CPBBB_ID)
FROM SWGL_DDCPBXX ddcpb
WHERE ddcpb.dd_id = t.dd_id)
AND t.fzgs_dm = '001093'
AND t.skzt IN ('4')
AND t.kpzt IN ('0', '1', '2', '3', '4')
AND (t.xjbz = '9999' OR
t.xjbz IN
(SELECT xj.xjbz
FROM SWGL_DDXJXX xj
WHERE xj.status IN ('0', '1', '2', '3', '4', '5')))
AND t.jfzt IN ('0', '1', '2', '3', '4', '5')
AND t.ddzt IN ('01', '11', '12', '13', '14')
AND t.ddly IN ('01', '02', '03', '04', '05', '06', '07')
AND t.FLOWSTATUS IN ('0', '1', '2', '3', '4')
AND t.SKZT = '4'
哥們回複說，隻要2秒。那麼現在應該把精力放在優化
AND EXISTS
(select
1
from SWGL_DDXJQRXX xj
where xj.xjbz = t.xjbz
and xj.xjqrlx = '1'
and xj.wdqrsj >=
to_date('2011-09-01 00:00:00', 'yyyy-mm-dd HH24:MI:ss')
and xj.wdqrsj <=
to_date('2011-09-01 23:59:59', 'yyyy-mm-dd HH24:MI:ss')
and xj.xjqr_id = (select
max(xjqr_id)
from SWGL_DDXJQRXX qr
where qr.xjbz = t.xjbz))
分析了原始執行計劃後，在SWGL_DDXJQRXX這個物化視圖上建立了2個索引
create index idx1 on SWGL_DDXJQRXX(wdqrsj,xjqrlx);
create index idx2 on SWGL_DDXJQRXX(xjqr_id,xjbz);
在物化視圖上面加了2個索引之後，讓哥們跑了一下SQL，發現沒起作用，真太悲劇了，物化視圖有點變态啊，居然不走索引(其實這裡還是CBO計算基數出了問題，暫時就不說這個了)
于是讓哥們加2個HINT，語句如下：
SQL> SELECT sum(t.ysje), sum(t.ssje) FROM SWGL_DDJBXX t, SWGL_DDCPBXX dd WHERE dd.dd_id = t.dd_id AND dd.CPBBB_ID = (SELECT MAX(ddcpb.CPBBB_ID) FROM SWGL_DDCPBXX ddcpb WHERE ddcpb.dd_id = t.dd_id) AND t.fzgs_dm = '001093' AND t.skzt IN ('4') AND
t.kpzt IN ('0', '1', '2', '3', '4') AND (t.xjbz = '9999' OR t.xjbz IN (SELECT xj.xjbz FROM SWGL_DDXJXX xj WHERE xj.status IN ('0', '1', '2', '3', '4', '5'))) AND t.jfzt IN ('0', '1', '2', '3', '4', '5') AND t.ddzt IN ('01', '11', '12', '13', '14') AND t.ddly I
N ('01', '02', '03', '04', '05', '06', '07') AND t.FLOWSTATUS IN ('0', '1', '2', '3', '4') AND t.SKZT = '4' AND EXISTS (select /*+ index(xj idx_SWGL_DDXJQRXX_wdq_xjq) */ 1 from SWGL_DDXJQRXX xj where xj.xjbz = t.xjbz and xj.xjqrlx = '1' and xj.wdqrs
j >= to_date('2011-09-01 00:00:00', 'yyyy-mm-dd HH24:MI:ss') and xj.wdqrsj <= to_date('2011-09-01 23:59:59', 'yyyy-mm-dd HH24:MI:ss') and xj.xjqr_id = (select /*+ index(qr idx_SWGL_DDXJQRXX_xjq_xjb) */ max(xjqr_id)
from SWGL_DDXJQRXX qr where qr.xjbz = t.xjbz)) AND t.SKZT = '4';
SUM(T.YSJE) SUM(T.SSJE)
----------- -----------
84000 84000
已用時間: 00: 00: 16.29
執行計劃
----------------------------------------------------------
Plan hash value: 3675331138
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 107 | | 59521 (1)| 00:11:55 |
| 1 | SORT AGGREGATE | | 1 | 107 | | | |
|* 2 | FILTER | | | | | | |
|* 3 | HASH JOIN | | 2628 | 274K| 9456K| 58858 (1)| 00:11:47 |
| 4 | MERGE JOIN | | 98724 | 8291K| | 50450 (1)| 00:10:06 |
| 5 | SORT JOIN | | 3063K| 90M| | 29372 (1)| 00:05:53 |
| 6 | VIEW | VW_SQ_1 | 3063K| 90M| | 29372 (1)| 00:05:53 |
| 7 | HASH GROUP BY | | 3063K| 61M| 189M| 29372 (1)| 00:05:53 |
| 8 | MAT_VIEW ACCESS FULL | SWGL_DDCPBXX | 3111K| 62M| | 9451 (1)| 00:01:54 |
|* 9 | SORT JOIN | | 98724 | 5302K| 15M| 21079 (1)| 00:04:13 |
|* 10 | MAT_VIEW ACCESS BY INDEX ROWID| SWGL_DDJBXX | 98724 | 5302K| | 19748 (1)| 00:03:57 |
|* 11 | INDEX RANGE SCAN | IDX_SWGL_DDJBXX_FZGS | 167K| | | 458 (1)| 00:00:06 |
| 12 | INDEX FAST FULL SCAN | IDX_SWGL_DDCPBXX_DD_ID_CPBB | 3111K| 62M| | 3066 (1)| 00:00:37 |
|* 13 | MAT_VIEW ACCESS BY INDEX ROWID | SWGL_DDXJXX | 1 | 24 | | 2 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_SWGL_DDXJXX | 1 | | | 1 (0)| 00:00:01 |
|* 15 | FILTER | | | | | | |
|* 16 | MAT_VIEW ACCESS BY INDEX ROWID | SWGL_DDXJQRXX | 1 | 37 | | 33 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | IDX_SWGL_DDXJQRXX_WDQ_XJQ | 152 | | | 2 (0)| 00:00:01 |
| 18 | SORT AGGREGATE | | 1 | 27 | | | |
|* 19 | INDEX FULL SCAN | IDX_SWGL_DDXJQRXX_XJQ_XJB | 2 | 54 | | 631 (1)| 00:00:08 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("T"."XJBZ"='9999' OR EXISTS (SELECT 0 FROM "SWGL_DDXJXX" "XJ" WHERE "XJ"."XJBZ"=:B1 AND
("XJ"."STATUS"='0' OR "XJ"."STATUS"='1' OR "XJ"."STATUS"='2' OR "XJ"."STATUS"='3' OR "XJ"."STATUS"='4' OR
"XJ"."STATUS"='5'))) AND EXISTS (SELECT /*+ INDEX ("XJ" "IDX_SWGL_DDXJQRXX_WDQ_XJQ") */ 0 FROM "SWGL_DDXJQRXX"
"XJ" WHERE "XJ"."XJQR_ID"= (SELECT /*+ INDEX ("QR" "IDX_SWGL_DDXJQRXX_XJQ_XJB") */ MAX("XJQR_ID") FROM
"SWGL_DDXJQRXX" "QR" WHERE "QR"."XJBZ"=:B2) AND "XJ"."WDQRSJ"<=TO_DATE('2011-09-01 23:59:59', 'yyyy-mm-dd
hh24:mi:ss') AND "XJ"."WDQRSJ">=TO_DATE('2011-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "XJ"."XJBZ"=:B3 AND
"XJ"."XJQRLX"='1'))
3 - access("DD"."DD_ID"="T"."DD_ID" AND "DD"."CPBBB_ID"="VW_COL_1")
9 - access("DD_ID"="T"."DD_ID")
filter("DD_ID"="T"."DD_ID")
10 - filter("T"."SKZT"='4' AND ("T"."DDLY"='01' OR "T"."DDLY"='02' OR "T"."DDLY"='03' OR "T"."DDLY"='04' OR
"T"."DDLY"='05' OR "T"."DDLY"='06' OR "T"."DDLY"='07') AND ("T"."JFZT"='0' OR "T"."JFZT"='1' OR "T"."JFZT"='2' OR
"T"."JFZT"='3' OR "T"."JFZT"='4' OR "T"."JFZT"='5') AND ("T"."KPZT"='0' OR "T"."KPZT"='1' OR "T"."KPZT"='2' OR
"T"."KPZT"='3' OR "T"."KPZT"='4') AND ("T"."DDZT"='01' OR "T"."DDZT"='11' OR "T"."DDZT"='12' OR "T"."DDZT"='13' OR
"T"."DDZT"='14') AND ("T"."FLOWSTATUS"='0' OR "T"."FLOWSTATUS"='1' OR "T"."FLOWSTATUS"='2' OR "T"."FLOWSTATUS"='3'
OR "T"."FLOWSTATUS"='4'))
11 - access("T"."FZGS_DM"='001093')
13 - filter("XJ"."STATUS"='0' OR "XJ"."STATUS"='1' OR "XJ"."STATUS"='2' OR "XJ"."STATUS"='3' OR
"XJ"."STATUS"='4' OR "XJ"."STATUS"='5')
14 - access("XJ"."XJBZ"=:B1)
15 - filter("XJ"."XJQR_ID"= (SELECT /*+ INDEX ("QR" "IDX_SWGL_DDXJQRXX_XJQ_XJB") */ MAX("XJQR_ID") FROM
"SWGL_DDXJQRXX" "QR" WHERE "QR"."XJBZ"=:B1))
16 - filter("XJ"."XJBZ"=:B1)
17 - access("XJ"."WDQRSJ">=TO_DATE('2011-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "XJ"."XJQRLX"='1' AND
"XJ"."WDQRSJ"<=TO_DATE('2011-09-01 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
filter("XJ"."XJQRLX"='1')
19 - access("QR"."XJBZ"=:B1)
filter("QR"."XJBZ"=:B1)
統計資訊
----------------------------------------------------------
131 recursive calls
8 db block gets
200563 consistent gets
16341 physical reads
0 redo size
589 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
1 rows processed
這下跑了16秒，發現執行計劃裡面有SORT MERGE JOIN，于是懷疑統計資訊有問題，很可惜，那哥們收集統計資訊出錯了(問題太複雜了，省略)，是以又加了個HINT
SQL> SELECT /*+ use_hash(t,dd) */ sum(t.ysje), sum(t.ssje) FROM SWGL_DDJBXX t, SWGL_DDCPBXX dd WHERE dd.dd_id = t.dd_id AND dd.CPBBB_ID = (SELECT MAX(ddcpb.CPBBB_ID) FROM SWGL_DDCPBXX ddcpb WHERE ddcpb.dd_id = t.dd_id) AND t.fzgs_dm = '001093' AND
t.skzt IN ('4') AND t.kpzt IN ('0', '1', '2', '3', '4') AND (t.xjbz = '9999' OR t.xjbz IN (SELECT xj.xjbz FROM SWGL_DDXJXX xj WHERE xj.status IN ('0', '1', '2', '3', '4', '5'))) AND t.jfzt IN ('0', '1', '2', '3', '4', '5') AND t.ddzt IN ('01', '11', '12', '13'
, '14') AND t.ddly IN ('01', '02', '03', '04', '05', '06', '07') AND t.FLOWSTATUS IN ('0', '1', '2', '3', '4') AND t.SKZT = '4' AND EXISTS (select /*+ index(xj idx_SWGL_DDXJQRXX_wdq_xjq) */ 1 from SWGL_DDXJQRXX xj where xj.xjbz = t.xjbz and xj.xjqrlx = '1'
and xj.wdqrsj >= to_date('2011-09-01 00:00:00', 'yyyy-mm-dd HH24:MI:ss') and xj.wdqrsj <= to_date('2011-09-01 23:59:59', 'yyyy-mm-dd HH24:MI:ss') and xj.xjqr_id = (select /*+ index(qr idx_SWGL_DDXJQRXX_xjq_xjb) */
max(xjqr_id) from SWGL_DDXJQRXX qr where qr.xjbz = t.xjbz)) AND t.SKZT = '4';
SUM(T.YSJE) SUM(T.SSJE)
----------- -----------
84000 84000
已用時間: 00: 00: 09.00
執行計劃
----------------------------------------------------------
Plan hash value: 2004668452
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 107 | | 64720 (1)| 00:12:57 |
| 1 | SORT AGGREGATE | | 1 | 107 | | | |
|* 2 | FILTER | | | | | | |
|* 3 | HASH JOIN | | 2628 | 274K| 8624K| 64057 (1)| 00:12:49 |
|* 4 | HASH JOIN | | 100K| 7442K| 6464K| 28011 (1)| 00:05:37 |
|* 5 | MAT_VIEW ACCESS BY INDEX ROWID| SWGL_DDJBXX | 98724 | 5302K| | 19748 (1)| 00:03:57 |
|* 6 | INDEX RANGE SCAN | IDX_SWGL_DDJBXX_FZGS | 167K| | | 458 (1)| 00:00:06 |
| 7 | INDEX FAST FULL SCAN | IDX_SWGL_DDCPBXX_DD_ID_CPBB | 3111K| 62M| | 3066 (1)| 00:00:37 |
| 8 | VIEW | VW_SQ_1 | 3063K| 90M| | 29372 (1)| 00:05:53 |
| 9 | HASH GROUP BY | | 3063K| 61M| 189M| 29372 (1)| 00:05:53 |
| 10 | MAT_VIEW ACCESS FULL | SWGL_DDCPBXX | 3111K| 62M| | 9451 (1)| 00:01:54 |
|* 11 | MAT_VIEW ACCESS BY INDEX ROWID | SWGL_DDXJXX | 1 | 24 | | 2 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PK_SWGL_DDXJXX | 1 | | | 1 (0)| 00:00:01 |
|* 13 | FILTER | | | | | | |
|* 14 | MAT_VIEW ACCESS BY INDEX ROWID| SWGL_DDXJQRXX | 1 | 37 | | 33 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | IDX_SWGL_DDXJQRXX_WDQ_XJQ | 152 | | | 2 (0)| 00:00:01 |
| 16 | SORT AGGREGATE | | 1 | 27 | | | |
|* 17 | INDEX FULL SCAN | IDX_SWGL_DDXJQRXX_XJQ_XJB | 2 | 54 | | 631 (1)| 00:00:08 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("T"."XJBZ"='9999' OR EXISTS (SELECT 0 FROM "SWGL_DDXJXX" "XJ" WHERE "XJ"."XJBZ"=:B1 AND
("XJ"."STATUS"='0' OR "XJ"."STATUS"='1' OR "XJ"."STATUS"='2' OR "XJ"."STATUS"='3' OR "XJ"."STATUS"='4' OR
"XJ"."STATUS"='5'))) AND EXISTS (SELECT /*+ INDEX ("XJ" "IDX_SWGL_DDXJQRXX_WDQ_XJQ") */ 0 FROM "SWGL_DDXJQRXX"
"XJ" WHERE "XJ"."XJQR_ID"= (SELECT /*+ INDEX ("QR" "IDX_SWGL_DDXJQRXX_XJQ_XJB") */ MAX("XJQR_ID") FROM
"SWGL_DDXJQRXX" "QR" WHERE "QR"."XJBZ"=:B2) AND "XJ"."WDQRSJ"<=TO_DATE('2011-09-01 23:59:59', 'yyyy-mm-dd
hh24:mi:ss') AND "XJ"."WDQRSJ">=TO_DATE('2011-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "XJ"."XJBZ"=:B3 AND
"XJ"."XJQRLX"='1'))
3 - access("DD"."CPBBB_ID"="VW_COL_1" AND "DD_ID"="T"."DD_ID")
4 - access("DD"."DD_ID"="T"."DD_ID")
5 - filter("T"."SKZT"='4' AND ("T"."DDLY"='01' OR "T"."DDLY"='02' OR "T"."DDLY"='03' OR "T"."DDLY"='04' OR
"T"."DDLY"='05' OR "T"."DDLY"='06' OR "T"."DDLY"='07') AND ("T"."JFZT"='0' OR "T"."JFZT"='1' OR "T"."JFZT"='2' OR
"T"."JFZT"='3' OR "T"."JFZT"='4' OR "T"."JFZT"='5') AND ("T"."KPZT"='0' OR "T"."KPZT"='1' OR "T"."KPZT"='2' OR
"T"."KPZT"='3' OR "T"."KPZT"='4') AND ("T"."DDZT"='01' OR "T"."DDZT"='11' OR "T"."DDZT"='12' OR "T"."DDZT"='13'
OR "T"."DDZT"='14') AND ("T"."FLOWSTATUS"='0' OR "T"."FLOWSTATUS"='1' OR "T"."FLOWSTATUS"='2' OR
"T"."FLOWSTATUS"='3' OR "T"."FLOWSTATUS"='4'))
6 - access("T"."FZGS_DM"='001093')
11 - filter("XJ"."STATUS"='0' OR "XJ"."STATUS"='1' OR "XJ"."STATUS"='2' OR "XJ"."STATUS"='3' OR
"XJ"."STATUS"='4' OR "XJ"."STATUS"='5')
12 - access("XJ"."XJBZ"=:B1)
13 - filter("XJ"."XJQR_ID"= (SELECT /*+ INDEX ("QR" "IDX_SWGL_DDXJQRXX_XJQ_XJB") */ MAX("XJQR_ID") FROM
"SWGL_DDXJQRXX" "QR" WHERE "QR"."XJBZ"=:B1))
14 - filter("XJ"."XJBZ"=:B1)
15 - access("XJ"."WDQRSJ">=TO_DATE('2011-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "XJ"."XJQRLX"='1' AND
"XJ"."WDQRSJ"<=TO_DATE('2011-09-01 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
filter("XJ"."XJQRLX"='1')
17 - access("QR"."XJBZ"=:B1)
filter("QR"."XJBZ"=:B1)
統計資訊
----------------------------------------------------------
47 recursive calls
0 db block gets
195216 consistent gets
5642 physical reads
0 redo size
589 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
到了這裡，這個SQL能在9秒左右跑完，
邏輯讀和實體讀分别由
9720499 consistent gets
15452 physical reads
降低到了
195216 consistent gets
5642 physical reads
你可能會想，為什麼還有5642的實體讀呢？那是因為PGA太小了，才1G
|* 3 | HASH JOIN | | 2628 | 274K| 8624K| 64057 (1)| 00:12:49 |
|* 4 | HASH JOIN | | 100K| 7442K| 6464K| 28011 (1)| 00:05:37 |
| 9 | HASH GROUP BY | | 3063K| 61M| 189M| 29372 (1)| 00:05:53 |
| 10 | MAT_VIEW ACCESS FULL | SWGL_DDCPBXX | 3111K| 62M| | 9451 (1)| 00:01:54 |
第3，4，第9步 這裡有2個HASH JOIN 需要磁盤hash join, 第九步那裡做HASH GROUP BY 也需要磁盤HASH GROUP BY，真搞不懂 是OLAP的倉庫PGA怎麼才1G
太悲劇了，SQL爛，資料庫伺服器也爛 嘎嘎。
![Oracle的基本操作[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)