Is there any way to disconnect the pre-assigned oracle libraries when are in idle state?

[SangramjitPanda]

Hi All,

Please suggest if there is any solution or workaround for below scenario:

We have 15 pre-assigned Oracle Libraries created in Management Console which are used to connect to 15 different schema. These 15 schema are on single Oracle database. Now what I saw is when any user try to access any one of 15 Oracle libraries from SAS Enterprise Guide, there are like 15 connections gets created on that Oracle Database and goes to inactive state after 1 or 2 mins. Libraries are working as excepted, concern here is the no. of connections(15) created even if we do access one or some libraries. 

Even I tried to replicate this and when I opened EG session and tried to open a library(any one out of 15), then there were 15 sessions were created on that Database. If this will be the case then if multiple users does access any one library then multiple connections will get created and it will cross the session limit there by having an outage on that particular database.

1. Is there any way to have only one connection create for one Library instead of having 15 connections created? 

2. Is there any way to identify from SAS end to see which all users are using which Oracle Libraries and their active/inactive state? As of now I am getting these session details from DBA team but I need to know if we can get details from SAS end?

3. Any setup need to done on SAS Management Console to add options to pre-assigned libraries to disconnect if they are in idle state for more than 1 day or any time frame?

Note: All of the oracle libraries are pre-assigned, and also during library creation, i have set them to defer=yes, type of connection=Sharedread. These schema's are on a single database

Any suggestions will be helpful. 

Thanks!

[Sven111]

I don't know about question 2 or 3, but why do you need to have 15 separate libraries setup to access the same DB? Can't you just have one connection to the DB and then access tables in other schemas using schema qualified tablenames? I have two separate connections to the same DB for some of my code, but that's only because I'm logging in as a different user with different permissions for one of my projects.

[SangramjitPanda]

@Sven111,

It is the way how it got setup before and as per user request and also are in PROD. prod sas codes do refer these librefs.

[SASKiwi]

What is the actual problem here? Are the DBA's complaining about the number of database connections? If this is not putting an extra load on the database what is the problem?

We have a very similar setup with EG using a similar number of preassigned libraries to SQL Server databases, using DEFER = YES and each user authenticating using Windows Authentication. Our DBA's aren't concerned about the number of connections, only about the number and performance of the database queries.

[SangramjitPanda]

@SASKiwi 

Yes, since these are pre-assigned libraries, upon connecting to SASApp(in EG), there are 15 connections gets created on that Oracle database for each EG session even if no one access these libraries and then it goes to inactive state. Actually DBA team have set session limit on Database level and also monitors them, even some monitoring setup is also available which send alerts if sessions crosses some threshold value.

I don't think there is any performance issue but concern is  15 connections are being created for each EG session even if these pre-assigned libraries are not in use. Is there any way to setup from SAS end to have these inactive session deleted and connections should be active only if they are in use.

Sample data:

DB User Logon Time Status SESS Program OS User Machine Name
dbaccnt** 3/25/2019 9:27 Inactive 34,811,740 SAS@SAS*** XXXXXX XXXX

[SASKiwi]

My understanding is that with the DEFER = YES option SAS does not establish the connection straightaway but only when the library is used. What differences do you see with this option? Are connections still established immediately using DEFER?

I suggest you also open a track with SAS Tech Support as will be able to provide a much more detailed understanding of how SAS and Oracle interact.

One way to reduce database connections would be to share them: https://documentation.sas.com/?docsetId=acreldb&docsetTarget=n0j8aq13jyowkon1a3difgmq5yow.htm&docset...

Another way I can see that will greatly reduce database connections would be to not assign libraries at all in metadata but to code them all in your programs then clear them afterwards. This would be a very backward way of working in my opinion. There is a huge advantage in having these libraries available automatically for all users so they don't need to be coded. Personally I really don't see this as a major problem as there is no performance impact. 

[SangramjitPanda]

With using DEFER = YES option for the pre-assigned libraries, still there are connections made to the Oracle database but what i noticed is when we open a EG session and expand SASApp, 15 connections are created on the oracle database and immediately goes to inactive state. 15 connections are created upon opening EG session even if you don't access any library.

I opened a track and following up with them but no luck. even they asked to use defer=yes and some more option but still it didn't worked out.. They said like if they are pre-assigned libraries then they will make connections even if you are not accessing any library.

My concern is here about the no. of connections that are made from SAS since DBA team put a limit on no. of connections to 800 or 1600 and from SAS end for each session creates a 15 connections to that Database so what if 40 or 50 users connect to SAS or opens more than 1 EG session? I don't see any performance impact because they are in inactive state.

[SASKiwi]

@SangramjitPanda - I would try the shared connection option then. See the link in my above post.

[SangramjitPanda]

@SASKiwi 

During creating the pre-assign libraries, I use Sharedread as connection type and defer=yes option.

It still creates connections to database upon opening EG session even if you don't access any of those pre-assign library.

Were you referring that option for performance improvement?

[SASKiwi]

The link in my post is to the CONNECTION_GROUP LIBNAME option. I've never used it myself but it looks like it may help.

[gyan39]

Hi,
We facing similar issues in Oracle with SAS Libraries. Does anyone have answer to the 3rd question above?

We want to kill all inactive Oracle sessions from SAS.

Thanks and Regards
Gyan

[SASKiwi]

@gyan39  - I'm not aware of any such capability short of killing the SAS sessions with idle Oracle connections. I suspect this would be something easier controlled from the Oracle side. Talk to your Oracle DBA.