Home » SQL & PL/SQL » SQL & PL/SQL » How to find out if a session is hung? (Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production)
How to find out if a session is hung? [message #327849] Wed, 18 June 2008 03:52 Go to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hi,

I have been running a package, but i guess it is hung or waiting for something, i have come to this conclusion because it is been almost 2 hours and the session is shown as active but it is not proceeding further..

Can anyone suggest to me how to find out what is it waiting for ?
Re: How to find out if a session is hung? [message #327850 is a reply to message #327849] Wed, 18 June 2008 03:54 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

See the V$session

kanish
Re: How to find out if a session is hung? [message #327853 is a reply to message #327850] Wed, 18 June 2008 03:58 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
ok, but there are so many columns there in v$session, which ones to look out for ?
Re: How to find out if a session is hung? [message #327856 is a reply to message #327849] Wed, 18 June 2008 04:03 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

status

Re: How to find out if a session is hung? [message #327861 is a reply to message #327849] Wed, 18 June 2008 04:08 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
The below query

select * from v$session_wait
where sid = 14 and event not in 
       ('SQL*Net message from client', 
        'SQL*Net message to client', 
        'jobq slave wait');


gave me the below output

PLEASE see attachment
Re: How to find out if a session is hung? [message #327862 is a reply to message #327856] Wed, 18 June 2008 04:10 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Check your temp tablespace usauge.This can be the reason why your package just hangs.

Regards,
Rajat Ratewal
Re: How to find out if a session is hung? [message #327864 is a reply to message #327849] Wed, 18 June 2008 04:14 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

use below query to know status of the session

select * from v$session where sid = 14


kanish
Re: How to find out if a session is hung? [message #327868 is a reply to message #327862] Wed, 18 June 2008 04:15 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Status of session is active

Quote:
Check your temp tablespace usauge.


Please guide me as to how i can check this ?
Re: How to find out if a session is hung? [message #327870 is a reply to message #327864] Wed, 18 June 2008 04:17 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
select * from v$session where sid = 14


gives me the session as "ACTIVE"
Re: How to find out if a session is hung? [message #327872 is a reply to message #327868] Wed, 18 June 2008 04:18 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Fire these queries and share the results

SELECT   
	 A.tablespace_name TABLESPACE,
         D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP BY A.tablespace_name, D.mb_total


SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.MODULE,
         S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.TABLESPACE,
         COUNT(*) sort_ops
FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
AND      T.TABLESPACE = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.MODULE,
         S.program, TBS.block_size, T.TABLESPACE
ORDER BY sid_serial


SELECT   S.sid || ',' || S.serial# sid_serial, S.username,
         T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.TABLESPACE,
         T.sqladdr address, Q.hash_value, Q.sql_text
FROM     v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE    T.session_addr = S.saddr
AND      T.sqladdr = Q.address (+)
AND      T.TABLESPACE = TBS.tablespace_name
ORDER BY S.sid


SELECT   b.TABLESPACE, b.segfile#, b.segblk#, b.blocks, a.SID, a.serial#,
         a.username, a.osuser, a.status
    FROM v$session a, v$sort_usage b
   WHERE a.saddr = b.session_addr
ORDER BY b.blocks, b.TABLESPACE


Regards,
Rajat Ratewal
Re: How to find out if a session is hung? [message #327875 is a reply to message #327872] Wed, 18 June 2008 04:28 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
jagannathkiran why you created seperate topic for the problem that you had yesterday.

I think this is the session executing that package which takes 45 minutes??

Regards,
Rajat Ratewal
Re: How to find out if a session is hung? [message #327877 is a reply to message #327872] Wed, 18 June 2008 04:31 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
PLEASE see attachment for the results of above query.

I have tried my best to keep the formatting good in the attachment
  • Attachment: results.txt
    (Size: 12.51KB, Downloaded 163 times)
Re: How to find out if a session is hung? [message #327878 is a reply to message #327875] Wed, 18 June 2008 04:33 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Quote:
I think this is the session executing that package which takes 45 minutes??



Yeah, I am extremely sorry for that !!
Re: How to find out if a session is hung? [message #327883 is a reply to message #327849] Wed, 18 June 2008 04:43 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
I wonder whether it is a deadlock !!
Re: How to find out if a session is hung? [message #327888 is a reply to message #327883] Wed, 18 June 2008 04:50 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

I wonder whether it is a deadlock !!



Not likely. Any locks from other sessions should also show up in the waits query I gave you earlier.
Re: How to find out if a session is hung? [message #327890 is a reply to message #327888] Wed, 18 June 2008 05:00 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
check this link-

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1191435335912

Regards,
Rajat Ratewal
Re: How to find out if a session is hung? [message #327895 is a reply to message #327890] Wed, 18 June 2008 05:17 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hmmm so any idea what seems to be the problem ?? Sad

[Updated on: Wed, 18 June 2008 05:19]

Report message to a moderator

Re: How to find out if a session is hung? [message #327896 is a reply to message #327895] Wed, 18 June 2008 05:26 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
jagannathkiran their can be many number of situations.

1) Temp Tablespace problem.
2) Locks
3) Datafile contention.
4) Query is not TUNED.Taking lot of time.

and so on...

you have to check all these.
you better get your DBA with you.

And please share the results if you find the reason because i also faces this kind of issues.

In my case it was temp tablespace problem.

Regards,
Rajat Ratewal
Re: How to find out if a session is hung? [message #327897 is a reply to message #327896] Wed, 18 June 2008 05:53 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Yeah I shall surely get back once the problem is resolved.

We can rule the below option out, because the same package ATLEAST runs to completion though it takes 40 minutes !!

Quote:
4) Query is not TUNED.Taking lot of time.

Re: How to find out if a session is hung? [message #327900 is a reply to message #327897] Wed, 18 June 2008 06:07 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
jagannathkiran wrote on Wed, 18 June 2008 05:53

Quote:

We can rule the below option out, because the same package ATLEAST runs to completion though it takes 40 minutes !!



No you can't because this is root cause of the problem.

Regards,
Rajat Ratewal
Re: How to find out if a session is hung? [message #327903 is a reply to message #327900] Wed, 18 June 2008 06:13 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Well But the same package is running QA environment in 40 minutes
Re: How to find out if a session is hung? [message #327911 is a reply to message #327903] Wed, 18 June 2008 06:27 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
jagannathkiran wrote on Wed, 18 June 2008 06:13

Quote:

Well But the same package is running QA environment in 40 minutes



Your QA environment is not same as your Testing enviroment.

This information you still have not provided.

If package is executing in 40 minutes??
Then it sound good to your ears but not mine.

In your package you must have written some SQL statements that is taking lot of time to execute.

Have you ever checked Explain Plan for your query you have written inside your package.Have you checked what resources each Sql statement consumes.What are the values for(physical gets,consistent gets,Cost of query,execution time etc.)

Regards,
Rajat Ratewal
Re: How to find out if a session is hung? [message #327918 is a reply to message #327911] Wed, 18 June 2008 06:36 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Oh No, I apologize for that, yeah my QA and TEST environment are the same.

I have not tried SQL PLAn as yet, but i have found out the block which takes most time, i have attached the query along.

Re: How to find out if a session is hung? [message #327924 is a reply to message #327918] Wed, 18 June 2008 06:46 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Please provide this information:-

1) How much time this query takes.
2) Explain Plan
3) Physical reads and consistent gets
4) Count of rows in each table referenced in query.
5) Stats are gathered or not on tables.

Regards,
Rajat Ratewal
Re: How to find out if a session is hung? [message #327931 is a reply to message #327924] Wed, 18 June 2008 07:05 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
ALL the below apply in QA environment where the query runs successfully

1. This Query takes 34 minutes to run in QA

2. (I could not turn on SQL PLAN because the DBA said that it would create too much of logs and there was insufficient disk space), but i tried to copy a snapshot of the SQL PLAN from TOAD Please see attachement

3. I could not get this because SQL PLAN was not turned ON

4. see attachment

5. I assume "stats" = statistics, I guess that is not gathered, How is it done actually ?
  • Attachment: sql plan.txt
    (Size: 2.75KB, Downloaded 126 times)

[Updated on: Wed, 18 June 2008 07:07]

Report message to a moderator

Re: How to find out if a session is hung? [message #327938 is a reply to message #327931] Wed, 18 June 2008 07:14 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
That was i am trying to say??

34 Minutes to run a single SQL statement.

Tune this query you will get rid of your hanging session issue.

Hints:-

1) Why are you picking this large amount of data from AV_RESREQ
table.
Is this actually required??

you have applied DISTINCT after joining tables.
what you think oracle do when you use joins??

2) Study DBMS_STATS package for gathering stats.

3) Get your DBA on your desk.

Regards,
Rajat Ratewal
Re: How to find out if a session is hung? [message #327943 is a reply to message #327938] Wed, 18 June 2008 07:23 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
And one more thing you said that QA and testing environment is same.

NO i don't agree with you.

Your user is same.Data is same.

But the QA server capacity is higher than your Testing server capacity in terms of RAM,Oracle parameters SGA etc.,Hard Disks,CPU.

Regards,
Rajat ratewal
Re: How to find out if a session is hung? [message #327945 is a reply to message #327943] Wed, 18 June 2008 07:30 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Quote:
Your user is same.Data is same.

But the QA server capacity is higher than your Testing server capacity in terms of RAM,Oracle parameters SGA etc.,Hard Disks,CPU.



Actually our QA and TEST databases are on the same server, so they are suppose to work in the same way i guess !!

hey, I have to attend the client call now, will catch up tomorrow with the updates, hope to come out with something positive
Re: How to find out if a session is hung? [message #328873 is a reply to message #327945] Mon, 23 June 2008 03:31 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
I have asked the DBA to Refresh my TEST DB from production, so i guess after this my package should run smoothly, because it ran smoothly in QA (which was refreshed from production around 2 months back)....
Re: How to find out if a session is hung? [message #330511 is a reply to message #328873] Mon, 30 June 2008 06:23 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
jagannathkiran wrote on Mon, 23 June 2008 14:01
I have asked the DBA to Refresh my TEST DB from production, so i guess after this my package should run smoothly, because it ran smoothly in QA (which was refreshed from production around 2 months back)....


The Package is running fine now after the refresh, Thanks for all your support Smile
Re: How to find out if a session is hung? [message #330526 is a reply to message #330511] Mon, 30 June 2008 06:51 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Now it taking how much time to execute.Still 45 minutes oe less.

Regards,
Rajat Ratewal
Re: How to find out if a session is hung? [message #330540 is a reply to message #330526] Mon, 30 June 2008 07:03 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Yeah Sad
Re: How to find out if a session is hung? [message #330549 is a reply to message #330540] Mon, 30 June 2008 07:23 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
So still a badly written piece of code is running in your production.

You have not solved the root cause of your problem.I am pretty sure that you will again get this hanging issue.

Start working on Tuning that SQL statement.

Regards,
Rajat Ratewal
Re: How to find out if a session is hung? [message #330737 is a reply to message #330549] Tue, 01 July 2008 01:31 Go to previous message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Well, I just hope to get it better one day !!
For now, I have been assigned another task, gotta work on that
Previous Topic: how to delete duplicate rows?
Next Topic: triggers
Goto Forum:
  


Current Time: Sat Dec 10 16:56:40 CST 2016

Total time taken to generate the page: 0.07967 seconds