将not exists更改为外连接

更新时间：2022-10-01 09:37:48

主机CPU一直100%，其中有一条SQL，每秒同时有15进程正在执行，并且性能还不好，要想降低CPU，就得先把这条SQL搞定，估计搞定这条SQL，CPU大概可以降到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

----------------------------------------------------------------------------------------------------------

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表走的全表扫描，整个逻辑读达到了400W，CPU不高才怪

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

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

-------------------------------------------------------------------------------------------------------------

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

------------------------------------------------------------------------------------------------------

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语句已经达到了优化的目标，等待开发上线后的效果。

本文转自7343696 51CTO博客，原文链接：http://blog.51cto.com/luoping/1417398，如需转载请自行联系原作者

上一篇 : ：VUE实现简单的记住密码功能下一篇 : rrdtool 安装

将not exists更改为外连接

相关阅读

推荐文章