## Question and Answer

## You Asked

Hi Tom,

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

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.

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 we 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.

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:

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

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

# Comments

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.

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.

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.

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.

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.