Oracle statistics types are not associated to st_geometry objects after importing the SDE schema

Description

When using Oracle's import or data pump utility to import the SDE schema, the following process returns an Oracle error:

"IMP-00017: following statement failed with ORACLE error 4020:
"ASSOCIATE STATISTICS WITH TYPES "ST_GEOMETRY" USING "ST_DOMAIN_STATS"".

Because of the error, the sde.st_domain_stats type is not associated with the sde.st_geometry type, spatial index, and dependent packages. Without this association, the Oracle optimizer is not able to properly cost st_geometry operators when parsing a SQL statement and may result in sub-optimal performance.

Cause

The cause of the error is Oracle Bug 3931471. For additional information, use Oracle's MetaLink in the Related Information section below, and search their bug database for details.

Oracle reports that the problem has been fixed in Oracle release 11.1.

Solution or Workaround

1. Verify that all objects in the SDE schema are compiled and valid. Using SQL*Plus, execute the following query to list any invalid objects:

   Code:
   SQL> SELECT object_name, object_type FROM user_objects WHERE status = 'INVALID';
   
   OBJECT_NAME          OBJECT_TYPE
   -------------------- -------------------
   ST_GEOM_UTIL         PACKAGE BODY
   ST_TYPE_EXPORT       PACKAGE BODY

   If any objects are returned, they must be compiled prior to associating the statistics.
   
   Using the Oracle SYS package utl_recomp, recompile all the invalid objects in the SDE schema. For additional information on how to recompile all objects, see the ESRI Knowledge Base article, "ORA-04061: existing state of package body 'SDE.ST_GEOMETRY_OPERATORS' has been invalidated" in the Related Information below.

   Code:
   SQL> EXECUTE sys.utl_recomp.recomp_serial('SDE');
   
   PL/SQL procedure successfully completed.

2. Once all objects are compiled and valid, the final step is to associate the statistics with the various dependent objects. In SQL*Plus, execute the ASSOCIATE STATISTICS command for each set of objects.

   Code:
   SQL> ASSOCIATE STATISTICS WITH PACKAGES sde.st_relation_operators
       USING sde.st_domain_stats;
   
   Statistics associated.
   
   SQL> ASSOCIATE STATISTICS WITH INDEXTYPES sde.st_spatial_index 
       USING sde.st_domain_stats;
   
   Statistics associated.
   
   SQL> ASSOCIATE STATISTICS WITH TYPES sde.st_geometry 
       USING sde.st_domain_stats;
   
   Statistics associated.