SDE and Domain Spatial Index (Sel - Undefined)

Question

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

explainplan.png

Original.png

Karissa_Barnes_0-1765831839683.png

SteveMcMaster · Answer

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

VinceAngelo · Answer

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

SteveMcMaster · Answer

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

explainplan2.png

ScaleBarUnit is obsolete.jpg