1.根据Alert报错信息，查询Trace日志
/oracle/app/oracle/admin/fgsquery/bdump/fgsquery_j001_11111.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4. - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/product/10.2./db_1
System name: Linux
Node name: pquerydb01
Release: 2.6.-.el5
Version: # SMP Wed Nov :: EST
Machine: x86_64
Instance name: fgsquery
Redo thread mounted by this instance:
Oracle process number:
Unix process pid: , image: oracle@pquerydb01 (J001) *** -- ::43.786
*** ACTION NAME:(GATHER_STATS_JOB) -- ::43.781
*** MODULE NAME:(DBMS_SCHEDULER) -- ::43.781
*** SERVICE NAME:(SYS$USERS) -- ::43.781
*** SESSION ID:(1172.38268) -- ::43.781
ORA-: unimplemented feature
*** -- ::43.786
GATHER_STATS_JOB: GATHER_TABLE_STATS('"LISPRDD"','"LCCONT"','""', ...)
ORA-: unimplemented feature 数据库自动收集统计信息也会报错？
表是否有异常，表相关的索引是否有异常？？？
2.文档搜索
由于在生产环境，并未直接手工收集统计信息进行测试
根据报错，匹配到了一个Mos文档
ORA-: Unimplemented Feature when Running DBMS_STATS.GATHER_INDEX_STATS (文档 ID 559389.1)
3.问题分析
测试环境进行测试 --导入测试对象
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=>,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 HR
CREATE INDEX "HR"."LCCONT_INDEX_VDATE1" ON "HR"."LCCONT" ("VISITDATE", )
PCTFREE INITRANS MAXTRANS
STORAGE(INITIAL NEXT MINEXTENTS MAXEXTENTS
PCTINCREASE FREELISTS FREELIST GROUPS BUFFER_POOL DEFAULT)
TABLESPACE "USERS" PARALLEL ;
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;
4.测试验证
--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(,sysdate,);
SQL> EXEC dbms_stats.gather_table_stats(ownname=>'HR',TABNAME=>'LCCONT',CASCADE=>TRUE,DEGREE=>,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'); 环境没有任何问题，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 :
ORA-: unimplemented feature
ORA-: at "SYS.DBMS_STATS", line
ORA-: at "SYS.DBMS_STATS", line
ORA-: at line --收集函数索引，无报错，排除函数索引的问题
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=>,ESTIMATE_PERCENT=>0.01);
BEGIN dbms_stats.gather_table_stats(ownname=>'HR',TABNAME=>'LCCONT',CASCADE=>TRUE,DEGREE=>,ESTIMATE_PERCENT=>0.01); END; *
ERROR at line :
ORA-: unimplemented feature
ORA-: at "SYS.DBMS_STATS", line
ORA-: at "SYS.DBMS_STATS", line
ORA-: at line --查询会话进程号
SQL> select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat group by sid));
SPID
------------ --查询进程对应的trace文件
ksedmp: internal or fatal error
ORA-: unimplemented feature
Current 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(,'L',t.rowid)) as nlb,
count(distinct hextoraw(sys_op_descend("VISITDATE")||sys_op_descend())) as
ndk,sys_op_countchg(substrb(t.rowid,,),) as clf from "HR"."LCCONT" t where "VISITDATE" is not null or is not null
----- PL/SQL Call Stack -----
object line object
handle number name
0xbe7c11b8 package body SYS.DBMS_STATS
0xbe7c11b8 package body SYS.DBMS_STATS
0xbe7c11b8 package body SYS.DBMS_STATS
0xbe7c11b8 package body SYS.DBMS_STATS
0xb3da0860 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=>,ESTIMATE_PERCENT=>0.01);
BEGIN dbms_stats.gather_table_stats(ownname=>'HR',TABNAME=>'LCCONT',CASCADE=>TRUE,DEGREE=>,ESTIMATE_PERCENT=>0.01); END; *
ERROR at line :
ORA-: unimplemented feature
ORA-: at "SYS.DBMS_STATS", line
ORA-: at "SYS.DBMS_STATS", line
ORA-: at line SQL> select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat group by sid)); SPID
------------
5.问题处理
按照MOS的文档，对该索引删除后，新建的索引第二列修改为字符格式，本次测试验证，修改为字符格式后，该索引的使用是否与修改前相同，不会造成性能问题。 此索引的创建意义，在于如果查询is null，通过索引能够取得结果
SQL> select count(*) from HR.LCCONT;
COUNT(*)
---------- Execution Plan
----------------------------------------------------------
Plan hash value:
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| | SELECT STATEMENT | | | ()| :: |
| | SORT AGGREGATE | | | | |
| | INDEX FULL SCAN| LCCONT_INDEX_VDATE1 | | ()| :: |
--------------------------------------------------------------------------------
--查询is null 是否走索引
SQL> select count(*) from HR.LCCONT where VISITDATE is null;
COUNT(*)
---------- --------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| | SELECT STATEMENT | | | | ()| :: |
| | SORT AGGREGATE | | | | | |
|* | INDEX RANGE SCAN| LCCONT_INDEX_VDATE1 | | | ()| :: | --------------------------------------------------------------------------------
SQL> select * from HR.LCCONT where VISITDATE is null
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
--------------------------------------------------------------------------------
| | SELECT STATEMENT | | | | ()| :: |
| | TABLE ACCESS BY INDEX ROWID| LCCONT | | | ()| :: |
|* | INDEX RANGE SCAN | LCCONT_INDEX_VDATE1 | | | ()| :: |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
- access("VISITDATE" IS NULL) SQL>select * from HR.LCCONT where VISITDATE =sysdate;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
--------------------------------------------------------------------------------
| | SELECT STATEMENT | | | | ()| :: |
| | TABLE ACCESS BY INDEX ROWID| LCCONT | | | ()| :: |
|* | INDEX RANGE SCAN | LCCONT_INDEX_VDATE1 | | | ()| :: |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
- access("VISITDATE"=SYSDATE@!)
*******************************索引修改前，上述SQL均使用了索引***********************************
--对索引进行重建
SQL> drop index hr.LCCONT_INDEX_VDATE1;
CREATE INDEX "HR"."LCCONT_INDEX_VDATE1" ON "HR"."LCCONT" ("VISITDATE",'')
PCTFREE INITRANS MAXTRANS
STORAGE(INITIAL NEXT MINEXTENTS MAXEXTENTS
PCTINCREASE FREELISTS FREELIST GROUPS BUFFER_POOL DEFAULT)
TABLESPACE "USERS" PARALLEL ;
--
再次收集统计信息：
--
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=>,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能够使用此索引，至于第二列的数值类型并不关心