About Me

I am Deepak Bhatnagar. An Oracle DBA with 11 years of experience in Oracle database technologies e.g Troubleshooting database performance issues, Troubleshooting ORA-4031 (unable to allocate x bytes of shared memory) errors, RAC, Dataguard, ASM, Goldengate etc..

Wednesday, October 19, 2016

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.

3 comments:

  1. Hello Deepak,

    Thanks for sharing the knowledge via this blog.

    It 's a very useful and informational blog but it also strikes a doubt in my mind.

    The block change is tracked in IMU pool where IMU nodes are present but as per Oracle normal behaviour there should be some mapping in UNDO as well to maintain the consistency so just want to know how that is maintained.

    Regards,
    Divanshu

    ReplyDelete
    Replies
    1. Hello Divanshu,

      When a block is modified, its Undo information is stored in IMU nodes. Since IMU nodes are not segments, hence modifications in IMU nodes does not generate redo. Once the change is commited or UNDO information is flushed from IMU Pool, then undo comes to the picture.

      Delete
  2. First Lyrics is the most popular lyrics website worldwide, There is lots of Lyrics of Bollywood songs, Punjabi songs and Hollywood songs.

    With a best-in-class design and first-to-market social media sharing features, First Lyrics is the best lyrics site

    ReplyDelete