Relation between Weblogic Data Sources DB Sessions Configuration with DB Instance session and process in RACK infrastructure !!!

In this post I will try to summarize the outcome of one POC which I did as part of an incident raised for DB connection failure error.

Scenarios -

SOA Servers were not able communicate with DB properly, business transactions were getting failed intermittently, when I looked into SOA WLS Servers logs, it was showing below error messages-

“ORA-12516, TNS: listener could not find available handler with matching protocol stack”

Later, I tried testing DB connection using sqlplus utility and there also I got same message-

Even, SQL Developer tool also was throwing same error –

When I contacted to DBA, he told even he is not able to login into Database coz of configure DB session and process got exhausted on the DB vServer.

After getting this reason, I stopped couple of Weblogic servers as we have clustered environment and shutting down couple of WLS servers does not impact business transactions.

After shutting down WLS servers, DB session and process count went down and DBA as able to do regular DBA administration on those nodes.

However, above situation has opened few questions for me as below stated –

1)      How much DB session and DB process count has been configured on each DB node?

2)      How much DB session are getting created using various Weblogic Data Sources?

3)      What is the relation between WLS Data sources Initial Capacity, Maximum Capacity, Minimum Capacity configuration with Database sessions count?

4)      What would be optimized connection configuration value for WLS data sources etc.?

5)      Does the WLS data sources sessions count get split between both RACK node equally or each RACK node get that many number of DB sessions?

Current WLS DS Configuration snap -

Solution:

In order to explore answers of all above questions, I did some POC and engage DBA to understand the impact of my POC which it was creating at DB sessions count, so that we can conclude what’s going on.

In my test environment I was having 14 WLS servers running. I had shut down all of them expect ‘AdminServer’ to reduce the session count in DB Instance.  

In WLS Admin server below data sources were deployed and 30 was number configured for initial, minimum and maximum capacity. All three config parameter has same value.

DataSourceName	Schema User Name	Max/Min/Initial Config Connection	Deployed Targets
mds-owsm	XXXPRE_MDS	30	AdminServer
mds-soa	XXXPRE_MDS	30	AdminServer
wlsbjmsrpDataSource	XXXPRE_SOAINFRA	30	AdminServer
OPSSDataSource	XXXPRE_OPSS	30	AdminServer

Looking at DB, we have captured the below session stats –

select count(1), username, inst_id from gv$session;

Session Distribution Explanation -

1)      XXXPRE_MDS schema is getting sessions from two WLS data sources mds-owsm and mds-soa which has 30 sessions configured for Initial, Min and Max each. All config parameter has same value as per Oracle recommendation as result we could see XXXPRE_MDS schema user has 30 sessions initiated in each DB instance node. 15 each Weblogic Data sources. Which means WLS has equally distributed number of sessions to both DB rack node for a Data Source.

2)      XXXPRE_OPSS schema sessions count distributed 14 and 16 in DB instance 1 and 2 respectively. Which means it’s not mandatory that always WLS distribute equally sessions but it’s make sure it creates the number of sessions in Database as per configuration in WLS console. The count in WLS configuration and count in DB session in both DB instance node are matching.

3)      XXXPRE_SOAINFRA schema sessions similarly distributed 17 and 16 (total 33), while it should be 30, it  is because it has 3 extra sessions created by external application e.g. SQL developer

Conclusion:

Based on above stats, I am concluding that if WLS Datasource has 30 connection configuration and we are using SCAN listener to connect to database running on RACK Infrastructure then total 30 sessions get distributes across both DB nodes. So, the thumb rule to define the count of session and process in DB is -

Session count in DB = WLS DS connection configuration * no. of WLS Servers deployed that DS  / No. of DB Instance in RACK

E.g.

Session count in DB = 30 (WLS Config) * 3 OSB WLS Servers / 2 DB Instance in RACK = 45 DB sessions required for one WLS Data source.

Similarly do calculation for all available Weblogic Data sources and configure DB sessions count accordingly.

The best way to find out the no. of sessions required for database is to create an excel sheet, write down all application datasources names, initial, min and max connection configuration, target server name, so that you can conclude the number that your application needs these many DB connection and accordingly DBA has to set session and process count in each database instance.

Sample excel template to capture WLS session count to drive DB session count –

DataSourceName	Schema User Name	Database Name	Max/Min/Initial Config Connection	Deployed Targets	No. Of WLS Servers	Total DB connection Required	Comment	Session Required at DB
XXXSOADataStore	SOAINFRA	SOA11DB	50	OSB_Cluster	3	150	Initial, Min and Max all are equal value	150 / Divided by no. of DB Instance in RACK

I hope this study will give meaningful way of calculating WLS Data source session configuration and accordingly increase or decrease session and process count at DB instance level.