APPEND Hint (Direct-Path) Insert with Values Causes Excessive Space Usage on 11G

(Doc ID 842374.1)

Last updated on JUNE 15, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.2 [Release 11.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 25-Sept-2014***


Symptoms

The APPEND hint (direct-path load) in a INSERT INTO <table> VALUES (...) statement causes excessive space usage in 11g Release 1. This does not happen on previous versions 9i and 10g.

Example:

show parameter db_block_size

/*
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
db_block_size                        integer     8192
*/

connect test/test

create table test (col varchar2(10))
/

insert into test values(1)
/

commit
/

select sum(bytes), sum(blocks) from dba_extents where segment_name = 'TEST' and owner = 'TEST'
/

/*
E.g.:
SUM(BYTES) SUM(BLOCKS)
---------- -----------
     65536           8


-- at this point we have allocated the first extent in the table of 8 blocks
-- we continue inserting values with APPEND (direct path insert) using one commit per insert
*/

insert /*+ APPEND */ into test values(2)
/

commit
/

insert /*+ APPEND */ into test values(2)
/

commit
/


-- at this point we can see that new blocks (one more extent) have been allocated above the high water mark

select sum(bytes), sum(blocks), count(*) from dba_extents where segment_name = 'TEST' and owner = 'TEST';

/*
SUM(BYTES) SUM(BLOCKS) COUNT(*)
---------- ----------- ----------
    131072          16          2

-- checking the number of rows per block we see one row per block due to the APPEND hint
*/

SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid), rowid, col FROM test;

/*

E.g.:

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ROWID              COL
------------------------------------ ------------------ ----------
                                 213 AAAR4ZAAEAAAADVAAA 1
                                 216 AAAR4ZAAEAAAADYAAA 2
                                 217 AAAR4ZAAEAAAADZAAA 2

-- in the above output the block numbers are different for each row
-- if we insert without APPEND, the space should be reused:

*/

insert into test values(3)
/

commit
/

insert into test values(4)
/

commit
/

SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid), rowid, col FROM test
/

/*
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ROWID              COL
------------------------------------ ------------------ ----------
                                 211 AAAR4ZAAEAAAADTAAA 3
                                 211 AAAR4ZAAEAAAADTAAB 4
                                 213 AAAR4ZAAEAAAADVAAA 1
                                 216 AAAR4ZAAEAAAADYAAA 2
                                 217 AAAR4ZAAEAAAADZAAA 2

-- in the above output we reused the space within block 211 below the high water mark for the new rows "3" and "4"; they are placed in the same block
-- if we insert one additional row with APPEND we will see a new block allocated above the high water mark
*/

insert /*+ APPEND */ into test values(6)
/

commit
/

SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid), rowid, col FROM test
/

/*

E.g.:

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ROWID              COL
------------------------------------ ------------------ ----------
                                 211 AAAR4ZAAEAAAADTAAA 3
                                 211 AAAR4ZAAEAAAADTAAB 4
                                 211 AAAR4ZAAEAAAADTAAC 5
                                 213 AAAR4ZAAEAAAADVAAA 1
                                 216 AAAR4ZAAEAAAADYAAA 2
                                 217 AAAR4ZAAEAAAADZAAA 2
                                 218 AAAR4ZAAEAAAADaAAA 6
*/

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms