Re: Re: Find / Kill Sessions Running Duplicate SQL with Duplicate Bind Var. Values?

It's not a solution to your problem, just an idea to ease the management of problematic sessions until the root cause is fixed - have you considered using Resource Manager and create different network service names so that each application's sessions (or if you have many data sources within the same application) connect to its own service name?

Using Resource Manager you could avoid overloading the database server with too many concurrently active sessions (active session pool) and thus avoid an even bigger degradation in repose time. To manage the runaway sessions:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm#ADMIN11842

*Manage runaway sessions or calls in the following ways:- By placing an absolute limit on the percentage of CPU that a group can consume- By detecting when a session or call consumes more than a specified amount of CPU or I/O, and then automatically either terminating the session or call, or switching it to a consumer group that is allocated a small amount of CPU, which would in effect mitigate the impact of the runaway session or call*
Of course it depends whether using such a strategy would be useful in your case, since there could be legitimate sessions consuming resources which would suffer from such a policy.

Using network services (in combination with Resource Manager) you might segregate sessions by application and if most of the sessions connected to a service are stuck, you could easily terminate them. Again, "proactively" killing user sessions is not a scenario I came across many times, and what I proposed might not be an acceptable solution for you, but maybe it might help to think about the problem from that perspective until the root cause is fixed.

Regards,
Jure Bratina

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