FAQ: Why is My Oracle Spatial Query Being Parsed on Each Execution?

Answer

For each layer an ArcSDE client session queries with a spatial envelope, ArcSDE holds up to 6 cursors (per layer) open in the database. Holding a cursor open in the database improves performance and scalability, by eliminating the parse phase prior to each cursor being executed.

An example of when multiple cursors are held open by ArcSDE is when ArcMap displays a layer and the user builds a map cache. Because the select list is different between the two queries, two cursors are active in the database.
Typically, when displaying a layer in ArcMap the application only fetches the geometry column. An example of when additional columns are fetched is if the feature class contains a subtype. In this case, in addition to the geometry column be retrieved, the subtype field is also fetched. An example of when all columns are fetched is when you build a map cache.

If it is detected that the spatial query is not being held open, meaning it is parsed prior to each execution, usually detected by capturing an Oracle trace and the queries parse count is > 0, then the application has either exceeded the threshold (6) or the layer's envelope is empty.

To check a layer's envelope use the sdelayer -o describe_long command -

c:\>sdelayer -o describe_long -l primaryoh,shape -u <username> -p <password>

Layer Description ....: <None>
...
Layer Envelope .......:
minx: 1147447.29990,
miny: 11647262.39221
maxx: 2114242.55866,
maxy: 12606262.75253

If the layer envelope is empty, use the sdelayer -o alter command with the -E option to calculate an envelope or by explicitly setting an envelope.

Once a layer's envelope has been defined, ArcSDE holds open queries with spatial envelopes.

Article ID:000006983

Software:

ArcMap 9 x

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Why is my Oracle spatial query being parsed on each execution?

Answer

Discover more on this topic