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. :
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.
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.
.
.
.
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.
.
.
.
No comments:
Post a Comment