当前位置: 首页 > 调优 > 正文

将not exists更改为外连接

主机CPU一直100%,其中有一条SQL,每秒同时有15进程正在执行,并且性能还不好,要想降低CPU,就得先把这条SQL搞定,估计搞定这条SQLCPU大概可以降到70%以下。

 

欢迎大家加入ORACLE超级群:17115662 免费解决各种ORACLE问题,以后BLOG将迁移到http://www.htz.pw

 

原始SQL的执行性能如下:

SQL> SELECT BILLFLOW_ID, PACKAGE_ID, FILE_CNT, BILLING_CYCLE_ID

  2    FROM (select * from B_FILE_PACKAGE ORDER BY CREATED_DATE) a

  3   where not exists (select *

  4            from B_PACKAGE_STATE_TRANS b

  5           where b.package_id = a.package_id

  6             and b.process_id = 11081)

  7     and A.STATE = ‘RDY’

  8     AND BILLFLOW_ID in (6, 25)

  9     and rownum < 1000;

 

 

Execution Plan

———————————————————-

Plan hash value: 2380269418

 

———————————————————————————————————-

| Id  | Operation             | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

———————————————————————————————————-

|   0 | SELECT STATEMENT      |                          |   999 | 57942 |       |  5752   (1)| 00:01:10 |

|*  1 |  COUNT STOPKEY        |                          |       |       |       |            |          |

|   2 |   NESTED LOOPS ANTI   |                          |  1000 | 58000 |       |  5752   (1)| 00:01:10 |

|   3 |    VIEW               |                          |  5666 |   254K|       |    82   (2)| 00:00:01 |

|   4 |     SORT ORDER BY     |                          |  1304K|    41M|    70M| 18767   (2)| 00:03:46 |

|*  5 |      TABLE ACCESS FULL| B_FILE_PACKAGE           |  1304K|    41M|       |  7086   (3)| 00:01:26 |

|*  6 |    INDEX UNIQUE SCAN  | PK_B_PACKAGE_STATE_TRANS |  1113K|    12M|       |     1   (0)| 00:00:01 |

———————————————————————————————————-

 

Predicate Information (identified by operation id):

—————————————————

 

   1 – filter(ROWNUM<1000)

   5 – filter((“B_FILE_PACKAGE”.”BILLFLOW_ID”=6 OR “B_FILE_PACKAGE”.”BILLFLOW_ID”=25) AND

              “B_FILE_PACKAGE”.”STATE”=’RDY’)

   6 – access(“B”.”PACKAGE_ID”=”A”.”PACKAGE_ID” AND “B”.”PROCESS_ID”=11081)

 

 

Statistics

———————————————————-

          0  recursive calls

          0  db block gets

    3905407  consistent gets

          0  physical reads

          0  redo size

        991  bytes sent via SQL*Net to client

        492  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

         12  rows processed

这里可以看到B_FILE_PACKAGE表走的全表扫描,整个逻辑读达到了400WCPU不高才怪

 

下面我们创建一个组合索引,并且手动指定表的连接方式,可以看到性能提高了很多,逻辑读下降到2W,但是觉得还是有点偏高,因为类型的SQL语句,每S20个进程同时在执行。

SQL> SELECT BILLFLOW_ID, PACKAGE_ID, FILE_CNT, BILLING_CYCLE_ID

  2    FROM (select /*+ index(c) */

  3           *

  4            from system.B_FILE_PACKAGE c

  5           ORDER BY CREATED_DATE) a

  6   where not exists (select /*+ use_hash(b)  swap_join_inputs(b) */

  7           *

  8            from system.B_PACKAGE_STATE_TRANS b

  9           where b.package_id = a.package_id

 10             and b.process_id = 11081)

 11     and A.STATE = ‘RDY’

 12     AND BILLFLOW_ID in (6, 25)

 13     and rownum < 1000;

 

no rows selected

 

 

Elapsed: 00:00:07.66

 

Execution Plan

———————————————————-

Plan hash value: 3575369339

 

————————————————————————————————————-

| Id  | Operation              | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

————————————————————————————————————-

|   0 | SELECT STATEMENT       |                            |     1 |   130 |       | 53727   (1)| 00:10:45 |

|*  1 |  COUNT STOPKEY         |                            |       |       |       |            |          |

|*  2 |   HASH JOIN RIGHT ANTI |                            |     1 |   130 |    60M| 53727   (1)| 00:10:45 |

