Redo/Undo explosion from thick declared table insert

This blog presents a Redo/Undo explosion caused by thick declared table insert. Originally there is a thin table (thin_tab) consisting of two columns: a number and a 40 char varchar2. Later two new columns with 1000 char varchar2 each are required to store some seldom occurred message, so a new table (thick_tab) is created by adding these two new columns.

The test code is performed on 10gR2 and 11gR2 on a NOARCHIVELOG-mode database with
   undo_management=AUTO,
   db_block_size=8192,
   nls_characterset=AL32UTF8.

First we insert 10000 rows into the thin_tab, then we insert the same content into the thick_tab. The two new columns in thick_tab are not used at all. Table test_stats is used to store the test statistics for each step.

drop table test_stats;

create table test_stats
    (step  varchar2(10),
     name  varchar2(30),
     value number       );

drop table thin_tab;

create table thin_tab
(
  num    number,
  txt    varchar2(40 char)
)
tablespace sysaux
pctused    0
pctfree    10
initrans   1
maxtrans   255
storage    (
            initial          64k
            next             1m
            minextents       1
            maxextents       unlimited
            pctincrease      0
            buffer_pool      default
           )
logging
nocompress
nocache
noparallel
monitoring;

drop table thick_tab;

create table thick_tab
(
  num    number,
  txt    varchar2(40 char),
  txtn1  varchar2(1000 char),
  txtn2  varchar2(1000 char)
)
tablespace sysaux
pctused    0
pctfree    10
initrans   1
maxtrans   255
storage    (
            initial          64k
            next             1m
            minextents       1
            maxextents       unlimited
            pctincrease      0
            buffer_pool      default
           )
logging
nocompress
nocache
noparallel
monitoring;

---- step_1 ----
insert into test_stats
select 'step_1' step, vn.name, vs.value
from   v$sesstat  vs
     , v$statname vn
where  vs.sid = userenv('sid')
  and  vs.statistic# = vn.statistic#
  and  vn.name in ('redo size', 'undo change vector size');

---- step_2 insert into thin_tab ----
insert into thin_tab(num, txt)
select level, 'abc' from dual connect by level <= 10000;

insert into test_stats
select 'step_2' step, vn.name, vs.value
from   v$sesstat  vs
     , v$statname vn
where  vs.sid = userenv('sid')
  and  vs.statistic# = vn.statistic#
  and  vn.name in ('redo size', 'undo change vector size');

---- step_3 insert into thick_tab ----
insert into thick_tab(num, txt)
select level, 'abc' from dual connect by level <= 10000;

insert into test_stats
select 'step_3' step, vn.name, vs.value
from   v$sesstat  vs
     , v$statname vn
where  vs.sid = userenv('sid')
  and  vs.statistic# = vn.statistic#
  and  vn.name in ('redo size', 'undo change vector size');
 
select step, name, value,
       (value - lag(value) over (partition by name order by step)) diff
from   test_stats;

commit;

select segment_name, blocks, bytes
from   dba_segments
where  segment_name in ('THIN_TAB', 'THICK_TAB');

Output:

STEP    NAME                     VALUE       DIFF
------- ------------------------ ----------  ----------
step_1  redo size                11'592'572 
step_2  redo size                11'793'484     200'912
step_3  redo size                14'335'284   2'541'800
step_1  undo change vector size   3'011'440 
step_2  undo change vector size   3'037'820      26'380
step_3  undo change vector size   3'720'120     682'300

SEGMENT_NAME  BLOCKS  BYTES
------------- ------  -------
THICK_TAB         24  196'608
THIN_TAB          24  196'608

Above output demonstrates thick_tab insert generated 10 times redo,and 30 times undo than thin_tab even though the two new columns in thick_tab have nothing inserted. But both data segments have the similar size.

By dumping the redo logfile, it turns out that Oracle uses row array allocation for thin_tab, but single row allocation for thick_tab. Probably that is an Oracle internal optimization.

If using direct-path insert (insert /*+ append */ ), there will be no big difference for both inserts, and redo and undo will be much less (redo size = 10K, undo change vector size = 2K).

For partitioned table, when multiple sessions concurrently make the direct-path(with append hint) for each partition per session, the PARTITION clause is mandatory:

 insert /*+ append */ into test_table_1 PARTITION (part_1) select *  from test_table_2;

Otherwise there is a TM lock contention among the sessions on global table.
The reason is because without PARTITION clause, direct-path makes:

 TM lock with LMODE 6 on global table;
 TM lock with LMODE 3 on the specified partition;

however, with PARTITION clause, they are:

  TM lock with LMODE 3 on global table;
 TM lock with LMODE 6 on the specified partition;

For non direct-path, with or without PARTITION clause are the same:

 TM lock with LMODE 3 on global table;
 TM lock with LMODE 3 on the specified partition;

By the way, Oracle official document about:
   Locking Considerations with Direct-Path INSERT
states:
  During direct-path INSERT, the database obtains exclusive locks on the table (or on all partitions of a partitioned table).
(see Oracle® Database Administrator's Guide 11g Release 2 (11.2)

This claim only holds when no PARTITION clause is specified.