Solved

sqlnet.expire_time

Posted on 2001-08-07
8
8,001 Views
Last Modified: 2010-05-18
Our db has been having issues with inactive sessions and upto 100 sessions from a single web server which is using taking up all the sessions/processes that are allocated.

The sqlnet parameter sqlnet.expire_time is set to 3min which is supposed to clean out dead connections.

How can do this for inactive sessions? In essence, I am looking to automate the killing of all dead/inactive sessions from web servers that are in that state for more than 30minutes.
0
Comment
Question by:tomreddy
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 9

Accepted Solution

by:
dbalaski earned 50 total points
Comment Utility
Hi,

the parameter  sqlnet.expire_time  does not do exactly what you think it does.

It only kills connections that do not respond via a probe packet sent out at regular intervals.  
Dead Connection Detection is intended primarily for environments in which clients power down their systems without disconnecting from their Oracle sessions, a problem characteristic of networks with PC clients.

One noted bug in "some" NT versions:
- Orphaned resources are not released if only the client application is terminated. Only after the client PC has been rebooted does DCD release these resources. For example, if a Windows application is killed yet Windows remains running, the probe packet may be received and discarded as if the connection is still active.  As it currently stands, it appears that DCD detects dead client machines, but not dead client processes.
This is logged as generic Bug#280848.

To quote oracle:
"Under no circumstances should you rely 100% on Dead Connection Detection. It was developed to handle clients that have abnormally exited.  Clients should always exit their applications gracefully."

---------------------------------------------

What it sounds like is the application itself is not completely closing off the connection.  So as far as DCD is concerned,  it is not "Dead"

This really would require a re-examination the code.


All is not lost!


Let me Present Some Options:

-------------------------------------------------------
Use a user Profile to limit Idle time.

Use the CREATE PROFILE statement to create a profile, which is a set of limits on database resources, specifically in your case:  IDLE_TIME  
which specifies the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.  
For this to work,  you should set the INIT.ORA parameter
RESOURCE_LIMIT = TRUE

example:
create profile SHORT_LIMIT
    IDLE_TIME   5;
(5 minute idle time)
Now, make this the user's default profile.
Example:  ALTER USER scott PROFILE short_limit;


This way -- if the session is idle for more than 5 minutes,  the connection is terminated.

This is still a band-aid to the real problem.  


Why launch new server process every time there is a connection?   Launching a new server process is pretty resource intensive on the machine..  

You might be better off looking into using Connection Pooling  (which utilizes Oracle MTS)...
There are some advantages to this,  especially when running OLTP system, such as with a website

To quote Oracle
--------------------------------------------------
Connection pooling is a resource utilization feature that enables you to maximize the number of physical network connections to a multi-threaded server. This is achieved by sharing or pooling a dispatcher's set of connections among multiple client processes.
By using a time-out mechanism to temporarily release transport connections that have been idle for a specified period of time, connection pooling makes these physical connections available for incoming clients, while still maintaining a logical session with the previous idle connection. When the idle client has more work to do, the physical connection is reestablished with the dispatcher.

This feature only works when multi-threaded server is configured.


Connection Pooling Advantages:
 (This feature works when MTS is configured.)

 1.  Limits the number of network resources used per process.
 2.  Maximizes the number of client/server sessions over a limited number of physical connections.
 3.  Optimizes resource utilization.

 Connection Pooling Recommended for:

 Networks where many clients run interactive "high think/search time"  applications such as messaging and OLAP.  


Sounds like this option is right up your alley (so to speak)

Hope this helps,

sincerely,
dBalaski
0
 
LVL 3

Expert Comment

by:Wadhwa
Comment Utility
Here is the procedure which you can execute to kill a session.
Your session must have alter system privilege.

The input paramter is idleTime which is the time when session is inactive and doing nothing.

Create or replace procedure KillSession(idleTime in Number)
as cursor c1 is
select sid,serial#,trunc((last_call_et - trunc(last_call_et/3600,0)*3600) / 60,0) TMIN from

v$session
  where status='INACTIVE' and username  not in ('SYS');
VString varchar2(50);
begin
for c1rec in c1 loop
 if c1rec.TMIN > idleTime then
     Vstring := 'alter system kill session

'||chr(39)||c1rec.sid||','||c1rec.serial#||chr(39);
    execute immediate(Vstring);
 end if;
end loop;
end;
/

so if you want to delete a session who's status is inactive and doing nothing with 30 min, you can execute procedure as
executue killsession(30);

Hope this helps
Sameer
0
 

Author Comment

by:tomreddy
Comment Utility
I have poured over some documentation before posting this question. This seems like something that Oracle needs to address.
I think another way might be to run dbms_jobs to check which sessions are inactive for more than 30 min and then to kill them but I am sure this will lead to more headaches.
Dbalaski, thanks for your proposed solution. I will leave this here for a day or two to see if anyone else can come up with a better way to do this.
0
 
LVL 3

Expert Comment

by:Wadhwa
Comment Utility
Hi tomreddy,


Dbalaski suggestion is good to set the profile with the idle time .
However, idle_time only begins to count after the user does something. For example, assume a user logs into sql*plus and issues no command. The clock for this user's idle time doesn't start. He can stay that way forever. Once is executes anything, even a 'select * from dual', the clock starts ticking.


After reaching the idle time ,if connected user will try to execute the sql statement he will get a
ORA-02396: exceeded maximum idle time, please connect again

Also when user has exceeded idle_time, their process will not be killed but it's status will be changed in V$session to 'SNIPED'. Only after the user tries something and gets the message 'you have exceeded idle_time...' (not exact error)  their session disappear from V$session

You can execute above procedure which i mentioned before with dbms_job or cron and set the idle time dynamically.

Hope this helps,
Sameer
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Expert Comment

by:UsamaMunir
Comment Utility
Hi You can Invokde PMON after say like 30 mins to clean up the dead connections;

-------------------------------------------------------------------------------
--
-- Script:     post_smon.sql
-- Purpose:     to post PMON to cleanup Dead connections
-- For:          8.1
--
--------------------
@save_sqlplus_settings

column pid new_value Smon

set termout off
select
  p.pid
from
  sys.v_$bgprocess b,
  sys.v_$process p
where
  b.name = 'PMON' and
  p.addr = b.paddr
/
set termout on

oradebug wakeup &Pmon

undefine Smon
@restore_sqlplus_settings

0
 
LVL 3

Expert Comment

by:UsamaMunir
Comment Utility
Sorry Some Typos , it's
undefine Pmon
0
 
LVL 4

Expert Comment

by:fva
Comment Utility
From my experience, MTS is somehow broken in NT versions, at least in 8.0.x. It seems to work but sometimes eats network resources (sockets) and the only way to free them is to restart the whole server.

F.
0
 

Author Comment

by:tomreddy
Comment Utility
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now