Skip to Main Content
  • Questions
  • Best Practices for App Server Keep Alive

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Tair.

Asked: August 01, 2005 - 2:51 pm UTC

Last updated: November 21, 2007 - 9:23 am UTC

Version: 9.2.0.4

Viewed 10K+ times! This question is

You Asked

I am looking to the Best Practice for the configuration of an Apps Server Connection Pool keep alive. I've seen where we use "select 1 from dual;" to keep the connection alive. Couldn't we use something like "show user" to do that?

Your thoughts on this would like always be greatly appreciated.

Thanks

and Tom said...

why keep alive at all????

The best practice would be to NOT DO ANYTHING AT ALL, unless an until an application needs the connection.

I've not understood the need to beat the database to death with thousands of "can you hear me now" calls.

mod_plsql (a connection pool) doesn't do this, you don't need to do this.

Rating

  (16 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Expensive

VA, August 02, 2005 - 9:40 am UTC

Well, you have stated on this site on some other thread that establishing a connection is the most expensive part of a web app so wouldnt it make sense to keep connections alive so they dont have to be created/destroyed over and over?

Tom Kyte
August 02, 2005 - 10:24 am UTC

yes, but what does having a connection persist have to do with sending a select * from dual ?

mod_plsql -- keeps the connection.
mod_plsql -- implements a connection pool for itself
mod_plsql -- does *not* send a probe sql statement every N seconds to "keep alive"


keep the connection, just DON'T DO anything on it until you need to.

what about shutting down db instance

Dav, August 02, 2005 - 9:42 am UTC

In one of my client env., they restart the database around 5.00 a.m in the morning and they restart the app server in the later/earlier time. In that case keep the connection pool active is the only way?

Tom Kyte
August 02, 2005 - 10:25 am UTC

I don't know what you mean -- how would sending a query every N seconds keep anything alive?

let me explain

Dav, August 02, 2005 - 10:35 am UTC

Sorry I was not clear on the previous post..

We've weblogic server running and connection pool connects to Oracle database.

In weblogic admin console there is a setting to check connection available every 60 seconds and it'll allow you to put a SQL statement to check the connection(select 1 from dual, you can put any SQL statement if you want to..)

So when database is brought down and brought back up it automatically tries to keep the connection pool active so that we don't need to restart weblogic app server because we re-started database.

As per my understanding if you don't have that setup you need to re-start the app server whenever you shutdown/start the database. Hope this make sense



Tom Kyte
August 02, 2005 - 1:48 pm UTC

A connection pool that cannot deal with a connection getting broken is a broken connection pool.

Why would it do it every N seconds -- when it ONLY NEEDS TO DO IT IF EVER RIGHT BEFORE IT GIVES YOU THE CONNECTION.

Why tap the database on the shoulder 500 times, when at most one is necessary (and heck, even that ONE is not necessary if the application itself is even marginally resilient to errors)

Why would an app server bounce be necessary??? In any case????

I've seen weblogic do this (not very logical), I've see dual be the hottest table on the system thanks to this.



Avoiding the select 1 from dual

Tair, August 02, 2005 - 12:14 pm UTC

I want to avoid the

select 1 from dual

weblogic requires a sql statement i want

show user

any ideas?

Tom Kyte
August 02, 2005 - 2:04 pm UTC

you know what show user is?

select user from dual;


best bet would be to figure out how to turn this thing OFF

Negative

A reader, August 02, 2005 - 11:40 pm UTC

select anything from dual oppppps parse

show user

nada

you try it I already did it

thanks but no thanks...

Tom Kyte
August 03, 2005 - 10:10 am UTC

Umm, my point here is -- do you know what "show user" (a sqlplus command, not SQL) is???

answer: it is select user from dual, in order for weblogic to do it, it would be select user from dual.

positive.  


there is no such thing as "show user" outside of the simple command line tool that is SQLPLUS.  Besides, show user doesn't touch the database -- it would not be the "ping" that you so so desperately (but incorrectly) believe you need or want

[tkyte@desktop tkyte]$ sqlplus /
 
SQL*Plus: Release 10.1.0.4.0 - Production on Wed Aug 3 10:01:01 2005
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
 
ops$tkyte@ORA10G> @getspid
 
DEDICATED_SE CLIENTPID
------------ ------------
8391         8389
 
ops$tkyte@ORA10G> !kill -9 8391
 
ops$tkyte@ORA10G> show user
USER is "OPS$TKYTE"
ops$tkyte@ORA10G> select user from dual;
select user from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
 
 
ERROR:
ORA-03114: not connected to ORACLE

 
 
ops$tkyte@ORA10G> show user
USER is "OPS$TKYTE"
ops$tkyte@ORA10G>


<b>show user is a sqlplus'ism that displays the cached user name , it did not even HIT the database.  Hence it would not even remotely be part of your solution</b>
 

I agree

A reader, August 02, 2005 - 11:42 pm UTC

select from dual does require an parse while show user does not. Does not really beat the DB does it? I like it...

Tom Kyte
August 03, 2005 - 10:11 am UTC

see above, it is funny.

I like show user too.

To implement in java:

a) have a global static variable
b) put your name in it
c) you are done.


