Home » Other » 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  |
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 885 times)
|
|
|
|
| Re: Is it Hung ? [message #327788 is a reply to message #327750] |
Tue, 17 June 2008 10:50  |
ThomasG
Messages: 3211
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');
|
|
|
|
 Re: Is it Hung ? :( [message #327842 is a reply to message #327788] |
Wed, 18 June 2008 03:26 |
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 1343 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 |
ThomasG
Messages: 3211
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 #327867 is a reply to message #327863] |
Wed, 18 June 2008 04:15 |
ThomasG
Messages: 3211
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 #327885 is a reply to message #327881] |
Wed, 18 June 2008 04:47 |
ThomasG
Messages: 3211
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 #327893 is a reply to message #327889] |
Wed, 18 June 2008 05:07 |
ThomasG
Messages: 3211
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 |
 |
Kevin Meade
Messages: 2103
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 |
|
|
|
Goto Forum:
Current Time: Fri Apr 19 13:12:11 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
