English

FAQ: Can reconcile performance be improved by converting Oracle LONG RAW attributes to BLOB attributes?

Question

Can reconcile performance be improved by converting Oracle LONG RAW attributes to BLOB attributes?

Answer

Yes, reconcile performance can be improved by converting Oracle LONG RAW attributes to BLOB attributes. The ArcGIS reconcile process copies all inserted and updated objects from the version being reconciled to the reconcile's target state.

When an object class contains a binary attribute stored as Oracle LONG RAW, such as the element attribute for an annotation class, the binary LONG RAW attribute must be individually updated in the target state for every inserted and updated object. This is a separate Structured Query Language (SQL) statement. Based upon the number of objects inserted or updated that contain a binary LONG RAW attribute, this separate SQL statement impacts the time required to perform the reconcile operation.

For example, when capturing an Oracle trace of a reconcile where an annotation object class has been updated in the version being reconciled, the following statement in the Oracle transient kernel profiler (TKPROF) file is executed once for every annotation feature inserted or updated:

Code:
UPDATE TOMB.A2049 SET ELEMENT = :1
WHERE
OBJECTID = :sde_rowid AND SDE_STATE_ID = :state_id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3016 19.39 33.28 0 8116484 26130 3016
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3017 19.39 33.28 0 8116484 26130 3016


The TKPROF output, shown above, reports that the adds table (TOMB.A2049) is updated 3016 times, while setting the element value in the target state. This process required 33.28 seconds to execute.

To improve the reconcile performance, convert the Oracle LONG RAW attribute to an Oracle binary large object (BLOB) attribute, which eliminates the need for ArcGIS to update each individual object during reconcile. Instead of performing the individual updates, ArcGIS is able to copy all attributes between the source state and the target state using one INSERT statement.

To convert existing LONG RAW attributes to BLOB attributes, see the link to the ArcGIS Server Web Help topic, "Migrating Oracle data from one storage type to another" in Related Information.

The result, after converting the LONG RAW attributes to BLOB attributes, is both improved reconcile times and better throughput, when reconciling multiple versions.

Related Information