帮哥们优化一个数据仓库的烂SQL

最新推荐文章于 2022-05-19 16:52:44 发布

iteye_20478

最新推荐文章于 2022-05-19 16:52:44 发布

阅读量93

点赞数

文章标签：数据库

昨天一位铁哥们发来一个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需要多久跑完

哥们回复说，只要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 |