Database hanging during insert
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Database hanging during insert

  1. #1
    Join Date
    Jan 2001
    Posts
    16
    Hi ,


    I'm doing a large insert (about 50 million rows) into a table . The system seems to hang. I cant see it doing anything. I checked v$session_wait ? Rollback segments seems to be adequate enough.

    Could you tell me what is happening ? and how do I rectify this.

    Thanks a lot

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Are you commiting after x thousand rows?? Check the network. Check for db hanging issues on Metalink also.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    what does v$session_wait show?

  4. #4
    Join Date
    Jan 2001
    Posts
    16
    select sid,EVENT ,WAIT_TIME,SECONDS_IN_WAIT,STATE from v$session_wait;

    SID EVENT WAIT_TIME SECONDS_IN_WAIT STATE
    ---------- ---------------------------------------------------------------- ---------- --------------- -------------------
    1 pmon timer 0 14636 WAITING
    2 rdbms ipc message 0 9 WAITING
    3 rdbms ipc message 0 0 WAITING
    6 rdbms ipc message 0 316 WAITING
    4 rdbms ipc message 0 3 WAITING
    9 slave wait 0 9 WAITING
    14 slave wait 0 12 WAITING
    16 slave wait 0 12 WAITING
    18 slave wait 0 9 WAITING
    17 slave wait 0 12 WAITING
    15 slave wait 0 12 WAITING

    SID EVENT WAIT_TIME SECONDS_IN_WAIT STATE
    ---------- ---------------------------------------------------------------- ---------- --------------- -------------------
    13 slave wait 0 12 WAITING
    10 slave wait 0 9 WAITING
    11 slave wait 0 9 WAITING
    12 slave wait 0 9 WAITING
    19 db file sequential read 0 0 WAITING
    5 smon timer 0 226 WAITING
    23 SQL*Net message to client -1 0 WAITED SHORT TIME
    24 SQL*Net message from client 0 641 WAITING
    26 SQL*Net message from client 0 27 WAITING
    28 SQL*Net message from client 0 15 WAITING
    29 SQL*Net message from client 0 5528 WAITING


    I'm sorry, but I cant make out what this is saying.

    Thanks for the help.

  5. #5
    Join Date
    Oct 2000
    Posts
    80
    Deadlock?
    Check your alert log.
    John Doyle

  6. #6
    Join Date
    Jan 2001
    Posts
    16
    it doesnt show any errors as such. It shows this :Wed Jan 31 16:28:22 2001
    kccrsz: expanded controlfile section 9 from 1588 to 1815 records
    requested to grow by 83 record(s); added 1 block(s) of records
    Thread 1 advanced to log sequence 13180
    Current log# 4 seq# 13180 mem# 0: /oracle2/oradata/totstats/redo_logs/log07.log
    Current log# 4 seq# 13180 mem# 1: /oracle/oradata/totstats/redo_logs/log08.log
    Wed Jan 31 16:31:48 2001
    Thread 1 advanced to log sequence 13181
    Current log# 5 seq# 13181 mem# 0: /oracle2/oradata/totstats/redo_logs/log09.log
    Current log# 5 seq# 13181 mem# 1: /oracle/oradata/totstats/redo_logs/log10.log
    Wed Jan 31 16:35:46 2001
    Thread 1 advanced to log sequence 13182
    Current log# 6 seq# 13182 mem# 0: /oracle2/oradata/totstats/redo_logs/log11.log
    Current log# 6 seq# 13182 mem# 1: /oracle/oradata/totstats/redo_logs/log12.log
    Wed Jan 31 16:40:03 2001
    Thread 1 advanced to log sequence 13183
    Current log# 1 seq# 13183 mem# 0: /oracle2/oradata/totstats/redo_logs/log01.log
    Current log# 1 seq# 13183 mem# 1: /oracle/oradata/totstats/redo_logs/log02.log
    Wed Jan 31 16:44:14 2001
    Thread 1 advanced to log sequence 13184
    Current log# 2 seq# 13184 mem# 0: /oracle2/oradata/totstats/redo_logs/log03.log
    Current log# 2 seq# 13184 mem# 1: /oracle/oradata/totstats/redo_logs/log04.log
    Wed Jan 31 16:48:04 2001


  7. #7
    Join Date
    Jan 2001
    Posts
    16
    is there any way I can stop this ?

  8. #8
    Join Date
    Oct 2000
    Posts
    80
    Stop the insert?
    Yes, kill the session. You need to find the SID and serial# from v$session and issue:
    ALTER SYSTEM KILL SESSION(SID, SERIAL#);

    Pretty drastic though, considering the size of your insert. Be sure its really hung up.
    John Doyle

  9. #9
    Join Date
    Jan 2001
    Posts
    16
    Should I just wailt for it to complete ? It doesnt seem to be doing anything.
    Is there anything else I can do?
    Please advice.

  10. #10
    Join Date
    Oct 2000
    Posts
    80
    If you have a lock contention issue going on, then another user may have a lock on data that your insert is trying to acquire. Using V$LOCK in conjuntion with v$session may tell you what user is hanging you up. If you determine this is the problem, it may be better to kill that user's session rather than your own. Some other ideas. Do the insert at night when transaction volume is low. Or your business situation may allow you to open the database restricted and do the insert.
    Also, explicity assign the transaction to a large rollback segment and make sure this RB segment can grow to accomodate the entire transaction. Or write a procedure to commit at intervals.
    John Doyle

Posting Permissions

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



Click Here to Expand Forum to Full Width