Troubleshooting > ThirdPartyIssues > Resolving Degraded Performance Issues When Performing Updates or Using User-Defined SQL with Oracle 11 Database Targets

Resolving Degraded Performance Issues When Performing Updates or Using User-Defined SQL with Oracle 11 Database Targets

Article #: Product: Version:

Summary

Tasks that perform update operations or use user-defined SQL statements with Oracle 11 database targets on Oracle server version 11.2.0.2 or earlier experience degraded performance under certain conditions.

Oracle known issue #7019313 can cause Oracle to roll back entire batch operations when an error is encountered. This issue affects DMExpress when connecting to affected versions of Oracle 11 servers if Abort task if any record is rejected in the Target Database Table dialog is not selected. In this case, DMExpress is unable to use batch mode internally, which causes degraded performance.

To resolve the performance issue, upgrade the Oracle server to version 11.2.0.3 or modify the specified options for the target in the DMExpress task.

Symptom

You may experience degraded performance when writing to Oracle database targets if your DMExpress task meets all of the following conditions:

If DMExpress detects that the performance of your task is significantly degraded because of this Oracle issue, the following message will be issued:

(ORABEBUG) performance of writing to target database "<DATABASE_NAME>" was not optimal

Resolution

To resolve the problem, upgrade your Oracle server to version 11.2.0.3.

If upgrading the Oracle server is not possible, modify the affected DMExpress task by selecting Abort task if any record is rejected.

Additional Information

Due to the same Oracle issue, if you set a commit interval for the target database table, you must still select Abort task if any record is rejected. Otherwise, the following error displays when running the task with an affected version of the Oracle server:

(TBLORCOL) unable to output records to database table "<table_name>". Additional messages will provide further details

(CMTBUGAB) User specified commit interval for target table "<table_name>" in database "<db_name>" can only be honored if you also specify the task to abort upon any error while writing to the target table.

For additional information, see Oracle known issue #7019313.

Last updated: