Home » SQL & PL/SQL » Client Tools » Is it Hung ? (Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production)
Is it Hung ? [message #327750] Tue, 17 June 2008 08:02 Go to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hi,

I had started running a package around 2 hours ago in SQL navigator (Release 5.0.0.607), then whenh i go and check in Toad, it shows that it is running (please see attachment), but then the "ROWS", "Bytes", "Cost", etc columns in the explain plan in toad is showing null, does this indicate that it is in a hung state ?
  • Attachment: 1.JPG
    (Size: 132.84KB, Downloaded 184 times)
Re: Is it Hung ? [message #327788 is a reply to message #327750] Tue, 17 June 2008 10:50 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
No, it does just indicate that it is still running. It MAY be waiting for something.

You can try this query in another session, to shows what this session might be waiting for :


/* query to show current waits */
select DISTINCT s.sid, event, p1text, p1, p2text, p2, 
       p3text, p3, wait_time, seconds_in_wait, state,
       osuser, program,client_info, s.MODULE, sql_text
  from v$session_wait e
  join v$session s on s.sid = e.sid
  join v$sql q ON q.hash_value = s.sql_hash_value
 where event not in 
       ('SQL*Net message from client', 
        'SQL*Net message to client', 
        'jobq slave wait');
icon8.gif  Re: Is it Hung ? :( [message #327842 is a reply to message #327788] Wed, 18 June 2008 03:26 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Quote:
/* query to show current waits */
select DISTINCT s.sid, event, p1text, p1, p2text, p2,
p3text, p3, wait_time, seconds_in_wait, state,
osuser, program,client_info, s.MODULE, sql_text
from v$session_wait e
join v$session s on s.sid = e.sid
join v$sql q ON q.hash_value = s.sql_hash_value
where event not in
('SQL*Net message from client',
'SQL*Net message to client',
'jobq slave wait');


The results are attached , is it waiting for any lock to release ?

Is my prblem related to this http://http://blog.tanelpoder.com/2007/06/18/advanced-oracle-troubleshooting-guide-when-the-wait-interface-is-not-enough-part-1/
  • Attachment: data.csv
    (Size: 1.14KB, Downloaded 170 times)

[Updated on: Wed, 18 June 2008 03:43]

Report message to a moderator

Re: Is it Hung ? :( [message #327855 is a reply to message #327842] Wed, 18 June 2008 04:02 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, in the one row you posted it is waiting for a direct path write into file number 203 to finish.

Run it multiple times every few seconds, to see if the thing that it waits on stays the same or changes.
Re: Is it Hung ? :( [message #327863 is a reply to message #327855] Wed, 18 June 2008 04:10 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Quote:
if the thing that it waits on


How to find out which is this "thing" ?
Re: Is it Hung ? :( [message #327867 is a reply to message #327863] Wed, 18 June 2008 04:15 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
With the query I gave you. In the CSV it clearly states, that it was waiting for a direct path write into file number 203.

Of course you have to actually READ the result.
Re: Is it Hung ? :( [message #327881 is a reply to message #327867] Wed, 18 June 2008 04:40 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
It is actually waiting on the same thing,
only the sequence numebr is increasing
Re: Is it Hung ? :( [message #327885 is a reply to message #327881] Wed, 18 June 2008 04:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
OK, then it is waiting for writes into that file to finish all the time.

You can do an

SELECT * FROM DBA_DATA_FILES WHERE file_id = 203;


to see WHICH of the database files it is.

If it's a file in the temporary tablespace, then the select that is causing the wait hasn't got enough RAM so it has to use disk space. Then either you need to make more RAM available to the database, or the select needs to be changed/tuned.


Re: Is it Hung ? :( [message #327889 is a reply to message #327885] Wed, 18 June 2008 04:56 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
There is no entry with file_id = 203, there are entries for file_id's from 1 to 22 !!
Re: Is it Hung ? :( [message #327893 is a reply to message #327889] Wed, 18 June 2008 05:07 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Was the file number in the result of my query actually 203 then, or did it maybe just get distorted in the Excel file you posted?

Also, I forgot that if it is a temp file it will actually show up in DBA_TEMP_FILES not in DBA_DATA_FILES, so additionally try :

SELECT * FROM DBA_TEMP_FILES WHERE file_id = 203
Re: Is it Hung ? [message #329013 is a reply to message #327750] Mon, 23 June 2008 13:35 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
These people are a lot smarter than I. They have get more information about a process doing work that I can get.

That said, I have some simple queries I use to figure out if a job is hung. First I figure out the session my job is tied to (this can require some doing in itself). Once I know the session or user doing the work then I run variations of these queries below to find out:

1) is a query doing any work (increasing numbers = doing work)
2) is an insert/update/delete moving forward or rolling back (see used_urec, increase = forward, decreasing = rollback)

Once it is determined that there is nothing happening, you can use some of the fine suggestions posted by others here to see if you are locked, waiting, or whatever.

Good luck, Kevin

select *
from v$sess_io
where sid in (
              select sid
              from v$session
              where username = upper('&&1')
             )
/

select *
from v$sess_io
where sid = &&1
/

select  s.username, rn.name,     rs.curext
       ,rs.curblk,  t.used_ublk, t.used_urec
from    v$transaction   t
       ,v$session       s
       ,v$rollname      rn
       ,v$rollstat      rs
where  t.addr     = s.taddr
and    t.xidusn   = rn.usn
and    rn.usn     = rs.usn
/


desc v$sess_io
desc v$session
desc v$transaction
desc v$rollname
desc v$rollstat
Previous Topic: RPC connection error
Next Topic: I can't compile or execute any procedures anymore.
Goto Forum:
  


Current Time: Fri Dec 09 07:43:48 CST 2016

Total time taken to generate the page: 0.09271 seconds