Note: Oracle database is no longer supported as of WorkflowGen version 7.16.0. We recommend migrating to MS SQL Server 2017 or later.
Optimizer mode
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 RULE .
System tables optimization
The System.Data.OracleClient provider used by WorkflowGen generates SQL instructions against certain system tables ( all_synonyms , 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_synonyms , all_cons_columns , and all_constraints ) from your database beforehand, then recreating them after completing the upgrade procedure.
SQL instructions
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';