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-owsmand 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.