A blog on Oracle Standard Edition

Here's a blog on Oracle Standard Edition by Ann Sjokvist.

.
.
.

Inserting into a table with potentially long rows

Note :  This post builds on blog posts by Nikolay Savvinov and Jonathan Lewis.


Nikolay Savvinov and Jonathan Lewis have identified that when you have a table with a potentially large row size -- where the theoretical maximum row length exceeds the block size -- redo overheads are significantly greater for multi-row inserts.

First, I demonstrate with a table with a small potential row size. A table with 1 number column and 3 columns of a total max length of 30 characters.  The actual data inserted is also very little.  The first row has the values (1,'1','1','1'), the second row has the values (2,'2','2','2') and so on to (1000,'1000','1000','1000) for a total of 1,000 rows. :

SQL> drop table hkc_test_small_row_size purge;
drop table hkc_test_small_row_size purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> 
SQL> create table hkc_test_small_row_size
  2  (id_column number, data_col_1 varchar2(10), data_col_2 varchar2(10), data_col_3 varchar2(10));

Table created.

SQL> create unique index hkc_tlrs_undx on hkc_test_small_row_size(id_column);

Index created.

SQL> 
SQL> 
SQL> 
SQL> select n.name, s.value At_Beginning
  2  from v$statname n, v$sesstat s
  3  where n.statistic#=s.statistic#
  4  and n.name in ('redo entries', 'redo size')
  5  and s.sid = (select distinct sid from v$mystat)
  6  order by 1
  7  /

NAME                                                             AT_BEGINNING
---------------------------------------------------------------- ------------
redo entries                                                              102
redo size                                                               23896

SQL> 
SQL> insert into hkc_test_small_row_size
  2  select rownum+4000, to_char(rownum), to_char(rownum), to_char(rownum)
  3  from dual
  4  connect  by level < 1001
  5  /

1000 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> select n.name, s.value Normal_Insert
  2  from v$statname n, v$sesstat s
  3  where n.statistic#=s.statistic#
  4  and n.name in ('redo entries', 'redo size')
  5  and s.sid = (select distinct sid from v$mystat)
  6  order by 1
  7  /

NAME                                                             NORMAL_INSERT
---------------------------------------------------------------- -------------
redo entries                                                               154
redo size                                                                92488

SQL> 
SQL> 
SQL> 
SQL> insert /*+ APPEND */ into hkc_test_small_row_size
  2  select rownum, to_char(rownum), to_char(rownum), to_char(rownum)
  3  from dual
  4  connect  by level < 1001
  5  /

1000 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select n.name, s.value APPEND_Insert
  2  from v$statname n, v$sesstat s
  3  where n.statistic#=s.statistic#
  4  and n.name in ('redo entries', 'redo size')
  5  and s.sid = (select distinct sid from v$mystat)
  6  order by 1
  7  /

NAME                                                             APPEND_INSERT
---------------------------------------------------------------- -------------
redo entries                                                               252
redo size                                                               193396

SQL> 
SQL> 
SQL> drop table hkc_test_small_row_size purge;

Table dropped.

SQL> 

Thus, we can see that, for the "SMALL_ROW_SIZE" table, the redo entries for 1000 of these small rows  :
Simple Insert of 1,000 rows :   52 redo entries and 68,592 bytes.
Direct Path (APPEND) of 1,000 rows : 98 redo entries and 100,908 bytes.


Next, I demonstrate with a  table with a large potential row size -- exceeding the database block size.  Exactly the same data set is inserted  --  The first row has the values (1,'1','1','1'), the second row has the values (2,'2','2','2') and so on to (1000,'1000','1000','1000) for a total of 1,000 rows.

SQL> drop table hkc_test_large_row_size purge;
drop table hkc_test_large_row_size purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> 
SQL> create table hkc_test_large_row_size
  2  (id_column number, data_col_1 varchar2(4000), data_col_2 varchar2(4000), data_col_3 varchar2(4000));

Table created.

SQL> create unique index hkc_tlrs_undx on hkc_test_large_row_size(id_column);

Index created.

SQL> 
SQL> 
SQL> 
SQL> select n.name, s.value At_Beginning
  2  from v$statname n, v$sesstat s
  3  where n.statistic#=s.statistic#
  4  and n.name in ('redo entries', 'redo size')
  5  and s.sid = (select distinct sid from v$mystat)
  6  order by 1
  7  /

NAME                                                             AT_BEGINNING
---------------------------------------------------------------- ------------
redo entries                                                              102
redo size                                                               23900

SQL> 
SQL> 
SQL> insert into hkc_test_large_row_size
  2  select rownum+4000, to_char(rownum), to_char(rownum), to_char(rownum)
  3  from dual
  4  connect  by level < 1001
  5  /

1000 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> select n.name, s.value Normal_Insert
  2  from v$statname n, v$sesstat s
  3  where n.statistic#=s.statistic#
  4  and n.name in ('redo entries', 'redo size')
  5  and s.sid = (select distinct sid from v$mystat)
  6  order by 1
  7  /

NAME                                                             NORMAL_INSERT
---------------------------------------------------------------- -------------
redo entries                                                              2145
redo size                                                               526320

SQL> 
SQL> 
SQL> insert /*+ APPEND */ into hkc_test_large_row_size
  2  select rownum, to_char(rownum), to_char(rownum), to_char(rownum)
  3  from dual
  4  connect  by level < 1001
  5  /

1000 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select n.name, s.value APPEND_Insert
  2  from v$statname n, v$sesstat s
  3  where n.statistic#=s.statistic#
  4  and n.name in ('redo entries', 'redo size')
  5  and s.sid = (select distinct sid from v$mystat)
  6  order by 1
  7  /

NAME                                                             APPEND_INSERT
---------------------------------------------------------------- -------------
redo entries                                                              2243
redo size                                                               627228

SQL> 
SQL> 
SQL> drop table hkc_test_large_row_size purge;

Table dropped.

SQL> 

Thus, we can see that, for the "LARGE_ROW_SIZE" table, the redo entries for 1000 of these actually small rows :
Simple Insert of 1,000 rows :   2,043 redo entries and 502,420 bytes.
Direct Path (APPEND) of 1,000 rows : 98 redo entries and 100,908 bytes.

Therefore, a simple (regular) Insert into such a table where the *potential* row size (not necessarily the actual row size) exceeds the block size is very expensive.  Apparently, the optimization to insert multiple rows into a single block with minimal redo is not invoked when Oracle thinks that the row may exceed the block size.  It switches to a row-by-row insert.  This is evident in the LARGE_ROW_SIZE case where 2,043 redo entries is more than 1000 rows + 1000 index entries.  The SMALL_ROW_SIZE had only 52 redo entries.
Remember : This happens when
(a) The potential row size exceeds the block size (irrespective of the actual row size which may be very few bytes)
AND
(b) a regular (non-Direct) Insert is used.
In such cases, a Direct Path Insert works out better.  Although there are obvious downsides to doing Direct Path Inserts -- the HighWaterMark keeps extending, irrespective of Deletes that may have been issued.
.
.
.