一次性能优化将filter转换

超、凢脫俗 2022-08-05 02:45 141阅读 0赞

有一条SQL性能有问题，在执行计划中发现filter，遇到它要小心了，类似于nestloop，我以前的blog对它有研究[**探索执行计划中filter的原理**][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

[filter]: http://blog.csdn.net/stevendbaguo/article/details/14455877