5

In the past few days I've playing around with Oracle's SQL*Loader in attempt to bulk load data into Oracle. After trying out different combination of options I was surprised to found the conventional path load runs much quicker than direct path load.

A few facts about the problem:

  • Number of records to load is 60K.
  • Number of records in target table, before load, is 700 million.
  • Oracle version is 11g r2.
  • The data file contains date, character (ascii, no conversion required), integer, float. No blob/clob.
  • Table is partitioned by hash. Hash function is same as PK.
  • Parallel of table is set to 4 while server has 16 CPU.
  • Index is locally partitioned. Parallel of index (from ALL_INDEXES) is 1.
  • There's only 1 PK and 1 index on target table. PK constraint built using index.
  • Check on index partitions revealed that records distribution among partitions are pretty even.
  • Data file is delimited.
  • APPEND option is used.
  • Select and delete of the loaded data through SQL is pretty fast, almost instant response.

With conventional path, loading completes in around 6 seconds.

With direct path load, loading takes around 20 minutes. The worst run takes 1.5 hour to complete yet server was not busy at all.

If skip_index_maintenance is enabled, direct path load completes in 2-3 seconds.

I've tried quite a number of options but none of them gives noticeable improvement... UNRECOVERABLE, SORTED INDEXES, MULTITHREADING (I am running SQL*Loader on a multiple CPU server). None of them improve the situation.

Here's the wait event I kept seeing during the time SQL*Loader runs in direct mode:

  • Event: db file sequential read
  • P1/2/3: file#, block#, blocks (check from dba_extents that it is an index block)
  • Wait class: User I/O

Does anyone has any idea what has gone wrong with direct path load? Or is there anything I can further check to really dig the root cause of the problem? Thanks in advance.

APC
  • 144,005
  • 19
  • 170
  • 281
Stanley
  • 115
  • 3
  • 7

1 Answers1

3

I guess you are falling fowl of this

"When loading a relatively small number of rows into a large indexed table

During a direct path load, the existing index is copied when it is merged with the new index keys. If the existing index is very large and the number of new keys is very small, then the index copy time can offset the time saved by a direct path load."

from When to Use a Conventional Path Load in: http://download.oracle.com/docs/cd/B14117_01/server.101/b10825/ldr_modes.htm

Kevin Burton
  • 11,676
  • 2
  • 24
  • 37
  • Do you know does that behaviour only occur for SQL Loader or does it behave the same way with insert append? I did a very quick test and it didn't seem to copy my existing index (about 92Meg) when I inserted 100 rows with the append hint. – Stephen ODonnell Aug 17 '11 at 12:57
  • I have no idea, I would guess if you tried to insert append 60k rows it would exhibit the same behaviour – Kevin Burton Aug 17 '11 at 13:16
  • Thanks Kevin. I totally missed that section when I scan through SQL*Loader's document. But what could be the best way to load a large amount of data into an non-empty table with relative large amount of records? – Stanley Aug 18 '11 at 01:47
  • good question, I think loading the data in parallel into the correct partitons would be the quickest way, this SO question is relevant http://stackoverflow.com/questions/3554049/data-load-to-huge-partitioned-table. – Kevin Burton Aug 18 '11 at 10:09