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?
