English

Bug: ArcGIS users working with versioned data and Oracle 10.2.0.4 encountering Oracle bug 7155655

Description

ArcGIS users working with versioned data and Oracle 10.2.0.4 have the potential of encountering Oracle bug 7155655.

The bug is specific to ESRI versioning queries when the Oracle optimizer chooses an execution plan using the view pushed predicate operation. When this occurs, there is a potential that the query will not return the correct result set and can lead to errors in ArcGIS while editing, or data inconsistencies in a versioned object class (occurring when compressing the geodatabase or saving an edit session).

Cause

The cause is specific to Oracle 10.2.0.4 and only happens when the Oracle optimizer selects an execution plans containing the operation 'VIEW PUSHED PREDICATE'. For specific details on the Oracle bug, please see Oracle Metalink and search for bug 7155655.

Not all versioning queries that use this operation and execution plan are affected by the problem, and all the possible SQL statements and conditions that might encounter this Oracle bug (one specific case was identified by a user attempting to delete an object with ArcGIS) are unknown to ESRI.

The following is an example of an execution plan for a versioning query with the operation 'VIEW PUSHED PREDICATE' that encountered the Oracle bug.

Code:
----------------------------------------
| Id | Operation |
----------------------------------------
|* 1 | FILTER |
| 2 | NESTED LOOPS OUTER |
| 3 | NESTED LOOPS |
|* 4 | TABLE ACCESS BY INDEX ROWID|
|* 5 | INDEX RANGE SCAN |
|* 6 | INDEX UNIQUE SCAN |
| 7 | VIEW PUSHED PREDICATE |
|* 8 | FILTER |
| 9 | NESTED LOOPS |
|* 10 | INDEX RANGE SCAN |
|* 11 | INDEX UNIQUE SCAN |
----------------------------------------

Workaround

ESRI recommends that customers with versioned geodatabases who are considering migrating to Oracle 10.2.0.4 do not migrate. Remain at the current release until Oracle releases 10.2.0.5 and ESRI has tested it to verify that the issue reported in the ESRI software alert for Oracle bug 7155655 has been resolved.

For customers that have already migrated to 10.2.04, or need to move to 10.2.04 for other business reasons, Oracle has given ESRI approval to inform our customers of a valid workaround. This workaround requires setting the hidden Oracle parameter "_push_join_union_view"=false or "_push_join_predicate"=false.