WorkflowGen performance improvement for Oracle database

Administration Performance
1

Note: Oracle database is no longer supported as of WorkflowGen version 7.16.0. We recommend migrating to MS SQL Server 2017 or later.

Background

The System.Data.OracleClient provider used by WorkflowGen generates SQL instructions against some system tables (all_synonyms, all_cons_columns, and all_constraints). When those tables contains a lot of records (e.g. when WorkflowGen database shares the same Oracle instance with other databases) the performance is affected when launching a new request/action or completing a request/action.

To minimize the impact, the solution is to redirect all these SQL instructions to a local scope views/tables which are filtered copies of these system tables. These changes are transparent to WorkflowGen.

If you are experiencing response time issue when launching a new request/action then we highly suggest to apply this solution to your WorkflowGen database running on Oracle.

Procedure

  1. Replace WFGEN_USER with your owner’s name in the following SQL instructions:

    create or replace force view WFGEN_USER.all_synonyms (owner, synonym_name, table_owner, table_name, db_link) asselect null, null, null, null, null from dual;
create table WFGEN_USER.all_cons_columns asselect * from sys.all_cons_columns where owner = 'WFGEN_USER';
create table WFGEN_USER.all_constraints asselect owner, constraint_name, constraint_type, table_name, r_owner, r_constraint_name, delete_rule, status, deferrable, deferred, validated, generated, bad, rely, last_change, index_owner, index_name, invalid, view_related from sys.all_constraints where owner = 'WFGEN_USER'

  2. Run them against your WorkflowGen database.

Note

Before each WorkflowGen version upgrade, we suggest dropping these views/tables from your database then recreate them after completing the upgrade procedure.

If the database owner (e.g. WFGEN_USER) has other tables used by other applications, you can still apply this procedure if those tables don’t use Synonyms.