APPEND_VALUES hint and Table NOLOGGING/LOGGING

The APPEND_VALUES(11gR2) hint uses direct-path INSERT with the VALUES clause.

SQL> create table tb (a number, b varchar2(100));

Table created.

SQL> insert /*+ APPEND_VALUES */ into tb values (1, 'ABCDEFG');

1 row created.

SQL> insert /*+ APPEND_VALUES */ into tb values (1, 'ABCDEFG');
insert /*+ APPEND_VALUES */ into tb values (1, 'ABCDEFG')
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

Upon Table NOLOGGING, We use APPEND hint to use direct-path INSERT with the subquery syntax of the INSERT statement, that will generate a minimal number of redo log.
Then, use APPEND_VALUES hint on Table NOLOGGING.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 161
Next log sequence to archive 163
Current log sequence 163

SQL> create table tb_nologging (a number, b varchar2(100)) nologging;

Table created.

SQL> select table_name, logging from user_tables where table_name = 'TB_NOLOGGING';

TABLE_NAME LOG
------------------------------ ---
TB_NOLOGGING NO

start INSERT with the VALUES clause.
session 1:

SQL> @redo-new

REDO_SIZE
----------
2004

SQL> insert into tb_nologging values (1, 'ABCDEFG');

1 row created.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 2004) REDO_SIZE

REDO_SIZE
----------
1880

SQL> @redo-new

REDO_SIZE
----------
3884

SQL> commit;

Commit complete.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 3884) REDO_SIZE

REDO_SIZE
----------
140

SQL> @redo-new

REDO_SIZE
----------
4024

SQL> insert into tb_nologging values (2, 'GFEDCBA');

1 row created.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 4024) REDO_SIZE

REDO_SIZE
----------
472

SQL> @redo-new

REDO_SIZE
----------
4496

SQL> commit;

Commit complete.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 4496) REDO_SIZE

REDO_SIZE
----------
140

session 2:

SQL> @redo-new

REDO_SIZE
----------
836

SQL> insert /*+ APPEND_VALUES */ into tb_nologging values (1, 'ABCDEFG');

1 row created.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 836) REDO_SIZE

REDO_SIZE
----------
888

SQL> @redo-new

REDO_SIZE
----------
1724

SQL> commit;

Commit complete.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 1724) REDO_SIZE

REDO_SIZE
----------
1200

SQL> @redo-new

REDO_SIZE
----------
2924

SQL> insert /*+ APPEND_VALUES */ into tb_nologging values (2, 'GFEDCBA');

1 row created.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 2924) REDO_SIZE

REDO_SIZE
----------
452

SQL> @redo-new

REDO_SIZE
----------
3376

SQL> commit;

Commit complete.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 3376) REDO_SIZE

REDO_SIZE
----------
1184

we don't see the different redo log size between APPEND_VALUES hint and no hint, when insert on the same session again. but i see APPEND_VALUES hint generated redo log size more than no hint, after committed.(test another else...)

use APPEND_VALUES hint on Table LOGGING.

SQL> create table tb_logging (a number, b varchar2(100)) logging;

Table created.

SQL> @redo-new

REDO_SIZE
----------
1756

SQL> insert into tb_logging values (1, 'ABCDEFG');

1 row created.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 1756) REDO_SIZE

REDO_SIZE
----------
1880

SQL> @redo-new

REDO_SIZE
----------
3636

SQL> commit;

Commit complete.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 3636) REDO_SIZE

REDO_SIZE
----------
140

SQL> @redo-new

REDO_SIZE
----------
3776

SQL> insert /*+ APPEND_VALUES */ into tb_logging values (1, 'ABCDEFG');

1 row created.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 3776) REDO_SIZE

REDO_SIZE
----------
10508

SQL> @redo-new

REDO_SIZE
----------
14284

SQL> commit;

Commit complete.

SQL> @redo-diff;
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 14284) REDO_SIZE

REDO_SIZE
----------
1200

SQL> @redo-new

REDO_SIZE
----------
15484

SQL> insert into tb_logging values (1, 'ABCDEFG');

1 row created.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 15484) REDO_SIZE

