-
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.
John Doyle
-
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.
John Doyle
-
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.
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|