Using Resource Manager to Detect and Kill Idle Sessions
(Doc ID 1557657.1)
Last updated on JUNE 30, 2021
Applies to:Oracle Database - Enterprise Edition - Version 184.108.40.206 to 220.127.116.11 [Release 11.2 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 21-Jul-2017***
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.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document