Memory problems with extproc (st_functions)

[StefanP__Jung]

Hi,

i am running into very strange problems when using complex st_functions on oracle. All of our statements are on the SQL level. We are using st_intersect, st_intersection and so on. The extproc prozess is using a lot of RAM and if to may statments are running at the same time the prozess will be interrupted with an error message (OCI-04030, ORA-06512, ORA 20001).

Over all the exproc prozesses are using ~30 GB of RAM on the oralce system. this is all the RAM that is available. It looks like they never recycle even if they are not used anymore. So we got a lot of exproc processes > 5 GB with 0% CPU usage. In 9.3.x there was a similar kind of problem with this st_shapelib.dll. Does anybody know of this problem in ArcGIS 10?

This is one other example of an error message that appears very often:

{message=org.springframework.jdbc.UncategorizedSQLException :
### Error querying database.  Cause: java.sql.SQLException: ORA-29902: error in executing ODCIIndexStart() routine
ORA-28576: lost RPC connection to external procedure agent
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 943
ORA-06512: at "SDE.SPX_UTIL", line 2959
ORA-06512: at "SDE.SPX_UTIL", line 3204
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 293

In this case it looks like that the exproc prozess is crashed.

System:
- ArcSDE, Library 10 SP 2 (st_shapelib)-
- Oracle 11G 11.2.0.2 on Windows 2008 64 Bit with 4 CPU and 32 GB RAM

Anyone else running in a similar problem? Any ideas?

Best Regards

Stefan

[ForrestJones]

Hi Stefan,

Does the memory increase quickly and do the queries take a lot of time to execute (or more time potentially than expected)? One thing you should check are the spatial index grid sizes of the spatial tables involved. If they are not optimal it could cause the extproc to do more work than necessary. The best way to check is through ArcGIS as the owner of the featureclass. Go to the properties and then the indexes tab. Look at the spatial index information and click recalculate. Do the values auto calculated by ArcGIS differ much from what is currently set? If so it would be worth applying and trying the values auto calculated by ArcGIS to see if that makes a difference.

Thanks,

[StefanP__Jung]

Hi,

all involved feature classes have an spatial index. Only the grid 1 is set, but this is how it supposed to be, i guess. ST Functions are only using grid 1, correct?

When i recalculate the spatial grid via ArcCatalog i will get the same grid which was already set.

Do the values auto calculated by ArcGIS differ much from what is currently set? If so it would be worth applying and trying the values auto calculated by ArcGIS to see if that makes a difference.

Can i somhow test if the grid gets auto calculated? The feature classes get new features every day, inserted via ST Geometry and apply edits via FeatureServer.

The Memory is increasing very quickly. I used one statement and the memory for one exproc process started from 70 MB:
=> 3.0 GB after 1 Minute
=> 4.8 GB after 2 Minutes
=> 6.0 GB after 3 Minutes
=> 7.0 GB after 4 Minutes
=> 30 GB after a few more Minutes
=> we killed the exproc manually cause the server runs out of memory.

When analysing the statement i saw that it makes 50.000 intersections - this is the problem. Our next step here is to find out some workaroud not using this statement.

But still i am wondering why we always get more and more exproc services even for small stamentens. Looks like the exproc process never ends and never recycles the memory.

Ill be back when I have some more Information. Just let me know if i need to test some other stuff.

Stefan

[StefanP__Jung]

If anyone else is running into this problem, some more information:

I did not find out why some ST statements are using such a lot of memory. We switched to "easier" statements, even if the other statements would be "more exact".

The reason why the exproc never recycled the memory is cause of oracle and pooled connections. The exproc will be running and keeping the memory till the connection gets closed. So using pooled connections is a very bad idea in this case.

Stefan