SQL Execution Plans

Performance issues can occur if particular SQL queries against ActiveMatrix BPM database tables are taking a long time to execute because they are using inefficient SQL execution plans.

To enable the database optimizer to calculate the best execution plans, the ActiveMatrix BPM database objects should have their statistics refreshed at regular intervals. This can be accomplished by allowing the database to automatically collect and refresh the statistics (GATHER_STATS_JOB). You may need to collect the statistics manually if large updates have been performed, for example when a large number of instances have been started after going live.

You can make use of the Explain Plan statement to display the execution plan that shows how Oracle will carry out a given database query, and test to see if this is in fact the best plan available. Note that:

  • If database access is slow, you may need to refresh stale database statistics.
  • Your application architect and database administrator may find it helpful to understand how users are to filter and order both work lists and audit trails, so that they can configure database indexes or add additional database indexes if necessary. See BPM Filtering and Sorting of Work Lists and Audit Entries for more details.

    If work list processing performance is slow, and deteriorating over time, this could be because a query to get a work list is executing slowly. This can impact other operations, resulting in error messages such as the following:

    • In the BPM node log: [httpConnector_205] [ERROR] org.hibernate.util.JDBCExceptionReporter - ORA-02049: timeout: distributed transaction waiting for lock
    • In the TIBCO Workspace or TIBCO Openspace log: <error code="
INTERNAL_SERVICE_FAULT_ALLOCATE_AND_OPEN_WORK_ITEM"message=
"could not load an entity:[com.tibco.n2.brm.orgentity.config.
OrganisationalEntityConfig#AD66DB42-13DF-4609-B3A9-5B6D93543D74]"xmlns=""><parameter>org.hibernate.exception.SQLGrammarException: could not load an entity:[com.tibco.n2.brm.orgentity.
config.OrganisationalEntityConfig#AD66DB42-13DF-4609-B3A9-5B6D93543D74]</parameter> </error>