AristaDBA’s Oracle Blog….

Okay, if you think that what the heck, this guy didn’t know this much even, well, its true. I didn’t know or to save myself, I would say, I did know what Select For Updat does , like it locks the rows and so on, but didn’t relate to the redo part. So when I came to know about it reading this thread(thanks to Mark) today I tested it out, it appears to be  true. So before I would forget about it, I thought to store it here. If you already knew, refresh your memories, if not , keep it in memory .

A Select statement is merely meant to select the data from the table, that’s it, plain and simple. It doesn’t do anything else. It doesn’t generate any undo and redo in normal conditions but for a delayed block cleanout, it may generate redo as well. There are no locks which are acquired by it , there is nothing which gets changed. Yes it may look for the Snapshot Blocks if there are DMLs happening on the data which it is looking for but that’s it. Other than all this, it behaves properly .

Select for update statement, is a cousin of Select only but it is not that simple as like its sibling. With For Update added , Oracle has to make sure that the candidate rows must be locked. And this means, for all the rows being effected, the lock byte of them would get locked , including the segment also whose row we are going to hit. This means, Select For Update is actually not just a select statement  but its a DML which actually would start a transaction, lock the rows, generate redo for changing their lock records and also would require a rollback explicitly given to get those locks released. Have a look,
<code>
SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production
SQL> create table testredo as select * from all_objects;
Table created.
SQL> set autot trace stat
SQL> select * from testredo;
49714 rows selected.
Statistics
———————————————————-
        288  recursive calls
          0  db block gets
       4041  consistent gets
        683  physical reads
          0  redo size
    5337330  bytes sent via SQL*Net to client
      36839  bytes received via SQL*Net from client
       3316  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49714  rows processed
SQL> /
49714 rows selected.
Statistics
———————————————————-
          0  recursive calls
          0  db block gets
       3956  consistent gets
          0  physical reads
          0  redo size
    5337330  bytes sent via SQL*Net to client
      36839  bytes received via SQL*Net from client
       3316  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49714  rows processed
</code>
There was no requirement for running the same select twice, I just ran it to ensure me that nothing fishy is going on .

So there is no redo with the normal select. Let’s try out For Update now,

<code>
SQL> select * from testredo for update;
49714 rows selected.
Statistics
———————————————————-
        771  recursive calls
      50738  db block gets
       4862  consistent gets
          1  physical reads
   10369188  redo size
    4444384  bytes sent via SQL*Net to client
      36839  bytes received via SQL*Net from client
       3316  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
      49714  rows processed
WHOOOAAA! There is a lot of redo. Let’s see if there is some transaction record that we have got,
SQL> select * from V$locked_object;
    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID
———- ———- ———- ———- ———-
ORACLE_USERNAME                OS_USER_NAME                   PROCESS
—————————— —————————— ————
LOCKED_MODE
———–
         9          8        364      52533        159
AMAN                           oracle                         4989
          3
SQL> select xidusn,xidslot,xidsqn from V$transaction;
    XIDUSN    XIDSLOT     XIDSQN
———- ———- ———-
         9          8        364
SQL>            
</code>
So there is indeed a transaction which got recorded and there is surely a lock that’s there on the object also. Let’s finish it off,
<code>
SQL>roll;
Rollback Complete
SQL> select * from V$locked_object;
no rows selected
SQL> select * from V$transaction;
no rows selected
SQL> set autot trace stat
SQL> select * from testredo;
49714 rows selected.
Statistics
———————————————————-
          0  recursive calls
          0  db block gets
       3956  consistent gets
          1  physical reads
          0  redo size
    5337330  bytes sent via SQL*Net to client
      36839  bytes received via SQL*Net from client
       3316  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49714  rows processed
SQL>               
</code>
So we are back to from where we started.

A small but good learning , at least for me, thanks to Mark and Charles for their explanation over the the original thread.

Conclusion, think more deeply about something. At times, just knowing about the concept is not enough . And yes, just for the record, select for update is a  DML statement, not just a normal select , that’s the actual conclusion right !