exp and redologs

[clinton]

Does Oracle exports generate redologs?

[dba1]

no.

[jmodic]

Generaly not, but in some circumstances it can - just like any table scans do sometimes.

[Axr2]

Originally posted by jmodic
Generaly not, but in some circumstances it can - just like any table scans do sometimes.

Can you elaborate? What circumstances cause exp and FTS queries to generate redo?

[dba1]

I'm confused. How can 'exp' generate redo records?

[jmodic]

It's called "deleyed block cleanout". When you mass-load a table (not record by record) Oracle might not bother "cleaning" table blocks that are not in buffer cache and have been written to disk long before you commit your transaction. So the changes to datablocks have been committed, but some bits in block headers might still mark that block as being "dirty", although it is not. The first time the query visits such a block, it checks its SCN and finds out that those changes have allready been commited, so it cleans the corresponding bits in block header to mark it "clean". And this operation generates redo entries!

You can find more detailed description about this mechanism here. Below is a short demonstration of this:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> REM 
SQL> REM First we'll create a table and mass-load it
SQL> REM 
SQL> 
SQL> CREATE TABLE test1 AS SELECT * FROM ALL_OBJECTS;

Table created.

SQL> INSERT INTO test1 SELECT * FROM test1;

25613 rows created.

SQL> /

51226 rows created.

SQL> /

102452 rows created.

SQL> COMMIT;

Commit complete.

SQL> REM 
SQL> REM Let's see the curent size of redo for my current session
SQL> REM 
SQL> COLUMN NAME format A15
SQL> 
SQL> SELECT sn.NAME, ms.VALUE
  2  FROM v$statname sn, v$mystat ms
  3  WHERE sn.statistic# = ms.statistic#
  4  AND sn.NAME = 'redo size';

NAME                 VALUE
--------------- ----------
redo size         19465836

SQL> REM 
SQL> REM Now let's perform a full table scan
SQL> REM 
SQL> 
SQL> SELECT COUNT(*) FROM test1;

  COUNT(*)
----------
    204904

SQL> REM 
SQL> REM That was an ordinary select, so it should not generate
SQL> REM an redo log, right? Let's see:
SQL> REM 
SQL> 
SQL> SELECT sn.NAME, ms.VALUE
  2  FROM v$statname sn, v$mystat ms
  3  WHERE sn.statistic# = ms.statistic#
  4  AND sn.NAME = 'redo size';

NAME                 VALUE
--------------- ----------
redo size         19538256

SQL> REM 
SQL> REM As wee see, the value of "redo size" increased, meaning that 
SQL> REM an ordinar query *DID* generate redo. Now that the delayed 
SQL> REM block cleanout has allready taken place, the subsequent table
SQL> REM scans won't generate any redo any more: 
SQL> 
SQL> SELECT COUNT(*) FROM test1;

  COUNT(*)
----------
    204904

SQL> SELECT sn.NAME, ms.VALUE
  2  FROM v$statname sn, v$mystat ms
  3  WHERE sn.statistic# = ms.statistic#
  4  AND sn.NAME = 'redo size';

NAME                 VALUE
--------------- ----------
redo size         19538256

SQL>

[Axr2]

Awesome. Thanks for sharing that. Always knew that the concept of delayed block cleanout, but never realized the implication - until I saw your demo.

[Nighthawki]

If I remember correctly Oracle will not clean out blocks if 20% or more are already on the disk.
Also important to ******** that the query is not the cause for the redo... it is only the mean.
The cause for the redo is the DML.

[DaPi]

Would you therefore want to force FTS on all tables in a TS before making it read-only?

[Nighthawki]

I'm not sure but I think when you put TS in read only it will clean out auto and checkpoint.