Does any Datablock Change Generates Flashback Logs?

In the “Database Backup and Recovery Basics” book from the 10g Release 2 (10.2) documentation, in the sub-chapter “5.3.7 Performance Tuning for Flashback Database” there is a remark which says:

“Queries do not change data and thus do not contribute to logging activity for Flashback Database.”

Hmm... indeed they do not change actual business data but they can change database blocks in the so called “block cleanout” process and, in such cases, they actually generate redolog information.

My first assumption was that “Flashback database” engine detects on the very low level any change in data blocks, either as a consequence of a DML or as a consequence of an internal data block maintenance process such as the “block cleanout” operation.

So, I've decided to test if the “block cleanout” process does or does not generate flashback logs. The scenario is simple: my test database has a datablock size of 8K so I will create a dummy table so that each of its rows to fit into one oracle datablock. Then I will insert enough records into this table so that, in the end, my transaction to simply give up cleaning out all modified blocks. In the end I will force a full table scan against the sample table in order to perform the cleanout process. During these operations I will closely look to some of my session statistics.

Here we go:

1.first of all, some checking and some rights are needed:

SQL> connect / as sysdba
Connected.
SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> grant select on v_$mystat to alek;

Grant succeeded.

SQL> grant select on v_$statname to alek;

Grant succeeded.

2.now, lets create our test environment:

10:57:46 SQL> connect alek
Enter password:
Connected.
10:57:55 SQL> create table muci (col1 char(2000), col2 char(2000), col3 char(2000));

Table created.

10:58:12 SQL> select name, value from v$mystat natural join v$statname
where regexp_like(name, '(.*flash.*)|(redo size)');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads for flashback new                                          6
redo size                                                              9564
flashback log writes                                                      0

10:58:22 SQL> insert into muci select 'a', 'b', 'c' from all_objects;

40710 rows created.

11:01:21 SQL> commit;

Commit complete.

11:01:37 SQL> select name, value from v$mystat natural join v$statname
where regexp_like(name, '(.*flash.*)|(redo size)');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads for flashback new                                      41526
redo size                                                         263629980
flashback log writes                                                      0

Once again after a minute:

11:01:45 SQL> select name, value from v$mystat natural join v$statname
where regexp_like(name, '(.*flash.*)|(redo size)');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads for flashback new                                      41526
redo size                                                         263629980
flashback log writes                                                      0

There's no change! Go on with the clean out process:

11:05:29 SQL> select count(1) from muci;

 COUNT(1)
----------
    40710

11:06:11 SQL> select name, value from v$mystat natural join v$statname
where regexp_like(name, '(.*flash.*)|(redo size)');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads for flashback new                                      41526
redo size                                                         266561132
flashback log writes                                                      0

Trying once again after some minutes within the same session reveals no changes, therefore we can conclude that flashback logs were not generated even asynchronously after some time.

11:06:21 SQL> select name, value from v$mystat natural join v$statname
where regexp_like(name, '(.*flash.*)|(redo size)');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads for flashback new                                      41526
redo size                                                         266561132
flashback log writes                                                      0

Thats interesting! As it can be seen, the cleanup process has, indeed, generated redolog information which means that it changed some header information from the cleaned out datablocks but no flashback logs were produced.

So, as far as these tests are concerned the above statement from the Oracle official documentation seems to be correct!