由于博客中有大量代码，通过页面浏览效果更佳。
Oracle性能优化：将not exists更改为外连接
主机CPU一直100%，其中有一条SQL，每秒同时有15进程正在执行，并且性能还不好，要想降低CPU，就得先把这条SQL搞定，估计搞定这条SQL，CPU大概可以降到70%以下。原始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表走的全表扫描，整个逻辑读达到了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
————————————————————————————————————-
| 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语句已经达到了优化的目标，等待后续上线后的效果。
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
