Spatial View very slow

[ScottSugden]

Hi,

Environment: Windows Server 2008 R2 Standard, Oracle 11g, ArcGisServer 10.1 SP1

Using an existing Feature class, CADASTRE, I created an Oracle view as follows:

CREATE OR REPLACE FORCE VIEW "CADASTRE_TEST" (
"OBJECTID",
"CADID",
"LOTNUMBER",
"SECTIONNUMBER",
"PLANNUMBER",
"PLANLABEL",
"SHAPE")
AS
  select /*+FIRST_ROWS*/ c.objectid, c.cadid, c.lotnumber, c.sectionnumber, c.plannumber, c.planlabel, c.shape
  from CADASTRE c;

As you can see this is about as basic as it gets. There is no join etc. The only thing it does it select a subset of columns. In theory this should be faster than selecting * on CADASTRE. I tried it with and without the /*+FIRST_ROWS*/ hint.

I then described the feature class CADASTRE using sde as follows:
sdelayer -o describe_long -l CADASTRE,SHAPE -i sde:oracle11g:xxx -u GIS -p xxx   (xxx is dumy)

From that I worked out the shape data type, the SRID and the feature types. I then registered CADASTRE_TEST with sde as follows:
sdelayer -o register -l CADASTRE_TEST,SHAPE -e nac+ -t ST_GEOMETRY -C OBJECTID,USER -R 7 -i sde:oracle11g:xxx -u GIS -p xxx

The spatial view is then visible, selectable, identifiable etc in ArcMap/ArcCatalog. However, it is very slow. It is almost as if the spatial view is not using the spatial index from the feature class?

Any ideas? Thank you in advance.

[VinceAngelo]

What happens if you reference the shape column without the "c" alias prefix?

- V

[ScottSugden]

Hi Vince,

Thanks for responding to this thread also.

There is no improvement to performance when I don't use an alias.

I forgot to mention that when using SQL Developer 'Select * From CADASTRE' is about the same speed as 'Select * From CADASTRE_TEST'.

[VinceAngelo]

An unbounded SELECT * doesn't really have anything to do with spatial query
performance.  You need to fashion a spatial query to test spatial search.
Then you can use Oracle to figure out the query plan. 

I don't ever add hints, so I can't help but wonder if that's the problem.

- V

[ScottSugden]

I have already tried it with and without the /*+FIRST_ROWS*/ hint - no change.

I agree with what you are saying about "An unbounded SELECT * doesn't really have anything to do with spatial query  performance.", but I suppose that is what I was trying to say. That the issue would appear to be either a spatial query performance problem (and not an attribute query problem) or Arc is having some sort of issue with the view? Oracle does not appear to be having any issue with the view?

Thanks.

[VinceAngelo]

You don't know if Oracle has a problem with the view until you try a pair
of spatial queries (on table and view) and look at the query plans.

- V

[ScottSugden]

True. However, I cannot get any ST_ functions to work. I am getting 'ORA-28595: Extproc agent : Invalid DLL Path'. I have tried a number of suggestions from online but none of them seem to help. So unfortunately I cannot run a spatial query in Oracle.

[VinceAngelo]

You'll need to fix the Listener/LIBRARY configuration issue first then.  Tech Support
can help with both.

- V

[ScottSugden]

Ok, finally got ST_ functions working. Turns out I needed to change 'SET EXTPROC_DLLS=' to 'SET EXTPROC_DLLS=ANY' in the extproc.ora file.

I tested the following query on both the CADASTRE table and the CADASTRE_TEST view:

Select *
From cadastre_test c
Where sde.st_intersects(sde.st_geometry ('polygon ((333733 6404057, 345653 6404057, 345653 6394105, 333733 6394105, 333733 6404057))', 28356), c.Shape) = 1;

Returning the first 500 rows over a WAN the CADASTRE table took 4.1 seconds and the CADASTRE_TEST view took 3.7 seconds. This is what I would expect, that is the view is slightly faster.

I think this also shows that Oracle does not have a performance issue with a spatial query on the view. So does this suggest and issue with ArcGIS or perhaps the way the view has been registered in the geodatabase?

Thanks,

[VinceAngelo]

ArcGIS does an ST_EnvIntersects query.  Since there is nothing special about
that query, if the return results are slow, then there is an Oracle optimizer issue.
Tech Support can help you diagnose the problem.  Make sure you evaluate the
view without any hints.

- V