Bad performance and error execution of ST_Geometry fuctions

DiegoLlamasOlivares

Hi,

I am experimenting some performance problems and error executions when I use ST_GEOMETRY functions in SQL and
I wanted to know how many features from my parcel layer intersect a required municipality. The parcel feature class is versioned and there are several versions created in our database. Using sql I wrote the following statement...

SELECT count(*)
FROM GISADMIN.parcelario08 p,GISADMIN.municipios m
WHERE sde.st_intersects (p.shape,m.shape) = 1
AND m.municipios='VILLALBA'
order by p.num_catastro;

this returns 7434 records in about just a few seconds. Using �??select by location�?� in ArcMap, the application returned 7436 selected features. Both queries were done relatively at the same time. I ran "exec sde.version_util.set_current_version('SDE.DEFAULT');" just in case in my latest tries, but still the same results. I figured out I was referencing the business table and not the corresponding versioned view so I modified the query to�?�


SELECT count(*)
FROM GISADMIN.parcelario08_evw p,GISADMIN.municipios m
WHERE sde.st_intersects (p.shape,m.shape) = 1
AND m.municipios='VILLALBA'
order by p.num_catastro;

Problem is I�??m not able to confirm the results because querying the versioned view takes a lot of time and after several minutes I receive the error message�?�

�??Error starting at line 3 in command:
SELECT count(*)
FROM GISADMIN.parcelario08_evw p,GISADMIN.municipios m
WHERE sde.st_intersects (p.shape,m.shape) = 1
AND m.municipios='VILLALBA'
order by p.num_catastro
Error report:
SQL Error: ORA-20011: Error generating shape. (SHAPE1)
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 100
ORA-06512: at "SDE.ST_RELATION_OPERATORS", line 340�?�

Another test was ran using another layer (non versioned) instead of the parcels fc and the performance is very bad (at the time this document is being edited, the oracle session is going for 30 minutes indicating the wait event �??External Procedure call�?�). I like using sql functionality to generate reports quickly and used to copy data to Postgis (no ESRI software) just to have this ability. In postgis the latter statement took just 2 seconds.  The gdb is compress and maintain daily.  After about 2 hours, I received the following errors�?�

�??ORA-29903: error in executing ODCIIndexFetch() routine
ORA-28576: lost RPC connection to external procedure agent
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 933
ORA-06512: at "SDE.SPX_UTIL", line 3954
ORA-06512: at "SDE.SPX_UTIL", line 3832
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 882
29903. 00000 -  "error in executing ODCIIndexFetch() routine"
*Cause:    The execution of ODCIIndexFetch routine caused an error.
*Action:   Examine the error messages produced by the indextype code and
           take appropriate action.�?�

I found some bugs but in my oracle version are solve
http://support.esri.com/zh-cn/knowledgebase/techarticles/detail/38823
http://support.esri.com/en/knowledgebase/techarticles/detail/32187
http://www.palladiumconsulting.com/blog/sebastian/2008/05/sde-92s-stgeometry-part-zero.html

Any one has any idea why is it? some recommendations?

Thanks for your help!

Diego Llamas

zhangsai_gis

Have you found the solution for the issue? I also encountered the error.