当前位置: 首页 > 调优 > 正文

测试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.

3update更新表

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表上面的索引扫描了1365Krange scan,每次是将a.object_id的值传送给htz1object_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了。

如果更新的表行数越多的时候,效果就会越明显

本文固定链接: http://www.htz.pw/2014/06/04/%e6%b5%8b%e8%af%95update%e8%af%ad%e5%8f%a5%e5%85%b3%e8%81%94%e8%a1%a8%e6%89%ab%e6%8f%8f%e7%9a%84%e6%ac%a1%e6%95%b0.html | 认真就输

该日志由 huangtingzhong 于2014年06月04日发表在 调优 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 测试update语句关联表扫描的次数 | 认真就输
关键字: , ,