English

Error: Encountering an Oracle error when analyzing or gathering statistics for an St_Geometry attribute

Error Message

When executing the Oracle analyze or dbms_stats command, the following Oracle error displays:

ERROR at line 1:
ORA-29927: error in executing the ODCIStatsCollect / ODCIStatsDelete routine
ORA-30562: SAMPLE percentage must be in the range [0.000001,100)
ORA-06512: at "SYS.DBMS_SYS_SQL", line 906
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "SDE.ST_DOMAIN_STATS", line 349

Cause

If a table contains more than 2.5 million rows and the sample size for estimating statistics is either 0 or 100 when gathering statistics on a table with an St_Geometry attribute (a spatial index), the command encounters the following Oracle error:

"ORA-30562: SAMPLE percentage must be in the range [0.000001,100)".

The internal problem for the spatial type's index function for gathering statistics is that the function executes a query using the Oracle clause SAMPLE BLOCK (percentage) so the value for percentage must be between 0.000001 and 99.

Solution or Workaround

To avoid encountering the error, ensure the sample size for gathering statistics is set to a value between 1 and 99.

The following example demonstrates gathering statistics for the PARCELS table in the current user's schema and setting the estimate size to a value of 10.

Code:
SQL> EXECUTE dbms_stats.gather_table_stats(user,'PARCELS',NULL,10);

PL/SQL procedure successfully completed.