Everything must eventually come to an end, even your database session.
‘Keep-alive functionality’
The basic gist is at some point your connection to the database is terminated due to an inactive connection. This can be controlled by the DBA via a PROFILE and the IDLE_TIME flag – go read the DOCs
“IDLE_TIME Specify 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.”
Some folks like to login in on Monday morning and logout on Friday afternoon. For various reasons, this is not smiled upon by the admins. To control this, a PROFILE can be established which will go and drop all inactive sessions after a certain time period has elapsed.
What users would like to see is their application (in this case SQL Developer) run a SELECT * FROM DUAL or BEGIN DBMS_OUTPUT.PUT_LINE(”); END; / request periodically. This would keep their connection ACTIVE for as long as their application is running.
Please believe me, I get it, I really do. And even if I didn’t at first, after the 1800th person asked me about it, I finally began to realize that this can be a pain. But.
I’m of the opinion that implementing something like this would not serve our users. I think where we can reach a happy middle ground is to have a really well implemented ‘RECONNECT’ feature which would re-establish any dropped session – whether that be due to someone yanking the ethernet plug from your machine, the database crashing, or a PROFILE limit being enforced.
Eventually this would just be ‘automagic’, but for now just having a button to go resurrect the session will be a big improvement.
Maybe I’m off my rocker? What do you think? DBAs, I’d love to get your feedback here too.
Twitter
RSS
GooglePlus
Facebook
Oct 27, 2011 @ 22:56:50
I like the approach that is taken in SSMS. If you execute a query, or statement, and your session has been disconnected you will receive an error message, but it also triggers the application to reconnect to the database. By doing this you if allows the user to immediately re-execute successfully without having to explicitly reconnect. It seems like a perfect trade-off between the DBAs’ and users’ needs.
Oct 27, 2011 @ 23:16:32
Thanks for the feedback Tim. I’m looking forward to seeing where we go with the reconnect stuff. I’ll pretend to not know what SSMS is though
Oct 28, 2011 @ 03:26:30
As I’m one of those nasty DBAs which want to get all out of the instance whenever possible, I like the easy possibility to reconnect at need. In some companies it might be a small problem, as there often are security rules implemented which forbid storing any password anywhere (and for a reconnect you need valid credentials at all?) This might be solvable by some methods to keep interim tokens after an initial handshake, but such a solution must be implemented in SQL*NET first – so it’s another story
Oct 28, 2011 @ 04:54:39
Last year I was at a place where the network would disconnect after 20 minutes of idle time. The Oracle connection would still be there, possibly in the middle of a long-running job. Of course when the server eventually tried to talk to the client and realised it was a dead connection it would rollback. SQLNET.EXPIRE_TIME was set to ensure they didn’t hang around indefinitely.
The people running the network tried to convince us it was in our own interests to have our work terminated. They eventually consented to up the timeout to two hours, but it still got killed after twenty minutes. I think the truth was they were just clueless as to how it was happening. The subject got raised every couple of months until we just gave up.
By denying the option you are basically saying that developers can’t be trusted to use the functionality responsibly. If you start following that path, then I forsee the product getting split into a “DBA Version” and “Dummies version for people who can’t be trusted to walk and chew gum”.
Oct 28, 2011 @ 06:15:11
If it’s a long running job, then by definition it’s not idle, right? Sounds like your problem was network related. If you had an artificial fix in your IDE, you’d still have sqlplus scripts getting dropped, right?
Oct 28, 2011 @ 19:04:12
It was idle as far as the network was concerned, so it got terminated. Honestly don’t know enough about Java/JDBC to know whether there’s a workaround for that while a procedure is executing or a big update is running.
When the connection wasn’t actually working usefully, I set up a user defined report with a query (eg select 1 from dual) and set the refresh value. Unfortunately the max there is 120 seconds so it ran more than I wanted plus it grabs focus every time it refreshes which is irritating. A max of 999 seconds (or even 9999 seconds) would be preferable.
Oct 31, 2011 @ 02:26:04
I’m going to try another tack to convince you.
If it isn’t provided as native functionality, it can be provided as an extension. And you can already get a Keep Alive extension by downloading a JAR from someone’s web site and putting it into the exentsions directory.
A really diligent developer will unzip the JAR and decompile the CLASS to check what SQL the extension is going to run. Most won’t, with the effect that they’ll run some hidden bit of SQL from a ‘bloke down the pub’ against the database on a regular basis.
If the thought of that doesn’t scare the DBA, then they are in the wrong job.
Oct 31, 2011 @ 03:02:12
And we wonder why DBAs are paranoid and don’t trust the developers
look, if you want to go that route, then that’s your perogative, and I won’t try to stop you.
I completely understand why you want this feature and know where the pain is coming from, I just don’t agree with this approach. I’m afraid it would be abused and hide infrastructure flaws in your network.
Imagine a dba running a top Sql report and noticing a few hundred Sql dev users running all this nonsense Sql, just to keep their sessions established.
I get asked all the time how to build logon triggers to keep users off a system – I think his might serve to add fuel to that fire.
Oct 31, 2011 @ 04:30:47
Gary, Jeff,
maybe you both can both flip by 180° and try to understand the main problem to solve it, instead of just fighting each other?
First of all, let’s sort out network timeouts. these are implemented on active network devices (mostly firewalls, sometimes also routers) to clear up their open access lists. To ‘solve’ this issue, SQLNET.EXPIRE_TIME is sufficient.
Now to your real issue: both DBAs and Developers want to avoid (unnecessary) work. Unfortunately they are see the measurement of ‘necessity’ quite different:
For a Developer it seems to be the worst thing on earth to hit a ‘reconnect’ button. It’s such a burden that this imaginary developer will even search the internet for tools to overcome that, decompose these and re-implement it, in case it’s tool does not provide this functionality out of the box.
For a DBA every resource (in his system) not utilized to ‘do work’ is a kind of dissipation. This makes the DBA running for any method to reduce this avoidable work, including additional triggers, profiles, scripts&reports, etc.
Now back to my request: If you can work together, maybe you can elaborate a setup where you can save both resources: developers time and DBAs DBs with minimal deduction on both sides?
Oct 31, 2011 @ 04:58:34
Automatic reconnect is the end-goal. It might just take awhile to get there.
Oct 31, 2011 @ 19:24:24
I feel compelled to point out the MOS “Your session has been idle for 1 hour. To continue using My Oracle Support, click continue. To end your session, click sign out.” has caused some complaints.
I can’t help but think they have login storms every hour, kicking people trying to log in to forums to a non-working server.
Hey, if we can’t know, our brains are hard-wired to fill in the blanks.
Oct 31, 2011 @ 20:52:34
Just a guess, but idle sessions are probably kicked for security purposes. I can dig into it deeper if you’re morbidly curious.
Nov 01, 2011 @ 18:27:55
i am not a developer, but… setting the datasource in application server as JBoss there are JDBC parameters like: valid-connection-checker-class-name
why not use that class to reconnect in dev apps?
using “check-valid-connection-sql” and “SELECT * FROM DUAL” always breaks the performance reports (i still have nightmares about this query…)
Nov 02, 2011 @ 08:39:43
I am not a developer either, so I invite you to check out the OTN message boards, our developers monitor that community very closely.
I can tell you that any sort of automatic reconnects that happen under the covers COULD be bad – you want to know a connection has dropped, a transaction has rolled back, etc.
Nov 02, 2011 @ 09:19:56
Jeff,
I’ve seen Weblogic systems doing “keep alives” all day and all night – usually “select * from dual” or “select count(*) from dual”. Drives me nuts as each W/L server is doing this any time they go idle from the main application.
Be a lot nicer if W/L decided to stop parsing the query every time. I know it’s a soft parse (mostly) after the first one, but come on, can’t W/L keep the statement parsed for reuse?
Profiles are a bad thing too, if your long running query/update/whatever doesn’t hit the network from time to time, the profile terminates it. All that work has to be rolled back as well – affecting performance on the database I should imagine.
Damned if you do, damned if you don’t.
Cheers,
Norm.