下面是测试一下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了。 如果更新的表行数越多的时候，效果就会越明显 |
测试update语句关联表扫描的次数：等您坐沙发呢！