How To: Improve Fetch Performance from some Oracle Spatial Feature Classes

Summary

** Internal Publish Only! This article may contain information that is not intended for external circulation. **

The SDO_GEOMETRY data type, which is used for geometry storage in Oracle Spatial, does not support annotation features, parametric curve features or CAD data. When creating new feature classes stored with Oracle Spatial, ArcGIS implements these types of features through a BLOB column called SE_ANNO_CAD_DATA. Whenever a feature class is created, ArcGIS assumes that this column will be needed. In the case of storing annotation, another VCHAR2(256) column, SE_ANNO_TEXT, is also added to the new feature class to store the text of the annotation.As a result of the presence of the SE_ANNO_CAD_DATA column, the new feature class can be used to store CAD data. Therefore, the entity flags for the new feature class are set to indicate this support. You can see this characteristic of the new feature class with the ArcSDE command "sdelayer -o describe". The entity flags appear as the item "Entities". When ArcGIS fetches data from an Oracle Spatial table that has entity flags indicating support for annotation or CAD data, it includes the SE_ANNO_CAD_DATA column in the query. Because this is a BLOB column, the array fetch size is forced to one (At ArcSDE 8.2 and previous versions) where it might otherwise be set to a higher value. For example, if the BLOB column were not part of the fetch, and only the rowid column and the SDO_GEOMETRY column were included in the query, then using the out-of-the-box settings in the giomgr.defs file would result in an array fetch size of twenty-four rows. The array fetch size is an important performance factor when retrieving data from the Oracle database. Long-term solutionESRI has developed a software solution that will not require user intervention. This was released in ArcSDE 8.2 patch 1.
Short-term solutionThe short-term solution has two parts: eliminating the fetch of the SE_ANNO_CAD_DATA column when it is not needed, and adjusting the size of the array fetch to a more optimum value.

Procedure

Eliminating the fetch of SE_ANNO_CAD_DATA

There are two ways to eliminate the fetch of the SE_ANNO_CAD_DATA column. First, you can load your data with shp2sde, which does not create this column unless you ask for it. Shp2sde is an ArcSDE data loading utility that, unlike the ArcGIS data loading tools, does not assume that the SE_ANNO_CAD_DATA column will needed. It adds this column to Oracle Spatial tables only if you specify, with the -e option, that the table should support annotation or CAD data. (The entity support flags for annotation and CAD feature support are "A" and "c", respectively.)

The alternative method to eliminate the fetch of the SE_ANNO_CAD_DATA column is to remove the annotation and CAD flags from an existing feature class and re-registering the table with ArcSDE. You can follow these steps to remove the entity flags.

1. Describe the layer. Record the entity flags listed next to "Entities". In this example, the table support nil, area (polygon), CAD and multipart entities.

sdelayer -o describe -l states,shape -u username -p password

ArcSDE 8.2 Build 967 Fri Feb 15 16:52:05 PST 2002
Layer Administration Utility
-----------------------------------------------------
----------------------------------------------------------------
Table Owner : USERNAME
Table Name : STATES
Spatial Column : SHAPE
Layer id : 19
Entities : nac+
Layer Type : In-Line Spatial Type
I/O Mode : NORMAL
User Privileges : SELECT, UPDATE, INSERT, DELETE
Layer Configuration: DEFAULTS

2. Get exclusive use of the ArcSDE server. If other users connect to the server during steps #5 through #6, the auto-registration process will re-register the layer with the annotation and CAD entity support flags.

3. Save the Oracle Spatial metadata for the table.

CREATE TABLE SAVED_METADATA AS
SELECT * FROM USER_SDO_GEOM_METADATA
WHERE TABLE_NAME = '<table name>';

4. Unregister the Oracle Spatial table from ArcSDE. WARNING! Make sure it is an Oracle Spatial table. If it is an ArcSDE compressed binary table, this command will delete the geometry from the table.

sdelayer -o delete -l <table name>,<shape column> -u <...>

5. Replace the Oracle Spatial metadata from the table you saved it in during step #2.

INSERT INTO USER_SDO_GEOM_METADATA
SELECT * FROM SAVED_METADATA;
COMMIT;

6. Manually register the table. Use all entity support flags listed in the output of sdelayer -o describe, excluding "c" or "A".

sdelayer -o register -l <table name>,<shape column> -e na+ -c <objectid column> -C SDE -u <...>

Adjusting the array fetch size

The array fetch size can be increased from 24 to more optimum size of 100 by modifying the giomgr.defs parameters ATTRBUFSIZE to 200800 (it is 50000 by default) and MAXARRAYBYTES to 600800 (it is 550000 by default). When fetching for the ROWID and SHAPE columns from an Oracle Spatial table, the row size is 2008 bytes, which includes 4 bytes for alignment.

array fetch size = 200800/2008 = 100

After modifying giomgr.defs, restart the ArcSDE server to re-read the parameters.

Article ID:000005079

Software:

Legacy Products
ArcMap 8 x

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Improve fetch performance from some Oracle Spatial feature classes

Summary

Procedure

Discover more on this topic