Database Version:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0
Create a test table:
SQL> create table a as select * from all_objects;
Table created.
SQL> set autotrace on statistics;
Insert data (hint append ):
SQL> insert/* + append */into a select * from all_objects;
9891 rows created.
Statistics
----------------------------------------------------------
302 recursive cballs
137 db block gets
6040 consistent gets
0 physical reads
1055332 redo size
627 bytes sent via SQL * Net to client
558 bytes encoded ed via SQL * Net from client
3 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9891 rows processed
SQL> commit;
Commit complete.
First Data Query:
SQL> select count (*) from;
COUNT (*)
----------
19782
Statistics
----------------------------------------------------------
0 recursive cballs
1 db block gets
255 consistent gets
248 physical reads
168 redo size ---------------------------------> ??? Generate redo ???
395 bytes sent via SQL * Net to client
507 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Second query:
SQL> select count (*) from;
COUNT (*)
----------
19782
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
252 consistent gets
1 physical reads
0 redo size
395 bytes sent via SQL * Net to client
507 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
========================================================== ==========
As shown above, why does a redo occur during query? What is the redo generated?
========================================================== ==========
----
Cancel hint append to insert data. The first query will not generate redo
SQL> insert into a select * from;
19782 rows created.
Statistics
----------------------------------------------------------
112 recursive cballs
21100 db block gets
699 consistent gets
0 physical reads
7149196 redo size
642 bytes sent via SQL * Net to client
534 bytes encoded ed via SQL * Net from client
3 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
19782 rows processed
SQL>
SQL>
SQL> select count (*) from;
COUNT (*)
----------
39564
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
502 consistent gets
0 physical reads
0 redo size
395 bytes sent via SQL * Net to client
507 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
---------------------------------------------------
After the truncate operation is performed on the table, redo is also displayed in the first query.
SQL> truncate table;
Table truncated.
SQL>
SQL> select count (*) from;
COUNT (*)
----------
0
Statistics
----------------------------------------------------------
1 recursive cballs
1 db block gets
6 consistent gets
0 physical reads
96 redo size
392 bytes sent via SQL * Net to client
507 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
---------------- Simply put, the blocks in oracle all have the identifier of an active transaction. If a transaction commit, because some blocks have been written back to datafile before commit, or if the number of Blocks Affected by the transaction is too large, the transaction table information in the undo segment header will be cleared only during commi. The transaction mark on the data block will not be cleared; otherwise, the cost is too high. When reading these blocks, you need to clear these transaction flags, that is, to clear delayed blocks.
------------------------- The select redo log is generated only when the append quotation is used. This indicates that the data block has been written before submission, and the direct insertion mode is further illustrated, that is, no cache is required, data Block writing and rollback are fast. The first condition for clearing delayed blocks is that the data has been written before submission. --------------------------- ==================================
The following is a test:
==========================================
SQL> insert into
2 select * from;
129103 rows created.
Statistics
----------------------------------------------------------
489 recursive cballs
137442 db block gets
4058 consistent gets
1516 physical reads
46645744 redo size
643 bytes sent via SQL * Net to client
534 bytes encoded ed via SQL * Net from client
3 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
129103 rows processed
SQL> alter system checkpoint;
System altered.
SQL>
SQL> select count (*) from;
COUNT (*)
----------
258206
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
3241 consistent gets
2790 physical reads
0 redo size
395 bytes sent via SQL * Net to client
507 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> commit;
Commit complete.
SQL> select count (*) from;
COUNT (*)
----------
258206
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
4857 consistent gets
2796 physical reads
116484 redo size ------------------------------------> the first query redo is generated (the delay block is cleared)
395 bytes sent via SQL * Net to client
507 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count (*) from;
COUNT (*)
----------
258206
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
3241 consistent gets
2746 physical reads
0 redo size
395 bytes sent via SQL * Net to client
507 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
------------------- To put it bluntly, the information on the data block has not been cleared before. select helps it clean up. Since the select statement has performed operations on the data block, it is necessary to write redo statements.
