topic Re: SDE and Domain Spatial Index (Sel - Undefined) in Data Management Questions

SDE and Domain Spatial Index (Sel - Undefined)

SteveMcMaster — Wed, 15 May 2013 06:30:55 GMT

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

Re: SDE and Domain Spatial Index (Sel - Undefined)

SteveMcMaster — Sat, 11 Dec 2021 16:39:41 GMT

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

Re: SDE and Domain Spatial Index (Sel - Undefined)

VinceAngelo — Mon, 20 May 2013 09:46:32 GMT

That query is for half the globe. ArcGIS won't use the spatial index unless
the search area is smaller than that.

-V

Re: SDE and Domain Spatial Index (Sel - Undefined)

SteveMcMaster — Tue, 21 May 2013 00:51:10 GMT

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

Re: SDE and Domain Spatial Index (Sel - Undefined)

VinceAngelo — Tue, 21 May 2013 01:22:49 GMT

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

Re: SDE and Domain Spatial Index (Sel - Undefined)

SteveMcMaster — Tue, 21 May 2013 02:15:43 GMT

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