SDE and Domain Spatial Index (Sel - Undefined)

2261
5
05-14-2013 11:30 PM
SteveMcMaster
New Contributor
We have a point feature class with approximately 800,000 features loaded into a 10.1 SDE environment (Oracle 11gR2).

I have noticed very poor performance compared to the the 9.3 environment we initially used. On examining the explain plan being used when ArcMap is attempting to display these points, I noticed that the domain index seems to be using an undefined "selectivity", as per the attached screenshot.

[ATTACH=CONFIG]24315[/ATTACH]

Reading online, ESRI seems to suggest that this situation should never eventuate (although it does not mention 10/10.1 for affected software). See: http://support.esri.com/en/knowledgebase/techarticles/detail/32594

It appears that we have this same poor performance on multiple database servers, and dropping and rebuilding the index does not seem to resolve the issue.

Has anybody got any suggestions or noticed similar behaviour in their environment?

Thanks.

Steve
0 Kudos
5 Replies
SteveMcMaster
New Contributor
Hi,

Would somebody please post their explain plan for the following statement on a 10.1 SDE install running on an Oracle 11gR2 instance?

I'm keen to know whether this domain index is the cause of our poor performane, or whether other users get the same output from an explain plan.
SELECT OBJECTID
  FROM FEATURE_CLASS
 WHERE SDE.ST_EnvIntersects (SHAPE, -90, -90, 90, 90) = 1


Thanks.

Steve
0 Kudos
VinceAngelo
Esri Esteemed Contributor
That query is for half the globe.  ArcGIS won't use the spatial index unless
the search area is smaller than that.

-V
0 Kudos
SteveMcMaster
New Contributor
Vince,

I understand the query is inefficient (I just arbitrarily picked some values) but when I run that query the explain plan shows that it is indeed using the index, and my question is still the same - I get "(sel: default - undefined)" for the selectivity and I'm trying to confirm whether this is a problem?

Please see below.

[ATTACH=CONFIG]24527[/ATTACH]

Could you please confirm whether this is an issue, as described in the ESRI help page at: http://support.esri.com/en/knowledgebase/techarticles/detail/32594

Thanks.

Steve
0 Kudos
VinceAngelo
Esri Esteemed Contributor
Quoting that document:
If (Sel: Default - Undefined) is reported this means the optimizer encountered an
error calculating selectivity or a NULL selectivity value was reported. One should
not encounter this value and should report the condition to ESRI Support Services.


Please contact Tech Support.

- V
0 Kudos
SteveMcMaster
New Contributor
Thanks Vince,

I saw that note but the document doesn't specify 10.1 as an affected version so I wasn't sure if it is still relevant.

I'll contact ESRI and see what they say.

Steve
0 Kudos