|*  3 |    INDEX FAST FULL SCAN| B_PACKAGE_STATE_PACKAGE_ID |  1662K|    41M|       |  3131   (2)| 00:00:38 |

|   4 |    VIEW                |                            |  2072K|   102M|       | 41285   (1)| 00:08:16 |

|   5 |     SORT ORDER BY      |                            |  2072K|   169M|   215M| 41285   (1)| 00:08:16 |

|   6 |      INLIST ITERATOR   |                            |       |       |       |            |          |

|*  7 |       INDEX RANGE SCAN | B_FILE_PACK_ALL            |   141 |       |       |     4   (0)| 00:00:01 |

————————————————————————————————————-

 

Predicate Information (identified by operation id):

—————————————————

 

   1 – filter(ROWNUM<1000)

   2 – access(“B”.”PACKAGE_ID”=”A”.”PACKAGE_ID”)

   3 – filter(“B”.”PROCESS_ID”=11081)

   7 – access((“C”.”BILLFLOW_ID”=6 OR “C”.”BILLFLOW_ID”=25) AND “C”.”STATE”=’RDY’)

 

Note

—–

   – dynamic sampling used for this statement

 

 

Statistics

———————————————————-

          0  recursive calls

          0  db block gets

      24503  consistent gets

          0  physical reads

          0  redo size

        536  bytes sent via SQL*Net to client

        481  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          0  rows processed

下面降not exists更改成外连接的方式,其实这里也可以不用更改为外连接的方式,也可以在上面的SQL中直接使用use_nl的方式来实现。

SQL> select *

       from (SELECT a.BILLFLOW_ID, a.PACKAGE_ID, a.FILE_CNT, a.BILLING_CYCLE_ID

               FROM system.B_FILE_PACKAGE a, system.B_PACKAGE_STATE_TRANS b

              where b.package_id(+) = a.package_id

                and b.process_id = 11081

                and A.STATE = ‘RDY’

                AND a.BILLFLOW_ID in (6, 25)

                and b.package_id is null

              order by a.created_date)

      where rownum < 1000;

 

no rows selected

 

Elapsed: 00:00:00.11

 

Execution Plan

———————————————————-

Plan hash value: 3347545174

 

——————————————————————————————————

| Id  | Operation               | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |

——————————————————————————————————

|   0 | SELECT STATEMENT        |                            |     1 |    52 |     6  (17)| 00:00:01 |

|*  1 |  COUNT STOPKEY          |                            |       |       |            |          |

|   2 |   VIEW                  |                            |     1 |    52 |     6  (17)| 00:00:01 |

|*  3 |    SORT ORDER BY STOPKEY|                            |     1 |    43 |     6  (17)| 00:00:01 |

|   4 |     NESTED LOOPS        |                            |     1 |    43 |     5   (0)| 00:00:01 |

|*  5 |      INDEX RANGE SCAN   | B_PACKAGE_STATE_PACKAGE_ID |     1 |    12 |     2   (0)| 00:00:01 |

|   6 |      INLIST ITERATOR    |                            |       |       |            |          |

|*  7 |       INDEX RANGE SCAN  | B_FILE_PACK_ALL            |     1 |    31 |     3   (0)| 00:00:01 |

——————————————————————————————————

 

Predicate Information (identified by operation id):

—————————————————

 

   1 – filter(ROWNUM<1000)

   3 – filter(ROWNUM<1000)

   5 – access(“B”.”PACKAGE_ID” IS NULL AND “B”.”PROCESS_ID”=11081)

       filter(“B”.”PROCESS_ID”=11081)

   7 – access((“A”.”BILLFLOW_ID”=6 OR “A”.”BILLFLOW_ID”=25) AND “A”.”STATE”=’RDY’ AND

              “B”.”PACKAGE_ID”=”A”.”PACKAGE_ID”)

 

 

Statistics

———————————————————-

          0  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

        536  bytes sent via SQL*Net to client

        481  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          0  rows processed

SQL语句已经达到了优化的目标,等待开发上线后的效果。

本文固定链接: http://www.htz.pw/2014/05/27/%e5%b0%86not-exists%e6%9b%b4%e6%94%b9%e4%b8%ba%e5%a4%96%e8%bf%9e%e6%8e%a5.html | 认真就输

该日志由 huangtingzhong 于2014年05月27日发表在 调优 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 将not exists更改为外连接 | 认真就输