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 ?
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/