Oracle & ST_GEOMETRY: slow performance

[SantiagoLastra]

Hi everybody.
There was a post in the old ESRI Discussion Forums (http://forums.esri.com/Thread.asp?c=158&f=2291&t=297314) related to slow performance of ArcSDE with Oracle and ST_GEOMETRY feature classes.
We also have the same problems described there. There was an user in this post, "T B", that seems to work at ESRI Inc., that wrote the following:
"I've started KB article 37468, "Oracle's optimizer uses a full table scan when executing a query against a st_geometry attribute" and will hopefully have this published before the year (2009) is over... "
I've been searching for this KB but I haven't found it. "T B", if you read this message, please update us with more information about this problem and tell us if you finally wrote the KB that you were talking about.
Thanks in advance,
Santiago Lastra.

[VaL]

Hi everybody.
There was a post in the old ESRI Discussion Forums (http://forums.esri.com/Thread.asp?c=158&f=2291&t=297314) related to slow performance of ArcSDE with Oracle and ST_GEOMETRY feature classes.
We also have the same problems described there. There was an user in this post, "T B", that seems to work at ESRI Inc., that wrote the following:
"I've started KB article 37468, "Oracle's optimizer uses a full table scan when executing a query against a st_geometry attribute" and will hopefully have this published before the year (2009) is over... "
I've been searching for this KB but I haven't found it. "T B", if you read this message, please update us with more information about this problem and tell us if you finally wrote the KB that you were talking about.
Thanks in advance,
Santiago Lastra.

Hi
slow performance in what sense?
We are using oracle sdo_geometry and havent noticed any slowness.
Is it slow when query data in catalog search tool or query one FClass or what?

[SantiagoLastra]

Hi
slow performance in what sense?
We are using oracle sdo_geometry and havent noticed any slowness.
Is it slow when query data in catalog search tool or query one FClass or what?

Hi, Garnet.
I can tell you that we have slow performance in nearly all senses, with ST_GEOMETRY not with SDO_GEOMETRY. We cannot move now to other storage scheme because we have developed an app with many ST functions and we really prefer not to do it. In the development environment everything worked well, but in the production environment (with much more and bigger data) all we get is slow performance. What I mean when I say "slow performance" is that we have an old system working (7 years old HW) with Oracle 8.1.7, ArcSDE 9.1, ArcIMS 9.1 and the performance is better that with new and better HW and with Oracle 10, ArcSDE 9.3.1, ArcGIS Server 9.3.1.
Anyway, the problem that we have is very well described in the following thread of the old ESRI forums:
http://forums.esri.com/Thread.asp?c=158&f=2291&t=297314. The problem is that Oracle's optimizer uses a full table scan when executing a query against a st_geometry attribute, instead of using the spatial index.
Finally, what I'm looking for is the solution that "T B" said in the above thread. "T B" said that he was writing the KB article 37468, but I couldn't find it.
Regards
Santiago Lastra

[SeanGrant]

Have you taken a look at these documents?:

http://resources.arcgis.com/content/kbase?fa=articleShow&d=32601

http://resources.arcgis.com/content/kbase?fa=articleShow&d=33341

http://resources.arcgis.com/content/kbase?fa=articleShow&d=33870

-Sean

[SantiagoLastra]

Thanks, Sean
I read before two of the three links that you included. In my opinion, including a hint in the query is a good solution for queries defined in some search tools of my ArcGIS Server web applications but, for example, not for the navigation tools. We have slow performance even when we're navigating over the map (zoomin, zoomout, pan,...). As far as I understand, including an specific hint in the navigation tools for every layer suppose to modify the code of my web application whenever I add a new layer to the MXD map. Maybe I'm wrong, but I think this is the case.

Anyway, the ESRI support representative in my country told me that this is a recognized bug by ESRI Inc., but their plans are to solve the problem in ArcSDE 9.4, not to issue a patch for ArcSDE 9.3.1. We began the project two years ago with ArcGIS 9.2, and because of many other performance problems we had to migrate to ArcGIS 9.3.1 (six months delay), and now, ¡¡¡ THE ONLY SOLUTION THAT ESRI GIVES ME IS TO MIGRATE AGAIN !!! Are they really fool? Do they think that my only work is to deal with ArcGIS software bugs? When ESRI supposes that I will put to work the system? Sorry for my comments about ESRI, but I needed to release some stress.

Santiago Lastra.

[SeanGrant]

Do you have the service pack for 9.3.1 installed? I thought I heard that there were some performance improvements for ST geometry. Also have you recalculated you spatial index grids?

What version of Oracle are you running for your 9.3.1 instacne (10.2.0.4?)

[SusanMcclendon]

We are having the same issues with slow downs - expecially utilizing dynamic segmentation on our roads layer in ST_GEOMETRY.  Also we are having slow downs using SDE vs direct connect.

Any update from ESRI on these issues would be great.  We do not have a developemnt Oracle 11g 64 bit DB to test ArcSDE 10/ST_Geometry type to see if the "bugs" are fixed.  Frankly, they are not bugs, but PIA.  If this was a know issue why did ESRI switch to ST_GEOMETRY as the default for Oracle Databases.

Susan

(my post on some of the issues we are having http://forums.arcgis.com/threads/19000-Citrix-slow-down-with-upgrade-Oracle-DB)

[danan]

Was KB 37468 ever written? I can't find it. And several threads ask about its existence. Is there a newer KB article number addressing the issue that supercedes it? The issue being setting the selectivity of the st_envintersects operator to prevent superfluous Full Table Scans on feature classes using ST_GEOMETRY as the shape type.

Wanted to know what the current suggested solution is for ArcSDE 9.3.1. Also, we're running SDE 9.3.1 with no service packs. Do any service packs improve query performance for ST_GEOMETRY? Or do SDE 9.3.1 installations with the latest service pack suffer from the same FTS problem?

Is the following still the best workaround:

This is what I'm recommending, instead of setting the num_rows = 0, please execute the following as the data owner -

From post: http://forums.esri.com/thread.asp?c=158&f=2291&t=297314


[INDENT]DECLARE

CURSOR each_table IS
SELECT table_name, column_name
FROM sde.st_geometry_index
WHERE owner = USER;

BEGIN

FOR each_row IN each_table LOOP

sde.spx_util.set_operator_selectivity(user,each_row.table_name,each_row.column_name,'st_envintersects',.005);

END LOOP;
END;
/ [/INDENT]

Also, can the code above *only* be run as the data owner. As a SDE, I'd prefer to run it as my DBA account. I don't have the password for the data owner.

Further, the SQL being generated is "shrinkwrapped" SQL coming from ArcGIS Explorer 1500 (AGX). So the three KB articles sean4788 referenced above are of no use to us (meaning we can't open up Esri's binaries and force indexes to be used). Those KB articles seem useful only to those writing their own SQL queries and not having them auto-generate in an Esri client like AGX.

Thanks.

[NickHarrison]

Santiago,

Have you considered that the performance differences yiou are seeing may be due to ArcIMS vs ArcGIS Server rendering and not the database at all?

The ArcIMS AXL render is significantly faster (although less extensive catographically) than ArcGIS Server. This is based on experience with ArcIMS/ArsGIS Server 9.2 and earlier.

Nick

[SusanMcclendon]

Did you try this?  Did is solve the performance issues?  Did you ever get any confirmation from ESRI?

Thanks,

Susan

Was KB 37468 ever written? I can't find it. And several threads ask about its existence. Is there a newer KB article number addressing the issue that supercedes it? The issue being setting the selectivity of the st_envintersects operator to prevent superfluous Full Table Scans on feature classes using ST_GEOMETRY as the shape type.

Wanted to know what the current suggested solution is for ArcSDE 9.3.1. Also, we're running SDE 9.3.1 with no service packs. Do any service packs improve query performance for ST_GEOMETRY? Or do SDE 9.3.1 installations with the latest service pack suffer from the same FTS problem?

Is the following still the best workaround:

This is what I'm recommending, instead of setting the num_rows = 0, please execute the following as the data owner -

From post: http://forums.esri.com/thread.asp?c=158&f=2291&t=297314


[INDENT]DECLARE

CURSOR each_table IS
SELECT table_name, column_name
FROM sde.st_geometry_index
WHERE owner = USER;

BEGIN

FOR each_row IN each_table LOOP

sde.spx_util.set_operator_selectivity(user,each_row.table_name,each_row.column_name,'st_envintersects',.005);

END LOOP;
END;
/ [/INDENT]

Also, can the code above *only* be run as the data owner. As a SDE, I'd prefer to run it as my DBA account. I don't have the password for the data owner.

Further, the SQL being generated is "shrinkwrapped" SQL coming from ArcGIS Explorer 1500 (AGX). So the three KB articles sean4788 referenced above are of no use to us (meaning we can't open up Esri's binaries and force indexes to be used). Those KB articles seem useful only to those writing their own SQL queries and not having them auto-generate in an Esri client like AGX.

Thanks.