一次效能最佳化將filter轉換，最佳化filter轉換
有一條SQL效能有問題，在執行計畫中發現filter，遇到它要小心了，類似於nestloop，我以前的blog對它有研究探索執行計畫中filter的原理.用exists極易引起filter.
最佳化前：
SELECT GGPI.ID,
GGPI.PLAN_STATE,
GGPI.PLAN_TYPE,
GGPI.PLAN_CODE,
GGPI.PLAN_SOURCE_TYPE,
GGPI.PLAN_BEGIN_DATE,
GGPI.PLAN_END_DATE,
GGPI.REAL_BEGIN_DATE,
GGPI.REAL_END_DATE,
GGPI.WORK_MASTER_UNAME,
GGPI.WORK_MASTER_UID,
GGPI.WORK_TEAM_ONAME,
GGPI.WORK_SITE_NAMES,
GGPI.WORK_CONTENT,
GGPI.WORK_TYPE,
GGPI.WORK_MEMBER_UNAMES,
GGPI.DIGGATCH_WORK_UNAME,
GGPI.WORKING
FROM GG_PD_PP_INFO GGPI
WHERE PLAN_STATE IN (50, 60, 70)
AND ((GGPI.PLAN_BEGIN_DATE >= sysdate and GGPI.PLAN_BEGIN_DATE <= sysdate) or
(GGPI.PLAN_END_DATE >= sysdate and GGPI.PLAN_END_DATE <= sysdate) or
(GGPI.PLAN_BEGIN_DATE < sysdate and GGPI.PLAN_END_DATE > sysdate))
AND GGPI.BUREAU_CODE = '0306'
AND (GGPI.WORK_MASTER_UID ='FA3502D1291A4A07A3B9E3E0F9A41904' OR EXISTS
(SELECT 1
FROM GG_PD_PP_WORK_MEMBER PMEM
WHERE PMEM.PROD_PLAN_ID = GGPI.ID
AND PMEM.WORK_MEMBER_UID = 'FA3502D1291A4A07A3B9E3E0F9A41904'
AND PMEM.BUREAU_CODE = '0306'));
Execution Plan
----------------------------------------------------------
Plan hash value: 980323934
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9670 | 2039K| 25694 (1)| 00:05:09 | | |
|* 1 |
FILTER | | | | | | | |
| 2 | PARTITION LIST SINGLE | | 72882 | 15M| 25694 (1)| 00:05:09 | KEY | KEY |
| 3 | PARTITION RANGE ALL | | 72882 | 15M| 25694 (1)| 00:05:09 | 1 | 14 |
|* 4 | TABLE ACCESS FULL | GG_PD_PP_INFO | 72882 | 15M| 25694 (1)| 00:05:09 | | |
|* 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| GG_PD_PP_WORK_MEMBER | 1 | 49 | 5 (0)| 00:00:01 | 4 | 4 |
|* 6 | INDEX RANGE SCAN | IDX_PP_WORK_MEMBER_PPID | 6 | | 3 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("GGPI"."WORK_MASTER_UID" IS NULL OR EXISTS (SELECT 0 FROM "GG_PD_PP_WORK_MEMBER"
"PMEM" WHERE "PMEM"."PROD_PLAN_ID"=:B1 AND "PMEM"."WORK_MEMBER_UID"='FA3502D1291A4A07A3B9E3E0F9A41904' AND
"PMEM"."BUREAU_CODE"='0306'))
4 - filter(("PLAN_STATE"=50 OR "PLAN_STATE"=60 OR "PLAN_STATE"=70) AND ("GGPI"."PLAN_END_DATE">SYSDATE@! AND
"GGPI"."PLAN_BEGIN_DATE"<SYSDATE@! OR "GGPI"."PLAN_BEGIN_DATE"=SYSDATE@! OR "GGPI"."PLAN_END_DATE"=SYSDATE@!))
5 - filter("PMEM"."WORK_MEMBER_UID"='FA3502D1291A4A07A3B9E3E0F9A41904' AND "PMEM"."BUREAU_CODE"='0306')
6 - access("PMEM"."PROD_PLAN_ID"=:B1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
103763 consistent gets
0 physical reads
0 redo size
3178 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processed
最佳化後：
SELECT GGPI.ID,
GGPI.PLAN_STATE,
GGPI.PLAN_TYPE,
GGPI.PLAN_CODE,
GGPI.PLAN_SOURCE_TYPE,
GGPI.PLAN_BEGIN_DATE,
GGPI.PLAN_END_DATE,
GGPI.REAL_BEGIN_DATE,
GGPI.REAL_END_DATE,
GGPI.WORK_MASTER_UNAME,
GGPI.WORK_MASTER_UID,
GGPI.WORK_TEAM_ONAME,
GGPI.WORK_SITE_NAMES,
GGPI.WORK_CONTENT,
GGPI.WORK_TYPE,
GGPI.WORK_MEMBER_UNAMES,
GGPI.DIGGATCH_WORK_UNAME,
GGPI.WORKING
FROM GG_PD_PP_INFO GGPI
WHERE PLAN_STATE IN (50, 60, 70)
AND ((GGPI.PLAN_BEGIN_DATE >= sysdate and GGPI.PLAN_BEGIN_DATE <= sysdate) or
(GGPI.PLAN_END_DATE >= sysdate and GGPI.PLAN_END_DATE <= sysdate) or
(GGPI.PLAN_BEGIN_DATE < sysdate and GGPI.PLAN_END_DATE > sysdate))
AND GGPI.BUREAU_CODE = '0306'
AND GGPI.WORK_MASTER_UID ='FA3502D1291A4A07A3B9E3E0F9A41904'
union all
SELECT GGPI.ID,
GGPI.PLAN_STATE,
GGPI.PLAN_TYPE,
GGPI.PLAN_CODE,
GGPI.PLAN_SOURCE_TYPE,
GGPI.PLAN_BEGIN_DATE,
GGPI.PLAN_END_DATE,
GGPI.REAL_BEGIN_DATE,
GGPI.REAL_END_DATE,
GGPI.WORK_MASTER_UNAME,
GGPI.WORK_MASTER_UID,
GGPI.WORK_TEAM_ONAME,
GGPI.WORK_SITE_NAMES,
GGPI.WORK_CONTENT,
GGPI.WORK_TYPE,
GGPI.WORK_MEMBER_UNAMES,
GGPI.DIGGATCH_WORK_UNAME,
GGPI.WORKING
FROM GG_PD_PP_INFO GGPI
WHERE PLAN_STATE IN (50, 60, 70)
AND ((GGPI.PLAN_BEGIN_DATE >= sysdate and GGPI.PLAN_BEGIN_DATE <= sysdate) or
(GGPI.PLAN_END_DATE >= sysdate and GGPI.PLAN_END_DATE <= sysdate) or
(GGPI.PLAN_BEGIN_DATE < sysdate and GGPI.PLAN_END_DATE > sysdate))
AND GGPI.BUREAU_CODE = '0306'
AND GGPI.ID in
(SELECT PMEM.PROD_PLAN_ID
FROM GG_PD_PP_WORK_MEMBER PMEM
WHERE PMEM.WORK_MEMBER_UID = 'FA3502D1291A4A07A3B9E3E0F9A41904'
AND PMEM.BUREAU_CODE = '0306');
Execution Plan
----------------------------------------------------------
Plan hash value: 1911592856
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1227 | 316K| 3740 (1)| 00:00:45 | | |
| 1 | UNION-ALL | | | | | | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID | GG_PD_PP_INFO | 22 | 4752 | 160 (0)| 00:00:02 | ROWID | ROWID |
|* 3 | INDEX RANGE SCAN | IND_GGPI_WORK_MASTER_UID | 222 | | 5 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 1205 | 311K| 3580 (1)| 00:00:43 | | |
| 5 | NESTED LOOPS | | 1205 | 311K| 3580 (1)| 00:00:43 | | |
| 6 | SORT UNIQUE | | 1205 | 59045 | 2373 (1)| 00:00:29 | | |
|* 7 | TABLE ACCESS BY GLOBAL INDEX ROWID| GG_PD_PP_WORK_MEMBER | 1205 | 59045 | 2373 (1)| 00:00:29 | 4 | 4 |
|* 8 | INDEX RANGE SCAN | IND_GGPW_WORK_MEMBER_UID | 4240 | | 31 (0)| 00:00:01 | | |
|* 9 | INDEX UNIQUE SCAN | PK_GG_PD_PP_INFO | 1 | | 1 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS BY GLOBAL INDEX ROWID | GG_PD_PP_INFO | 1 | 216 | 2 (0)| 00:00:01 | ROWID | ROWID |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("PLAN_STATE"=50 OR "PLAN_STATE"=60 OR "PLAN_STATE"=70) AND ("GGPI"."PLAN_END_DATE">SYSDATE@! AND
"GGPI"."PLAN_BEGIN_DATE"<SYSDATE@! OR "GGPI"."PLAN_BEGIN_DATE"=SYSDATE@! OR "GGPI"."PLAN_END_DATE"=SYSDATE@!) AND
"GGPI"."BUREAU_CODE"='0306')
3 - access("GGPI"."WORK_MASTER_UID"='FA3502D1291A4A07A3B9E3E0F9A41904')
7 - filter("PMEM"."BUREAU_CODE"='0306')
8 - access("PMEM"."WORK_MEMBER_UID"='FA3502D1291A4A07A3B9E3E0F9A41904')
9 - access("GGPI"."ID"="PMEM"."PROD_PLAN_ID")
10 - filter(("GGPI"."PLAN_END_DATE">SYSDATE@! AND "GGPI"."PLAN_BEGIN_DATE"<SYSDATE@! OR
"GGPI"."PLAN_BEGIN_DATE"=SYSDATE@! OR "GGPI"."PLAN_END_DATE"=SYSDATE@!) AND ("PLAN_STATE"=50 OR "PLAN_STATE"=60 OR
"PLAN_STATE"=70) AND "GGPI"."BUREAU_CODE"='0306')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4321 consistent gets
550 physical reads
0 redo size
3228 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
7 rows processed
