构造cr块产生redo??

chaicsd14

1. SQL> select * from v$version;
   
2. 
   
3. BANNER
   
4. ----------------------------------------------------------------
   
5. Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
   
6. PL/SQL Release 10.2.0.4.0 - Production
   
7. CORE    10.2.0.4.0      Production
   
8. TNS for Linux: Version 10.2.0.4.0 - Production
   
9. NLSRTL Version 10.2.0.4.0 - Production
   

复制代码

session a:

1. SQL> select * from v$mystat where rownum=1;
   
2. 
   
3.        SID STATISTIC#      VALUE
   
4. ---------- ---------- ----------
   
5.        159          0          1
   
6. 
   
7. SQL> set autot on stat
   
8. SQL> select * from m;
   
9. 
   
10.         ID NAME
    
11. ---------- ----------
    
12.          2 cyx
    
13.          1 cyx
    
14. 
    
15. 
    
16. Statistics
    
17. ----------------------------------------------------------
    
18.           0  recursive calls
    
19.           0  db block gets
    
20.          24  consistent gets
    
21.           0  physical reads
    
22.           0  redo size
    
23.         630  bytes sent via SQL*Net to client
    
24.         492  bytes received via SQL*Net from client
    
25.           2  SQL*Net roundtrips to/from client
    
26.           0  sorts (memory)
    
27.           0  sorts (disk)
    
28.           2  rows processed
    

复制代码

session b:

1. SQL> insert into m values(3,'cyx');
   
2. 
   
3. 1 row created.
   

复制代码

没有提交

session a:

1. SQL> select * from m;
   
2. 
   
3.         ID NAME
   
4. ---------- ----------
   
5.          2 cyx
   
6.          1 cyx
   
7. 
   
8. 
   
9. Statistics
   
10. ----------------------------------------------------------
    
11.           0  recursive calls
    
12.           0  db block gets
    
13.          26  consistent gets
    
14.           0  physical reads
    
15.         [color=Red] 64  redo size[/color]
    
16.         630  bytes sent via SQL*Net to client
    
17.         492  bytes received via SQL*Net from client
    
18.           2  SQL*Net roundtrips to/from client
    
19.           0  sorts (memory)
    
20.           0  sorts (disk)
    
21.           2  rows processed
    
22. 
    
23. SQL> /
    
24. 
    
25.         ID NAME
    
26. ---------- ----------
    
27.          2 cyx
    
28.          1 cyx
    
29. 
    
30. 
    
31. Statistics
    
32. ----------------------------------------------------------
    
33.           0  recursive calls
    
34.           0  db block gets
    
35.          26  consistent gets
    
36.           0  physical reads
    
37.          [color=Red]64  redo size[/color]
    
38.         630  bytes sent via SQL*Net to client
    
39.         492  bytes received via SQL*Net from client
    
40.           2  SQL*Net roundtrips to/from client
    
41.           0  sorts (memory)
    
42.           0  sorts (disk)
    
43.           2  rows processed
    

复制代码

执行了两遍,都产生了redo,构造cr块产生redo??请高手们帮忙解释下

Maclean Liu(刘相兵

INSTANCE_NAME
----------------
MACDBN

SQL>
SQL>
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL>
SQL> create table maclean(t1 int ,t2 varchar2(20));

Table created.

SQL> insert into maclean values (1,'ABC');

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from maclean;

        T1 T2
---------- --------------------
         1 ABC
         1 ABC

SQL> /

        T1 T2
---------- --------------------
         1 ABC
         1 ABC

SQL> insert into maclean values(3,'ABC');

1 row created.





session B:





SQL> exec dbms_stats.gather_table_stats(user,'MACLEAN');

PL/SQL procedure successfully completed.



SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2012825

SQL> select * from maclean;

        T1 T2
---------- --------------------
         1 ABC
         1 ABC


Execution Plan
----------------------------------------------------------
Plan hash value: 2568761675

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     2 |    14 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MACLEAN |     2 |    14 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
        108  redo size
        654  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
                  
SQL> commit;

Commit complete.

SQL> alter system dump logfile '+DATA/macdb/onlinelog/group_1.269.816597463';


System altered.

SQL> SQL>
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/s01/diag/rdbms/macdbn/MACDBN/trace/MACDBN_ora_7310.trc                  
                  

                  
                  2012825==>1EB699  大于scn 1EB699的是相关redo

                  
SQL> select name from obj$ where obj#=77452;

NAME
------------------------------
MACLEAN


                  
                  OP:4.1
                  
REDO RECORD - Thread:1 RBA: 0x00003b.00004c01.0010 LEN: 0x006c VLD: 0x05
SCN: 0x0000.001eb69c SUBSCN:  1 06/05/2013 08:47:34
(LWN RBA: 0x00003b.00004c01.0010 LEN: 0001 NST: 0001 SCN: 0x0000.001eb69c)
CHANGE #1 TYP:0 CLS:1 AFN:1 DBA:0x00415fd1 OBJ:77452 SCN:0x0000.001e9cf7 SEQ:1 OP:4.1 ENC:0 RBL:0
Block cleanout record, scn:  0x0000.001eb69c ver: 0x01 opt: 0x01, entries follow...

REDO RECORD - Thread:1 RBA: 0x00003b.00004c05.0010 LEN: 0x006c VLD: 0x05
SCN: 0x0000.001eb6a1 SUBSCN:  1 06/05/2013 08:47:39
(LWN RBA: 0x00003b.00004c05.0010 LEN: 0001 NST: 0001 SCN: 0x0000.001eb6a1)
CHANGE #1 TYP:0 CLS:1 AFN:1 DBA:0x00415fd1 OBJ:77452 SCN:0x0000.001eb69c SEQ:1 OP:4.1 ENC:0 RBL:0
Block cleanout record, scn:  0x0000.001eb6a1 ver: 0x01 opt: 0x01, entries follow...                  


OP:4.1 是 Transaction Block-Block Cleanout


Layer 4 : Transaction Block

Opcode 1 : Block Cleanout
Opcode 2 : Physical Cleanout
Opcode 3 : Single Array Change
Opcode 4 : Multiple Changes to an Array
Opcode 5 : Format Block

chaicsd14

谢谢刘大，精辟