|
-
Massive INSERT crashes Oracle?
I'm going to deal with an OLTP application that inserts thousands of rows into the database whenever the user presses the "save" button. The application is made using Oracle developer.
The problem is, everytime a user presses the save button (thus, inserting those rows), you cannot do anything else with the database until the insert process finishes. Furthermore, sometimes the database crashes during the insert and needs to be restarted.
My first suspect is database locking problem, but what do you think? Will changing the lock mode to shared alleviate the problem? How about commit behaviour? What else should I check? By the way, I haven't been touching Oracle for three years. :(
Last edited by KAN; 01-15-2009 at 10:32 PM.
-
-
Look at the alert log and trace the offending query.
Did you said an OLTP application is inserting "thousands of rows whenever the user presses save"?... if that's the case I would consider the application to be Batch rather than OLTP.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
 Originally Posted by PAVB
Look at the alert log and trace the offending query.
Did you said an OLTP application is inserting "thousands of rows whenever the user presses save"?... if that's the case I would consider the application to be Batch rather than OLTP.
Actually, it is supposed to be an OLTP app handling front-desk transaction. Why they choose to insert the data in batch (in contrary to per transaction) is beyond me.
By the way, I forgot to mention that the application also updates as well as inserts. Sorry.
Anyway, even when the application only inserts/updates tens of row (25 rows or such), the database became very slow if there are more than one users pressing the "save" button, especially if a user depends on the rows inserted by another user. However, if there is only one user entering the data, the database never crashes, let alone slowing down. That's why I think it is locking problem (due to bad app design) instead of database performance issue in general. A long term solution should be rebuilding the entire app, but how can changing committ behavior work as short-term solution? Let say, commit for every 1 row or such?
-
you need to stop guessing and trace the sessions, se what they are actualy doing
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote
