473,226 Members | 1,646 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

Performance bottleneck in bulk updates/inserts

I have been working on doing bulk updates and inserts through sqlapi++ using a bulk udpate extention library, developed, a long time ago, internally. The database is oracle. The bulk inserts/updates are done in batches of 50K. I am facing a very peculiar performance issue with regard to these bulk operations.

The initial batch of bulk insert (50K records) is finished within 3 seconds; while the next similar batch for bulk insert or update (50 records again) takes a whopping 373 seconds. Using 'top' on a RHEL-AS-4 server, I could see that it's oracle process that takes up the whole 373 secods for completion; so sqlapi++ or the internally developed extention is not the culprit.

The third batch of 50K records in the sequence take a lot more time (913 seconds). The time goes on increasing exponentially; and there doesnt seem to be >any< pattern out of it.

Surprisingly, this is not consistent. On a good day, I can have the seconds batch going through in 3 seconds. All records intact and perfect without any sort of defect in the data. In fact, all of the next batches would finish in or around 3 seconds.

Yet more surprisingly, if I truncate the table and start the process, the performance problem would reappear. It would again start taking 370-380 seconds for 2nd batch. Again, if I had used 'delete from' query instead of 'truncate table' query for deleteing all the records from the table, there wouldn't be any problem!

So in short, I came to conclusion that the performance bottleneck occurs when the table is truncated (or is created brand new), and not when all records are deleted using 'delete from' query.

Any guesses why it could be occuring? I confess I am not very good at databases, so any help would be very much appreciated.

Thanks in advance.

-
Shreyas
Sep 22 '08 #1
4 6492
debasisdas
8,127 Expert 4TB
you need to check for any blocking locks in the tababase.
Sep 22 '08 #2
you need to check for any blocking locks in the tababase.
i tried a 'select * from dba_waiters' and 'select * from dba_blockers'; but the views returned no rows. so i suppose there are no blocking locks, correct me if i am wrong.
i also rechecked for commits after sessions, and found they were in place. in fact, this bottleneck occurs only when the table is truncated. that is for sure.

what i dont understand is what could oracle be doing differently in truncating the table and deleting records from the table using 'delete from' query.
Sep 23 '08 #3
Dave44
153 100+
i tried a 'select * from dba_waiters' and 'select * from dba_blockers'; but the views returned no rows. so i suppose there are no blocking locks, correct me if i am wrong.
i also rechecked for commits after sessions, and found they were in place. in fact, this bottleneck occurs only when the table is truncated. that is for sure.

what i dont understand is what could oracle be doing differently in truncating the table and deleting records from the table using 'delete from' query.
when you truncate a table in oracle it removes all the allocated space from the table and puts it back into the free list. therefore the inserts need to go and request new space before it can insert. While i have seen this process slow things down some i wouldnt think that requesting enough space for 50K rows would take that long. what all is on the same I/O path that the datafiles you are using are on.

also using top to see that oracle is running a process on the oracle server doesnt really tell you a lot because if you installed the database under the linux user oracle then all the various processes will be oracle. you should (or get a dba to) perform a trace of your session so you can see what is taking all the time. AWR reports can also be a good indicator as to where all your time is being taken up.
Sep 25 '08 #4
yes, i had tried truncate with space reuse.

anyways, the problem has been resolved. if you are interested, you can have a look at this thread on OTN -
Performance bottleneck in bulk updates-inserts
Sep 25 '08 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Kiran Dalvi | last post by:
Hi, Does anybody know how to update Bulk Data into Oracle Database, efficiently ? {e.g. 1000+ rows in a table need to modified / Added, with Data coming in the form of a XML File.} Please...
10
by: Alex Gerdemann | last post by:
Hello, I have spent a bunch of time converting a Java program I wrote to C++ in order to improve performance, and have found that it is not necessarily faster. Specifically, I'm writing a...
1
by: SPG | last post by:
Hi, We have some datasets that are not linked to any form of database, it is just a means for us to pass info about. We need to perform an update on this dataset, based on a filter. Just like...
1
by: yaron | last post by:
Hi, I am looking for tools for detecting performance bottleneck and deadlocks in c# application. Thanks.
3
by: Glenn | last post by:
I have a performance issue that needs resolving, and am not sure which options we have come up with are the best. Let me explain. Our site has a report designer that allows users to create...
1
by: Jeff | last post by:
Okay, I know there is likely no straght-forward way to get a definitive answer to the question, "why is my page loading so slowly tonight?"; but I'm thinking that there *are* some things we can...
4
by: T.H.N. | last post by:
I'm trying to work out a database design to make it quicker for my client program to read and display updates to the data set. Currently it reads in the entire data set again after each change,...
0
by: samindra | last post by:
I need to Add & update 100 plus users info daily basis. Right now I am doing one at a time through the vendors GUI interface. I need to do this process with bulk additions & bulk updates. My db...
3
by: akdemirc | last post by:
i have a problem with large data import to a db in sql server.. Actually i have an application that collects data from an environment and dispatches this data to different csv files for sql server to...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.