Oracle SQL*loader running in direct mode is much slower than conventional path load

performance oracle sql-loader
Stanley picture Stanley · Aug 17, 2011 · Viewed 8.3k times · Source

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.

Answer

Kevin Burton picture Kevin Burton · Aug 17, 2011

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

Related questions

Inner join vs Where

Is there a difference in performance (in oracle) between Select * from Table1 T1 Inner Join Table2 T2 On T1.ID = T2.ID And Select * from Table1 T1, Table2 T2 Where T1.ID = T2.ID ?

Read More
Difference between Oracle's plus (+) notation and ansi JOIN notation?

What's the difference between using oracle's plus notation (+) over the ansi standard join notation? Is there a difference in performance? Is the plus notation deprecated?

Read More
BETWEEN clause versus <= AND >=

Is there a performance difference between using a BETWEEN clause or using <= AND >= comparisons? i.e. these two queries: SELECT * FROM table WHERE year BETWEEN '2005' AND '2010'; ...and SELECT * FROM table WHERE year >= '2005…

Read More