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.

[danan]

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