it would have the same effect. I like it too -- it is my point actually, YOU DON'T NEED TO PING the database.



You go girl....

A reader, August 02, 2005 - 11:44 pm UTC


Interesting

Nodip Manura, August 02, 2005 - 11:58 pm UTC

We use WebLogic as well and yes I can see that we "select 1 from dual" and yes it does a parse.

I would be interested if anyone using WebLogic knows how to change its configuration so we can "show user" verses "select 1 from dual".



show user???

Vladimir Andreev, August 03, 2005 - 4:48 am UTC

People, the only "show user" command I know of is the sqlplus command - I doubt  you can use that in WebLogic. And even if you somehow managed to call sqlplus from WebLogic and actually use 'show user', it doesn't do what you all (mistakenly) want to achieve, namely to check whether the connection is still alive. Take a look:

SQL> connect flado
Enter password: 
Connected.
SQL> select 'alter system kill session '''||sid||','||serial#||''';'
  2  from v$session
  3  where sid=(select sid from v$mystat where rownum=1);

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
--------------------------------------------------------------------------------
alter system kill session '14,58455';

<b>I cannot commit suicide, so I start another session: </b>
SQL> !
[sqllogs]$ sqlplus flado

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Aug 3 10:11:26 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password: 

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> alter system kill session '14,58455';

System altered.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[sqllogs]$ exit
exit

<b>Now that my first session is dead, let's check who notices this:</b>

SQL> show user
USER is "FLADO"

<b>'show user' is happily unaware</b>

SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-00028: your session has been killed

<b>not so 'select 1 from dual'</b>

SQL> show user
USER is "FLADO"
SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-01012: not logged on


SQL> show user
USER is "FLADO"
SQL> 

See, even though sqlplus has been repeatedly told it has no session and therefore no user anymore, it still reports the username.

So your quest for a command that doesn't call the database yet is somehow able to detect that it is 'not there anymore' seems futile. Any solution you might come up with will necessarily call the database 100 times a second or so - which brings us back to what Tom said: "figure out how to turn this thing OFF"

Cheers,
Flado 

Tom Kyte
August 03, 2005 - 11:01 am UTC

thanks, I did not see this before I did my suicide one above :)

Not fair! :-)

Flado, August 03, 2005 - 10:50 am UTC

But I'm glad Tom said essentially the same thing :-)

Beating a Dead Horse

John spencer, August 03, 2005 - 1:00 pm UTC

If the demonstrations by Tom and Flado don't convince, then consider:

[oracle@rhpc2 admin]$ export ORACLE_SID=OEMREP
[oracle@rhpc2 oracle]$ sqlplus /nolog
 
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Aug 3 11:32:41 2005
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
SQL> connect / as sysdba
Connected to an idle instance.
SQL> show user;
USER is "SYS"
 
[oracle@rhpc2 oracle]$ export ORACLE_SID=asktom
[oracle@rhpc2 oracle]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Aug 3 11:33:45 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.
SQL> show user
USER is "SYS"

