Re: redo size =0 ? update ?

"lfree" <aaa_at_163.com> a écrit dans le message de news: hm85v0$dbi$1_at_www.shinco.com...
|> sqlplus scott/xxxxx
|
|
| SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 26 17:51:24 2010
|
| Copyright (c) 1982, 2009, Oracle. All rights reserved.
|
|
| Connected to:
| Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
| With the Partitioning, OLAP, Data Mining and Real Application Testing
| options
|
| SQL> select * from v$version ;
|
| BANNER
| ----------------------------------------------------------------------------
| ----
| Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
| PL/SQL Release 11.2.0.1.0 - Production
| CORE 11.2.0.1.0 Production
| TNS for Linux: Version 11.2.0.1.0 - Production
| NLSRTL Version 11.2.0.1.0 - Production
|
|
| SQL> select * from dept ;
|
| DEPTNO DNAME LOC
| ---------- -------------- -------------
| 10 ACCOUNTING NEW YORK
| 20 RESEARCH DALLAS
| 30 SALES CHICAGO
| 40 OPERATIONS BOSTON
|
|
| SQL> update dept set dname= 'ACCOUNTING' where deptno=10 ;
|
| 1 row updated.
|
|
| Execution Plan
| ----------------------------------------------------------
| Plan hash value: 267198286
|
| ----------------------------------------------------------------------------
| --
|| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
||
| ----------------------------------------------------------------------------
| --
|| 0 | UPDATE STATEMENT | | 1 | 12 | 1 (0)| 00:00:01
||
|| 1 | UPDATE | DEPT | | | |
||
||* 2 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 12 | 0 (0)| 00:00:01
||
| ----------------------------------------------------------------------------
| --
|
| Predicate Information (identified by operation id):
| ---------------------------------------------------
|
| 2 - access("DEPTNO"=10)
|
|
| Statistics
| ----------------------------------------------------------
| 1 recursive calls
| 3 db block gets
| 1 consistent gets
| 0 physical reads
| 0 redo size
| 830 bytes sent via SQL*Net to client
| 804 bytes received via SQL*Net from client
| 3 SQL*Net roundtrips to/from client
| 1 sorts (memory)
| 0 sorts (disk)
| 1 rows processed
|
|
| SQL> update dept set dname= 'ACCOUNTING' where deptno=10 ;
|
| 1 row updated.
|
|
| Execution Plan
| ----------------------------------------------------------
| Plan hash value: 267198286
|
| ----------------------------------------------------------------------------
| --
|| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
||
| ----------------------------------------------------------------------------
| --
|| 0 | UPDATE STATEMENT | | 1 | 12 | 1 (0)| 00:00:01
||
|| 1 | UPDATE | DEPT | | | |
||
||* 2 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 12 | 0 (0)| 00:00:01
||
| ----------------------------------------------------------------------------
| --
|
| Predicate Information (identified by operation id):
| ---------------------------------------------------
|
| 2 - access("DEPTNO"=10)
|
|
| Statistics
| ----------------------------------------------------------
| 0 recursive calls
| 1 db block gets
| 1 consistent gets
| 0 physical reads
| 308 redo size
| 832 bytes sent via SQL*Net to client
| 804 bytes received via SQL*Net from client
| 3 SQL*Net roundtrips to/from client
| 1 sorts (memory)
| 0 sorts (disk)
| 1 rows processed
|
|
| SQL> update dept set dname= 'ACCOUNTING' where deptno=10 ;
|
| 1 row updated.
|
|
| Execution Plan
| ----------------------------------------------------------
| Plan hash value: 267198286
|
| ----------------------------------------------------------------------------
| --
|| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
||
| ----------------------------------------------------------------------------
| --
|| 0 | UPDATE STATEMENT | | 1 | 12 | 1 (0)| 00:00:01
||
|| 1 | UPDATE | DEPT | | | |
||
||* 2 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 12 | 0 (0)| 00:00:01
||
| ----------------------------------------------------------------------------
| --
|
| Predicate Information (identified by operation id):
| ---------------------------------------------------
|
| 2 - access("DEPTNO"=10)
|
|
| Statistics
| ----------------------------------------------------------
| 0 recursive calls
| 1 db block gets
| 1 consistent gets
| 0 physical reads
| 308 redo size
| 832 bytes sent via SQL*Net to client
| 804 bytes received via SQL*Net from client
| 3 SQL*Net roundtrips to/from client
| 1 sorts (memory)
| 0 sorts (disk)
| 1 rows processed
|
| why the first is redo size = 0 , the second redo size= 308?
|
| if update content is same , the first redo size=0 . other redo size<>0 .
|
| if update content is same and commit , loop , reod size= 0 .
|
| why?
|
| thank advanced.
|

This is due to "in memory undo" mechanism.

See:
http://www.oracle.com/education/america_eblasts/fy09/q1/craig_shallahamer_all_about_oracle_s_in_memory_undo.pdf

Regards
Michel Received on Fri Feb 26 2010 - 09:42:49 CST