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
2- TEST case 2 : TEMP_UNDO_ENABLED set to true
Retry the same test and observe
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 :
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 :
Extract from redo log dump (Recursive transaction for leaft node splits) :
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 😀
Hatem Mahmoud's blog 