Tricky that SELECT statement generates REDO

After gaining theoretical knowledge from the Oracle documents or from Oracle university training every DBA will be under presumption that only DML operations in the database generates REDO segments. But practically it is not completely true. On few occasions you can identify that SELECT statements generates REDO segments in the instance. Next question you might get is whether REDO generated by SELECT statement are pushed into REDO log file and used for recovery operations or not. This blog will explain why SELECT statement generates REDO and eventually let you know if this REDO is used elsewhere.

Examples:

1. SELECT statement after inserting – Huge data
2. SELECT statement after inserting – Small data

Considerations:

• Database Buffer cache is of fixed size 300MB – Not ideal though.
• Explain plan statistics are used to demonstrate the concept.

1. SELECT statement after inserting – Huge data

I will be referring to the image below to explain the behavior of SELECT statement just after heavy INSERT operation.

Explanation for each numbered stages in the image:

1. A user performs a HUGE insert operation of 90MB data on a table (30% of the total buffer cache size).
   • These data blocks headers in the buffer cache will have transaction lock and its details.

SQL> create table scott.table1 as select * from dba_objects;

Table created.

SQL> insert into scott.table1 select * from dba_objects;

74638 rows created.

SQL>/

74638 rows created.

SQL>/

74638 rows created.

2. To end the transaction, User issues COMMIT.

SQL> commit;

Commit complete.

3. As a response to COMMIT, these data blocks are pushed to datafiles and are physically saved in the disk.
   • This operation is done by DBWR background process.
4. Data blocks occupying only 10% of buffer cache which are part of this transaction are cleaned out.
   • Buffer clean out is the operation of removing transaction locks from the headers of data blocks in the cache.
5. Rest of the 20% of data blocks in the buffer cache which are part of this transaction still holds details of INACTIVE transaction locks.
   • This is referred to COMMIT DELAY some times. To let the commit perform faster database doesn’t clean up all the headers of data blocks in the buffer cache if it occupies more than 10 to 15% of buffer cache size.
6. Consider User immediately fires a SELECT statement after the COMMIT.

SQL> select * from scott.TABLE1 where owner=’SCOTT’;

162 rows selected.

7. As data blocks required by this SELECT statement are already in the buffer cache, instance will not perform any I/O operation.
   • Instance while accessing first 10% of blocks don’t find any difficulty, but to access other 20% of blocks it identifies inactive transaction lock in the headers and cleans them.
8. Now this cleaning the inactive transaction lock from the headers of buffer cache results in GENERATING REDO.

Statistics

———————————————————-
9  recursive calls2  db block gets

16848  consistent gets

1063  physical reads

497420  redo size

12079  bytes sent via SQL*Net to client

529  bytes received via SQL*Net from client

12  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

162  rows processed

9. Re-run the same SELECT query and you don’t find REDO generation as BLOCKS are now cleaned.

SQL> select * from scott.TABLE1 where owner=’SCOTT’;
162 rows selected.

Statistics

———————————————————-

4  recursive calls

2  db block gets

9938  consistent gets

0  physical reads

0  redo size

12079  bytes sent via SQL*Net to client

529  bytes received via SQL*Net from client

12  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

162  rows processed

 2. SELECT statement after inserting – Small data

I will be referring to the image below to explain the behavior of SELECT statement just after small INSERT operation.

Explanation for each numbered stages in the image:

1. A user performs a SMALL insert operation of 15MB data on a table (5% of the total buffer cache size).
   • These data blocks headers in the buffer cache will have transaction lock and its details.

SQL> create table scott.table2 as select * from dba_tables where owner=’SCOTT’;
Table created.

SQL> insert into scott.table2 select * from dba_tables where owner=’SCOTT’;
15 rows created.

2. To end the transaction, User issues COMMIT.
3. As a response to COMMIT, these data blocks are pushed to datafiles and are physically saved in the disk.
   • This operation is done by DBWR background process.
4. All the data blocks in buffer cache which are part of this transaction are cleaned out.
   • Buffer clean out is the operation of removing transaction locks from the headers of data blocks in the cache.
5. There are NO data blocks in the buffer cache which are part of this transaction holding details of INACTIVE transaction locks.
   • There is NO COMMIT DELAY now.
6. Consider User immediately fires a SELECT statement after the COMMIT.

SQL> select * from scott.table2 where table_name like ‘T%’;
13 rows selected.

7. As data blocks required by this SELECT statement are already in the buffer cache, instance will not perform any I/O operation.
   • There is NO action of cleaning up headers of these data blocks.
8. NO REDO generation for this SELECT statement.

Statistics

———————————————————-

5  recursive calls0  db block gets

16  consistent gets

1  physical reads

0  redo size

4644  bytes sent via SQL*Net to client

419  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

How GOOD is this REDO?

• The REDO generated in the above scenario doesn’t have any details on DATA modifications.
• It writes the details in REDO buffer cache but it turns out to be DIRTY buffer.
• These details are also pushed into REDO LOG FILE.
• But this REDO is not at all useful for database RECOVERY as these are the modifications to blocks in buffer rather than modifications to blocks in datafile.

Conclusion:

• It is true that SELECT statements generate REDO on few occasions.
• OLAP systems will mostly face these situations. After heavy data modifications, it is recommended to run important reports so that end user doesn’t face any performance issue because of BUFFER CLEAN operation.
• REDO generated by SELECT statement is not useful for any RECOVERY purposes.
• Small amount of data modifications per transaction will avoid COMMIT DELAY.