Help needed. Inserting large number of records becomes very slow?

[Sina]

Hello everyone.

I have to insert over 500,000 records via an appliation on to the oracle 8i or 9i server.

Inserts run just fine, however I have noticed that at the begining, basically records get inserted on to the database at the rate of about 1000 records / per seconds.

but as the time goes by, after about 200,000 records have been inserted, I can see from the application that the speed at which the records get inserted becomes lower and lower.

do I have to do anything to clear the server cache and or memory and or log file between inserts in to each table?
(like check points or something)?

thank you all

 

[karluk]

My guess is that you are having problems with your rollback segments. They are probably enormous by the time you've completed that many inserts. I would experment with breaking down your insert into smaller chunks and doing commits in between.

 

[gherries]

I agree this is almost certainly the case. We had a methodology of 1000 inserts per commit to try and minimise this. Could be an index rebuild problem considering the number of inserts, check how many indexes you have on the table and consider temporarily disabling them, but only if you can re-enable them when its not peak server use

If your still having problems then I'd suggest taking a look at Quest Software Server monitoroing tools. I think they let you download a trial version, which could get help you find the problem if the rollback problem isnt the actual problem. Great tool for developers and DBA's, saves hours and hours of manual performance monitoring!

 

[ThomVF]

This is good advice.

Also, if you have the option, consider using SQL*Loader with Direct load option instead - this is very fast. You can insert to a blank table or append to an already populated table.

Lastly, as another method to improve performance, you could put the table into NOLOGGING state when doing the inserts. This won't minimize the rollback issue but it will reduce the Redo Activity, which can be another bottleneck. Check with the DBA before considering NOLOGGING - if he/she has a standby database operating, he won't appreciate you doing it.

Good Luck.

Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[Turkbear]

Hi,
The implications for using direct mode in SqlLoader need to be carefully reviewed before using it with production data -e.g. IIRC, It will not be bound by any of the constaints that may be on the table(s) being loaded and therefore can cause data integrity problems..

( If, however, you are loading into a temporary table that will then be processed by a SP to populate the production table , direct load will probably be fine)

 

[raygg]

I attended a presentation given by Kevin Loney - author of the Oracle8i DBA Handbook - who claims that as you increase the blevel of the index - performance will degrade. You can inquiry the blevel in the dba_indexes view. The blevel starts at a value of 1 - and at some value - which varies by table - will increase to 2 and then 3. These increments at rowcounts in the 100's ok K or millions of rows, varying by table and schema. Each time the blevel increments, performance drops noticeabley.