REDO_SIZE
----------
592

SQL> @redo-new

REDO_SIZE
----------
16076

SQL> commit
2 ;

Commit complete.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 16076) REDO_SIZE

REDO_SIZE
----------
140

SQL> @redo-new

REDO_SIZE
----------
16216

SQL> insert /*+ APPEND_VALUES */ into tb_logging values (1, 'ABCDEFG');

1 row created.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 16216) REDO_SIZE

REDO_SIZE
----------
8508

SQL> @redo-new

REDO_SIZE
----------
24724

SQL> commit;

Commit complete.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 24724) REDO_SIZE

REDO_SIZE
----------
1200

On Table Logging... APPEND_VALUES hint generated many redo logs... (strange)
So, we tested with (PL/SQL) FORALL

SQL> create table tb1 nologging as select * from all_objects where 1=0;

Table created.

SQL> c/tb1/tb2
1* create table tb2 nologging as select * from all_objects where 1=0
SQL> /

Table created.

SQL> select table_name, logging from user_tables where table_name in ('TB1','TB2');

TABLE_NAME LOG
------------------------------ ---
TB1 NO
TB2 NO

PL/SQL Scripts: a1=no hint, a2=APPEND_VALUES hint

CREATE OR REPLACE PROCEDURE a1 (p_array_size IN PLS_INTEGER DEFAULT 100)
IS

TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;

CURSOR c IS
SELECT *
FROM all_objects;

BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

FORALL i IN 1..l_data.COUNT
INSERT INTO tb1 VALUES l_data(i);
commit;
EXIT WHEN c%NOTFOUND;
END LOOP;
close c;
END a1;
/

CREATE OR REPLACE PROCEDURE a2 (p_array_size IN PLS_INTEGER DEFAULT 100)
IS

TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;

CURSOR c IS
SELECT *
FROM all_objects;

BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

FORALL i IN 1..l_data.COUNT
INSERT /*+ APPEND_VALUES */ INTO tb2 VALUES l_data(i);
commit;
EXIT WHEN c%NOTFOUND;
END LOOP;
close c;
END a2;
/

start with Table NOLOGGING

SQL> @redo-new

REDO_SIZE
----------
3060

SQL> exec a1;

PL/SQL procedure successfully completed.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 3060) REDO_SIZE

REDO_SIZE
----------
6871852

SQL> @redo-new

REDO_SIZE
----------
6874912

SQL> exec a2(100);

PL/SQL procedure successfully completed.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 6874912) REDO_SIZE

REDO_SIZE
----------
956988

SQL> select count(*) from tb1;

COUNT(*)
----------
55796

SQL> select count(*) from tb2;

COUNT(*)
----------
55796

APPEND_VALUES hint on Table NOLOGGING generated redo log less than NO hint.
start with Table LOGGING

SQL> truncate table tb1;

Table truncated.

SQL> truncate table tb2;

Table truncated.

SQL> alter table tb1 logging;

Table altered.

SQL> alter table tb2 logging;

Table altered.

SQL> select table_name, logging from user_tables where table_name in ('TB1','TB2');

TABLE_NAME LOG
------------------------------ ---
TB1 YES
TB2 YES

SQL> @redo-new

REDO_SIZE
----------
89088

SQL> exec a1;

PL/SQL procedure successfully completed.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 89088) REDO_SIZE

REDO_SIZE
----------
6869772

SQL> @redo-new

REDO_SIZE
----------
6958860

SQL> exec a2;

PL/SQL procedure successfully completed.

SQL> @redo-diff
old 1: select (value - &REDO_SIZE) REDO_SIZE
new 1: select (value - 6958860) REDO_SIZE

REDO_SIZE
----------
10107816

APPEND_VALUES hint on Table LOGGING generated redo log more than NO hint.
By the way if use APPEND_VALUES hint (direct-path), we should use with FORALL.

Scripts:
$ cat redo-new.sql
column REDO_SIZE new_value REDO_SIZE
select value REDO_SIZE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';

$ cat redo-diff.sql
select (value - &REDO_SIZE) REDO_SIZE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';