20 May, 2008

APPEND, NOLOGGING and Indexes

Running some tests on 10gR2, I find that the presence of Indexes can have a very significant impact on Direct Path Inserts done with the APPEND Hint, even if the target table is NOLOGGING.

Thus, for example, a Normal INSERT on an unindexed table is :

SQL> insert /* normal insert */ into test_append ta
2 select * from source_table;

301974 rows created.

Elapsed: 00:00:03.22
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> select sn.name, s.value
2 from v$statname sn, v$mystat s
3 where sn.statistic#=s.statistic#
4 and sn.name in ('CPU used by this session', 'consistent gets', 'db block changes', 'db block gets', 'physical reads direct','physical writes direct','redo entries', 'redo size','undo change vector size')
5 order by sn.name;

NAME VALUE
---------------------------------------------------------------- ---------------
CPU used by this session 145
consistent gets 12,608
db block changes 30,725
db block gets 36,672
physical reads direct 0
physical writes direct 0
redo entries 22,796
redo size 33,808,976
undo change vector size 1,122,508

While an INSERT with the table set to NOLOGGING is :

SQL> insert /*+ APPEND */ into test_append ta
2 select * from source_table;

301980 rows created.

Elapsed: 00:00:01.61
SQL> commit;

Commit complete.

Elapsed: 00:00:00.03
SQL> select sn.name, s.value
2 from v$statname sn, v$mystat s
3 where sn.statistic#=s.statistic#
4 and sn.name in ('CPU used by this session', 'consistent gets', 'db block changes', 'db block gets', 'physical reads direct','physical writes direct','redo entries', 'redo size','undo change vector size')
5 order by sn.name;

NAME VALUE
---------------------------------------------------------------- ---------------
CPU used by this session 129
consistent gets 4,353
db block changes 194
db block gets 4,353
physical reads direct 0
physical writes direct 4,146
redo entries 260
redo size 26,816
undo change vector size 4,664

However, if I have a "large" (multi column, concatenated index with a large key size) Index on the target table I get :

SQL> insert /*+ APPEND */ into test_append ta
2 select * from source_table;

301974 rows created.

Elapsed: 00:00:24.45
SQL> commit;

Commit complete.

Elapsed: 00:00:00.18
SQL> select sn.name, s.value
2 from v$statname sn, v$mystat s
3 where sn.statistic#=s.statistic#
4 and sn.name in ('CPU used by this session', 'consistent gets', 'db block changes', 'db block gets', 'physical reads direct','physical writes direct','redo entries', 'redo size','undo change vector size')
5 order by sn.name;

NAME VALUE
---------------------------------------------------------------- ---------------
CPU used by this session 1,107
consistent gets 21,592
db block changes 186,229
db block gets 194,635
physical reads direct 4,775
physical writes direct 8,921
redo entries 100,228
redo size 152,819,412
undo change vector size 72,298,884


When a Direct Path INSERT is executed Index Maintenance is deferred. However, Index Maintenance itself generates Undo and is NOT a NoLogging operation.

Thus, my INSERT that normally generates 33MB of Redo and can be optimized to 26KB with NOLOGGING set at the Table level and APPEND as a Hint (directive), becomes a 152MB Redo Generator if I have an Index.

YMMV, depending on the table columns, Index key length etc. But it is important to note that an Index can really hurt your happiness when you are doing bulk loads.

5 comments:

Anonymous said...

why don't you read the book (effective oracle by design, page 118) :)

Hemant K Chitale said...
This comment has been removed by the author.
Hemant K Chitale said...

Yes, I do know about UNUSABLE. My blogpost was
not about me being surprised about the impact of Indexes in APPEND, NOLOGGING Inserts. It was to provide information to those who may not have noticed this and/or read Pages 118 and 119 of Tom Kyte's book. (Read the first two paragraphs and the Tip on Page 119 as well).
The 9i version of the book (which you might referenced) does show how UNUSABLE is an advantage over DROP and CREATE.

*HOWEVER* the 10gR2 behaviour is different. In 10gR2, the default value for SKIP_UNUSABLE_INDEXES is TRUE. Therefore, even users wouldn't get errors if the Index rebuild failed. They might get slow performance in queries but NO errors.

Maybe we should catchup on the 10g version of the book.

SSK said...

Hi Hemant,

During your tests, was the index also in nologging mode?

Since we use direct path load heavily and with indexes (albeit nologging), I tested your experiment and could not replicate this behavior.

With an index it did take longer to insert (5 minutes versus 8 minutes) and there was more redo (824KB versus 826KB) - however it was insignificant.

I was inserting 2 mill rows at one go.

Thanks
Krishna Manoharan

Hemant K Chitale said...

NOLOGGING on an Index applies only during a CREATE or REBUILD not for a direct path insert.
An INSERT does cause Logging for the index.