Saturday, 27 August 2011

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




7 comments:

Saurabh Priyadarshi said...

Very well explained with suitable examples .
Covered in detail every aspect of the topic and truly answered the "WHY" part of the question which is generally missed.
Great work !!! and keep it up.

Vineet Arya said...

Thanks Much Guys!!! Thanks for appreciations.

khushbu said...

Keep it up Vineet. Very nice step towards your Orcale Journey. Really appreciable.

K.Vidhyasagar said...

Fine explanation with very good example. Thanks Vineet.

Vineet Arya said...

Thanks K. Vidhyasagar...

Guilherme Mesquita said...

Thank you for the walkthrough and excellent example!

Vineet Arya said...

Many Thanks Guilherme

Post a Comment