 | |
redo size =0 ? update ?
comp.databases.oracle.server comp.databases.oracle.server
 |
| | Thread Tools | Display Modes |
#1
 
| |||||||
| |||||||
|
redo size =0 ? update ? -
02-26-2010
, 03:57 AM
|
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
----------------------------------------------------------------------------
--
Quote:
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ---------------------------------------------------------------------------- |
Quote:
|
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
----------------------------------------------------------------------------
--
Quote:
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ---------------------------------------------------------------------------- |
Quote:
|
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
----------------------------------------------------------------------------
--
Quote:
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ---------------------------------------------------------------------------- |
Quote:
|
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.
#2
| |||
| |||
|
#3
| |||
| |||
|
|
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. |
See:
http://www.oracle.com/education/amer...ory_und o.pdf
Regards
Michel
#4
| |||
| |||
|
|
This is due to "in memory undo" mechanism. See: http://www.oracle.com/education/amer...ory_und o.pdf |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.