I assure you that I do not have a database instance named asktom ('though I would like to get my hands on the AskTom database).

 

Tom Kyte
August 03, 2005 - 1:26 pm UTC

or even

[tkyte@dellpe tkyte]$ sqlplus /nolog

SQL*Plus: Release 10.1.0.3.0 - Production on Wed Aug 3 13:10:49 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.

idle> show user
USER is ""
idle>


Business case

Vladimir Andreev, August 04, 2005 - 5:17 am UTC

In fact, I can imagine a business case where a keep-alive would be necessary.
It is a pretty common configuration where your App/Web Server is in a demilitarized zone while you want to keep your database server hidden behind a firewall. Some firewalls do have the habit of cutting inactive connections off, and requiring significant amounts of time (or even manual intervention) for establishing a new connection. Under these circumstances I can see a keep-alive mechanism useful - but with a very low frequency that wouldn't beat the database to death or make DUAL the hottest segment.

Thus, I guess the 'best practice' should be: if you don't need the keep-alive, switch it off; if you definitely need it, make the ping frequency as low as possible, in which case 'select 1 from dual where 1=0' is just fine.

Comments?

Cheers,

Flado

Tom Kyte
August 04, 2005 - 9:31 am UTC

sqlnet dead client detection, the database will ping the client with a very lightweight packet just to see if the client is alive. The client must respond with an answer.

That'll keep the network alive. Simple configuration parameter.


That and you have just defeated the security protocol put in place by your business for some reason. You should ask for an exception truth be told.


Cool

Vladimir Andreev, August 04, 2005 - 10:49 am UTC

I didn't know about dead client detection - very cool :-)

I never realised that cutting off inactive network connections could be part of a security protocol... How does it help security?
I mean, that a Web site logs me off after a period of inactivity is a security measure for the case when I walk away from my browser without logging of, but would that apply to an Application Server's connections to its database?

Tom Kyte
August 04, 2005 - 11:34 am UTC

why did they enable "kill the connection if idle" -- they must have had a *reason* and if it is part of the DMZ, it must be related to security.

Your networking security team turned on a "security option" -- kill connections after N units of idle time.

If this rule should apply to you, they should say "why" (and you shouldn't subvert it perhaps)

If this rule SHOULD NOT apply to you, they should exempt you from the rule - rather then you having to hack a way around it.

Oh, I got your point at last...

Vladimir Andreev, August 04, 2005 - 12:20 pm UTC

It took me some time, as this 'business case' is imaginary and therefore I have no one to go to and ask 'why are you cutting inactive connections off?'.

Not a very convincing business case then, is it?

Moral of the story: If someone does something you have a problem with, ask them to either tell you why or stop doing it. Do not try to work around it.

Cheers,
Flado

Inevitable...

Antonio, August 19, 2005 - 6:12 am UTC

<sarcasm>
But Tom, by forcing "select 1 from dual;" every 60 secs from all app-server sessions will serve 2 good purposes:

1. Subvert network security, always good.
2. Send up shared pool hit ratio, always good.

Two good things for a so small change, what more could you ask???
</sarcasm>


is it possible to track ORA-3114 on server site

Pauline, November 14, 2007 - 3:57 pm UTC

Tom,
We have users got "ORA-03114: not connect to ORACLE" either from nightly reporting job or from TOAD sessions. The database users connect to is up and running and no kill session activities against users. No any ORA- errors in alert.log. Except TNS-12502 ,no other TNS- errors in listener.log. Now we want to figure out what is root cause
for getting ORA-03114. One way we want to keep tracking
when it occur from client connection. Is it any way to do
it on Oracle database server site?

Thanks.
Tom Kyte
November 21, 2007 - 10:31 am UTC

well, if you are not connected, you are not connected - there is no connection to the server. There is nothing to track.

typically - if they WERE connected and then get this - there would be something prior to this reported by the client as well.

what I suspect might be happening is your network times out connections after some period of inactivity. If the nightly reporting job runs long running statements - that network connection would appear idle and the network would terminate the connection. If the toad users sit there for a long time - the network would terminate the connection.

So, does your network time out - ask your network guys - don't just say "no, it never timed out on me" - that is not sufficient (you might not sit idle for long periods of time)