阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

Oracle exists子查询出现rownum引起性能问题的优化

175次阅读
没有评论

共计 6199 个字符,预计需要花费 16 分钟才能阅读完成。

生产环境中有一支 SQL 消耗 cpu 资源很大,逻辑读为 299361,相关问题描述:

Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID “7ptu1y0d95s2r” was executed 3209 times and
had an average elapsed time of 20 seconds.
原 SQL:
update WWH
set WWH.STATUS = (case
when (select count(1)
from WWD
where

…………………….

and exists (select 1
from WWD
where 1 = 1
and WWH.WH = WWD.WH
and WWH.WAVE = WWD.WAVE
and WWD.SO in (‘2349212263452’)
and rownum = 1)

set line 1000
set pagesize 1000
set timing on
set autotrace traceonly
执行 SQL………

查看执行计划如下:

Execution Plan
———————————————————-
Plan hash value: 3044987130

————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————————–
| 0 | UPDATE STATEMENT | | 1 | 45 | 191K (1)| 00:38:18 |
| 1 | UPDATE | WWH | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL | WWH | 98070 | 4309K| 449 (1)| 00:00:06 |
|* 4 | COUNT STOPKEY | | | | | |
|* 5 | INDEX UNIQUE SCAN | UNQ_WWD | 1 | 40 | 2 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 24 | | |
|* 7 | COUNT STOPKEY | | | | | |
|* 8 | FILTER | | | | | |
|* 9 | INDEX RANGE SCAN | UNQ_WWD| 1 | 24 | 3 (0)| 00:00:01 |
| 10 | SORT AGGREGATE | | 1 | 27 | | |
|* 11 | TABLE ACCESS BY INDEX ROWID| WWD | 1 | 27 | 4 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | UNQ_WWD| 1 | | 3 (0)| 00:00:01 |
————————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

2 – filter(EXISTS (SELECT 0 FROM “WWD” WHERE ROWNUM=1 AND
“WWD”.”WH”=:B1 AND “WWD”.”SO”=’2349212263452′ AND “WWD”.”WAVE”=:B2))
-……………………………………..

………………………………………

Statistics
———————————————————-
1 recursive calls
0 db block gets
263080 consistent gets
0 physical reads
72 redo size
829 bytes sent via SQL*Net to client
3583 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
上面显示表没走索引,逻辑读为 263080,查看表的字段 (WAVE,WH) 存在索引,我们再建一个选择性更好的索引:
增加索引:
create index IND_MODIFY_TIME_IX on WWH(Modify_Time,WAVE,WH) tablespace wsx;

再查看执行计划依然没有走索引,逻辑读同样是 263080 大。

强制加入索引后,虽然执行计划中有显示走了全索引扫描,且驱动表由 WWH 表变成了 WWD 表,但逻辑读变得更大为 299361,走索引前后性能都差的最主要原因是执行计划中看到有
“filter(EXISTS………..” 这行,表示子查询没有展开。
SQL> update /*+index(WWH IND_MODIFY_TIME_IX) */ WWH …………….

Elapsed: 00:00:01.11

Execution Plan
———————————————————-
Plan hash value: 389457693

—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————-
| 0 | UPDATE STATEMENT | | 1 | 45 | 196K (1)| 00:39:16 |
| 1 | UPDATE | WWH | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | WWD | 4903 | 215K| 5302 (1)| 00:01:04 |
|* 3 | INDEX FULL SCAN | IND_MODIFY_TIME_IX | 4996 | | 602 (1)| 00:00:08 |
|* 4 | COUNT STOPKEY | | | | | |
|* 5 | INDEX UNIQUE SCAN | UNQ_WWD | 1 | 40 | 2 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 24 | | |
|* 7 | COUNT STOPKEY | | | | | |
|* 8 | FILTER | | | | | |
|* 9 | INDEX RANGE SCAN | UNQ_WWD | 1 | 24 | 3 (0)| 00:00:01 |
| 10 | SORT AGGREGATE | | 1 | 27 | | |
|* 11 | TABLE ACCESS BY INDEX ROWID| WWD | 1 | 27 | 4 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | UNQ_WWD | 1 | | 3 (0)| 00:00:01 |
—————————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – filter(EXISTS (SELECT 0 FROM “WWD” WHERE ROWNUM=1 AND
“WWD”.”WH”=:B1 AND “WWD”.”SO”=’2349212263452′ AND “WWD”.”WAVE”=:B2))
-……………………………………..

………………………………………

Statistics
———————————————————-
1 recursive calls
0 db block gets
299361 consistent gets
2 physical reads
0 redo size
840 bytes sent via SQL*Net to client
3619 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed

手工加入 /*+unnest*/ 让子查询展开,但从执行计划查看,依然没有展开。

exists 无法展开子查询,我们将 SQL 等价改成 in 子查询,如下:

SQL 略

 

Execution Plan
———————————————————-
Plan hash value: 1922347980

——————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————————–
| 0 | UPDATE STATEMENT | | 1 | 81 | 616 (1)| 00:00:08 |
| 1 | UPDATE | WWH | | | | |
| 2 | NESTED LOOPS | | 1 | 81 | 607 (1)| 00:00:08 |
| 3 | NESTED LOOPS | | 1 | 81 | 607 (1)| 00:00:08 |
| 4 | VIEW | VW_NSO_1 | 1 | 36 | 4 (0)| 00:00:01 |
| 5 | SORT UNIQUE | | 1 | 40 | | |
|* 6 | COUNT STOPKEY | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| WWD | 1 | 40 | 4 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | UNQ_WWD | 1 | | 3 (0)| 00:00:01 |
|* 9 | INDEX FULL SCAN | IND_MODIFY_TIME_IX | 1 | | 601 (1)| 00:00:08 |
|* 10 | TABLE ACCESS BY INDEX ROWID | WWH | 1 | 45 | 602 (1)| 00:00:08 |
| 11 | SORT AGGREGATE | | 1 | 24 | | |
|* 12 | COUNT STOPKEY | | | | | |
|* 13 | FILTER | | | | | |
|* 14 | INDEX RANGE SCAN | UNQ_WWD | 1 | 24 | 3 (0)| 00:00:01 |
| 15 | SORT AGGREGATE | | 1 | 27 | | |
|* 16 | TABLE ACCESS BY INDEX ROWID | WWD | 1 | 27 | 4 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | UNQ_WWD | 1 | | 3 (0)| 00:00:01 |
——————————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

此部分看不到 exists 和 filter 信息了,说明有展开

Statistics
———————————————————-
1 recursive calls
0 db block gets
3 consistent gets
2 physical reads
0 redo size
842 bytes sent via SQL*Net to client
3546 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
0 rows processed
发现上面已走索引了,且逻辑读降为 3。

以上是在测试环境中模拟测试,因数据与正式环境有差异,我们再看下正式环境中查询操作性能比较:

select count(*) from WWH
where exists (select 1
from WWD
where 1 = 1
and WWH.WH= WWD.WH
and WWH.WAVE= WWD.WAVE
and WWD.SO in (‘SO201612345’)
and rownum = 1
)
and WWH.STATUS <> ’11’
and WWH.STATUS <> ’22’

— 逻辑读 625043,执行时间为 1 秒

改成 in 子查询后:

SELECT COUNT (*)
FROM  WWH
WHERE (wwh.WH, wwh.WAVE) IN
(SELECT WWD.WH, WWD.WAVE
FROM  WWD
WHERE 1 = 1
AND WWD.SO_NO IN (‘SO201612345’)
AND ROWNUM = 1
)
AND WWH.STATUS <> ’11’

and WWH.STATUS <> ’22’

— 逻辑读变为 8,执行时间 58 豪秒。

也可直接去掉 rownum=1。
总结:当 exists 子查询中出现有 rownum 时,子查询无法展开,可以改成 in 子查询或表联接,或根据实际逻辑需求,去掉 rownum。

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址:http://www.linuxidc.com/Linux/2016-12/138202.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计6199字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中