I’m getting lots of connections in my oracle database that contains all the repositories and some source data.
The connections are from a JDBC Thin Client and the OS Username is the user running all the services for BODS.
These connections are used for 1-2 seconds and then become inactive. After a few hours another connection for each repository is created. Slowly but surely these connections are filling the connections on my oracle server.
Any Ideas ?
Oracle version:11.2.0.3.0 64bit
BODS version: XI 4.0 SP2 Patch 4 (14.0.2.409)
Java version on server: 1.6.0_26
are these connections getting reused ? do you see number of sessions increasing and then stop at a certain value ? what is the count of sessions that you see in the database ?
how many DS Management Console users connect in parallel ?
do you have multiple web servers ? how many EIMAPS you have ?
We’re having same issue and was wondering if there’s any further information on where these sessions are coming from and what can be done to reduce the number.
Our dev/test share same oracle database and we are having to restart Oracle weekly to clear out these seemingly redundant sessions. After 2 days our stats are,
28 sessions from AL_Designer.exe,
48 from al_engine and other BO procs: cms, audit and
340 from JDBC Thin Client.
We have 25 dev and 5 testers so the numbers for al_designer and job servers are fine but the number of jdbc connections seems way to high for a small number of users.
These jdbc sessions are coming from our the Job Server machine, Apache Tomcat server and the primary node in our BOE cluster.
We have Oracle SQL Net Client across these machines so what is requiring JDBC and can these be configured to use Oracle client?
Sadly enough I’ve accepted the connections as part of the product (badly designed product that is). Since there is no connection pooling on the BODS server server side the amount of connections is massive.
We kill on the Oracle side connections that are idling for too long to keep the amount of connections down.
But even then we have sometimes up to 500 connections open.
And Werner will raise with support and will update here on progress but there are connections from servers that aren’t the web app servers so it is not just this.
FYI we’re on,
SAP BOE sp2 patch 18
SAP Data Services sp2 patch 6
SAP Information Steward patch 4
Oracle 11g R2
Regarding the jdbc connections I see 1-4 connections per repo per BODS server open.
And that accounts for 95% of the (in)active connections to our database. At 17:00 local time there are not much developers online and there is also not much running in production at this hour.
I cannot really see a pattern. In general I think that the more quiet repo’s have less connections, but a repo that I’ve worked in today with jobs, designer, SQL Developer and the management console has only 1 jdbc connection.
In DS 4.0, every repository is also configured from Central
management Server, as opposed to prior versions of Data services. So
in addition to connections from Data Services Management Console or
Designer there are extra connections to database server hosting
repositories. This is new overhead to DS14 compared to DS12. Is this
the specific issue you are concerned with regarding additional
connections to the Oracle Instance?
As per the supported platform recommendation for SAP Data Services
4.0 install you need a minimum 4 processor or 2 dual core processor
server. With IPS, Data services and other services running on the same box, this could cause issues. So you may want to consider upgrading hardware for the DS 4.0 install.
For what it’s worth, my (current) client’s repositories are all on SQL Server while the data warehouse is on Oracle. There are no orphaned connections on SQL Server. We’ve seen orphaned connections on Oracle but we’re pretty sure these were from jobs that ended badly.
As far as I know my other clients that have repositores on Oracle are using the Oracle native client (OCI?) not JDBC or ODBC.
what is the incident #, if you have filed a support case ?
can you try the following when you see number of connection going up
1 -stop the EIM APS, check the # of connections
2 -stop the tomcat check the # of connections
also, can you check what is the EXPIRE_TIME setting on the database?
It looks like IPS is using JDBC, because I have OCI installed everywhere…
It is not the amount hardware or overhead on the BODS server, it is the amount of connections that the BODS server keeps open with the Oracle server. And Oracle has the feature that it has a maximum amount of connections it can maintain. We raised it already, but it goes now to an amount of connections (750?) that a DBA considers weird for a small application with not that much users.
EIM APs runnig in IPS uses JDBC for connectivity to DS Repo, OCI is used by DS Job server for DS Jobs, since the number of connections that you see are coming from thinClient it’s most likely DS services running in EIM APS or webapp
yes, it’s not the hardware that is the problem here, increasing number of sessions not going to help, there seems to be connection leak (connection is opened but not closed at the end of the operation), as mentioned earlier try to findout which thinclient app is causing this APS or webServer, when you see DB reaching 750 thinClient connections, stop the EIMAPS and check the number of connection then stop tomcat and then check th enumber of session, which one of them causes connection to go down ?
open a case with support, this may require enabling some additional debug tracing in your env to get more details
(BOB member since 2006-05-16)
(BOB member since 2012-03-20)
(BOB member since 2004-03-17)
(BOB member since 2004-12-17)
(BOB member since 2007-09-12)
(BOB member since 2012-03-20)