Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Albert Nelson.

Asked: November 21, 2011 - 9:03 pm UTC

Last updated: November 23, 2011 - 8:24 am UTC

Version: 11.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I want to understand redo generation for inserts into a global temporary table.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create  global temporary table tmp_gtt (n number);

Table created.

SQL> column abs_value new_value prev_value
SQL> column redo_abs_value new_value redo_prev_value
SQL> set echo on
SQL> @undo_vector
SQL> select t.value as abs_value, t.value - &prev_value as change
  2  from   v$mystat t
  3  join   v$statname n on t.statistic# = n.STATISTIC#
  4  where  lower(n.name) = 'undo change vector size';
Enter value for prev_value: 0
old   1: select t.value as abs_value, t.value - &prev_value as change
new   1: select t.value as abs_value, t.value - 0 as change

 ABS_VALUE     CHANGE
---------- ----------
       132        132

SQL> @redo_size
SQL> select t.value as redo_abs_value, t.value - &redo_prev_value as change
  2  from   v$mystat t
  3  join   v$statname n on t.statistic# = n.STATISTIC#
  4  where  lower(n.name) = 'redo size';
Enter value for redo_prev_value: 0
old   1: select t.value as redo_abs_value, t.value - &redo_prev_value as change
new   1: select t.value as redo_abs_value, t.value - 0 as change

REDO_ABS_VALUE     CHANGE
-------------- ----------
           816        816

SQL> insert into tmp_gtt values (1);

1 row created.

SQL> @undo_vector
SQL> select t.value as abs_value, t.value - &prev_value as change
  2  from   v$mystat t
  3  join   v$statname n on t.statistic# = n.STATISTIC#
  4  where  lower(n.name) = 'undo change vector size';
old   1: select t.value as abs_value, t.value - &prev_value as change
new   1: select t.value as abs_value, t.value -        132 as change

 ABS_VALUE     CHANGE
---------- ----------
       240        108

SQL> @redo_size
SQL> select t.value as redo_abs_value, t.value - &redo_prev_value as change
  2  from   v$mystat t
  3  join   v$statname n on t.statistic# = n.STATISTIC#
  4  where  lower(n.name) = 'redo size';
old   1: select t.value as redo_abs_value, t.value - &redo_prev_value as change
new   1: select t.value as redo_abs_value, t.value -        816 as change

REDO_ABS_VALUE     CHANGE
-------------- ----------
          1100        284

SQL> insert into tmp_gtt values (1);

1 row created.

SQL> @undo_vector
SQL> select t.value as abs_value, t.value - &prev_value as change
  2  from   v$mystat t
  3  join   v$statname n on t.statistic# = n.STATISTIC#
  4  where  lower(n.name) = 'undo change vector size';
old   1: select t.value as abs_value, t.value - &prev_value as change
new   1: select t.value as abs_value, t.value -        240 as change

 ABS_VALUE     CHANGE
---------- ----------
       304         64

SQL> @redo_size
SQL> select t.value as redo_abs_value, t.value - &redo_prev_value as change
  2  from   v$mystat t
  3  join   v$statname n on t.statistic# = n.STATISTIC#
  4  where  lower(n.name) = 'redo size';
old   1: select t.value as redo_abs_value, t.value - &redo_prev_value as change
new   1: select t.value as redo_abs_value, t.value -       1100 as change

REDO_ABS_VALUE     CHANGE
-------------- ----------
          1280        180

SQL> insert into tmp_gtt values (1);

1 row created.

SQL> @redo_size
SQL> select t.value as redo_abs_value, t.value - &redo_prev_value as change
  2  from   v$mystat t
  3  join   v$statname n on t.statistic# = n.STATISTIC#
  4  where  lower(n.name) = 'redo size';
old   1: select t.value as redo_abs_value, t.value - &redo_prev_value as change
new   1: select t.value as redo_abs_value, t.value -       1280 as change

REDO_ABS_VALUE     CHANGE
-------------- ----------
          1460        180

SQL> @undo_vector
SQL> select t.value as abs_value, t.value - &prev_value as change
  2  from   v$mystat t
  3  join   v$statname n on t.statistic# = n.STATISTIC#
  4  where  lower(n.name) = 'undo change vector size';
old   1: select t.value as abs_value, t.value - &prev_value as change
new   1: select t.value as abs_value, t.value -        304 as change

 ABS_VALUE     CHANGE
---------- ----------
       368         64

SQL> insert into tmp_gtt values (1);

1 row created.

SQL> @undo_vector
SQL> select t.value as abs_value, t.value - &prev_value as change
  2  from   v$mystat t
  3  join   v$statname n on t.statistic# = n.STATISTIC#
  4  where  lower(n.name) = 'undo change vector size';
old   1: select t.value as abs_value, t.value - &prev_value as change
new   1: select t.value as abs_value, t.value -        368 as change

 ABS_VALUE     CHANGE
---------- ----------
       432         64

SQL> @redo_size
SQL> select t.value as redo_abs_value, t.value - &redo_prev_value as change
  2  from   v$mystat t
  3  join   v$statname n on t.statistic# = n.STATISTIC#
  4  where  lower(n.name) = 'redo size';
old   1: select t.value as redo_abs_value, t.value - &redo_prev_value as change
new   1: select t.value as redo_abs_value, t.value -       1460 as change

REDO_ABS_VALUE     CHANGE
-------------- ----------
          1640        180

