对同一条记录先后开了5个会话进行更新,结果发现每次更新前后的日志量产生不一样!
创建表
CREATE TABLE tb_byf AS
SELECT object_id,object_name,object_type,created FROM dba_objects;
找一条记录用来更新
SQL> SELECT * FROM tb_byf WHERE object_id=20;
OBJECT_ID OBJECT_NAME OBJECT_TYPE CREATED
---------- -------------------------------- ------------------- -----------
20 ICOL$ TABLE 2010-4-2 13
会话1:
SQL> select sid from v$mystat where rownum=1;
SID
----------
146
SQL> update tb_byf set created=to_date('2010-4-2 13:19:38','yyyy-mm-dd hh24:mi:ss')--原值2010-4-2 13:18:38
2 where object_id=394;
1 row updated
SQL> SELECT VALUE
2 FROM v$mystat v1,v$statname v2
3 WHERE v1.STATISTIC#=v2.STATISTIC#
4 AND NAME='redo size';
VALUE
----------
1432
SQL> rollback;
Rollback complete
会话2:
SQL> select sid from v$mystat where rownum=1;
SID
----------
25
SQL> update tb_byf set created=to_date('2010-4-2 13:19:38','yyyy-mm-dd hh24:mi:ss')--原值2010-4-2 13:18:38
2 where object_id=394;
1 row updated
SQL> SELECT VALUE
2 FROM v$mystat v1,v$statname v2
3 WHERE v1.STATISTIC#=v2.STATISTIC#
4 AND NAME='redo size';
VALUE
----------
1384
SQL> rollback;
Rollback complete
会话3:
SQL> select sid from v$mystat where rownum=1;
SID
----------
23
SQL> update tb_byf set created=to_date('2010-4-2 13:19:38','yyyy-mm-dd hh24:mi:ss')--原值2010-4-2 13:18:38
2 where object_id=394;
1 row updated
SQL> SELECT VALUE
2 FROM v$mystat v1,v$statname v2
3 WHERE v1.STATISTIC#=v2.STATISTIC#
4 AND NAME='redo size';
VALUE
----------
1388
SQL> rollback;
Rollback complete
会话4:
SQL> select sid from v$mystat where rownum=1;
SID
----------
18
SQL> update tb_byf set created=to_date('2010-4-2 13:19:38','yyyy-mm-dd hh24:mi:ss')--原值2010-4-2 13:18:38
2 where object_id=394;
1 row updated
SQL> SELECT VALUE
2 FROM v$mystat v1,v$statname v2
3 WHERE v1.STATISTIC#=v2.STATISTIC#
4 AND NAME='redo size';
VALUE
----------
1432
SQL> rollback;
Rollback complete
会话5:
SQL> select sid from v$mystat where rownum=1;
SID
----------
20
SQL> update tb_byf set created=to_date('2010-4-2 13:19:38','yyyy-mm-dd hh24:mi:ss')--原值2010-4-2 13:18:38
2 where object_id=394;
1 row updated
SQL> SELECT VALUE
2 FROM v$mystat v1,v$statname v2
3 WHERE v1.STATISTIC#=v2.STATISTIC#
4 AND NAME='redo size';
VALUE
----------
1396
SQL> rollback;
Rollback complete
会话6:
SQL> select sid from v$mystat where rownum=1;
SID
----------
29
SQL> update tb_byf set created=to_date('2010-4-2 13:19:38','yyyy-mm-dd hh24:mi:ss')--原值2010-4-2 13:18:38
2 where object_id=394;
1 row updated
SQL> SELECT VALUE
2 FROM v$mystat v1,v$statname v2
3 WHERE v1.STATISTIC#=v2.STATISTIC#
4 AND NAME='redo size';
VALUE
----------
1432
SQL> rollback;
Rollback complete
会话7:
SQL> select sid from v$mystat where rownum=1;
SID
----------
38
SQL> update tb_byf set created=to_date('2010-4-2 13:19:38','yyyy-mm-dd hh24:mi:ss')--原值2010-4-2 13:18:38
2 where object_id=394;
1 row updated
SQL> SELECT VALUE
2 FROM v$mystat v1,v$statname v2
3 WHERE v1.STATISTIC#=v2.STATISTIC#
4 AND NAME='redo size';
VALUE
----------
1432
SQL> rollback;
Rollback complete
汇总:
执行次序 SESSION.SID 日志量
1 146 1432
2 25 1384
3 23 1388
4 18 1432
5 20 1396
6 29 1432
7 38 1432
发现再做下去趋于稳定在1432!
创建表
CREATE TABLE tb_byf AS
SELECT object_id,object_name,object_type,created FROM dba_objects;
找一条记录用来更新
SQL> SELECT * FROM tb_byf WHERE object_id=20;
OBJECT_ID OBJECT_NAME OBJECT_TYPE CREATED
---------- -------------------------------- ------------------- -----------
20 ICOL$ TABLE 2010-4-2 13
会话1:
SQL> select sid from v$mystat where rownum=1;
SID
----------
146
SQL> update tb_byf set created=to_date('2010-4-2 13:19:38','yyyy-mm-dd hh24:mi:ss')--原值2010-4-2 13:18:38
2 where object_id=394;
1 row updated
SQL> SELECT VALUE
2 FROM v$mystat v1,v$statname v2
3 WHERE v1.STATISTIC#=v2.STATISTIC#
4 AND NAME='redo size';
VALUE
----------
1432
SQL> rollback;
Rollback complete
会话2:
SQL> select sid from v$mystat where rownum=1;
SID
----------
25
SQL> update tb_byf set created=to_date('2010-4-2 13:19:38','yyyy-mm-dd hh24:mi:ss')--原值2010-4-2 13:18:38
2 where object_id=394;
1 row updated
SQL> SELECT VALUE
2 FROM v$mystat v1,v$statname v2
3 WHERE v1.STATISTIC#=v2.STATISTIC#
4 AND NAME='redo size';
VALUE
----------
1384
SQL> rollback;
Rollback complete
会话3:
SQL> select sid from v$mystat where rownum=1;
SID
----------
23
SQL> update tb_byf set created=to_date('2010-4-2 13:19:38','yyyy-mm-dd hh24:mi:ss')--原值2010-4-2 13:18:38
2 where object_id=394;
1 row updated
SQL> SELECT VALUE
2 FROM v$mystat v1,v$statname v2
3 WHERE v1.STATISTIC#=v2.STATISTIC#
4 AND NAME='redo size';
VALUE
----------
1388
SQL> rollback;
Rollback complete
会话4:
SQL> select sid from v$mystat where rownum=1;
SID
----------
18
SQL> update tb_byf set created=to_date('2010-4-2 13:19:38','yyyy-mm-dd hh24:mi:ss')--原值2010-4-2 13:18:38
2 where object_id=394;
1 row updated
SQL> SELECT VALUE
2 FROM v$mystat v1,v$statname v2
3 WHERE v1.STATISTIC#=v2.STATISTIC#
4 AND NAME='redo size';
VALUE
----------
1432
SQL> rollback;
Rollback complete
会话5:
SQL> select sid from v$mystat where rownum=1;
SID
----------
20
SQL> update tb_byf set created=to_date('2010-4-2 13:19:38','yyyy-mm-dd hh24:mi:ss')--原值2010-4-2 13:18:38
2 where object_id=394;
1 row updated
SQL> SELECT VALUE
2 FROM v$mystat v1,v$statname v2
3 WHERE v1.STATISTIC#=v2.STATISTIC#
4 AND NAME='redo size';
VALUE
----------
1396
SQL> rollback;
Rollback complete
会话6:
SQL> select sid from v$mystat where rownum=1;
SID
----------
29
SQL> update tb_byf set created=to_date('2010-4-2 13:19:38','yyyy-mm-dd hh24:mi:ss')--原值2010-4-2 13:18:38
2 where object_id=394;
1 row updated
SQL> SELECT VALUE
2 FROM v$mystat v1,v$statname v2
3 WHERE v1.STATISTIC#=v2.STATISTIC#
4 AND NAME='redo size';
VALUE
----------
1432
SQL> rollback;
Rollback complete
会话7:
SQL> select sid from v$mystat where rownum=1;
SID
----------
38
SQL> update tb_byf set created=to_date('2010-4-2 13:19:38','yyyy-mm-dd hh24:mi:ss')--原值2010-4-2 13:18:38
2 where object_id=394;
1 row updated
SQL> SELECT VALUE
2 FROM v$mystat v1,v$statname v2
3 WHERE v1.STATISTIC#=v2.STATISTIC#
4 AND NAME='redo size';
VALUE
----------
1432
SQL> rollback;
Rollback complete
汇总:
执行次序 SESSION.SID 日志量
1 146 1432
2 25 1384
3 23 1388
4 18 1432
5 20 1396
6 29 1432
7 38 1432
发现再做下去趋于稳定在1432!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/751051/viewspace-731751/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/751051/viewspace-731751/