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 12.2.1.1.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 –
No comments:
Post a Comment