Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to speed up dataprocessing?

Re: How to speed up dataprocessing?

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Mon, 3 Sep 2001 14:11:22 +0200
Message-ID: <9mvs19$eod$1@ctb-nnrp2.saix.net>


"Wei Chen" <wei.chen_at_gzs.de> wrote

> I am going to insert a huge data into tables. I've tryed to drop the
> index (PK) first and insert the data, then re-create the index(PK).
> But it seems not so much faster than without dropping the index
> when I insert data, but it seems much faster when I delete data
> from tables. Why? Is there any better way to speed up the
> insert-processing?

How do you insert the data?

Consider a client (Visual Basic/Delphi) program doing the insert VS. PL/SQL cursor loop insert VS. a SQL INSERT SELECT VS. a parallel SQL*Loader append. Each of these has pros and cons and provide different levels of performance.

Dropping the PK is not usually a good idea, unless you are either sure that the data inserted is unique, or alternatively you have a way to deal with duplicates when putting the PK back. Dropping secondary indexes otoh though is not a bad idea and can change a 20+ hour insert runtime to just a few minutes (seen it myself on an ODS run on Oracle).

Finally, in order to fix a problem, you need to identify it. Simply saying that the insert is too slow, does not work. Is it really slow? Or are your expectations too high? Or maybe it is a hardware problem, disk problem or even network problem? Are you maybe pulling the data to insert across the network? Are you sure there is not hardware bottlenecks?

You thus also need to look at the external factors influencing the performance of this massive insert. On the Oracle side, it usually pays to see what the insert process are doing by looking at the session stats, accumulated events and current waiting events for that process (see the V$ tables for details).

--
Billy
Received on Mon Sep 03 2001 - 07:11:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US