topic Oracle & ST_GEOMETRY: slow performance in Data Management Questions

Oracle & ST_GEOMETRY: slow performance

SantiagoLastra — Tue, 06 Apr 2010 07:12:07 GMT

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.

Oracle & ST_GEOMETRY: slow performance

VaL — Tue, 06 Apr 2010 09:31:55 GMT

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?

Oracle & ST_GEOMETRY: slow performance

SantiagoLastra — Tue, 06 Apr 2010 13:53:54 GMT

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

Oracle & ST_GEOMETRY: slow performance

SeanGrant — Mon, 12 Apr 2010 23:13:41 GMT

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

Oracle & ST_GEOMETRY: slow performance

SantiagoLastra — Thu, 15 Apr 2010 06:11:13 GMT

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.

Oracle & ST_GEOMETRY: slow performance

SeanGrant — Thu, 15 Apr 2010 18:58:44 GMT

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?)

Re: Oracle & ST_GEOMETRY: slow performance

SusanMcclendon — Thu, 10 Mar 2011 16:04:53 GMT

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)

Re: Oracle & ST_GEOMETRY: slow performance

danan — Wed, 23 Mar 2011 11:08:52 GMT

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.

Re: Oracle & ST_GEOMETRY: slow performance

NickHarrison — Wed, 23 Mar 2011 19:58:22 GMT

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

Re: Oracle & ST_GEOMETRY: slow performance

SusanMcclendon — Thu, 28 Jul 2011 16:10:10 GMT

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.

Re: Oracle & ST_GEOMETRY: slow performance

danan — Sat, 11 Dec 2021 14:00:56 GMT

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

Hi Susan,

We ended-up following the instructions in KB 38019 per offline discussion with Esri. It solved our Full Table Scan issue. Very simple solution. And I believe it's what Esri would currently advise if you opened a support ticket. Don't think the other KB article (one referenced in some Oracle SDE presentations at past UCs--circa 2008?) was ever written.

Disassociate the st_domain_operators from the st_domain_stats type in Oracle
http://support.esri.com/en/knowledgebase/techarticles/detail/38019

SQL> DISASSOCIATE STATISTICS FROM PACKAGES st_domain_operators;

First caveat: We applied the KB 38019 fix prior to applying SP 2 for 9.3.1. When we later applied SP 2 for 9.3.1 something funky happened. I believe stats were re-associated. And SP2 did not itself perform the DISASSOCIATE STATISTICS operation. SP2 for 9.3.1 is supposed to execute KB 38019 fix. I vaguely remember seeing the DISASSOCIATE STATISTICS SQL statement appear in a log file or standard output while applying SP2. The following is a separate issue, but applying SP 2 for 9.3.1 dropped all our SDE logfile tables. We had to recreate them by doing by executing sdeconfig and respecifying sde logfile parameters. Strange.

Second caveat: I could be mistaken. But after this work around Oracle's CBO may always favor indexes regardless of the requested map extent (Or I may be confusing this with the workaround you quoted). That may be ok. Because people generally ought not, or ought not be allowed to, pan and zoom at full extents and small map scales (e.g. statewide). At least not with dynamic map services. Use scale dependencies in MXDs or MSDs to prevent inappropriate amounts of features to display in dynamic map services.

Cached map services perform best for geographic orientation (finding your way to features whose attributes you *really* care about). Features you can do an Identify on should generally only appear at large map scales / tightly zoomed into extents. If you can't reasonably click on a map and return only one feature then you're probably displaying too many features.

Hope this helps.

I would like to get a copy of Jonathan Lewis' Cost-Based Oracle Fundamentals and do a Deeper Dive into Oracle's CBO. Would like to understand even more about what's going on under the hood. For anyone reading, is Cost-Based Oracle Fundamentals still the best book on Oracle's optimizer (for 10gR2 and 11gR2)?