OraFAQ Forum: Server Administration » LAST SQL RAN BEFORE KILLING A SESSION

Home » RDBMS Server » Server Administration » LAST SQL RAN BEFORE KILLING A SESSION (Oralce 11g Rel 2)

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

LAST SQL RAN BEFORE KILLING A SESSION [message #615333]

Mon, 02 June 2014 10:42

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

Report message to a moderator

Re: LAST SQL RAN BEFORE KILLING A SESSION [message #615335 is a reply to message #615333]

Mon, 02 June 2014 11:29

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: LAST SQL RAN BEFORE KILLING A SESSION [message #615337 is a reply to message #615335]

Mon, 02 June 2014 11:38

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)

Report message to a moderator

Re: LAST SQL RAN BEFORE KILLING A SESSION [message #615338 is a reply to message #615337]

Mon, 02 June 2014 11:43

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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$

Report message to a moderator

Re: LAST SQL RAN BEFORE KILLING A SESSION [message #615339 is a reply to message #615338]

Mon, 02 June 2014 12:10

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

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

First check time with v$ views.

Report message to a moderator

Re: LAST SQL RAN BEFORE KILLING A SESSION [message #615341 is a reply to message #615340]

Mon, 02 June 2014 12:30

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

Report message to a moderator

Re: LAST SQL RAN BEFORE KILLING A SESSION [message #615343 is a reply to message #615341]

Mon, 02 June 2014 12:56

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: LAST SQL RAN BEFORE KILLING A SESSION [message #615346 is a reply to message #615343]

Mon, 02 June 2014 13:07

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

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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...

Report message to a moderator

Re: LAST SQL RAN BEFORE KILLING A SESSION [message #615351 is a reply to message #615348]

Mon, 02 June 2014 15:39

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 22 21:42:31 CST 2025