/oracle/app/oracle/admin/fgsquery/bdump/fgsquery_j001_11111.trcOracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORACLE_HOME = /oracle/app/oracle/product/10.2.0/db_1System name:    LinuxNode name:    pquerydb01Release:    2.6.18-348.el5Version:    #1 SMP Wed Nov 28 21:22:00 EST 2012Machine:    x86_64Instance name: fgsqueryRedo thread mounted by this instance: 1Oracle process number: 117Unix process pid: 11111, image: oracle@pquerydb01 (J001)*** 2018-10-30 22:04:43.786*** ACTION NAME:(GATHER_STATS_JOB) 2018-10-30 22:04:43.781*** MODULE NAME:(DBMS_SCHEDULER) 2018-10-30 22:04:43.781*** SERVICE NAME:(SYS$USERS) 2018-10-30 22:04:43.781*** SESSION ID:(1172.38268) 2018-10-30 22:04:43.781ORA-03001: unimplemented feature*** 2018-10-30 22:04:43.786GATHER_STATS_JOB: GATHER_TABLE_STATS('"LISPRDD"','"LCCONT"','""', ...)ORA-03001: unimplemented feature 数据库自动收集统计信息也会报错？ 表是否有异常，表相关的索引是否有异常？？？

由于在生产环境，并未直接手工收集统计信息进行测试

根据报错，匹配到了一个Mos文档

ORA-03001: Unimplemented Feature when Running DBMS_STATS.GATHER_INDEX_STATS (文档 ID 559389.1)

测试环境进行测试--导入测试对象impdp \'/ as sysdba\' directory=dump  dumpfile=LISPRDD_LCCONT.dmp logfile=LISPRDD_LCCONT.log remap_schema=LISPRDD:hr REMAP_TABLESPACE=LISPRD1:users--收集统计信息，未报错~EXEC dbms_stats.gather_table_stats(ownname=>'HR',TABNAME=>'LCCONT',CASCADE=>TRUE,DEGREE=>1,ESTIMATE_PERCENT=>0.01);--根据sqlfile查询创建索引语句，发现存在两个特殊的索引，一个为复合索引且第二列为数字类型，复合MOS报错信息，另一个为函数索引，都列举出来impdp \'/ as sysdba\' directory=dump  dumpfile=LISPRDD_LCCONT.dmp logfile=LISPRDD_LCCONT.log remap_schema=LISPRDD:hr REMAP_TABLESPACE=LISPRD1:users sqlfile=create_table_index.sql-- new object type path is: SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX-- CONNECT HRCREATE INDEX "HR"."LCCONT_INDEX_VDATE1" ON "HR"."LCCONT" ("VISITDATE", 1)  PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "USERS" PARALLEL 1 ;  ALTER INDEX "HR"."LCCONT_INDEX_VDATE1" NOPARALLEL;

--如果vis= null

--visitdate is null -> 全表扫描

CREATE INDEX "HR"."IDX_LCCONT_SUBSTATE" ON "HR"."LCCONT" (SUBSTR("STATE",1,4))

PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" PARALLEL 1 ;

ALTER INDEX "HR"."IDX_LCCONT_SUBSTATE" NOPARALLEL;

--insert 测试记录禁用所有约束SQL> select 'alter table '||owner||'.'||TABLE_NAME||' drop constraints '||CONSTRAINT_NAME ||';' from dba_constraints where owner='HR' and TABLE_NAME='LCCONT';'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DROPCONSTRAINTS'||CONSTRAINT_NAME||';'--------------------------------------------------------------------------------alter table HR.LCCONT drop constraints SYS_C005407;insert into HR.LCCONT(GRPCONTNO,VISITDATE,STATE) values(1,sysdate,1);SQL> EXEC dbms_stats.gather_table_stats(ownname=>'HR',TABNAME=>'LCCONT',CASCADE=>TRUE,DEGREE=>1,ESTIMATE_PERCENT=>0.01);PL/SQL procedure successfully completed.GATHER_INDEX_STATS EXEC DBMS_STATS.gather_index_stats('HR','LCCONT_INDEX_VDATE1');EXEC DBMS_STATS.gather_index_stats('HR','IDX_LCCONT_SUBSTATE');10205 环境没有任何问题，MOS 文档介绍该报错影响范围10201-10204，但是往往有时候版本不一定准确，但是本次是准确的，如果有相同服务器版本测试尽量严格保持一致10204环境再次进行测试，无数据，收集统计信息未报错，存在数据后，收集统计信息报错 在10204测试环境，重演上述操作，禁用约束 --插入测试数据

