Home » RDBMS Server » Server Administration » session management (10g 10.2.0 RHEL5)
session management [message #546838] Fri, 09 March 2012 04:39 Go to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
AOA

My application is opening a lot of sessions in my DB server. I applied resource_limit=true and idle_time=15 min. ans assign this profile to all application user.
Now I am seeing a lot of sessions having status sniped in v$session.

I want to clean up these sniped sessions and what they mean...
Re: session management [message #546840 is a reply to message #546838] Fri, 09 March 2012 04:51 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
A sniper kills from a distance. You have to collect the bodies later.
Use ALTER SYSTEM KILL SESSION to remove them.
Re: session management [message #546844 is a reply to message #546838] Fri, 09 March 2012 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A sniped session is a session that was terminated by Oracle due a resource limit exceeded.
Oracle then waits for the client to make an operation to return it the information that his session has been terminated.
Until the client does an action the session entry remains (but all the holden resources have been released).
This is logical, Oracle has to give the information to the client that his session has been terminated and why and it only can do it when the client gives it the turn.

Regards
Michel
Re: session management [message #546845 is a reply to message #546840] Fri, 09 March 2012 04:56 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
you don't fancy leaving them to the vultures then?
Re: session management [message #546857 is a reply to message #546845] Fri, 09 March 2012 05:50 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
The problem is that Oracle doesn't provide a vulture for this. So you can end up hitting max sessions, which is a major problem.

@smunir362, if you have Enterprise Edition licences, a better technique for killing idle sessions is to do it with the Resource Manager: this really does kill them (and remove the bodies). Or upgrade to 11g, and use Database Resident Connection Pooling which can deliver the same result.
Re: session management [message #547039 is a reply to message #546857] Mon, 12 March 2012 00:30 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
Is setting the resource_limit=true in spfile and idle_time in profile not enough...
Or resource manager will kill idle session and remove them from v$session as well as os (LOCAL=NO).....
Re: session management [message #547054 is a reply to message #547039] Mon, 12 March 2012 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is setting the resource_limit=true in spfile and idle_time in profile not enough...


Enough for what?

Regards
Michel
Re: session management [message #547193 is a reply to message #547054] Tue, 13 March 2012 05:32 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
I mean it is enough to set
"resource_limit=true in spfile and idle_time in profile"
OR I have to configure resource manager
OR use 11g Database.
Re: session management [message #547195 is a reply to message #547193] Tue, 13 March 2012 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And I repeat "enough for what?".

Regards
Michel
Re: session management [message #547639 is a reply to message #547195] Thu, 15 March 2012 05:39 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
For
automatically clean idle processes from v$session as well as shadow process at os level (LOCAL=NO)
Re: session management [message #547645 is a reply to message #547639] Thu, 15 March 2012 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The answer is NO.
But what is the problem with snipped sessions?

Regards
Michel
Re: session management [message #547712 is a reply to message #547645] Thu, 15 March 2012 23:08 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
sniped session are counted as oracle session.Means If i set the session=3000 and there are 2500 sniped sessions then only 500 new connections can be made.
So I want sniped sessions should automatically clean from oracle session as well as os process......
Re: session management [message #547713 is a reply to message #547712] Thu, 15 March 2012 23:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
In my experience, only after termination of the OS process will the session be actually terminated within the DB.
Re: session management [message #547715 is a reply to message #547713] Fri, 16 March 2012 00:05 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
but at os level how can we identify that which process is sniped process.....
Re: session management [message #547718 is a reply to message #547715] Fri, 16 March 2012 00:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>but at os level how can we identify that which process is sniped process.....
too little, too late.
You must identify OS process, before terminating session.
Re: session management [message #547720 is a reply to message #547718] Fri, 16 March 2012 00:50 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
but at os level how can we identify that which process is sniped process.....

BUT....HOW .....
Re: session management [message #547730 is a reply to message #547720] Fri, 16 March 2012 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
sniped session are counted as oracle session.Means If i set the session=3000 and there are 2500 sniped sessions


The question is why are they so many sessions? Why the application does not cleanly close its session(s)?

Quote:
but at os level how can we identify that which process is sniped process


Maybe joining v$session and v$process.
Both are described in Database Reference

Regards
Michel
Re: session management [message #547772 is a reply to message #547730] Fri, 16 March 2012 08:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>but at os level how can we identify that which process is sniped process.....
You should be able to get relatively close by using LOGON_TIME & OS process creation time to match the two values.
Re: session management [message #547914 is a reply to message #547772] Sun, 18 March 2012 23:21 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
ok thanks for your reply.... U mean I have to develop some shell script for that....
Re: session management [message #547915 is a reply to message #547914] Sun, 18 March 2012 23:29 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
U is not a member of this forum.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Previous Topic: Undo tablespace
Next Topic: not able to run setup.exe
Goto Forum:
  


Current Time: Thu Mar 28 17:51:01 CDT 2024