Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Smith.

Asked: December 17, 2018 - 1:17 pm UTC

Last updated: April 19, 2021 - 5:35 am UTC

Version: 12c

Viewed 1000+ times

You Asked

what is the use of "TEMP_UNDO_ENABLED" in oracle database. when our user also use global temporary table.

When it's necessary to set TEMP_UNDO_ENABLED = TRUE in which circumtance?

If I enable this parameter to true so it's decrese the performance of database ?

and we said...

Setting this to true means that the database writes undo for temporary tables to the temporary tablespace. Instead of the undo tablespace.

The database records undo changes in the redo log. But not temp writes.

So if you use temporary tables, setting this to true reduces your redo => smaller archive logs.

You can see the effect in the example below.

The first run, with temp_undo_enables = false, the delete uses ~2mb of redo. But with it enabled, it generates none!

SQL> alter session set temp_undo_enabled=false;

Session altered.

SQL>
SQL> drop table t cascade constraints purge;

Table dropped.

SQL>
SQL> create global temporary table t (
  2    c1 int, c2 date, c3 varchar2(10)
  3  );

Table created.

SQL>
SQL> set autotrace on stat
SQL> insert into t
  2    select level, sysdate+level, lpad ( 'x', 10, 'x' )
  3    from   dual
  4    connect by level <= 10000;

10000 rows created.


Statistics
----------------------------------------------------------
         14  recursive calls
        239  db block gets
         51  consistent gets
          0  physical reads
      31840  redo size
        778  bytes sent via SQL*Net to client
       1443  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL>
SQL> delete t;

10000 rows deleted.


Statistics
----------------------------------------------------------
         11  recursive calls
      10384  db block gets
         91  consistent gets
          0  physical reads
    2098764  redo size
        779  bytes sent via SQL*Net to client
       1343  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL>
SQL>
SQL> conn chris/chris@//localhost/orcl
Connected.
SQL>
SQL> alter session set temp_undo_enabled=true;

Session altered.

SQL>
SQL> drop table t cascade constraints purge;

Table dropped.

SQL>
SQL> create global temporary table t (
  2    c1 int, c2 date, c3 varchar2(10)
  3  );

Table created.

SQL>
SQL> set autotrace on stat
SQL> insert into t
  2    select level, sysdate+level, lpad ( 'x', 10, 'x' )
  3    from   dual
  4    connect by level <= 10000;

10000 rows created.


Statistics
----------------------------------------------------------
         14  recursive calls
        240  db block gets
         51  consistent gets
          0  physical reads
        280  redo size
        774  bytes sent via SQL*Net to client
       1443  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL>
SQL> delete t;

10000 rows deleted.


Statistics
----------------------------------------------------------
          7  recursive calls
      10411  db block gets
         87  consistent gets
          0  physical reads
          0  redo size
        778  bytes sent via SQL*Net to client
       1343  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed


It also means there's more space in undo to meet the retention target.

But perhaps the most interesting advantage is a functional one.

If you have a read-only database, say an Active Data Guard physical standby, you can run DML against these temporary tables!

Which is handy as reports often write a few rows of metadata when running. This increases the reports you can offload to the standby.

Note: you need to set this at the start of the session. You can't change part-way through https://connor-mcdonald.com/2015/04/28/temporary-undo-in-12c/

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Dhritiman Deb, December 09, 2020 - 4:46 pm UTC

Awesome explanation and very helpful.

Impact of temp_undo_enabled on primary database

Flávio Melo, April 16, 2021 - 3:00 pm UTC

Does the use of the temp_undo_enabled parameter have any impact on standby if applied to the primary database?
Connor McDonald
April 19, 2021 - 5:35 am UTC

Not to my knowledge, but it if you are going to use it for the primary it would make sense to do it for the standby no?

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database