Sunday, December 06, 2009

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';

1 comment:

Surachart said...

After post this idea on oracle community...
they comment to check ID 842374.1

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

So, I tested on 11gR2 with APPEND_VALUES hint...

SQL> show parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

SQL> create table test (col varchar2(10))
/

Table created.

SQL> insert into test values(1)
/

1 row created.


SQL> commit;

Commit complete.

SQL> select sum(bytes), sum(blocks) from dba_extents where segment_name = 'TEST' and owner = 'SURACHART';

SUM(BYTES) SUM(BLOCKS)
---------- -----------
65536 8

SQL> insert /*+ APPEND_VALUES */ into test values(2) ;

1 row created.

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


SQL> commit;

Commit complete.

SQL> insert /*+ APPEND_VALUES */ into test values(2) ;

1 row created.

SQL> commit;

Commit complete.

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

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


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

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ROWID COL
------------------------------------ ------------------ ----------
13779 AAAVZmAAGAAADXTAAA 1
13784 AAAVZmAAGAAADXYAAA 2
13785 AAAVZmAAGAAADXZAAA 2

SQL> insert into test values(3)
/

1 row created.

SQL> commit
/

Commit complete.

SQL> insert into test values(4)
/

1 row created.

SQL> commit
/

Commit complete.

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

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ROWID COL
------------------------------------ ------------------ ----------
13779 AAAVZmAAGAAADXTAAA 1
13779 AAAVZmAAGAAADXTAAB 3
13779 AAAVZmAAGAAADXTAAC 4
13784 AAAVZmAAGAAADXYAAA 2
13785 AAAVZmAAGAAADXZAAA 2

SQL> insert /*+ APPEND_VALUES */ into test values(6);

1 row created.

SQL> commit;

Commit complete.

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

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ROWID COL
------------------------------------ ------------------ ----------
13779 AAAVZmAAGAAADXTAAA 1
13779 AAAVZmAAGAAADXTAAB 3
13779 AAAVZmAAGAAADXTAAC 4
13784 AAAVZmAAGAAADXYAAA 2
13785 AAAVZmAAGAAADXZAAA 2
13786 AAAVZmAAGAAADXaAAA 6

6 rows selected.

Oh Bug 8595132