Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Optimizing!
In article <7uq0bo$26f$1_at_nnrp1.deja.com>,
Nandakumar <N.Kumar_at_rocketmail.com> wrote:
> Hi
>
> I have a pl/sql procedure that populates fact tables from a temporary
> table. But it takes about 40 minutes to populate 5 tables with 5000
> records from the temporary table. Some of the reasons that i could
think
> of for the procedure's taking this long,
> 1) procedure is using a cursor
Not a problem, although maybe the query could be tuned.
> 2) manipulation of the records in temporary table
Doubtful, seeing as there are only 5000 records but this depends on the machine you are using.
> 3) written to perform update in few records and insert in few others
> 4) update and insert are performed thru' a dynamic PL/SQL as the table
> names are decided in run time.
You really should avoid this. I tuned a script the other day that took 8 hours to run using dynamic SQL, but when I hard-coded all the possible statements, I cut execution time down to 5 hours. This was processing 1,500,000 rows. It is generally possible to cut out all dynamic SQL... it may not look good as 'pretty' design, but you get better performance because Oracle doesn't have to reparse every SQL statement.
The problem I see is that you are probably inserting or updating one row at a time. If you can, try to have bulk updates or inserts as this is much faster.
Also, try committing less frequently. Say every 1000 rows as opposed to every row, as this can significantly improve speed.
> If i want to optimize the SQL for better performance, i think of the
> hint APPEND in the insert statement. The oracle reference says it
should
> be used when a huge number of records are inserted. Could someone tell
> me what would be the huge number in this regard? And if the APPEND
hint
> is used, would it lead to more unused(wasted) disk space?
You could try it although I doubt it would cut time down by very much.
> I also remember if the indexes are enabled, the IMPORT will be very
> slow. Is it the same case when performing an INSERT? In other words,
> does indexing help a fast insert/update?
It will be slow*er*, but not by much... because of more fragmented disk access.
A good bet here is to export your data to a flat file, and then use SQL*Loader in unrecoverable mode: this avoids the use of rollback segments, and updates the indexes afterwards. This is the quickest way of inserting lots of rows into tables that I've found.
Hope this helps!
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Fri Oct 22 1999 - 11:56:20 CDT