Re: Query does not finish

From: Tim Gorman <tim_at_evdbt.com>
Date: Fri, 18 Apr 2014 10:43:53 -0600
Message-ID: <53515649.2000901_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
Received on Fri Apr 18 2014 - 18:43:53 CEST

Original text of this message