I need to learn more about Oracle idle sessions, why/how they are created and why some of the idle sessions does not terminate easily.
Is there any ways to determine which SQLs are responsible for idle sessions. Also SQL way to identify and destroy idle sessions.
Sessions are idle because they don't execute SQL or PL/SQL statements: it is the application code that you should investigate especially in 3 tier application where database sessions are managed by some application server/web server connection pool.
V$SESSION.LAST_CALL_ET should help identify idle sessions:
LAST_CALL_ET NUMBER If the session STATUS is currently ACTIVE, then the value represents the elapsed time (in seconds) since the session has become active.
If the session STATUS is currently INACTIVE, then the value represents the elapsed time (in seconds) since the session has become inactive.
You can create a profile with the right idle_time setting to ask database instance to automatically end idle session:
First, why do you believe that idle sessions are a problem?
A session is IDLE if it is not, at the instant you query the data dictionary, executing a SQL statement. Most sessions will be idle most of the time because most of the time your applications are waiting on humans to do something or spending time processing data in the application. Just because a session is idle now is no reason to believe that it won't be ACTIVE shortly.
A modern three-tier application will generally create a pool of connections to the database on each middle tier server. Generally, those pools will be sized for at least reasonably busy conditions. If you happen to look at the database when the application isn't particularly busy, you're likely to see a bunch of idle sessions. That's not a problem. While you could force the application server admins to decrease the size of their connection pools, that may negatively affect the performance of the applications and introduce extra load on the database server for no real benefit.