帮哥们优化一个数据仓库的烂SQL
昨天一位铁哥们发来一个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烂，数据库服务器也烂 嘎嘎。
