Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Dusan.

Asked: October 27, 2016 - 10:36 am UTC

Last updated: October 27, 2016 - 3:47 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hello there,

I am quite curious why redo is still generated when I set temp_undo_enabled=true, having parameter compatible 12.1.0.2.0. I expected redo to be close to 0.
The same result I got years ago, when I first introduced with 12.1.0.1

I performed the test on pluggable database.



dv@PDB1> drop table t purge;

Table dropped.

dv@PDB1> create global temporary table t
on commit delete rows as
--on commit preserve rows as
select * from all_objects where 1=0;

Table created.

dv@PDB1> alter system set temp_undo_enabled=false;
dv@PDB1> set autotrace traceonly statistics;


dv@PDB1> insert into t select * from all_objects;
89143 rows created.

Statistics
----------------------------------------------------------
646 recursive calls
8286 db block gets
63045 consistent gets
0 physical reads
588828 redo size
1167 bytes sent via SQL*Net to client
1365 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1497 sorts (memory)
0 sorts (disk)
89143 rows processed

dv@PDB1> alter system set temp_undo_enabled=true;
System altered.

dv@PDB1> insert into t select * from all_objects;

89143 rows created.


Statistics
----------------------------------------------------------
100 recursive calls
8266 db block gets
62648 consistent gets
0 physical reads
587508 redo size
1167 bytes sent via SQL*Net to client
1365 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1475 sorts (memory)
0 sorts (disk)
89143 rows processed

dv@PDB1>



and Chris said...

You need to start a new session to see the effect:

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.

https://docs.oracle.com/database/121/REFRN/GUID-E2A01A84-2D63-401F-B64E-C96B18C5DCA6.htm#REFRN10326

SQL> drop table t purge;

Table dropped.

SQL> create global temporary table t
  2  on commit delete rows as
  3  select * from all_objects where 1=0;

Table created.

SQL>
SQL> alter system set temp_undo_enabled=false;

System altered.

SQL>
SQL> set autotrace traceonly statistics;
SQL>
SQL> insert into t select * from all_objects;

90254 rows created.


Statistics
----------------------------------------------------------
       1293  recursive calls
       7835  db block gets
      72364  consistent gets
       2405  physical reads
     567100  redo size
        817  bytes sent via SQL*Net to client
       1246  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
       1547  sorts (memory)
          0  sorts (disk)
      90254  rows processed

SQL> commit;

Commit complete.

SQL> alter system set temp_undo_enabled=true;

System altered.

SQL>
SQL> insert into t select * from all_objects;

90254 rows created.


Statistics
----------------------------------------------------------
         99  recursive calls
       7804  db block gets
      70979  consistent gets
       2276  physical reads
     565944  redo size
        820  bytes sent via SQL*Net to client
       1246  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
       1504  sorts (memory)
          0  sorts (disk)
      90254  rows processed

SQL>
SQL> conn chris/****@pdb
Connected.
SP2-0158: unknown SET option "sqlformat"
SQL>
SQL> alter system set temp_undo_enabled=true;

System altered.

SQL>
SQL> set autotrace traceonly statistics;
SQL>
SQL> insert into t select * from all_objects;

90254 rows created.


Statistics
----------------------------------------------------------
        105  recursive calls
       7816  db block gets
      70983  consistent gets
       2270  physical reads
        272  redo size
        812  bytes sent via SQL*Net to client
       1246  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
       1504  sorts (memory)
          0  sorts (disk)
      90254  rows processed

Rating

  (1 rating)

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

Comments

redo and temp table 12c - works

Dusan Valasek, October 28, 2016 - 8:33 am UTC

I have retested this, works ok. Good to remember that alter system commands start to be effective since next session.
Thanks,
Dusan

More to Explore

Multitenant

Need more information on Multitenant? Check out the Multitenant docs for the Oracle Database