Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2006
    Posts
    8

    Unanswered: 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.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >you cannot do anything else with the database until the insert process finishes.

    I am will to bet you that the statement above is FALSE;
    & I am willing to give you 100 to 1 odds.
    With Oracle, readers do not block writers & writers do not block readers.

    >Furthermore, sometimes the database crashes during the insert and needs to be restarted.
    Why do the crashes occur?
    What are the contents of the crash trace files.


    >My first suspect is database locking problem, but what do you think?
    I know this wild guess is wrong.

    >Will changing the lock mode to shared alleviate the problem?
    Who knows?

    >How about commit behaviour?
    I don't understand this question.
    Either a COMMIT happens or it does not happen.
    EXACTLY how can you change COMMIT behavior?

    >What else should I check?
    I suggest that you enable SQLTRACE.

    >By the way, I haven't been touching Oracle for three years.
    Are you bragging or complaining?


    Exactly what SQL precipitate this behavior?
    What OS name & version & what Oracle version (to 4 decimal places)?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2006
    Posts
    8
    Quote Originally Posted by anacedent
    I am will to bet you that the statement above is FALSE;
    & I am willing to give you 100 to 1 odds.
    With Oracle, readers do not block writers & writers do not block readers.
    Unfortunately, it is what actually happens.


    Quote Originally Posted by anacedent
    I don't understand this question.
    Either a COMMIT happens or it does not happen.
    EXACTLY how can you change COMMIT behavior?
    COMMIT [WRITE [IMMEDIATE | BATCH] [WAIT | NOWAIT] ]


    Quote Originally Posted by anacedent
    Are you bragging or complaining?
    The latter, obviously.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Unfortunately, it is what actually happens.
    While it may be the case that YOUR application appears to "freeze",
    I am 100% confident that you could access Oracle DB via SQL*Plus
    & SELECT anything you deem interesting.

    You're On Your Own (YOYO)!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As you've said that application has been created using Forms Developer ("pressing the "Save" button"), what code stands behind that button?

    Furthermore, where do you take these records from and how exactly do you insert them into a database? As you didn't provide much information (database and Developer Suite versions, code, ...), this might be a blind shot: can you avoid INSERT SQL statement and use SQL*Loader?

    Moreover:
    Anacedent > With Oracle, readers do not block writers & writers do not block readers.
    KAN > Unfortunately, it is what actually happens.
    Most probably not, as unless "readers" used SELECT ... FOR UPDATE. Oracle 10g Concepts, How Oracle locks data section:
    Readers and writers do not block one another in Oracle.
    However, as you are INSERTING data, these records did not exist before that action so "readers" can not read what does not exist and, therefore, I believe that no lock is possible in this case.

    Quote Originally Posted by KAN
    you cannot do anything else with the database until the insert process finishes.
    Probably not true either (as Anacedent has already said). You most probably can't use that form, exit it and do something else in that Forms session. If you, however, log on on another computer or simply open SQL*Plus session or even another Forms application using another browser (if it is a web application), you'll be able to use your database.

    So ... could you provide some more information so that someone could assist you to solve the problem?

  6. #6
    Join Date
    Feb 2006
    Posts
    8
    Quote Originally Posted by Littlefoot
    Moreover:Most probably not, as unless "readers" used SELECT ... FOR UPDATE. Oracle 10g Concepts, How Oracle locks data section:However, as you are INSERTING data, these records did not exist before that action so "readers" can not read what does not exist and, therefore, I believe that no lock is possible in this case.
    Actually they use INSERT...SELECT, although it doesn't matter because the selected rows are from other table. So you're right, it is not about locking. Unfortunately I don't have the query handy right now, but thanks!

    Quote Originally Posted by Littlefoot
    So ... could you provide some more information so that someone could assist you to solve the problem?
    Will do. Thanks again, I'll be back with more information.

  7. #7
    Join Date
    Jan 2009
    Posts
    2
    Dear Kan

    i am 50% sure this the rollback segment problem. Increase the rollback segment size or do the cmmit after 1000 record. :-)

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by omer ahsan
    do the cmmit after 1000 record.
    That would probably be one of the worst solutions, as it might lead to the snapshot too old error. Commit is to be done once, at the end of the transaction.

  9. #9
    Join Date
    Feb 2006
    Posts
    8
    Quote Originally Posted by omer_ahsan
    i am 50% sure this the rollback segment problem.
    THANKS! Now I remember! I don't exactly remember the details of the case (it happened years ago), but I remember where I increase rollback segment size to solve similar problem.


    Quote Originally Posted by Littlefoot
    That would probably be one of the worst solutions, as it might lead to the snapshot too old error. Commit is to be done once, at the end of the transaction.
    Well, at least it still works as the very last resort, doesn't it?

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What is the last resort? Commit after ever 1000 (or any number of) records? I'd forget about it if I were you. Though, if you enjoy opening new topics, here's a suggestion for the next title: "How to resolve the ORA-01555 error?".

  11. #11
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by KAN
    Well, at least it still works as the very last resort, doesn't it?
    No. Frequent commits are never a solution in Oracle.
    It will make things worse.

    Read this discussion on asktom.com

    http://asktom.oracle.com/pls/asktom/...:4951966319022

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •