Re: Query does not finish

I will check the SQLNET.EXPIRE_TIME and another new information for me is : the process and sockets from the pooled connection from JBoss still exist

In the query i will use the gv$SESSION because the environment is a RAC.

I think this is the way to justify "why" and "what need" to kill the session.

Thanks a lot for your answer.
Eriovaldo

2014-04-18 13:43 GMT-03:00 Tim Gorman <tim_at_evdbt.com>:

> Eriovaldo,
>
> From what you describe, it is likely that the sessions are not "running"
> but are probably STATUS='INACTIVE' and sitting waiting for JBoss to say
> something by waiting on the event "SQL*Net message from client".
>
> You should check out the functionality surrounding the SQL*Net parameter
> SQLNET.EXPIRE_TIME and find out why that doesn't seem to be working, but
> chances are good that it isn't working is because the process and sockets
> from the pooled connection from JBoss still exist, so SQL*Net still thinks
> everything is OK at the network layer.
>
> So, in the meantime, how about running something like this a couple times
> daily and emailing yourself the resulting output...
>
> SELECT 'alter system kill session '''||sid||','||serial#||''' /*
> '||username||', '||program||' */ ;' cmd
> FROM V$SESSION
> WHERE STATUS <> 'ACTIVE'
> AND TYPE = 'USER'
> AND EVENT = 'SQL*Net message from client'
> AND SECONDS_IN_WAIT > 86400 /* 1 day */ ;
>
>
> You can add more inclusive or exclusive filters on columns like USERNAME,
> MACHINE, etc.
>
> Having the generated commands just emailed to you initially allows you to
> determine how effective (or dangerous) this "sniper" might be, providing
> the opportunity to check things out before running the ALTER SYSTEM KILL
> command, to ensure you're not killing something that shouldn't be killed.
> After a while, when you're confident, you can have the script just run the
> ALTER SYSTEM KILL command automatically and then have it email you the
> output.
>
> Or, just kill 'em all and let God sort 'em out. :-)
>
> Hope this helps...
>
> -Tim
>
>
> On 4/18/2014 8:00 AM, Eriovaldo Andrietta wrote:
>
> Hello,
>
> I would like to know why does it happen:
>
> I have a scheduled process in Jboss, that calls a java application and it
> sends a SQL to the database.
>
> All threads from JBoss were canceled and the SQL is still running. If we
> does not kill it, it stays there running for a week and does not finish.
>
> Note : If we run the SQL manually , via SQLPLUS, it runs fully, return
> data and complete the execution.
>
> My doubt is :
>
> How can I indentify , in the database, looking to the v$session, v$sqlarea
> and others views and get some information for making decision (for sure)
> in order to Kill de session,
> It spend a lot of database resource and cannot keep executing for a week.
>
> I imagine that the session cannot give a return to the listner , because
> the Jboss were canceled.
>
> Does anyone have a query to identify it.
>
> Regards
> Eriovaldo
>
>
>

--
http://www.freelists.org/webpage/oracle-l