Does Select Statement Generates Redo???

Surprising to many people, but “In some cases select statement generates redo too?? “

Background:

I was working on one scenario regarding Oracle Statistics (Tracing statistics like sorts, consistent gets etc). So I created one table

Please Note for testing purpose:  Db Block Size is 8k

                                                Buffer Cache Size is 4M

SQL> create table test(x char(2000),y char(2000),z char(2000));

Every row will be 6k so it means only one row in one block.

 

Then for checking the statistics I fired auto-trace command as stated below

SQL> set autotrace traceonly statistics

Then I inserted the 10000 rows.

SQL> insert into test select 'x','y','z' from all_objects where rownum<=10000;

10000 rows created.

Statistics

----------------------------------------------------------

       2274  recursive calls

      46503  db block gets

      12033  consistent gets

          7  physical reads

   66050648  redo size

        821  bytes sent via SQL*Net to client

        822  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

      10000  rows processed

Then I finally commit the data.

SQL> commit;

Commit complete.

SQL> select * from test; 

10000 rows selected.

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

      13669  consistent gets

          0  physical reads

     263680  redo size--------------------à“263680  redo size” 

 60514555  bytes sent via SQL*Net to client

       7846  bytes received via SQL*Net from client

        668  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      10000  rows processed

So what I see here is only statistics which looks ok to me. But after double checking above result, I found “263680 redo size”. This is suprising

 What went wrong as I am the only user in Db.

So fired the select statement again

SQL> select * from test ;

10000 rows selected.

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

      10008  consistent gets

          0  physical reads

          0  redo size

   60514555  bytes sent via SQL*Net to client

       7846  bytes received via SQL*Net from client

        668  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      10000  rows processed

Redo size is again 0(0 redo size)

 I took a step ahead to find” Why Select Statement generates redo??” I reached one of the internals of Oracle” Block Cleanout”

What is Block Cleanout and Why select statement generated redo: Whenever one fire any dml then oracle automatically acquire lock. In oracle data locks are actually the attributes of the data and they are stored in block header. The transaction will simply go to the data and locks it( if it is not locked). When a row is locked by a transaction, that information is placed in the block header where the row is located. When another transaction wishes to acquire the lock on the same row, it has to travel to the block containing the row anyway, and upon reaching there, it can easily identify that the row is locked, from the block header.

So what portion of the block header contains information on locking? It is a simple data structure called Interested Transaction List (ITL), a linked list data structure that maintains information on transaction address and row-id. What basically it contains is “TRANSACTION ID” which is unique to transactions and represents roll back segment number, slot number, sequence number.

 


When the transaction is released ID is left behind. So whenever another session comes to lock the same block which was released earlier checks this ID and see the status whether the transaction is active or not. If not then lock it else wait.

In order to reach our subject we need to understand one of the step of COMMIT?? 

One of the steps of COMMIT is to revisit our blocks if they are still in SGA, if they are accessible (no one modifying them), and then clean out the transaction information known as COMMIT CLEANOUT. Database Commit can clean out the blocks so that “select” which is just for reading will have not to clean it out.

***In Oracle---commit is related with commit list which is 20 blocks long and Oracle will allocate as many as of these list till it needs up to a point. If the sum of the blocks we modify exceeds 10% of the buffer cache size. *** For e.g. ------if blocks in cache is 5000 so oracle will maintain list of 500 blocks. Upon commit, oracle will process 500 blocks for clean out( remove transaction and lock info) and if the blocks are more than this then commit will delayed it and skips it(doesn’t clean them).

In this case when any other session comes say a ‘select’ statement on the same blocks then it will check the block header for all the blocks and tries to clean out the transaction and lock info if present: and if transaction information is still there (but not active) then it will generate redo information (because block become dirty as select statement is cleaning info from the blocks)

So from above its clear select statement generated redo’s (sometimes).

Do remember if blocks are perfectly inside 10% of buffer cache (as commit cleanout says) then there will be no delayed cleanout i.e. no redo generation

See below

SQL> set autotrace traceonly statistics

SQL> insert into test select 'x','y','z' from all_objects where rownum<=500;

500 rows created.

Statistics

----------------------------------------------------------

       2155  recursive calls

       4863  db block gets

       1755  consistent gets

          5  physical reads

    6615428  redo size

        830  bytes sent via SQL*Net to client

        821  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

       500  rows processed

SQL> commit;

Commit complete.

SQL> select * from test;

500 rows selected.

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

       1005  consistent gets

          0  physical reads

          0  redo size----------   (No redo generated. Commit already clean the information)

    6051955  bytes sent via SQL*Net to client

       1246  bytes received via SQL*Net from client

         68  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       500  rows processed