SQL> insert into HR.LCCONT(GRPCONTNO,VISITDATE,STATE) select rownum,sysdate-rownum,mod(rownum,3) from dba_objects;

50664 rows created.

SQL> insert into HR.LCCONT(GRPCONTNO,VISITDATE,STATE) values(966,'','abc');

SQL> insert into HR.LCCONT(GRPCONTNO,VISITDATE,STATE) values(889,'','efg');

SQL> commit;

--收集表的统计信息报错如下SQL> EXEC DBMS_STATS.gather_index_stats('HR','LCCONT_INDEX_VDATE1');BEGIN DBMS_STATS.gather_index_stats('HR','LCCONT_INDEX_VDATE1'); END;*ERROR at line 1:ORA-03001: unimplemented featureORA-06512: at "SYS.DBMS_STATS", line 10872ORA-06512: at "SYS.DBMS_STATS", line 10896ORA-06512: at line 1--收集函数索引，无报错，排除函数索引的问题SQL> EXEC DBMS_STATS.gather_index_stats('HR','IDX_LCCONT_SUBSTATE');PL/SQL procedure successfully completed. --收集复合索引统计信息，报错相同SQL> EXEC dbms_stats.gather_table_stats(ownname=>'HR',TABNAME=>'LCCONT',CASCADE=>TRUE,DEGREE=>1,ESTIMATE_PERCENT=>0.01);BEGIN dbms_stats.gather_table_stats(ownname=>'HR',TABNAME=>'LCCONT',CASCADE=>TRUE,DEGREE=>1,ESTIMATE_PERCENT=>0.01); END;*ERROR at line 1:ORA-03001: unimplemented featureORA-06512: at "SYS.DBMS_STATS", line 13437ORA-06512: at "SYS.DBMS_STATS", line 13457ORA-06512: at line 1--查询会话进程号SQL> select spid  from v$process  where addr=(select paddr from v$session where sid=(select sid from v$mystat group by sid));SPID------------25471--查询进程对应的trace文件ksedmp: internal or fatal errorORA-03001: unimplemented featureCurrent SQL statement for this session:select /*+ no_parallel_index(t,"LCCONT_INDEX_VDATE1") dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index(t,"LCCONT_INDEX_VDATE1")  */ count(*) as nrw,count(distinct sys_op_lbid(53035,'L',t.rowid)) as nlb, count(distinct hextoraw(sys_op_descend("VISITDATE")||sys_op_descend(1))) as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "HR"."LCCONT" t where "VISITDATE" is not null or 1 is not null----- PL/SQL Call Stack -----  object      line  object  handle    number  name0xbe7c11b8      9598  package body SYS.DBMS_STATS0xbe7c11b8     10157  package body SYS.DBMS_STATS0xbe7c11b8     10792  package body SYS.DBMS_STATS0xbe7c11b8     10896  package body SYS.DBMS_STATS0xb3da0860         1  anonymous block--再次对会话进行追踪，本次只收集索引的统计信息，报错相同，无异常SQL> alter session set events '3001 trace name ERRORSTACK level 3'; Session altered.SQL> EXEC dbms_stats.gather_table_stats(ownname=>'HR',TABNAME=>'LCCONT',CASCADE=>TRUE,DEGREE=>1,ESTIMATE_PERCENT=>0.01);BEGIN dbms_stats.gather_table_stats(ownname=>'HR',TABNAME=>'LCCONT',CASCADE=>TRUE,DEGREE=>1,ESTIMATE_PERCENT=>0.01); END;*ERROR at line 1:ORA-03001: unimplemented featureORA-06512: at "SYS.DBMS_STATS", line 13437ORA-06512: at "SYS.DBMS_STATS", line 13457ORA-06512: at line 1SQL> select spid  from v$process  where addr=(select paddr from v$session where sid=(select sid from v$mystat group by sid));SPID------------25676

