Using Resource Manager to Detect and Kill Idle Sessions (Doc ID 1557657.1)

Purpose

Sessions that are idle for long periods of time cause problems because they hold onto valuable resources such as PGA memory that may be needed by other workloads.  Idle sessions are even more problematic when the session is processing a query that returns a large number of rows and partway through, the session becomes idle, waiting for the client to issue a call to fetch the next set of rows.  Such a wait can be extremely long if the application has timed out or the application is waiting for a user input to fetch the next set of rows.  In this case, the session is likely to be holding many resources, including locks, PGA memory, temp space, and undo space.  If the query is using parallel execution, then the session will also be holding onto parallel servers, each of which may also be holding onto more locks, PGA memory, etc. 

This scenario is often seen with ad-hoc query tools like SQL Developer and Toad.  For a DBA, the only solution is to detect these idle sessions and kill them.  This article describes how to use Resource Manager to do this automatically. 

Scope

Details

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.