SQL> select t.used_ublk * 8  as txn_undo_used_bytes,
  2         ss.value as session_undo_vector_bytes
  3  from   v$mystat ss
  4  join   v$statname n on ss.statistic# = n.statistic# and
  5                         n.name like '%undo change vector size%'
  6  join   v$session s on s.sid = ss.sid
  7  join   v$transaction t on t.addr = s.taddr;

TXN_UNDO_USED_BYTES SESSION_UNDO_VECTOR_BYTES
------------------- -------------------------
                  8                      4468

SQL>



1) Is the redo generated for undo created?
2) If so, is there any way we can completely avoid redo generation for inserts into a temporary table?

Regards,
Albert Nelson A.

and Tom said...

Yes, temporary tables generate UNDO - and therefore generate REDO for the UNDO.

The redo for the undo must be created because all undo is treated the same, the undo tablespace would appear corrupt upon an instance failure/media recovery event if the undo went missing.

The undo must be generated in support of read consistency. For example, if you:

a) load a temporary table gtt with some data
b) open cursor_1 for select * from gtt
c) update the data in gtt
d) open cursor_2 for select * from gtt
e) delete most of the data in gtt
f) open cursor_3 for select * from gtt

Each of those cursors must see a different result set - we haven't fetched from them, just opened them. cursor_1 must roll back any changed block to the point in time of (b). That requires UNDO. So, the undo must be generated - and undo is always protected by redo.


HOWEVER, there is a trick you can use if you are using a session based global temporary table (on commit preserve rows)

You can insert /*+ append */ (direct path load) the global temporary table - and after committing - read from it.


ops$tkyte%ORA11GR2> create global temporary table gtt
  2  ( data varchar2(2000) )
  3  on commit preserve rows
  4  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly statistics;
ops$tkyte%ORA11GR2> insert into gtt select rpad('x',2000,'x') from all_objects;

72273 rows created.


Statistics
----------------------------------------------------------
       2609  recursive calls
     124151  db block gets
      68680  consistent gets
       1860  physical reads<b>
    5128640  redo size</b>
        909  bytes sent via SQL*Net to client
       1026  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
       1609  sorts (memory)
          0  sorts (disk)
      72273  rows processed

ops$tkyte%ORA11GR2> insert /*+ append */ into gtt select rpad('x',2000,'x') from all_objects;

72273 rows created.


Statistics
----------------------------------------------------------
        323  recursive calls
      24468  db block gets
      47369  consistent gets
       1594  physical reads<b>
          0  redo size</b>
        900  bytes sent via SQL*Net to client
       1043  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
       1496  sorts (memory)
          0  sorts (disk)
      72273  rows processed

ops$tkyte%ORA11GR2> set autotrace off



HOWEVER part II - if you have indexes on the global temporary table, there will always be undo generated for the index maintenance - and hence redo. So you will not see a big difference between the above two approaches:

ops$tkyte%ORA11GR2> create index gtt_index on gtt(data);

Index created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly statistics;
ops$tkyte%ORA11GR2> insert into gtt select rpad('x',2000,'x') from all_objects where rownum <= 1000;

1000 rows created.


Statistics
----------------------------------------------------------
        701  recursive calls
      13010  db block gets
       3097  consistent gets
         34  physical reads
    6109268  redo size
        912  bytes sent via SQL*Net to client
       1050  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         37  sorts (memory)
          0  sorts (disk)
       1000  rows processed

ops$tkyte%ORA11GR2> insert /*+ append */ into gtt select rpad('x',2000,'x') from all_objects where rownum <= 1000;

1000 rows created.


Statistics
----------------------------------------------------------
        107  recursive calls
      11520  db block gets
       2079  consistent gets
          3  physical reads
    6026416  redo size
        898  bytes sent via SQL*Net to client
       1064  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       1000  rows processed

ops$tkyte%ORA11GR2> set autotrace off

Rating

  (2 ratings)

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

Comments

Thanks.

Albert Nelson, November 22, 2011 - 9:53 pm UTC

Hi Tom,

Thanks for the explanation and hint of using direct path inserts to bypass undo. Excellent as always.

I look forward for a feature in future releases where the undo for temporary tablespace objects would be created in temporary tablespace itself and thereby redo can be avoided totally (assuming redo is not generated for any objects in temporary tablespace).

By the way, this question arose because we wanted to reduce redo generation from a batch job that uses temporary tables in our dataguard environment. Due to the spike in redo generation of inserts into this temporary tables bandwidth requirement for dataguard increases.


Regards,
Albert Nelson A.
Tom Kyte
November 23, 2011 - 8:24 am UTC

It is pretty fundamental that all undo for a given transaction is contained in a single undo segment. I'm not sure that'll be changing, it is a rather 'core' concept.



Index logging in temp tables

Michael, April 29, 2014 - 4:41 pm UTC

Hi Tom,

The temp table is created with no logging option but its index is created with logging option.

select TABLE_NAME,LOGGING
from dba_tables
where temporary ='Y'
AND table_name like '%GTT_REDO_LOG%';

GTT_REDO_LOG NO

select index_name,logging
from dba_indexes
where table_name in (select TABLE_NAME
from dba_tables
where temporary ='Y'
AND table_name like '%GTT_REDO_LOG%');

GTT_REDO_LOGIDX1 YES

Does it leads problem for redo log size with indexes?.
How to alter index for nologging option.
i tried and i got unimplement future error.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.