[Solved] 1000s of Inactive session in Oracle
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

1000s of Inactive session in Oracle

Database type:

There are 1000s of Inactive sessions are there in Oracle database. Some are even 1 month old. sql_text shows a particular query in 90% of the cases. Query is working fine without any slowness.OracleConnection is disposed properly.

Any idea what can be reason ?

Query used to find inactive sessions

SELECT s.SID, s.STATUS, s.process, s.osuser, a.sql_text, p.program
FROM v$session s, v$sqlarea a, v$process p
WHERE s.PREV_HASH_VALUE = a.hash_value
    AND s.PREV_SQL_ADDR = a.address 
    AND s.paddr = p.addr 
    AND s.STATUS = 'INACTIVE'

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX vprocess_idx_addr ON v$process (addr);
CREATE INDEX vsession_idx_status_prev_va_prev_ad_paddr ON v$session (STATUS,PREV_HASH_VALUE,PREV_SQL_ADDR,paddr);
CREATE INDEX vsqlarea_idx_hash_value_address ON v$sqlarea (hash_value,address);
The optimized query:
SELECT
        s.SID,
        s.STATUS,
        s.process,
        s.osuser,
        a.sql_text,
        p.program 
    FROM
        v$session s,
        v$sqlarea a,
        v$process p 
    WHERE
        s.PREV_HASH_VALUE = a.hash_value 
        AND s.PREV_SQL_ADDR = a.address 
        AND s.paddr = p.addr 
        AND s.STATUS = 'INACTIVE'

Related Articles



* original question posted on StackOverflow here.