OraFAQ Forum: Performance Tuning » Is there a way to find out a killed session associated sql

Home » RDBMS Server » Performance Tuning » Is there a way to find out a killed session associated sql (11.2.0.2,SE windows 2008 r2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Is there a way to find out a killed session associated sql [message #652566]

Tue, 14 June 2016 03:20

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear all,

while I was executing ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; my statement hangs

upon googling https://community.oracle.com/thread/2191134?tstart=0

I kill the holder session, is there a way to find out the sql that is associate with that session that is killed by me?

I did install statspack, not too sure whether it will help me or not

thanks a lot

Report message to a moderator

Re: Is there a way to find out a killed session associated sql [message #652568 is a reply to message #652566]

Tue, 14 June 2016 03:25

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

You could quiesce the database,
http://www.skillbuilders.com/Oracle/Oracle-Consulting-Training.cfm?category=blogs&tab=john-watsons-blog&node=2943

Report message to a moderator

Re: Is there a way to find out a killed session associated sql [message #652569 is a reply to message #652566]

Tue, 14 June 2016 03:27

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

Unless you've got auditing of selects on a doubt it. Statspack aggregates SQL across sessions so won't be much help.
Really you needed to see what the other session was doing before you killed it.
Also the SQL it was running at the point you killed it might not be the SQL that generated the lock that was blocking you since all locks are held for the duration of a transaction and the transaction may well be multiple SQL statements.

Report message to a moderator

Re: Is there a way to find out a killed session associated sql [message #652570 is a reply to message #652566]

Tue, 14 June 2016 03:28

Frank Naude
Messages: 4596
Registered: April 1998

Senior Member

If you have the SID, you can get the 'sql_hash_value' from v$session and relate it to the v$sql% views. for example:

Code: [Select all] [Show/ hide]

select sql_text
from   sys.v_$sqltext_with_newlines
where  hash_value = &&1

Report message to a moderator

Re: Is there a way to find out a killed session associated sql [message #652571 is a reply to message #652570]

Tue, 14 June 2016 03:34

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

Isn't it a bit too late to do that once the session is dead?

Report message to a moderator

Re: Is there a way to find out a killed session associated sql [message #652572 is a reply to message #652571]

Tue, 14 June 2016 03:38

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

I feel like there's another question under this one. Why do you want to know this? The end user will have an error thrown so would know anyway?

Report message to a moderator

Re: Is there a way to find out a killed session associated sql [message #652587 is a reply to message #652572]

Tue, 14 June 2016 06:11

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

fortunately I will not have another question,

because my CIO ask me the session details, but then i kill the session already. what to do? next time I got to stay calm and not panic.

thanks all for your answers

Report message to a moderator

Previous Topic:	Performance degrade after migration from 11g exadata to 12c
Next Topic:	How to make sure no archivelog being generated for a particular table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 00:19:00 CDT 2025