3

I just inherited a server running SDE application server 10.1 against an Oracle 11g database. I'm finding that many connections are not getting properly dropped and stay on as Orphans. Eventually the number or Oracle connections maxes out and new users can't connect.

sdemon -o info -I users 

will show only a handful of valid connections but windows task manager will show many hundreds of gsrvr.exe processes and Oracles v$session table will also show many hundreds of connections, most with a status of "killed". The only way to actually remove these sessions is to kill the corresponding gsrvr.exe process in task manager. This clears out the v$session table and allows new users to connect.

So my question is how are these orphans generated and how do I prevent it from happening. Also is there and automated way to kill then instead of manually going through windows task manager?

  • 1
    The processes start out being legitimate but then end up being orphans in situations like ArcMap stops responding during select by attributes and the user end-tasks it, Network hiccups, spontaneous restarts, Query starting but then crashing due to lack of resources... In PostgreSQL you can stop the server which takes out the PostMaster and all the children... I would expect Oracle to be similar (or even better).... also from PgAdmin you can flick the processes - the users don't notice as the existing calls are restarted, it just seems to take a little longer. – Michael Stimson Jul 17 '15 at 5:10
  • @MichaelMiles-Stimson Thanks Michael. I think bouncing the database might work for Oracle as far as allowing new connections but I don't have the luxury of being able to stop the whole server. I'm also not sure it will kill the windows processes. I just found an esri article on a TCPKEEPALIVE parameter, support.esri.com/ru/knowledgebase/techarticles/detail/34233, that I am going to try. Hopefully it does what I need. If it does I have to ask, why is this not set by default ;) – Dowlers Jul 17 '15 at 5:27
  • 1
    This is one of the many reasons to use Direct Connect instead of application server connections (which are no longer possible at 10.3.0/10.3.1). sdemon -o kill will terminate both specific connections and all application server connections (sde user needs exotic permissions to kill Direct Conect sessions). TCPKEEPALIVE is chatty, and can hurt network performance. – Vince Jul 17 '15 at 11:12
  • @Vince Thanks for the heads up about TCPKEEPALIVE maybe that why the previous DBA didn't implement it. I'll use with caution. I wish we could enforce direct connect but one of our main clients/funders won't put the Oracle client on their users desktops :( – Dowlers Jul 17 '15 at 15:12
  • 1
    A full Oracle Client is no longer necessary, and the Instant Client is tiny, and doesn't use any registry keys (no installer). – Vince Jul 17 '15 at 16:25
3

Esri has two mechanisms for making database connections embedded in the ArcSDE 'C' API. The first (original) protocol uses an application server (giomgr) process, usually running on the database server, to accept network connections and then bequeaths the connection to a child (gsrvr) process to manage database interaction on behalf of the client. While efficient in terms of network load, the application server paradigm has its faults:

  • It increases CPU and RAM utilization on the database server
  • It increases licensing cost if not run on the ArcGIS Server host (and CPU load when run on the AGS server)
  • It's subject to transient events that cause the service process to hang, creating orphan sessions

ArcSDE has always had the ability to kill running sessions (using sdemon -o kill), and has the option of enabling TCPKEEPALIVE (which is a misleading name, since it's purpose is to locate network sessions which have died, mostly by increasing traffic [making it "chatty], so that quiet sessions can be killed), but orphan sessions can still accumulate over time. Restarting the application server clears out accumulated sessions, but will also kill all active sessions, so it's not available in all environments.

The newer (and now default) Direct Connect protocol uses the same exact code as the gsrvr, but bundles it as a DLL, to be run as a separate thread in the client application, shifting the database load to the clients (which are much more powerful than when Direct Connect was originally released). The drawbacks to using Direct Connect are:

  • Administrative privileges are needed for the 'SDE' user to terminate Direct Connect sessions
  • Older releases were not as flexible in heterogeneous geodatabase operation (binary client not matching the geodatabase instance)
  • The clients must each have a viable database client install

Most database clients are relatively small, but until recently, the required Oracle Client needed at least 500Mb. Fortunately, that is no longer the case -- The "Oracle Instant Client" install is bundled within recent ArcGIS Desktop and Server installs, and weighs in closer to 60Mb, with a deployed size under 150Mb, and it doesn't require "Setup.exe" install, so the client doesn't need administrative rights for deployment on Windows operating systems.

Much like the "Doctor, Doctor" joke ("Doctor, doctor! It hurts when I do this"), the solution to application server hangups is, "Don't do that." The situations which create orphan application service processes are not easily solved, but using Direct Connect makes the issue moot (albeit with a more complicated "kill" solution for otherwise operational clients).

Even though you're using an older ArcGIS release, you should also be aware that application server use was deprecated at ArcGIS 10.2, and is not available at ArcGIS 10.3 (though application server client connections are still possible to pre-10.3 geodatabases), so moving in the direction Esri has been advocating for the last five major releases is probably a good move.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.