按照MOS的文档，对该索引删除后，新建的索引第二列修改为字符格式，本次测试验证，修改为字符格式后，该索引的使用是否与修改前相同，不会造成性能问题。 此索引的创建意义，在于如果查询is null，通过索引能够取得结果 SQL> select count(*) from HR.LCCONT;  COUNT(*)----------     50667Execution Plan----------------------------------------------------------Plan hash value: 4114441950--------------------------------------------------------------------------------| Id  | Operation        | Name                | Rows  | Cost (%CPU)| Time     |--------------------------------------------------------------------------------|   0 | SELECT STATEMENT |                     |     1 |     0   (0)| 00:00:01 ||   1 |  SORT AGGREGATE  |                     |     1 |            |          ||   2 |   INDEX FULL SCAN| LCCONT_INDEX_VDATE1 |     1 |     0   (0)| 00:00:01 |----------------------------------------------------------------------------------查询is null 是否走索引SQL> select count(*) from HR.LCCONT where VISITDATE is null;  COUNT(*)----------         2--------------------------------------------------------------------------------| Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |                     |     1 |     8 |     0   (0)| 00:00:01 ||   1 |  SORT AGGREGATE   |                     |     1 |     8 |            |       ||*  2 |   INDEX RANGE SCAN| LCCONT_INDEX_VDATE1 |     1 |     8 |     0   (0)| 00:00:01 |--------------------------------------------------------------------------------SQL> select * from HR.LCCONT where VISITDATE is null--------------------------------------------------------------------------------| Id  | Operation                   | Name                | Rows  | Bytes | Cost(%CPU)| Time     |--------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |                     |     1 |    36 |0   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| LCCONT              |     1 |    36 |0   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | LCCONT_INDEX_VDATE1 |     1 |       |0   (0)| 00:00:01 |--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("VISITDATE" IS NULL)SQL>select * from HR.LCCONT where VISITDATE =sysdate;--------------------------------------------------------------------------------| Id  | Operation                   | Name                | Rows  | Bytes | Cost(%CPU)| Time     |--------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |                     |     1 |    36 |0   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| LCCONT              |     1 |    36 |0   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | LCCONT_INDEX_VDATE1 |     1 |       |0   (0)| 00:00:01 |--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("VISITDATE"=SYSDATE@!) *******************************索引修改前，上述SQL均使用了索引*********************************** --对索引进行重建SQL> drop index hr.LCCONT_INDEX_VDATE1;CREATE INDEX "HR"."LCCONT_INDEX_VDATE1" ON "HR"."LCCONT" ("VISITDATE",'1')  PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "USERS" PARALLEL 1 ;--再次收集统计信息：--SQL> EXEC DBMS_STATS.gather_index_stats('HR','LCCONT_INDEX_VDATE1');PL/SQL procedure successfully completed.SQL> EXEC dbms_stats.gather_table_stats(ownname=>'HR',TABNAME=>'LCCONT',CASCADE=>TRUE,DEGREE=>1,ESTIMATE_PERCENT=>0.01);PL/SQL procedure successfully completed. --报错可以解决 --校验重建后的索引，是否正常使用 SQL> select count(*) from HR.LCCONT

COUNT(*)

---------- 50667

Execution Plan

----------------------------------------------------------Plan hash value: 2339219198

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

| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 34 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | | || 2 | INDEX FAST FULL SCAN| LCCONT_INDEX_VDATE1 | 50550 | 34 (0)| 00:00:01 |-------------------------------------------------------------------------------------

SQL> select count(*) from HR.LCCONT where VISITDATE is null;

COUNT(*)

---------- 2

Execution Plan----------------------------------------------------------Plan hash value: 2244137702

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 8 | 1 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 8 | | ||* 2 | INDEX RANGE SCAN| LCCONT_INDEX_VDATE1 | 1 | 8 | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

2 - access("VISITDATE" IS NULL)

 

SQL> select * from HR.LCCONT where VISITDATE is null

Execution Plan

----------------------------------------------------------Plan hash value: 3258936949

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| LCCONT | 1 | 38 | 1 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | LCCONT_INDEX_VDATE1 | 1 | | 1 (0)| 00:00:01 |---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

2 - access("VISITDATE" IS NULL)

 

SQL> select * from HR.LCCONT where VISITDATE =sysdate;

no rows selected

Execution Plan

----------------------------------------------------------Plan hash value: 3258936949

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| LCCONT | 1 | 38 | 3 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | LCCONT_INDEX_VDATE1 | 1 | | 2 (0)| 00:00:01 |---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

2 - access("VISITDATE"=SYSDATE@!)

 

结论：1.对索引drop 后，重建索引，

CREATE INDEX "HR"."LCCONT_INDEX_VDATE1" ON "HR"."LCCONT" ("VISITDATE",'1')    2.重建索引并不会影像sql的执行选择，从原理上说，此类索引的目的是让索引能存储单列的Null值，使得业务Is null能够使用此索引，至于第二列的数值类型并不关心