Wednesday, April 03, 2013

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>

No comments:

Post a Comment