English

Problem: SQL statements using st_geometry operators experience decreased performance in Oracle11g

Description

When executing SQL statements with Oracle 11g containing st_geometry operators, performance, when compared with Oracle 10g, is significantly worse.

In addition, when an Oracle session calls out to the external process to access a shared library, the Oracle Heterogeneous Agent creates a file in the directory Oracle\product\11.1.0\db_1\hs\trace named PLSExtProc_agt_<pid>.trc, where <pid> is the value of the session's process identifier.

Below is the text that is visible when expecting the file:

Code:
Oracle Corporation --- TUESDAY MAR 11 2008 20:14:27.156

Heterogeneous Agent Release
11.1.0.6.0

Unable to open init file in directory D:\Oracle\product\11.1.0\db_1/hs/admin_

Unable to open init file _¢�`ø Ò_¼�¬`”

�_Ì
�_´§Ñ_�<�_ in directory D:\Oracle\product\11.1.0\db_1/hs/admin_

On each call to the extproc the same error message is written -

Unable to open init file _¢�`ø Ò_¼�¬`”

�_Ì
�_´§Ñ_ì<�_D:\O_ in directory D:\Oracle\product\11.1.0\db_1/hs/admin_

Cause

The cause of the problem is unknown.

It appears as if debug statements in the Oracle server were mistakenly left enabled, and are therefore logging information for every session performing calls to an external library, which will have a negative impact on performance.

Solution or Workaround

At this time, there is no solution to the problem except to revert back to using Oracle 10g.

This problem does not impact ArcGIS users because ArcGIS does not use any st_geometry operators.

The problem is specific to consumers who write their own applications.