玉面飞龙的BLOG

Smart Oracle

December 1st, 2007 | Categories: Boring | Tags:

在作记录更新的时候，有些由数据库开发工具生成的update语句，会去更新所有的字段，而不管这个字段是否有实际的内容改动。这样带来额外的over head.

时常见到如下类似的SQL,根据primary key去做更新，且primary key还包括在被updated的column中。

update emp set ID=:1,name=:2,birthdate=:3,description=:4 where ID=:5;

ID列自然是PK，且更新的时候ID的数值不会改变。（如果主键有更新的需求，则一般是表逻辑设计问题）

那么update语句中，有无这个主键的更新，会有什么性能影响哪？

先建立测试环境

SQL> drop table test;
Table dropped.

SQL> create table test as select * from dba_objects;
Table created.

SQL> delete from test where owner is null or object_id is null or object_type is null;
21 rows deleted.

SQL> alter table test add primary key(owner,object_id,object_type);
Table altered.

SQL>
SQL> analyze table test compute statistics;
Table analyzed.

SQL> select count(*) from test;
COUNT(*)
----------
12126

SQL> select /*+ full(a) */ count(*) from test a;
COUNT(*)
----------
12126

无primary key更新的测试

SQL> select name,value from v$mystat m,v$statname s where m.STATISTIC#=s.STATISTIC# and name='redo size';
SQL> alter session set sql_trace=true;
Session altered.

SQL> begin
2 for i in (select owner,object_type,object_id from dba_objects) loop
3 update test set SUBOBJECT_NAME='S' ,TEMPORARY='S',GENERATED='S',
4 CREATED=sysdate where owner=i.owner and object_id=i.object_id and object_type=i.object_type;
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> alter session set sql_trace=false;
Session altered.
SQL> select name,value from v$mystat m,v$statname s where m.STATISTIC#=s.STATISTIC# and name='redo size';

包括primary key更新的测试，但实际primary key的数值不变

SQL> select name,value from v$mystat m,v$statname s where m.STATISTIC#=s.STATISTIC# and name='redo size';
SQL> alter session set sql_trace=true;
Session altered.

SQL> begin
2 for i in (select owner,object_type,object_id from dba_objects) loop
3 update test set owner=i.owner,object_id=i.object_id,object_type=i.object_type,
4 SUBOBJECT_NAME='S' ,TEMPORARY='S',GENERATED='S',
5 CREATED=sysdate where owner=i.owner and object_id=i.object_id and object_type=i.object_type;
6 end loop;
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.

SQL> alter session set sql_trace=false;
SQL> select name,value from v$mystat m,v$statname s where m.STATISTIC#=s.STATISTIC# and name='redo size';

主要观察两个更新语句在logical reads, cpu, redo size上的不同。

******************* PK gets updated in update statement***********************

UPDATE TEST SET OWNER=:B6 ,OBJECT_ID=:B5 ,OBJECT_TYPE=:B4 , SUBOBJECT_NAME=
'S' ,TEMPORARY='S',GENERATED='S', CREATED=SYSDATE
WHERE
OWNER=:B3 AND OBJECT_ID=:B2 AND OBJECT_TYPE=:B1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 12148 2.02 2.06 0 24254 12617 12126
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12149 2.02 2.06 0 24254 12617 12126

Rows Row Source Operation
------- ---------------------------------------------------
12126 UPDATE TEST (cr=24254 pr=0 pw=0 time=1327587 us) redo size=4461084
12126 INDEX UNIQUE SCAN SYS_C0044327 (cr=24254 pr=0 pw=0 time=206653 us)(object id 444952)

*****************************good update statement*********************************

UPDATE TEST SET SUBOBJECT_NAME='S' ,TEMPORARY='S',GENERATED='S', CREATED=
SYSDATE
WHERE
OWNER=:B3 AND OBJECT_ID=:B2 AND OBJECT_TYPE=:B1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 12148 1.75 1.84 0 24254 12519 12126
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12149 1.75 1.84 0 24254 12519 12126

Rows Row Source Operation
------- ---------------------------------------------------
12126 UPDATE TEST (cr=24254 pr=0 pw=0 time=1158086 us) redo size=3704140
12126 INDEX UNIQUE SCAN SYS_C0044325 (cr=24254 pr=0 pw=0 time=209671 us) (object id 444948)

从结果上看，logical reads基本一样。redo size 和 cpu 使用，略有不同。这说明，当PK索引更新数值一样的时候，oracle不会去做PK索引的实际更新,有够Smart。但仍然要在redo size和cpu上多些overhead，对于关键的应用，仍然要有良好的编码习惯。