Slow Oracle Database Table Inserts 2

[Hind]

Hi,

I have a VB program that reads about 120,000 records from a file and inserts them into an Oracle database table. The first 60,00 inserts are as fast as 50 to 70 inserts per second and it slows down after that to an average of 1 insert per second.

Are there any reasons why an insert statement takes such a long time?
Any recommnended approaches to fix the problem?

Thank you
Hind

 

[JamesGordon]

Look at your next extent size on the table it might have had say 10MB of space originally and when it fills up it tries to allocate an additional extent of 'next' extent size. If this is too small it will continually have to create a new extent to gain extra space to insert records.
How many extents has the table?
What is the next extent size?
Try changing the next extent size to something bigger.
Don't make it too big or your table will grab too much space and then have it hanging around as waste.

 

[sem]

The same with rollback segment: try to use "dedicated" rollback segment by SET TRANSACTION USE ROLLBACK SEGMENT. It must be large enough to prevent expanding.

 

[KALYANIDRV]

Hi Hind,

Please you have to add the following code into your program. It will be improve your performance without making any changes on the database side.


-----------------------------------------------------------
declaring section

T_count number(10);

Execution Section
begin
T_count := 0;
if (T_count = 1000) then
insert into table name vlaues(..........);
commit;
t_count := 0;
else
t_count := t_count + 1;
end if;

end;
----------------------------------------------------------
Please let me know on the same.
(ravich_74@hotmail.com)

Thanks,
Ravi.

 

[Dagon]

Another thing to consider is whether you have any indexes on the table you are inserting into. I had a problem where the inserts started OK but got slower and slower as the indexes on the table got bigger. The solution was to drop the indexes before the load and recreate them at the end. Bitmap indexes are especially slow when doing inserts or updates.

You could also look at whether the VB script is doing single row inserts or array inserts. Over a network, this will make a substantial difference.

 

[raygg]

Indexes may be the clue - separate the indexes onto another tablespace. You may also be encountering a lesser known problem where you are passing a threshhold from blevel1 to blevel2 - or blevel 2 to blevel3. At these threshholds, performance is proven to decline. What version VB and what version Oracle? It may help to do your inserts in a sorted ascending order matching the order of your indexes. If you are continually inserting into the middle of your table, and not appending values to the end of it, consider increasing the percentfree value. Rebuilding indexes daily may be necessary as previously suggested. Look at the design of your indexes to ensure you put the highest selectivity value first when ordering index columns. Good luck.

 

[rtefft]

Another possibility... if you have a lot of indexes on the table, it will gradually take longer and longer for your inserts because the indexes grow. If the table is empty to start, consider dropping your indexes (other than PK), loading the data, and then indexing afterwards. Bulk indexing for a data load is always much faster.

I wouldn't expect performance to be a problem for only 120,000 records, but I don't know what your system and DB structure looks like.

Hope it's useful,
Rich ____________________________
Rich Tefft
PL/SQL Programmer

 

[raygg]

If dropping indexes is the solution, then I disagree with rtefft - there is no reason NOT to drop the PK index. It is no different than any other of the indexes, and in fact if an index problem exists, then the PK index is often most responsible for the problem and should be dropped and rebuilt. All of the following can contribute to the problem: initial/next extent size, pctincrease - for the tables and the tablespace(s) - also the pctfreee, pctused, avg size of row, db_blocksize can cause problems.

 

[rtefft]

Raygg -
The reason that I usually leave the PK index on is that it makes it easier to identify the dup rows before they get into the table. You can use the EXCEPTIONS INTO clause to ID them later at PK creation time if you prefer. I'm used to using PL/SQL where I write an error handler up front. Since Hind is using VB, you are probably right that dropping the PK would be better.

I might suspect the VB/Oracle interface (ODBC?) to be the cause, as some ODBC drivers are terribly slow. However, since the performance was fine until a certain point then dropped off, I doubt this is the case.

I missed your inital "Indexes" reply when I wrote mine. Apologies for duplication.

R ____________________________
Rich Tefft
PL/SQL Programmer

 

[Guest_imported]

Hi,

If you are inserting one row at a time the performance will be very slow. Consider the following bulk insert.

loop
str = "insert string ;"
if ncnt = 1000 then
conn.execute "begin str end;"
str = ""
end if
ncnt = ncnt + 1
end loop
if ncnt > 0 then
conn.execute "begin str end;"
conn.committrans
end if
This will improve performance.

If you want an dramatic performance improvement, then do not code it in VB, use oracle UTL_FILE package to do it. This is more than 10 times faster than coding it in front-end.

Regards
Vinod