12C Temporary Undo : Under the hood

You have probably already heard of and used Temporary Undo (TEMP_UNDO_ENABLED) if not more info here and here .In this post i will try to show whats going on under the hood.So let begin :

Test case :

Oracle 12.1.0.2.0

CREATE GLOBAL TEMPORARY TABLE test_undo_temp (
a  NUMBER,
b  NUMBER
) ON COMMIT DELETE ROWS;

create index on test_undo_temp_idx1 on test_undo_temp(a,b);

1- TEST case 1 : TEMP_UNDO_ENABLED set to false

Insert two lines and observe :

insert into test_undo_temp select 1111111111111111111,1111111111111111111 from dual;
insert into test_undo_temp select 1111111111111111111,1111111111111111111 from dual;

take look a the stats

Capture

Capture

2- TEST case 2 : TEMP_UNDO_ENABLED set to true

Retry the same test and observe

Capture

Capture

3- Observation :

So there is indeed a serious reduction in the redo generated from 1056 to 272.But what is this 272 bytes generated ? a quick redo dump will reveal the truth :

Capture

As we see the redo dump contain two change vector :

  • First change vector       :   Allocate transaction slot in undo header transaction table
  • Second change vector  :   Contain undo for transaction start and as it’is the first undo record it contain also a copy of the transaction control information

Also If for example i insert multiple data to cause a left block split it will generate permanent undo for the recursive transaction.

  insert into test_undo_temp select 1111111111111111111,1111111111111111111 from dual connect by level < 250;

Stats show increase in redo generation :

Capture

Capture

Extract from redo log dump (Recursive transaction for leaft node splits) :

Capture

So modification in a temporary table still generate small amount of permanent undo (in the undo tablespace) for some operations.

Other observation, the session using the temporary undo created a new segment in the temporary tablespace “UNDEFINED” and allocated one extent of 128 blocks (I am using uniform extent size of 1MB). So it is obvious that the session is allocating more temporary undo blocks than it needs (In test case 1 it’s allocating 2 undo block and test case 2 it’s allocating 129 undo blocks : 1 undo blocks from the undo tablespace and 128 from the temporary tablespace). Beside every new session will allocate a new “UNDEFINED” segments (Jonathan Lewis post :https://jonathanlewis.wordpress.com/2014/03/14/12c-temporary/) so watch out for space allocation.We may need to create a specific temporary tablespace for undo management and adjust his extent size.

That’s it 😀

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s