In this blog I am just sharing one of the experience which I had in one of my recent engagement. As part of performance testing phase, I was expected to improve the performance of composite (SCA) services response. As most of you already aware that SCA heavily rely on database and the interface between SCA to database is weblogic datasource. When we install the Oracle SOA product and create weblogic SOA domain it creates couple of out-of-box datasouces which connect to SOA repository in database.
This experience was specific to Weblogic/SOA 18.104.22.168.0 version.
While reviewing oracle performance tuning guide, I came across one property of datasource connection pool called “Pinned-To-Thread”, by definition this parameter is expected to increase the performance. Here is the definition given in oracle document –
“To minimize the time it takes for an application to reserve a database connection from a data source and to eliminate contention between threads for a database connection, you can add the Pinned-To-Thread property in the connection Properties list for the data source, and set its value to true. When Pinned-To-Thread is enabled, WebLogic Server pins a database connection from the data source to an execution thread the first time an application uses the thread to reserve a connection. When the application finishes using the connection and calls connection.close(), which otherwise returns the connection to the data source, WebLogic Server keeps the connection with the execute thread and does not return it to the data source. When an application subsequently requests a connection using the same execute thread, WebLogic Server provides the connection already reserved by the thread. There is no locking contention on the data source that occurs when multiple threads attempt to reserve a connection at the same time and there is no contention for threads that attempt to reserve the same connection from a limited number of database connections”
Above definition seems interesting, as result I did implement this change there, along with some other performance changes.
This configuration exists at following path >> Domain Home >Summary of JDBC Data Sources >SOADataSource >> advanced >> Enable “Pinned-To-Thread”
However, I have noticed a wired behavior in environment after few performance changes, although was not sure which particular configuration is causing the issue.
DBA have configured a fixed number of process and session for SOA database, but in that environment the DB process and session count keep increasing over the period of time and eventually Weblogic servers crashes it won’t find any available process and session. This was that much worse even DBA can’t log into database using sysadm privilege, they finally need to kill database. Below was the error message appearing in weblogic server logs –
[2016-09-07T09:08:48.178+10:00] [wls_soa01] [ERROR]  [oracle.integration.platform.blocks.scheduler.FabricJobStoreCMT] [tid: QuartzScheduler_FabricScheduler-1328406670_ClusterManager] [userId: <anonymous>] [ecid: e45e0ed7-45d9-4dd6-a7bf-9e4ce6f91db4-00000442,0] [APP: soa-infra] [partition-name: DOMAIN] [tenant-name: GLOBAL] ClusterManager: Error managing cluster: Failed to obtain DB connection from data source 'soaNonManagedDS': java.sql.SQLException: Could not retrieve datasource via JNDI url 'jdbc/SOALocalTxDataSource' weblogic.jdbc.extensions.ConnectionDeadSQLException: weblogic.common.resourcepool.ResourceDeadException: 0:weblogic.common.ResourceException: Could not create pool connection for datasource 'SOALocalTxDataSource'. The DBMS driver exception was: Listener refused the connection with the following error:[[
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
Above error was coming for all the datasource not just one, obviously when we looked inside database were able to notice that all DB process and session count hit to max capacity. Below SQL query can be used to verify the same-
select count(*) from v$session;
select count(*) from v$process;
You can also verify max Process allocated to particular DB using below query –
SELECT name, value FROM v$parameter WHERE name = 'processes';
After verifying DB process and session max usage, I was sure some Weblogic configuration is causing this to happen. Later I started rollback of few performance config changes one after one and finally roll backed “Pinned-To-Thread” configuration.
After rollback of this change DB process and session count didn’t increase and it stay as it,
only few process and session count was increasing which is expected when load get processed.
While this change was active, even datasource max capacity configuration was getting ignored, datasource was not honoring the max capacity and was keep increasing no. of datasource connection.
After going through that phase I personally don’t recommend to anyone to implement this parameter and create chaos in your Weblogic SOA environment.
For more detail about this parameter, please refer below link –