Insert hangs???

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

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.

Do the Alert logs tell you anything ??

Gregg

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

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

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.

Quote:

---

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

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.

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

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

Jodie

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

What do you mean hung? Can you log in as sysdba?

Did you issue "archive log list" as adrianomp suggested?

Jodie,

Thanks for your reply. I have 7 table names. But the table where I am doing insert is not in the list.
What should I do

Quote:

---

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

---

If your database hung due to an archival pending, you can archive manually and database gets back to normal. Again, that's not your case since other operations are running and your alert log shows sucessful archives.

I think we're losing focus here. Based on what you said:

Quote:

---

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.

---

Doing NOTHING in 2 hours smells "lock". If it is doing anything, but doing slowly, you may check various types of waits and performance issues. Also, it's possible that the application is waiting for some input or in a infinite loop state.

Here is the reply when i do the archive log list command


Database log mode Archive Mode
Automatic archival Enabled
Archive destination O:\MOPROD
Oldest online log sequence 4160
Next log sequence to archive 4163
Current log sequence 4163

So the automatic archival is enabled

Adriano - the waits indicate that it is waiting on DB File Scattered Read. If it was a lock, wouldn't it be waiting on an Enqueue? Please correct me if I'm wrong.

If the table you are inserting into isn't in the list, my hunch is something else is going on. Do you have a trigger on the table you are inserting into? If so, you could be modifying other objects.

If you want to know what the SQL is that the query is executing, you can use:

SELECT osuser, address, piece, b.sql_text
FROM v$session a, v$sqltext b
WHERE a.sql_address = b.address
AND sid=&sid
ORDER BY osuser, address, piece;

You may want to execute several times, to see if the SQL is changing or it's stuck on one statement.


Jodie

There is no trigger in this table.
I am watching from toad the query is the same since 18h30

In toad i see the RBS usage,
The consistent gets, physical IO, and undoblocks used are changing every second.
This means it is inserting in the table is it not!!!

Naeem,
Well, something is using the rollback, probably your insert.

If your session is still waiting on DBFile Scattered Read, I'd guess it's an IO problem. Is there contention? There could be many causes.

Maybe someone else has an idea???

In the mean time, you could set Event tracing on to get a trace file. It may give you more information.

Here is some code (copied from Oracle Performance Tuning 101):

Code:

---

select s.username, p.spid
  from v$session s v$process p
 where s.paddr = p.addr
  and s.sid=&sid

alter system set timed_statistics=true;

alter system set max_dump_file_size=unlimited;

oradebug setospid=SPID_from_above

oradebug unlimit

oradebug event 10046 trace name context forever, level 8

---

Trace for some time, then look for the trace file (using the spid) in the user_dump_dest). Scan the file for all lines that begin with WAIT

HTH...
Jodie

Thanks Jodie,

It is IO problem. The DBFile is 148 GB. The data are pletely dispersed. I am recreating the table in a different tablespace in a differnt partition(with RAID 1).
Hope this will workout.

Thanks all of you, It was of great help.

Quote:

---

Originally posted by Naeem
The DBFile is 148 GB.

---

Single file and 148GB..r u sure?

Does WIN2000 Server suport such big file?

Abhay.