An Oracle blog – Amardeep Sidhu

Little bit of fun with Oracle and the related technologies…

GATHER_SCHEMA_STATS & ORA-03001: unimplemented feature

with 7 comments

Today i was gathering stats on one schema (10.2.0.3 on AIX 5.3, 64 bit) and it said:

ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 13336
ORA-06512: at "SYS.DBMS_STATS", line 13682
ORA-06512: at "SYS.DBMS_STATS", line 13760
ORA-06512: at "SYS.DBMS_STATS", line 13719
ORA-06512: at line 1

Little bit of searching on Metalink revealed that i had hit Bug no 6011068 which points to the base Bug 576661 which is related to function based indexes. There were 2 function based indexes in the schema. Before talking about the workaround let us re-produce the test case. Here i am doing it on my laptop (10.2.0.1 on Windows XP 32 bit)

SCOTT@TESTING >create table test1 as select * from emp;

Table created.

SCOTT@TESTING >create index ind1 on test1(comm,1);

Index created.

SCOTT@TESTING >

SYSTEM@TESTING>exec dbms_stats.gather_schema_stats('SCOTT');
BEGIN dbms_stats.gather_schema_stats('SCOTT'); END;

*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1

SYSTEM@TESTING>

As suggested in the metalink article let us set event 3001 before running the GATHER_SCHEMA_STATS command.

SYSTEM@TESTING >alter session set tracefile_identifier=stats1;

Session altered.

SYSTEM@TESTING >alter session set events '3001 trace name ERRORSTACK level 3';

Session altered.

SYSTEM@TESTING >exec dbms_stats.gather_schema_stats('SCOTT');
BEGIN dbms_stats.gather_schema_stats('SCOTT'); END;

*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1

SYSTEM@TESTING >

Part of the trace file reads:

ksedmp: internal or fatal error
ORA-03001: unimplemented feature
Current SQL statement for this session:
select /*+ no_parallel_index(t,IND1) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index(t,"IND1") */ count(*) as nrw,count(distinct sys_op_lbid(51966,'L',t.rowid)) as nlb,count(distinct hextoraw(sys_op_descend("COMM")||sys_op_descend(1))) as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "SCOTT"."TEST1" t where "COMM" is not null or 1 is not null
----- PL/SQL Call Stack -----
object      line  object
handle    number  name
65AA77D4      9406  package body SYS.DBMS_STATS
65AA77D4      9919  package body SYS.DBMS_STATS

So the problem is being caused by the index ind1 we created on (comm,1). This bug has been fixed in 10.2.0.5 and 11.1.0.7. The available workaround for other versions is to create index using 1 as character instead of number.

SCOTT@TESTING >drop index ind1;

Index dropped.

SCOTT@TESTING >create index ind1 on test1(comm,'1');

Index created.

SCOTT@TESTING >

And now running GATHER_SCHEMA_STATS:

SYSTEM@TESTING >exec dbms_stats.gather_schema_stats('SCOTT');

PL/SQL procedure successfully completed.

SYSTEM@TESTING >

Written by Sidhu

July 8th, 2009 at 11:33 pm

7 Responses to 'GATHER_SCHEMA_STATS & ORA-03001: unimplemented feature'

Subscribe to comments with RSS or TrackBack to 'GATHER_SCHEMA_STATS & ORA-03001: unimplemented feature'.

  1. Why do you ever want to create index as test1(comm,1);
    Any special reasons??

    sarayu

    9 Jul 09 at 2:29 am

  2. My question is what is this function based index actually doing?

    Coskan

    9 Jul 09 at 6:42 am

  3. @ Sarayu & Coskan

    I didn’t create this index ;). But to best of my knowledge it has been created to index NULL entries. Something like this:

    SQL> create table st1 as select * from all_objects;
    
    Table created.
    
    SQL> select count(*),status
      2  from st1
      3  group by status;
    
      COUNT(*) STATUS
    ---------- -------
             1 INVALID
         23559 VALID
    
    SQL> update st1 set status=null where rownum < 15;
    
    14 rows updated.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> create index ind1_st1 on st1(status);
    
    Index created.
    
    SQL> exec dbms_stats.gather_table_stats(user,'ST1',cascade=>true);
    
    PL/SQL procedure successfully completed.
    
    SQL> explain plan for select * from st1 where status is null;
    
    Explained.
    
    SQL> @e
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------
    
    --------------------------------------------------------------------
    | Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
    --------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |             |    14 |  1302 |    33 |
    |*  1 |  TABLE ACCESS FULL   | ST1         |    14 |  1302 |    33 |
    --------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("ST1"."STATUS" IS NULL)
    
    Note: cpu costing is off
    
    14 rows selected.
    
    SQL> create index ind1_st2 on st1(status,1);
    
    Index created.
    
    SQL> explain plan for select * from st1 where status is null;
    
    Explained.
    
    SQL> @e
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------
    
    ---------------------------------------------------------------------------
    | Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |             |    14 |  1302 |     3 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| ST1         |    14 |  1302 |     3 |
    |*  2 |   INDEX RANGE SCAN          | IND1_ST2    |    14 |       |     2 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("ST1"."STATUS" IS NULL)
    
    Note: cpu costing is off
    
    15 rows selected.
    
    SQL> drop index ind1_st2;
    
    Index dropped.
    
    SQL> create index ind1_st2 on st1(status,'1');
    
    Index created.
    
    SQL> explain plan for select * from st1 where status is null;
    
    Explained.
    
    SQL> @e
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------
    
    ---------------------------------------------------------------------------
    | Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |             |    14 |  1302 |     3 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| ST1         |    14 |  1302 |     3 |
    |*  2 |   INDEX RANGE SCAN          | IND1_ST2    |    14 |       |     2 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("ST1"."STATUS" IS NULL)
    
    Note: cpu costing is off
    
    15 rows selected.
    
    SQL>

    Sidhu

    9 Jul 09 at 8:53 pm

  4. Wow very neat and clean post.

    Didn’t understand much but it looks great :)

    Vaibhav Garg

    9 Jul 09 at 9:34 pm

  5. Thanks man ! We never cross each other in this area of topics for technical blogs ;)

    Sidhu

    9 Jul 09 at 10:29 pm

  6. [...] Amardeep Sidhu – GATHER_SCHEMA_STATS & ORA-03001: unimplemented feature [...]

Leave a Reply

*
To prove you're a person (not a spam script), type the answer to the math equation shown in the picture. Click on the picture to hear an audio file of the equation.
Click to hear an audio file of the anti-spam equation