Note: Oracle database is no longer supported as of WorkflowGen version 7.16.0. We recommend migrating to MS SQL Server 2017 or later.
To improve WorkflowGen database performance, check your database optimizer mode. If
optimizer_mode is set to
choose (the default in Oracle 10g), you have to frequently (weekly, for example)
ANALYZE all the tables of your databases. This operation will generate statistics used by the optimizer to select the best mode (
RULE instead of
all_rows ) to run an SQL query. Another solution is to set the optimizer mode to
System tables optimization
System.Data.OracleClient provider used by WorkflowGen generates SQL instructions against certain system tables (
all_cons_columns , and
all_constraints ). When those tables contain a lot of records (such as when WorkflowGen database shares the same Oracle instance with other databases), performance can be affected when launching or completing a request or action.
One way to minimize issues with response times when launching new requests or actions is to redirect these SQL instructions to local scope views/tables, which are filtered copies of these system tables. These changes are transparent to WorkflowGen.
To do this, run the SQL instructions below (replacing
WFGEN_USER with your owner name) against your WorkflowGen database. (If the database owner has other tables used by other applications, you can still apply this procedure if those tables don’t use synonyms.)
Note: When upgrading WorkflowGen to a new version, we suggest dropping these existing views and tables (
all_cons_columns , and
all_constraints ) from your database beforehand, then recreating them after completing the upgrade procedure.
create or replace force view WFGEN_USER.all_synonyms (owner, synonym_name, table_owner, table_name, db_link) as select null, null, null, null, null from dual;
create table WFGEN_USER.all_cons_columns as select * from sys.all_cons_columns where owner = 'WFGEN_USER';
create table WFGEN_USER.all_constraints as select 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';