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