DBMS_STATS Causes ORA-00600 [15851]

I noticed this in an Oracle 11.2.0.1.0 database. DBMS_STATS failed with an ORA-00600 and the first argument was [15851]. On investigation, it seemed to have something to do with the fact that the table had a function based index:

 

SQL> SELECT COUNT(*) FROM DBA_INDEXES

  2  WHERE OWNER = 'PDD'

  3  AND TABLE_NAME = 'DD_INSTRUCTION'

  4  AND INDEX_TYPE = 'FUNCTION-BASED NORMAL'

  5  /

 

  COUNT(*)

----------

         1

 

SQL>

 

… and that ESTIMATE_PERCENT was the same as AUTO_SAMPLE_SIZE:

 

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS -

> ('PDD','DD_INSTRUCTION', -

>  'ESTIMATE_PERCENT',DBMS_STATS.AUTO_SAMPLE_SIZE);

 

PL/SQL procedure successfully completed.

 

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS -

> ('PDD','DD_INSTRUCTION');

BEGIN DBMS_STATS.GATHER_TABLE_STATS  ('PDD','DD_INSTRUCTION'); END;

 

*

ERROR at line 1:

ORA-00600: internal error code, arguments: [15851],

[3], [2], [1], [1], [], [], [], [], [], [], []

ORA-06512: at "SYS.DBMS_STATS", line 20337

ORA-06512: at "SYS.DBMS_STATS", line 20360

ORA-06512: at line 1

 

SQL>

 

I did not want to remove the function based index so I changed the ESTIMATE_PERCENT:

 

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS -

> ('PDD','DD_INSTRUCTION', -

>  'ESTIMATE_PERCENT',100);

 

PL/SQL procedure successfully completed.

 

SQL>

 

… and the problem went away:

 

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS -

> ('PDD','DD_INSTRUCTION');

 

PL/SQL procedure successfully completed.

 

SQL>

How to Gather Database Statistics

This post was tested on an Oracle 9 database. You can calculate optimizer statistics for an entire database using dbms_stats.gather_database_stats. The estimate_percent parameter allows you to base the statistics on a given percentage of the data, if you wish to do so. Note that statistics are not calculated for objects owned by SYS or DBSNMP. Conversely, you can delete database statistics with dbms_stats.delete_database_stats:

 

TEST9 > sqlplus '/ as sysdba'

 

SQL*Plus: Release 9.2.0.5.0 - Production on Mon Sep 12 16:02:16 2011

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 - Production

 

SQL> select trunc(last_analyzed), count(*)

  2  from dba_tables

  3  group by trunc(last_analyzed);

 

TRUNC(LAS   COUNT(*)

--------- ----------

                 748

 

SQL> exec dbms_stats.gather_database_stats -

> (estimate_percent => 10);

 

PL/SQL procedure successfully completed.

 

SQL> select trunc(last_analyzed), count(*)

  2  from dba_tables

  3  group by trunc(last_analyzed);

 

TRUNC(LAS   COUNT(*)

--------- ----------

12-SEP-11        424

                 324

 

SQL> select distinct owner

  2  from dba_tables

  3  where last_analyzed is null;

 

OWNER

------------------------------

DBSNMP

SYS

 

SQL> exec dbms_stats.delete_database_stats;

 

PL/SQL procedure successfully completed.

 

SQL> select trunc(last_analyzed), count(*)

  2  from dba_tables

  3  group by trunc(last_analyzed);

 

TRUNC(LAS   COUNT(*)

--------- ----------

                 748

 

SQL>

The following statement was tested on Oracle 11.1. You can gather statistics just on tables with stale statistics as follows: 

SQL> exec dbms_stats.gather_database_stats -

> (options=>'gather stale',cascade=>true);

PL/SQL procedure successfully completed.

SQL>