How do we kill inactive session in oracle?

This article is witten in oracle9i. It should apply to oracle1og and further release too.

Inactive session
Oracle Marks the session as INACTIVE when the moment, session is ideal. These sessions are remain connected to the database with a status in v$session of INACTIVE. A user starts a program/session, then leaves it running and idle for an extended period of time. Some time, user connect to the database and go for coffee and chat with somebody and come back to desk and check mails. During this time, the session becomes INACTIVE.

Dead Connection
Sometime, user shutdown the machine without logout the database connection or network prevent to connect the database, then connection becomes dead connection. SQL NET detect the dead connection and release the resource. Please refer metalink id (Note:395505.1, Note:151972.1) for more details about DCD(dead connection deduction).

How do we kill the inactive session?

There are couple of ways to kill the inactive session.

Option 1
We can write the script to kill the session. The below script kills any session which is INACTIVE.

SELECT 'ALTER SYSTEM KILL SESSION '''''sid','serial#''''' immediate;'
FROM v$session
WHERE status ='INACTIVE'

In case, if you need to kill all the session which are inactive for last four hours, then the above script is not an option. The above command kills all the session regardless of when it becomes inactive. Next option is best option to kill the session which are inactive for certain period of time.

Option 2
We can disconnect the inactive session through Oracle profile. We can change the idle_time in the user profile. Here are the steps to change the idle_time.

Step1 First we need to change the resource_limit to TRUE. By default, it is FALSE.

SQL> select value from gv$parameter
2 where name='resource_limit';

VALUE
--------------------------------------------------------------------------------
FALSE

SQL> ALTER SYSTEM SET resource_limit=TRUE;
System altered.

Step2 Alter the profile to change the idle_time. Here i am using custom profile named developer.

SQL> ALTER profile developer limit idle_time 240;

Profile altered.

Step 3 If you are not using developer profile for application schema, then assign this profile to application schema.

SQL> alter user app_data profile developer;

User altered.

How do we see what transaction is currently running on the database?

This queries are tested on oracle 9.2.0.8

Display all the statement which are available in memory?

Select * from v$sqlarea;

Note : If full SQL text is not shown in V$SQLAREA view, because it was larger than 1000 characters, this V$SQLTEXT view should be queried to extract the full SQL. So you need to use sql_text column in v$sql_text view instead of using sql_text column in v$sqlarea.

Display only currently running transactions in database?

The below query is used to find what is actively running(uncommitted transactions) in database. The column query_active_time is time between transaction starting time and commit time. For instance, the transaction started at 10AM and completed at 10.05AM. But user commits the transaction at 10.30PM. So the query_active_time will be 30 Seconds. V$transaction does not show any SELECT statement which are running in the database.

select
osuser,
username,
sid,
serial#,
program,
machine,
sql_text,
start_time,
cpu_time/1000000 cpu_time,
elapsed_time/1000000 run_time,
to_number(sysdate-to_date(c.start_time,'MM/DD/YY HH24:MI:SS'))*24*60 query_active_time
from
v$session a,
v$sqlarea b,
v$transaction c
where
((a.sql_address = b.address
and a.sql_hash_value = b.hash_value )
or ( a.prev_sql_addr = b.address
and a.prev_hash_value = b.hash_value))
and c.ses_addr = a.saddr
--and sysdate-to_date(v$transaction.start_time,'MM/DD/YY HH24:MI:SS') > 2/(60*24)


Display all the statement for only connected users?

This query will display all statement in memory only for connected sessions.

select
osuser,
username,
sid,
serial#,
program,
machine,
sql_text,
cpu_time/1000000 cpu_time,
elapsed_time/1000000 run_time
from
v$session a,
v$sqlarea b
where
((a.sql_address = b.address
and a.sql_hash_value = b.hash_value )
or ( a.prev_sql_addr = b.address
and a.prev_hash_value = b.hash_value))
and username is not null


Display all the statement with I/O statistics for connected session?

The below query provides all the transaction which are in memory with I/0 statistics. Sometime, user might be complaining that their query is Stuck. As a DBA, we can check v$sess_io view and see GETS, READS or CHANGES column continue to increase for a session, then you can tell them that the statement is not stuck.

select
a.sid,
a.username,
optimizer_mode,
hash_value,
address,
cpu_time/1000000,
elapsed_time/1000000,
sql_text,
a.sid,
block_gets,
consistent_gets,
physical_reads,
block_changes,
consistent_changes
from
v$session a,
v$sqlarea b,
v$sess_io c
where
a.sql_hash_value = b.hash_value
and a.sql_address = b.address
and a.sid = c.sid
and a.username is not null


Note : The I/0 statistics(consistent_gets, physical_reads, block_changes) continue to increase for each statement for the session. It would not show the statistics for individual statement.

Display all the long running statement in memory?

This query provides all the long transactions for connected/disconnected sessions.

Select * from v$session_longops;

Display all the long running completed transactions(connected/disconnected sessions)?

Select * from v$session_longops where time_remaining = 0

Display all the long running active transactions?

Select * from v$session_longops where time_remaining > 0

Display all the long running transactions for connected users with I/0 Statistics?

select
a.sid,
a.username,
optimizer_mode,
hash_value,
address,
cpu_time/1000000,
elapsed_time/1000000,
sql_text,
block_gets,
consistent_gets,
physical_reads,
block_changes,
consistent_changes,
d.time_remaining,
message
from
v$session a,
v$sqlarea b,
v$sess_io c,
v$session_longops d
where
a.sql_hash_value = b.hash_value
and a.sql_address = b.address
and a.sid = c.sid
and a.sql_hash_value = d.sql_hash_value
and a.sql_address = d.sql_address
and a.username is not null

How do we check which SQL is currently taking more CPU's in UNIX?
First we need to run the top command and top command will show the top CPU session.

Here is the top command partial output.

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
27486 oracle 19 0 603M 603M 601M S 16.9 5.2 35:12 1 oracle

27486 is process id which takes 16.9% CPU time. The process id 27486 is equivalent to v$process.spid in database. So we can join v$process, v$session and get the required information.

Here is the query to find out which query is currently taking high CPU.

select
a.sid,
a.username,
optimizer_mode,
hash_value,
address,
cpu_time/1000000,
elapsed_time/1000000,
sql_text,
a.sid,
block_gets,
consistent_gets,
physical_reads,
block_changes,
consistent_changes
from
v$session a,
v$sqlarea b,
v$sess_io c,
v$process d
wherea.sql_hash_value = b.hash_value
and a.sql_address = b.address
and a.sid = c.sid
and d.addr = a.paddr
and a.username is not null
and d.spid= 32479

Note : 32479 is taken from Unix TOP output.