English

Problem: 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

After importing the SDE schema, the association must be performed in SQL*Plus by the SDE user.

The association associates the sde.st_domain_stats type with the sde.st_geometry type, the sde.st_spatial_index domain index and sde.st_domain_operators, and sde.st_relation_operators packages.

  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_domain_operators, sde.st_relation_operators
    2 USING sde.st_domain_stats;

    Statistics associated.

    SQL> ASSOCIATE STATISTICS WITH INDEXTYPES sde.st_spatial_index
    2 USING sde.st_domain_stats;

    Statistics associated.

    SQL> ASSOCIATE STATISTICS WITH TYPES sde.st_geometry
    2 USING sde.st_domain_stats;

    Statistics associated.

Related Information