OraFAQ Forum: Server Administration » session management

Home » RDBMS Server » Server Administration » session management (10g 10.2.0 RHEL5)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

session management [message #546838]

Fri, 09 March 2012 04:39

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...

Report message to a moderator

Re: session management [message #546840 is a reply to message #546838]

Fri, 09 March 2012 04:51

John Watson
Messages: 8997
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.

Report message to a moderator

Re: session management [message #546844 is a reply to message #546838]

Fri, 09 March 2012 04:55

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: session management [message #546845 is a reply to message #546840]

Fri, 09 March 2012 04:56

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

you don't fancy leaving them to the vultures then?

Report message to a moderator

Re: session management [message #546857 is a reply to message #546845]

Fri, 09 March 2012 05:50

John Watson
Messages: 8997
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.

Report message to a moderator

Re: session management [message #547039 is a reply to message #546857]

Mon, 12 March 2012 00:30

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).....

Report message to a moderator

Re: session management [message #547054 is a reply to message #547039]

Mon, 12 March 2012 01:52

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: session management [message #547193 is a reply to message #547054]

Tue, 13 March 2012 05:32

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.

Report message to a moderator

Re: session management [message #547195 is a reply to message #547193]

Tue, 13 March 2012 05:35

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And I repeat "enough for what?".

Regards
Michel

Report message to a moderator

Re: session management [message #547639 is a reply to message #547195]

Thu, 15 March 2012 05:39

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)

Report message to a moderator

Re: session management [message #547645 is a reply to message #547639]

Thu, 15 March 2012 06:30

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The answer is NO.
But what is the problem with snipped sessions?

Regards
Michel

Report message to a moderator

Re: session management [message #547712 is a reply to message #547645]

Thu, 15 March 2012 23:08

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......

Report message to a moderator

Re: session management [message #547713 is a reply to message #547712]

Thu, 15 March 2012 23:11

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.

Report message to a moderator

Re: session management [message #547715 is a reply to message #547713]

Fri, 16 March 2012 00:05

smunir362
Messages: 310
Registered: September 2007

Senior Member

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

Report message to a moderator

Re: session management [message #547718 is a reply to message #547715]

Fri, 16 March 2012 00:15

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.

Report message to a moderator

Re: session management [message #547720 is a reply to message #547718]

Fri, 16 March 2012 00:50

smunir362
Messages: 310
Registered: September 2007

Senior Member

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

BUT....HOW .....

Report message to a moderator

Re: session management [message #547730 is a reply to message #547720]

Fri, 16 March 2012 01:57

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: session management [message #547772 is a reply to message #547730]

Fri, 16 March 2012 08:34

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.

Report message to a moderator

Re: session management [message #547914 is a reply to message #547772]

Sun, 18 March 2012 23:21

smunir362
Messages: 310
Registered: September 2007

Senior Member

ok thanks for your reply.... U mean I have to develop some shell script for that....

Report message to a moderator

Re: session management [message #547915 is a reply to message #547914]

Sun, 18 March 2012 23:29

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/

Report message to a moderator

Previous Topic:	Undo tablespace
Next Topic:	not able to run setup.exe

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Feb 27 04:17:56 CST 2026