sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA > Oracle性能优化 >

【案例】Oracle性能优化之将not exists更改为外连接

时间:2016-10-22 22:21   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 将not exists更改为外连接,运维DBA反映当前Oracle数据库环境中有一条SQL语句占用CPU较高,通过SQL语句的执行计划来优化该语句降低CPU占用。
主机CPU一直100%,其中有一条SQL,每秒同时有15进程正在执行,并且性能还不好,要想降低CPU,就得先把这条SQL搞定Oracle oracleplus.net,估计搞定这条SQL,CPU大概可以降到70%以下。

1.原始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

2.将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 | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle性能优化之将not exists更改为外连接

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1030.html

Oracle研究中心

关键词:

Oracle SQL语句性能优化

优化SQL语句之将not exists更改为外连接