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)
Is there a way to find out a killed session associated sql [message #652566] Tue, 14 June 2016 03:20 Go to next message
juniordbanewbie
Messages: 217
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
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 Go to previous messageGo to next message
John Watson
Messages: 7618
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
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 Go to previous messageGo to next message
cookiemonster
Messages: 13279
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.
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 Go to previous messageGo to next message
Frank Naude
Messages: 4557
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:

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

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 Go to previous messageGo to next message
cookiemonster
Messages: 13279
Registered: September 2008
Location: Rainy Manchester
Senior Member
Isn't it a bit too late to do that once the session is dead?
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 Go to previous messageGo to next message
Roachcoach
Messages: 1571
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?
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 Go to previous message
juniordbanewbie
Messages: 217
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
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:
  


Current Time: Wed Oct 17 21:52:02 CDT 2018