Oracle笔记：测试update语句关联表扫描的次数
我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
Oracle笔记：测试update语句关联表扫描的次数
下面是测试一下update语句执行时，与更新表关联的表被扫描的次数，也是为什么我们常常将update语句，更改为merge into或者是pl/sql的方式来实现
欢迎大家加入ORACLE超级群：17115662 免费解决各种ORACLE问题,以后BLOG将迁移到http://www.htz.pw
1，数据库版本
www.htz.pw > select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
PL/SQL Release 11.2.0.4.0 – Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 – Production
NLSRTL Version 11.2.0.4.0 – Production
2，创建测试表
www.htz.pw > insert into scott.htz select * from scott.htz;
690192 rows created.
www.htz.pw > commit;
Commit complete.
www.htz.pw > select count(*) from scott.htz;
COUNT(*)
———-
1380384
www.htz.pw > update scott.htz1 set owner=’HTZ’;
86275 rows updated.
www.htz.pw > commit;
Commit complete.
www.htz.pw > create index scott.ind_htz2_object_id on scott.htz1(object_id,object_type,owner);
Index created.
3，update更新表
www.htz.pw > update /*+ gather_plan_statistics */scott.htz a set (a.object_type,a.owner) = (select object_type,owner from scott.htz1 b where a.object_id=b.object_id);
1380384 rows updated.
Elapsed: 00:00:29.40
Execution Plan
———————————————————-
Plan hash value: 932534721
—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————–
| 0 | UPDATE STATEMENT | | 1442K| 56M| 11M (25)| 38:28:25 |
| 1 | UPDATE | HTZ | | | | |
| 2 | TABLE ACCESS FULL| HTZ | 1442K| 56M| 5416 (1)| 00:01:05 |
|* 3 | INDEX RANGE SCAN | IND_HTZ2_OBJECT_ID | 886 | 36326 | 3 (0)| 00:00:01 |
—————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
3 – access("B"."OBJECT_ID"=:B1)
Note
—–
– dynamic sampling used for this statement (level=2)
Statistics
———————————————————-
270 recursive calls
1419046 db block gets
1631433 consistent gets
4781 physical reads
382985292 redo size
846 bytes sent via SQL*Net to client
913 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1380384 rows processed
这里看到逻辑读是相当的高
下面查看一下htz1被扫描的次数
www.htz.pw > @find_sql
www.htz.pw > set echo off
Enter value for sql_text: gather_plan_statistics
Enter value for sql_id:
SQL_ID CHILD HASH_VALUE PLAN_HASH EXECS ETIME AVG_ETIME USERNAME
—————— —— ———- ———- ———- ————- ————- ————-
SQLTEXT
———————————————————————————————————————————————————————————————-
219bgfbrqx1ck 0 4016997778 932534721 1 36.51 36.51 SYS
update /*+ gather_plan_statistics */scott.htz a set (a.object_type,a.owner) = (select object_type,owner from scott.htz1 b where a.object_id=b.object_id)
www.htz.pw > @plan_by_last.sql
Enter value for sqlid: 219bgfbrqx1ck
PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————————————————————–
SQL_ID 219bgfbrqx1ck, child number 0
————————————-
update /*+ gather_plan_statistics */scott.htz a set
(a.object_type,a.owner) = (select object_type,owner from scott.htz1 b
where a.object_id=b.object_id)
Plan hash value: 932534721
————————————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
————————————————————————————————————
| 0 | UPDATE STATEMENT | | 1 | | 0 |00:00:36.51 | 3050K| 19226 |
| 1 | UPDATE | HTZ | 1 | | 0 |00:00:36.51 | 3050K| 19226 |
| 2 | TABLE ACCESS FULL| HTZ | 1 | 1442K| 1380K|00:00:20.62 | 19674 | 18876 |
|* 3 | INDEX RANGE SCAN | IND_HTZ2_OBJECT_ID | 1365K| 886 | 1365K|00:00:07.04 | 1611K| 344 |
————————————————————————————————————
这里看到了HTZ1表上面的索引扫描了1365K次range scan，每次是将a.object_id的值传送给htz1的object_id
Predicate Information (identified by operation id):
—————————————————
3 – access("B"."OBJECT_ID"=:B1)
Note
—–
– dynamic sampling used for this statement (level=2)
26 rows selected.
4，更改为merge into语句
www.htz.pw > MERGE INTO /*+ gather_plan_statistics */
2 scott.htz a
3 USING (SELECT b.object_type, b.owner, b.object_id
4 FROM scott.htz1 b) c
5 ON (a.object_id = c.object_id)
6 WHEN MATCHED
7 THEN
8 UPDATE SET a.object_type = c.object_type, a.owner = c.owner;
1380384 rows merged.
Elapsed: 00:00:32.20
Execution Plan
———————————————————-
Plan hash value: 3787432690
——————————————————————————————————
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
——————————————————————————————————
| 0 | MERGE STATEMENT | | 1272K| 67M| | 21499 (1)| 00:04:18 |
| 1 | MERGE | HTZ | | | | | |
| 2 | VIEW | | | | | | |
|* 3 | HASH JOIN | | 1272K| 315M| 4584K| 21499 (1)| 00:04:18 |
| 4 | INDEX FAST FULL SCAN| IND_HTZ2_OBJECT_ID | 88560 | 3545K| | 99 (0)| 00:00:02 |
| 5 | TABLE ACCESS FULL | HTZ | 1442K| 301M| | 5423 (1)| 00:01:06 |
——————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
3 – access("A"."OBJECT_ID"="B"."OBJECT_ID")
Note
—–
– dynamic sampling used for this statement (level=2)
Statistics
———————————————————-
309 recursive calls
1419444 db block gets
20518 consistent gets
6175 physical reads
382998348 redo size
846 bytes sent via SQL*Net to client
1034 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1380384 rows processed
SQL_ID 153haxsb6d8p1, child number 1
————————————-
MERGE INTO /*+ gather_plan_statistics */ scott.htz a
USING (SELECT b.object_type, b.owner, b.object_id FROM
scott.htz1 b) c ON (a.object_id = c.object_id) WHEN MATCHED
THEN UPDATE SET a.object_type = c.object_type, a.owner = c.owner
Plan hash value: 3787432690
——————————————————————————————————————————————–
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
——————————————————————————————————————————————–
| 0 | MERGE STATEMENT | | 1 | | 0 |00:00:43.86 | 1439K| 5757 | | | |
| 1 | MERGE | HTZ | 1 | | 0 |00:00:43.86 | 1439K| 5757 | | | |
| 2 | VIEW | | 1 | | 1380K|00:00:11.45 | 20025 | 5754 | | | |
|* 3 | HASH JOIN | | 1 | 1272K| 1380K|00:00:08.89 | 20025 | 5754 | 7628K| 3091K| 7690K (0)|
| 4 | INDEX FAST FULL SCAN| IND_HTZ2_OBJECT_ID | 1 | 88560 | 86275 |00:00:00.15 | 351 | 162 | | | |
| 5 | TABLE ACCESS FULL | HTZ | 1 | 1442K| 1380K|00:00:01.95 | 19674 | 5592 | | | |
——————————————————————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
3 – access("A"."OBJECT_ID"="B"."OBJECT_ID")
Note
—–
– dynamic sampling used for this statement (level=2)
这里能明显的看到HTZ1表上面的索引，只扫描了一次，逻辑读已经下降到20518了。
如果更新的表行数越多的时候，效果就会越明显
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
提供ORACLE技术支持(系统优化，故障处理，安装升级，数据恢复等） TEL:18081072613，微信、QQ同手机号。
浙公网安备 33010602011771号