Enabling & disabling In-Memory Undo impacts the redo generation in Oracle database

Enabling & disabling In-Memory Undo impacts the redo generation in Oracle database

We all know that Redo is one of the most important component in every Oracle database. While troubleshooting performance related issues we have noticed that sometimes excessive redo generation can cause slowness.

Today I did some experiment on my test database....

I created a very small table TEST_1 with 2 columns X and Y. This table has only one row.  

Scenario -1 (When IMU is disabled) - 

IMU can be enabled or disabled by setting hidden parameter "_in_memory_undo". This parameter value can be TRUE or FALSE. I disabled the IMU for the database.

ALTER SYSTEM SET "_in_memory_undo" = false;

 I performed a simple UPDATE DML and checked how much redo was generated. A simple update on 1 row generated 908 bytes of redo. When I performed another UPDATE statement on same row from same session, the second update generated 756 bytes redo.

After two DMLs, I performed a COMMIT. The COMMIT operation generated 140 bytes of redo. Total REDO generated by two DMLs and one COMMIT operation was 1804 bytes.

Scenario -2 (When IMU is enabled) -

In second scenario, I executed same UPDATE statement to update the same row in same table. But this time, the update statement didn't generate any redo. 
Then I performed another UPDATE statement on same row. But again, it didn't generate any redo. But when I performed a COMMIT, then redo was generated. Before COMMIT operation, my session did not generate any redo.

Note that I did not change the table defnition. Also, I did not use NOLOGGING option. 

In  the second scnenario, I enabled the In Memory Undo.

ALTER SYSTEM SET "_in_memory_undo" = true;

How In-Memory Undo impacts redo generation. We all know that when a buffer is changed, its past image is stored in Undo segments to provide read consistency. It means, a change in buffer also makes a change in undo buffers also. This change in undo also generates redo. Whenever a query requires to access a block in buffer cache and if the block has been modified, it checks the ITL from buffer header and finds the address of related undo segment. This is traditional behavior in Oracle.

But with the launch of In-Memory Undo concept, Oracle performs most of Undo related work in memory only. Most of undo related work is done in IMU nodes. When a block is changed in buffer cache, undo information is stored in IMU nodes and buffer header now contains IMU node information. The read consistency is provided from IMU nodes. Since IMU nodes are not segments, hence any modification in IMU nodes does not generate Redo. When we perform a commit, then consolidated undo information is flushed from multiple IMU nodes to undo segment as a batch operation. The redo generated at COMMIT is lesser than redo generated by same DMLs in traditional behavior. My experiment shows that when IMU was enabled, test generated 1668 bytes of redo. But when IMU was disabled, same test generated 1804 bytes redo.

If the database is configured with RAC configuration, data guard or streams, then IMU will be disabled.

Buffered Read or Direct Path Read? Understand query behavior with the help of "ENQ: KO - FAST OBJECT CHECKPOINT" event.

Today while doing some performance research on wait event "ENQ: KO - FAST OBJECT CHECKPOINT", I experienced that it's not Query Optimizer who decides, whether query will perform Buffered Read or it will perform Direct Path Read operation. Optimizer just decides the access method of operations involved in query execution. For example, Optimizer just decides if query will use FULL TABLE SCAN or INDEX SCAN. But FULL TABLE SCAN will be performed by buffered read operation or direct path read, it is decided at query run time during the execution phase, not in parsing phase.

To demonstrate this, I performed a little experiment. I performed two simple tests.

In the first test case, I forced "ENQ: KO - FAST OBJECT CHECKPOINT" wait event. I updated a single row of a big table, then I performed a full table scan operation and monitored the behavior of SELECT query. In the second test case, I updated 99% rows of a big table, then again performed a full table scan and again I monitored the behavior of SELECT query.

The results were little surprising...

During the first test case, I updated only 1 row of the table TEST_A. When I executed SELECT query,  it performed direct path read because table size is big as compared to buffer cache size. Before direct path read, a wait event ENQ: KO - FAST OBJECT CHECKPOINT was posted. This is because, before reading data blocks from data file directly to PGA,  object level checkpoint was occurred and dirty blocks of the table TEST_A were written from buffer cache to data file. 

In the second test case, I updated 99% rows of the table TEST_A.  During update operation, buffered read operation was performed. But this time, when I performed SELECT query (Full table scan), query did not performed DIRECT PATH READ, but it performed buffered read. This is because, most of table block data (buffers) were already found in buffer cache, so query did not perform DIRECT operation and hence event ENQ: KO - FAST OBJECT CHECKPOINT did not occur.

 Conclusion -

Important to note here is that SQL_ID, HASH VALUE and PLAN HASH VALUE of SELECT query are same in both the cases. So, we can conclude that optimizer has chosen same plan in both cases, but at run time, query performed differently. 

SQL Ordered by Elapsed Time Section of AWR Report Can Mislead your Analysis!!!

Whenever users complains that database is performing slow, most of DBAs immediately generate AWR reports for analysis. SQL Ordered by Elapsed Time is one of the major areas where most of DBAs look when analyzing AWR report.  Most of DBAs just assume that the topmost query in "SQL Ordered by Elapsed Time" section is the slowest query and they start looking for tuning opportunities.

But it is not necessary that Query Elapsed Time is always same what end user experiences. It is possible that Top most query in "SQL Ordered by Elapsed Time" section of AWR Report is NOT the slowest query and your analysis can go in wrong direction. Sometimes, SQL ordered by Elapsed Time section of AWR report can mislead your analysis.

Query Elapsed Time can be different from what end user experiences. To demonstrate this, I executed a simple query 4 times (4 tests). In each test, I increased the elapsed time to 2 times, 3 time and 4 times of actual time taken by the query and Elapsed Time is what is reported in AWR reports.

Test -1  - Elapsed Time of query is same what user experiences.

Test - 2 - Elapsed Time of query is 2 times of what user experiences.


Test - 3 - Elapsed Time of query is 3 times of what user experiences.

Test - 4 - Elapsed Time of query is 4 times of what user experiences.

Finally Interesting part is that, AWR report does not show the actual time taken by the query what user experiences. AWR Report shows Elapsed Time which is 95.2 seconds, not the user experienced time 24.41 seconds.