temporary undo in 12c

This feature seems a no-brainer once you’re on 12c. After all, why would you want your global temporary tables to be hammering away at your redo logs.  With that in mind, my initial tinkering with the feature had me getting ready for a “blog rant” because it did not seem to work.  Let’s see how you might end up unimpressed. 

Here’s the standard usage of undo (as per 11.2 and below).

 

SQL> create global temporary table T
  2  ( x char(1000));

Table created.

SQL> insert into T
  2  select 'a'
  3  from dual
  4  connect by level <= 50000;

50000 rows created.

SQL> set autotrace traceonly stat
SQL> delete from T;

50000 rows deleted.

Statistics
----------------------------------------------------------
         55  recursive calls
      71980  db block gets
       7288  consistent gets
          0  physical reads
   60562540  redo size
        859  bytes sent via SQL*Net to client
        822  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      50000  rows processed
      

So you can see that the delete’s (which are probably the expensive of operations when it comes to undo, and hence redo for that undo) chewed up 60 megabytes of redo.

I then thought I’d try the new feature, and simply edited my script to set temp_undo_enabled before my delete statement:

 

SQL> create global temporary table T
  2  ( x char(1000));

Table created.

SQL> insert into T
  2  select 'a'
  3  from dual
  4  connect by level <= 50000;

50000 rows created.

SQL> set autotrace traceonly stat
SQL> alter session set temp_undo_enabled=true;

Session altered.

SQL> delete from T;

50000 rows deleted.


Statistics
----------------------------------------------------------
         65  recursive calls
      72100  db block gets
       7283  consistent gets
          0  physical reads
   60566000  redo size
        859  bytes sent via SQL*Net to client
        822  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      50000  rows processed

Well, that was an underwhelming result.  However,  there is nothing wrong with the feature…just my usage of it.  This is actually documented in the reference manual.  I just had neglected to read it carefully enough Smile

“Once the value of the parameter is set, it cannot be changed for the lifetime of the session. If the session has temporary objects using temporary undo, the parameter cannot be disabled for the session. Similarly, if the session already has temporary objects using regular undo, setting this parameter will have no effect.”

So let’s try again, this time setting temp_undo_enabled as soon as we commence the session

SQL> alter session set temp_undo_enabled=true;

Session altered.

SQL>
SQL>
SQL> create global temporary table T
  2  ( x char(1000));

Table created.

SQL>
SQL> insert into T
  2  select 'a'
  3  from dual
  4  connect by level <= 50000;

50000 rows created.

SQL>
SQL> set autotrace traceonly stat
SQL>
SQL> delete from T;

50000 rows deleted.


Statistics
----------------------------------------------------------
         64  recursive calls
      71923  db block gets
       7242  consistent gets
          0  physical reads
          0  redo size
        858  bytes sent via SQL*Net to client
        822  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      50000  rows processed

So if you’re planning on using this feature,  consider setting it either system-wide, or with a logon trigger for appropriate sessions.

(And read the manuals more carefully than I did …. )