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>

QUERY_REWRITE_ENABLED

In some books and also in Oracle’s own documentation, it says that the QUERY_REWRITE_ENABLED initialisation parameter has to be set to TRUE before the optimiser will be able to use a function based index. I decided to check this for myself.

According to Oracle’s own documentation, the default for this parameter in Oracle 9 was FALSE. This does seem to be correct:

ORACLE 9 > sqlplus /

SQL*Plus: Release 9.2.0.7.0 - Production on Tue Oct 9 16:57:46 2012

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

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

SQL> col value format a10

SQL> select value, isdefault

  2  from v$parameter

  3  where name = 'query_rewrite_enabled'

  4  /

VALUE      ISDEFAULT

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

false      TRUE

SQL>

Oracle’s own documentation also says that, in later versions, the default value is TRUE. This seems to be correct too:

ORACLE 10 > sqlplus /

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Oct 9 17:04:04 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> col value format a10

SQL> select value, isdefault

  2  from v$parameter

  3  where name = 'query_rewrite_enabled';

VALUE      ISDEFAULT

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

TRUE       TRUE

SQL>

I did the rest of this test on Oracle 9.2.0.7.0. First I set the parameter to FALSE:

SQL> alter session

  2  set query_rewrite_enabled = false

  3  /

Session altered.

SQL>

Then I created a table with a function based index:

SQL> create table andrew

  2  as select * from dba_objects

  3  /

Table created.

SQL> create index fbi

  2  on andrew(to_char(created,'YYYYMM'))

  3  /

Index created.

SQL>

I checked that Oracle knew it was a function based index:

SQL> select index_type, funcidx_status

  2  from user_indexes

  3  where index_name = 'FBI'

  4  /

INDEX_TYPE             FUNCIDX_STATUS

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

FUNCTION-BASED NORMAL  ENABLED

SQL>

I monitored the usage of the index:

SQL> alter index fbi monitoring usage

  2  /

Index altered.

SQL>

Then I ran a query on the table. I did not expect Oracle to use the index, as QUERY_REWRITE_ENABLED was set to FALSE, but it did (you may need to use your browser's zoom button to read the execution plan):

SQL> set autotrace on explain

SQL> select count(*) from andrew

  2  where to_char(created,'YYYYMM') = '201201'

  3  /

  COUNT(*)

----------

       181

Execution Plan

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

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=9)

   1    0   SORT (AGGREGATE)

   2    1     INDEX (RANGE SCAN) OF 'FBI' (NON-UNIQUE) (Cost=1 Card=35

          1 Bytes=3159)

   

SQL> set autotrace off

SQL>

... and when I checked afterwards, Oracle confirmed that the index had been used:

SQL> select index_name, start_monitoring,

  2  monitoring, used from v$object_usage

  3  /

INDEX_NAME START_MONITORING    MONITORING USED

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

FBI        10/09/2012 18:27:01 YES        YES

SQL>

So, on the basis of this test, I do not agree with the documentation.

Function Based Indexes

(Tested on an Oracle 10 database.)

This post introduces function based indexes. A normal B-tree index allows you to access a table quickly using the full column value. A function based index, on the other hand, allows you to apply a function to a column then use the resultant value to access the table. First create a table:

SQL> CREATE TABLE ANDREWS_TABLE
  2  (FIRST_NAME VARCHAR2(10),
  3   DOB        DATE)
  4  /

Table created.

SQL>

Next add some data:

SQL> INSERT INTO ANDREWS_TABLE
  2  VALUES (
  3  'Fred',
  4  TO_DATE('10-MAY-1983','DD-MON-YYYY'))
  5  /

1 row created.

SQL>

Then create a function based index. This one will allow you to access the table using the year of birth in YYYY format.:

SQL> CREATE INDEX YEAR_BORN_INDEX
  2  ON ANDREWS_TABLE(TO_CHAR(DOB,'YYYY'))
  3  /

Index created.

SQL>

And a B-tree index for comparison:

SQL> CREATE INDEX FIRST_NAME_INDEX
  2  ON ANDREWS_TABLE(FIRST_NAME)
  3  /

Index created.

SQL>

USER_IND_COLUMNS shows which columns are indexed. Note that Oracle has added a pseudo column for the function based index:

SQL> SELECT INDEX_NAME, COLUMN_POSITION, COLUMN_NAME
  2  FROM USER_IND_COLUMNS
  3  WHERE TABLE_NAME = 'ANDREWS_TABLE'
  4  ORDER BY 1,2
  5  /

INDEX_NAME           COLUMN_POSITION COLUMN_NAME
-------------------- --------------- --------------------
FIRST_NAME_INDEX                   1 FIRST_NAME
YEAR_BORN_INDEX                    1 SYS_NC00003$

SQL>

You can identify function based indexes as they have
INDEX_TYPE = FUNCTION-BASED NORMAL and FUNCIDX_STATUS = ENABLED:

SQL> SELECT INDEX_NAME, INDEX_TYPE, FUNCIDX_STATUS
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME = 'ANDREWS_TABLE'
  4  /

INDEX_NAME           INDEX_TYPE                  FUNCIDX_STATUS
-------------------- --------------------------- --------------
FIRST_NAME_INDEX     NORMAL
YEAR_BORN_INDEX      FUNCTION-BASED NORMAL       ENABLED

SQL>

You cannot see the pseudo column used by the function based index when you describe the table:

SQL> DESC ANDREWS_TABLE
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 FIRST_NAME                             VARCHAR2(10)
 DOB                                    DATE

SQL>

But you can see it in USER_TAB_COLS. The DATA_DEFAULT column shows how it is populated and the HIDDEN_COLUMN column shows that it is hidden:

SQL> SELECT TABLE_NAME, COLUMN_NAME,
  2  DATA_DEFAULT DERIVATION, HIDDEN_COLUMN HIDDEN
  3  FROM USER_TAB_COLS
  4  WHERE COLUMN_NAME = 'SYS_NC00003$'
  5  /

TABLE_NAME    COLUMN_NAME   DERIVATION             HIDDEN
------------- ------------- ---------------------- ------
ANDREWS_TABLE SYS_NC00003$  TO_CHAR("DOB",'YYYY')  YES

SQL>

And you can also select the contents of the pseudo column:

SQL> SELECT SYS_NC00003$ "Pseudo Column" FROM ANDREWS_TABLE
  2  /

Pseudo Column
-------------
1983

SQL>