We are using the JDBC adapter to invoke a stored procedure on Oracle (using the stored procedure template). The problem is that Oracle seems to be using a lot of CPU (it’s on a separate server), and processing very slowly. We’re only processing about 6 docs/sec with 3 ISes in a cluster.
The webMethods service simply takes a document off a queue, invokes the STP. The STP just inserts data into a table.
The DB experts informed us that webMethods is invoking the stored procedure in a way that causes the stored procedure to be loaded and parsed each time the service is run. webMethods TS have confirmed that webMethods uses a Callable statement and that we cannot change the way webMethods behaves in this regard.
Has anyone seen this type of issue before and/or worked around this?
What kind of performance have you seen inserting into Oracle?
First, I would suggest creating a feature request for this issue so that it gets “fixed” in a future release.
You may need to write a java service to invoke this stored procedure in the manner that your DBA suggests is correct and repeat your load tests. This approach would not take advantage of the JDBC Adapter’s connection pool and therefore may have it’s own performance issues.
Thanks for the reply…
After a bit of pulling my hair out, the DBA sent me a snippet of Java code to try and show me which properties he was talking about.
It turns out the properties they require are not a characteristic of the Statement, but rather a characteristic of the Connection.
Therefore adding the following to the “Other Properties” within the JDBC Adapter connection helped to resolve the issue:
implicitCachingEnabled=true;cacheSize=500
This enabled us to move from 6docs/sec to 22 docs/sec.