We are using version 9.3.1 SP2. We recently went from long raw data types to st_geometry. We are seeing very slow performance all of a sudden when zoomed in to an area like 1:1800 or 1:2400. Redraws that used to take 5-10 seconds now require 2.5 minutes or longer to complete. We have been working with ESRI but they have been unable to find a solution. One of the issues that we have been able to isolate is that oracle in choosing a sub-optimal plan when it executes queries that look like this, for tables that are iot's (no spatial index) :
SELECT distinct sp_id FROM ARCFM.S16_IDX$ WHERE gx >= :1 AND gx <= :2 AND gy >= :3 AND gy <= :4 AND minx <= :e1 AND miny <= :e2 AND maxx >= :e3 AND maxy >= :e4
If we drop the index IX2, that the oracle optimizer wants to use, it will use index IX1, that WE want it to use. Index IX1 is the primary key and has all the columns that the query needs. Index IX2 has the column spid, but because it is an index organized table, the index accesses the data by storing the primary key values (sort of) so index IX2 has the columns it needs too. However, to get all the values, it has to scan the entire index, because the column SPID is the only one in IX2. If the oracle optimizer would use index IX1, it could do the range scan which is much faster and cuts out the middle man. When we supply literals, the oracle optimzer can 'see' that it should use index IX1; when we use bind variables (which is what arcmap uses), it isn't estimating selectivity properly and is choosing to use index IX2.
Is anyone else having this problem? Can anyone help us?
Hi TJ. Do you have a raw level 12 trace file that captures the problem that includes #stat lines (rowsource operations) for the SQL? If so can you attach to this thread? The stat lines in addition to waits and binds are very important. If you don't have stat lines and are using 10g, you'll need to reproduce and close out of arcmap completely to close the cursors and stop the trace vs stopping the trace manually in the database.
We don't want to remove the second index on the sp_id attribute because that will have an impact when deleting entries from the domain index.
The next step will be to investigate a 10053 trace when statistics are present on the object. Hopefully the trace will show why the optimizer is choosing the sp_id index verses the composite index (driving access path by gx/gy).
One thing to look into... what is the distribution of gx/gy values? If the min/max range of gx or gy is low (possibly all features reside within the same gx/gy?) then the optimizer could easily lean towards the sp_id index. We'll have support provide you a query which returns the distribution of values by grid cell just to make sure you're not working with a suboptimal grid index size.
Finally, you're not encountering the issue which was referenced in the other threads - why the optimizer is choosing a full table scan on the table and not using the domain index. That's a different issue which was resolved in 9.3.1 SP where the st_envintersects operator is no longer associated with the st_stats type (meaning the access path will always be the spatial index when the st_envintersects operator is present).
How was this problem resolved? Was the gx/gy distribution a contributing factor?
We have a similar issue that has lead us to make the IX2 Oracle index invisible for during the daytime read only Arcmap and ArcGIS services. We have scheduled processes that make the IX2 indexes visible only for the period when we are running the replication sync from our edit database to the publication database (nightly). This allows the deletes from the spatial index to occur efficiently.
We are running ArcGIS 10.0 SP4 on Oracle 126.96.36.199.0