Insert hangs???

[Naeem]

Hello Gurus,

We are using a tool called Marketic one to process letters.
In the beginning it inserts in a table. The insertion seems to hangs. It is been 2 hours it is doing nothing.
I have looked for the locks there is no locks on the table.
My database is in Archive log mode.
DB version is oracle 8.1.7.4
operating system is Win2000 advanced server
Can somebody guide me where I have to look for to find a solution?

Thanks

[adrianomp]

Is just the INSERT hung? Can you do other operations on other sessions? If you have not enabled automatic archival, the entire instance will hang until you archive logs manually (although I don't think there is a good chance of that happening on a production database without being noticed before).

Hard to guess without further details.

[gbrabham]

Do the Alert logs tell you anything ??

Gregg

[jodie]

Have you looked to see what the session is waiting for?

Here are a couple of queries that may help (need to provide the sid)

col minutes for 999.999
select event, total_waits, (time_waited/100)/60 Minutes, average_wait, max_wait
from v$session_event
where sid = &sid
AND event NOT LIKE 'SQL*Net%'
/


SELECT sw.sid, s.username, sw.event, sw.wait_time,
sw.state, sw.seconds_in_wait SEC_IN_WAIT
FROM v$session s, v$session_wait sw
WHERE s.username IS NOT NULL
AND sw.sid = s.sid
AND sw.event NOT LIKE '%SQL*Net%'
/


HTH,
Jodie

[Naeem]

Last time something was written in alert log was at 12.20 today afternoon
here is the log

Current log# 4 seq# 4163 mem# 0: H:\REDO\LOG4.ORA
Thu Jul 03 12:23:27 2003
ARC3: Beginning to archive log# 6 seq# 4162
Thu Jul 03 12:24:42 2003
ARC3: Completed archiving log# 6 seq# 4162

There is one process called WIStorageManage takes lot of system resource. Does anybody knows what this process is?

Only Insert hungs. I am able to do select on the tables.
How can I find out there is a automatic archive is going on???

Thanks

[Naeem]

Here is the reply for your second query

SID USERNAME EVENT WAIT_TIME STATE SEC_IN_WAIT
31 MOPROD db file scattered read -2 WAITED UNKNOWN TIME 80

Does this say anything to you.

[adrianomp]

How can I find out there is a automatic archive is going on???

You can check it by two ways, basically:
- Type "archive log list" on SQL*Plus when connect as sysdba
- Your database will hang when a log switch try to overwrite an unarchived redo log.

That's not your case. I'd bet it's a lock issue, but since you told us there's nothing and the database is running fine, you may check if it is something related to the application.

[jodie]

The insert is waiting on IO for a FULL table scan.

The following query will tell you the file#, block# and blocks.

select sid, event, P1text, p1, p2text, p2, p3text, p3
from v$session_wait
where sid=&sid
and event not like '%SQL%' and
event not like '%rdbms%';


Then put the results in:

select owner, segment_name, segment_type, tablespace_name
from dba_extents
where file_id=&fileid_in
and &blockid_in between block_id and Block_id + Blocks -1;


Jodie

[jodie]

Also, the STATE = WAITED_UNKNOWN_TIME because timed_statistics is set to false. Set TIMED_STATISTICS=TRUE, and you'll get acurate timing.

Jodie

[Naeem]

What should I do If my database hangs? Do I have to re-start the database?