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: insert programs take 5 hrs

Re: insert programs take 5 hrs

From: Andrew Allen <ajallen_at_mailhost.det.ameritech.net>
Date: Wed, 15 May 2002 11:33:50 GMT
Message-ID: <3CE24760.909E8BEC@mailhost.det.ameritech.net>


res0asig wrote:
>
> hi,
> I need help in determining where the performance problem is for a batch
> program that
> inserts 26000 records into many tables. The batch program continuously
> builds a flat file and as it inserts into these tables. The data in the
> tables is inserted through a views. I did a trace on the batch program and
> I found the following sql statement being parsed a lot of times. "select
> text from view$ where rowid= :1". All the views are valid. the only waits
> i see are "sqlnet message from client" and "sqlnet message to client" in the
> trace file. However the batch program is a local connection. (BEQ).
> Could some one please help me in resloving this issue.
>
> -Thanks in advance
> sri

  1. Use SQL*Loader to import the data into a 'load' table. Quit using the batch program.
  2. Use a set of procedures bundled into a package to edit and distribute the data to the 'active' tables.
  3. Insert/update the base tables instead of the views.

Depending upon the platform your Oracle is installed upon, etc. You should be able to execute this load in a few minutes. In the databases I manage 26,000 records to import and apply is not many at all.

-- 
Andrew Allen
Livonia, MI
E- Mailto:ajalle_at_ameritech.net
Received on Wed May 15 2002 - 06:33:50 CDT

Original text of this message

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