Home » RDBMS Server » Server Administration » LAST SQL RAN BEFORE KILLING A SESSION (Oralce 11g Rel 2)
LAST SQL RAN BEFORE KILLING A SESSION [message #615333] Mon, 02 June 2014 10:42 Go to next message
azeem87
Messages: 116
Registered: September 2005
Location: dallas
Senior Member
Hi,

we have an code issue which we know and working to fix that as it's vendor + inhouse code,
for time being till that fix is done we need to avoind an issue of too many inactive connections, where connections are not being closed by APP code,
I have this SQL to kill the session since we know from which machine they are coming from,
can i have a SQL that will show the last ran SQL from these SID's that will be killed , to be more specific to find which code is having this issue.

SELECT    'alter system kill session '''
       || sid
       || ','
       || serial#
       || ',@'
       || inst_id
       || ''' immediate ;'
  FROM gv$session
 WHERE     status LIKE 'INACTIVE'
       AND username LIKE 'APPLICATIONUSER%'
       and MACHINE='abc.yx.net' 
       AND last_call_et > 2000;

Thanks
Re: LAST SQL RAN BEFORE KILLING A SESSION [message #615335 is a reply to message #615333] Mon, 02 June 2014 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Join v$session and v$sql on sql_id or prev_sql_id.

Re: LAST SQL RAN BEFORE KILLING A SESSION [message #615337 is a reply to message #615335] Mon, 02 June 2014 11:38 Go to previous messageGo to next message
azeem87
Messages: 116
Registered: September 2005
Location: dallas
Senior Member
thanks for quick response..
i tried this , and cancelled after 1 mins.. do you think this will take time..
inner select comes back in 2 secs.

       select sql_text from gv$sql where sql_id in (select prev_sql_id from gv$session  WHERE     status LIKE 'INACTIVE'
       AND username LIKE 'APPLICATIONUSER%'
       and MACHINE='abc.yx.net' 
       AND last_call_et > 200)

Re: LAST SQL RAN BEFORE KILLING A SESSION [message #615338 is a reply to message #615337] Mon, 02 June 2014 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I'd use "nvl(sql_id,prev_sql_id) from v$session"

If you don't use RAC or don't want to see sessions from other instances then use v$ never gv$

Re: LAST SQL RAN BEFORE KILLING A SESSION [message #615339 is a reply to message #615338] Mon, 02 June 2014 12:10 Go to previous messageGo to next message
azeem87
Messages: 116
Registered: September 2005
Location: dallas
Senior Member
thanks Michel,
we are on RAC, so was using GV$,
inner select is still good with in 2 secs, the complete SQL still same.. might SQL_TEXT column/sql_fulltext ( clob ) takes time..?
don't want to run too long also in prod cancelled again after 3 mins..

any other thoughts.

thanks for your time.

[Updated on: Mon, 02 June 2014 12:14]

Report message to a moderator

Re: LAST SQL RAN BEFORE KILLING A SESSION [message #615340 is a reply to message #615339] Mon, 02 June 2014 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

First check time with v$ views.

Re: LAST SQL RAN BEFORE KILLING A SESSION [message #615341 is a reply to message #615340] Mon, 02 June 2014 12:30 Go to previous messageGo to next message
azeem87
Messages: 116
Registered: September 2005
Location: dallas
Senior Member
5 mins:31secs with V$
with GV$ i never ran more than 3 mins.. next time will let it run with GV$ views and see.

Thanks for your time
Re: LAST SQL RAN BEFORE KILLING A SESSION [message #615343 is a reply to message #615341] Mon, 02 June 2014 12:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Try to query [g]v$sqltext instead of [g]v$sql.

Re: LAST SQL RAN BEFORE KILLING A SESSION [message #615346 is a reply to message #615343] Mon, 02 June 2014 13:07 Go to previous messageGo to next message
azeem87
Messages: 116
Registered: September 2005
Location: dallas
Senior Member
SQL_TEXT 7 is just VARCHAR2 (64 Byte) in gv$sqltext

so the output is something like this
1. ADDENDA = :6 , STATUS = :7  WHERE TNUM = :8 
2. S'') ||  TO_CHAR(TEMPLATETNUM) ||  TO_CHAR(TEMPLATE_PROTECTFIELD


this trimmed data, either from starting or at the end it's trimmed really doesn't gives full idea on the complete SQL.

but performance on gv$SQLTEXT is great.. in 1 sec..

[Updated on: Mon, 02 June 2014 13:07]

Report message to a moderator

Re: LAST SQL RAN BEFORE KILLING A SESSION [message #615348 is a reply to message #615346] Mon, 02 June 2014 13:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It contains the complete text but in chunks of 64 characters, so the query is:
select sql_text from gv$sqltext 
where sql_id in (select nvl(sql_id,prev_sql_id) from gv$session 
                 WHERE status LIKE 'INACTIVE'
                   AND username LIKE 'APPLICATIONUSER%'
                   and MACHINE='abc.yx.net' 
                   AND last_call_et > 200)
order by sql_id, piece
/

If you use SQL*Plus, you can use to make it clearer:
col sql_id noprint
break on sql_id skip 1
select sql_id, sql_text...

icon14.gif  Re: LAST SQL RAN BEFORE KILLING A SESSION [message #615351 is a reply to message #615348] Mon, 02 June 2014 15:39 Go to previous message
azeem87
Messages: 116
Registered: September 2005
Location: dallas
Senior Member
Thanks Michel.

[Updated on: Mon, 02 June 2014 15:40]

Report message to a moderator

Previous Topic: Managing DB users
Next Topic: Suggestion to add ASM disk to ASM group
Goto Forum:
  


Current Time: Mon Mar 18 23:12:48 CDT 2024