Re: Slow Load Performance

From: Herman de Boer <h.de.boer_at_itcg.nl>
Date: 22 Jul 2003 00:40:13 -0700
Message-ID: <bde5777e.0307212340.54b81ddd@posting.google.com>

barryh_at_quadrus.com (Barry Hensch) wrote in message news:<5757a2ca.0307211359.4a6450dc_at_posting.google.com>...
> Hello experts:
>
> We are experiencing very slow insert performance in one of our DW fact
> tables. The fact table is just under 100 millions rows currently, is
> partitioned by month and has 6 bitmap and 3 B-Tree indicies. (The
> Oracle database is v8.1.7.3).
>

....
>
> Thanks very much.
>
> Barry

Hello Barry,

if you didn't use the APPEND hint so far, I suggest you try that one (insert /*+ append */ into ... select ....) By using that hint, the insert-select uses the direct load interface, like sqlloader can. Probably a few times faster. I guess that your current insert is slow because of the index maintenance; probably a few I/O's per row must be performed.

And if you want to use more than one of the 16 cpu's available, you must introduce some kind of parallelism, either user-written or Oracle's PQ.

Kind Regards,

Herman de Boer. Received on Tue Jul 22 2003 - 02:40:13 CDT