-
exp and redologs
Does Oracle exports generate redologs?
-
-
Generaly not, but in some circumstances it can - just like any table scans do sometimes.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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?
-
I'm confused. How can 'exp' generate redo records?
-
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:
Code:
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>
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Awesome. Thanks for sharing that. Always knew that the concept of delayed block cleanout, but never realized the implication - until I saw your demo.
-
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.
-
Would you therefore want to force FTS on all tables in a TS before making it read-only?
-
I'm not sure but I think when you put TS in read only it will clean out auto and checkpoint.
Oracle Certified Associate.
DBA & Programming.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|