Database hanging during insert

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

Are you commiting after x thousand rows?? Check the network. Check for db hanging issues on Metalink also.

what does v$session_wait show?

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.

Deadlock?
Check your alert log.

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

is there any way I can stop this ?

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.

Should I just wailt for it to complete ? It doesnt seem to be doing anything.
Is there anything else I can do?
Please advice.

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.

Noboby else is using this data.

I'm kinda out of ideas for you. You could kill the session and retry the job, but as they say insanity is doing the same thing over and over again and expecting a different result.

How do you know that, it is not doing anything. Open another SQL session and count the number of rows in that table. If it is still hanging, may be you need to kill the session. You will be able to see those rows if you are committing for x number of rows.

Assuming that you are running archivelog mode:

Check to see if your archive log directory is full. (df -k .)
make sure you have enough space available to create additional files.

The view v$log_history should tell you the frequency of log switch
if you are generating too many log files it is possible that your archiver
process is unable to keep up with the load. If you check the v$log
and see all of them as active or current you should consider either
increasing the size of your redo logs or create more redo groups.

Hope it helps.


Thanks