Oracle SOA Suite 11g (188.8.131.52) environment is connected to a database which is its dehydration store database, used to store process status data, especially for asynchronous BPEL processes. Production environments tend to produce millions of records in tables of that database. This, when not well maintained, can become a true performance bottleneck in the database and SOA Suite engine itself. Some of the performance issues can be caused by long running queries being queued in a database. Here is the example. Three SQL queries were executed in a database in parallel, multiple times per minute but it required 1-4 minutes for database in order to resolve each of them. Next to that there were more than 150M of records in CUBE_INSTANCE table, because historical composites’ instances were not being purged properly. The entire environment was monitored by Oracle Enterprise Manager 12c.
Here are the 2 of 3 SQL queries (3rd query has different solution):
SELECT ci.DOMAIN_NAME , ci.COMPOSITE_NAME , ci.COMPOSITE_REVISION, ci.COMPONENT_NAME , ci.STATE , COUNT(*) FROM CUBE_INSTANCE ci WHERE ci.COMPONENTTYPE = :1 GROUP BY ci.DOMAIN_NAME , ci.COMPOSITE_NAME , ci.COMPOSITE_REVISION, ci.COMPONENT_NAME , ci.STATE ORDER BY ci.DOMAIN_NAME , ci.COMPOSITE_NAME , ci.COMPOSITE_REVISION, ci.COMPONENT_NAME , ci.STATE
SELECT COUNT(wfn.taskId) taskCount, wfn.applicationName , wfn.compositeName , wfn.compositeVersion , wfn.componentName , COALESCE(wfn.state,'COMPLETED') state FROM WFTask wfn WHERE ( ( ( ( wfn.rootTaskId = wfn.taskId ) AND ( wfn.componentType IS NULL OR wfn.componentType = :1 ) ) ) AND ( wfn.isTemplateTask IS NULL OR wfn.isTemplateTask <> :2 ) ) GROUP BY wfn.applicationName , wfn.compositeName , wfn.compositeVersion , WFN.COMPONENTNAME , wfn.state
First action was to run SQL Tuning advisor in order to search for better SQL Profiles. This helped a bit, we were able to limit SQL execution time from 4 minutes to 1. But that was still not enough. Queries were queued in a database:
Here is a temporary fix:
Disable or limit Data Collection frequency of the following monitoring metric in Oracle Enterprise Manager 12c: “SOA Infrastructure – Recoverable Faults”
Go to OEM12c Middleware Targets page and right mouse click on
Monitoring end click
Scroll down All Metrics list and select
SOA Infrastructure - Recoverable Faults. Click Modify and modify collection schedule.
Once disabled the queries are no longer executed in SOA Database.
Here is potential fix for the 3rd query:
SELECT COMPONENT_NAME , COMPONENT_STATE, COUNT(*) FROM MEDIATOR_INSTANCE GROUP BY COMPONENT_NAME, COMPONENT_STATE ORDER BY COMPONENT_NAME, COMPONENT_STATE
Go to Oracle Enterprise Manager 11g Fusion Middleware Control (not OEM12c)
Expand SOA and right click on
SOA Administration >
Mediator Properties, set
Metric level to
Once disabled, all the three queries were no longer executed in a database.
However this is not a permanent solution. The reason behind this issue is certainly a large number of historical instances in a database causing database processing certain queries much longer than it should be. Next step is to purge/delete unwanted data from the database and implement good purging strategy for SOA Suite environment.
Here are materials about purging large number of instances in SOA Suite 11g environments:
The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.