redo size =0 ? update ?
From: lfree <aaa_at_163.com>
Date: Fri, 26 Feb 2010 17:57:52 +0800
Message-ID: <hm85v0$dbi$1_at_www.shinco.com>
> sqlplus scott/xxxxx
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
Plan hash value: 267198286
Date: Fri, 26 Feb 2010 17:57:52 +0800
Message-ID: <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
--Received on Fri Feb 26 2010 - 03:57:52 CST
| 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.