WRH$_SYSMETRIC_HISTORY_INDEX UNUSABLE After 18c Upgrade

The index WRH$_SYSMETRIC_HISTORY_INDEX became UNUSABLE a couple of days after an Oracle 18c upgrade. Rebuilding the index helped only temporarily, because it became UNUSABLE again. Interestingly, while I’ve been observing this problem on every database upgraded from 12c, none of the databases created on 18c were affected.

The first clue came from the alert log:

2019-09-13T00:28:11.009410+02:00
Some indexes or index [sub]partitions of table SYS.WRH$_SYSMETRIC_HISTORY have been marked unusable

V$ACTIVE_SESSION_HISTORY showed only some MMAN_SLAVE activity around this time, but the captured statements weren’t really related to this problem. Therefore, I set up the SQL trace for MMAN_SLAVE:

begin 
  dbms_monitor.serv_mod_act_trace_enable( 
    service_name=>'SYS$BACKGROUND', module_name=>'MMON_SLAVE', binds=>TRUE
  ); 
end; 
/ 

begin 
  dbms_monitor.serv_mod_act_trace_disable(
    service_name=>'SYS$BACKGROUND', module_name=>'MMON_SLAVE'
  ); 
end; 
/

The trace containing the following statements, among others, indeed revealed the problem:

alter table WRH$_SYSMETRIC_HISTORY drop partition WRH$_SYSMETRIC_HISTORY_1896533961_17248
LOCK TABLE "WRH$_SYSMETRIC_HISTORY" PARTITION ("WRH$_SYSMETRIC_HISTORY_1896533961_17439")  IN EXCLUSIVE MODE  NOWAIT
alter table WRH$_SYSMETRIC_HISTORY split partition WRH$_SYSMETRIC_HISTORY_1896533961_17439 at (1896533961,17464) into (partition WRH$_SYSMETRIC_HISTORY_1896533961_17439, partition WRH$_SYSMETRIC_HISTORY_1896533961_17464 tablespace SYSAUX) update indexes

It was the first statement (that is, ALTER TABLE DROP PARTITION) that invalidated the index. That happened because the index WRH$_SYSMETRIC_HISTORY_INDEX is global:

select partitioned from dba_indexes 
  where index_name = 'WRH$_SYSMETRIC_HISTORY_INDEX' ;

PAR
---
NO

A global index will become UNUSABLE when DROP PARTITION is issued without UPDATE INDEXES (or at least UPDATE GLOBAL INDEXES) [McDonald].

In contrast, the same index is local in a newly created 18c database:

column owner format a3
column name format a30
column column_name format a7

select owner,name,column_name from dba_part_key_columns 
  where name in ('WRH$_SYSMETRIC_HISTORY', 'WRH$_SYSMETRIC_HISTORY_INDEX')
  order by 1,2,3;

OWN NAME                           COLUMN_
--- ------------------------------ -------
SYS WRH$_SYSMETRIC_HISTORY         DBID
SYS WRH$_SYSMETRIC_HISTORY         SNAP_ID
SYS WRH$_SYSMETRIC_HISTORY_INDEX   DBID
SYS WRH$_SYSMETRIC_HISTORY_INDEX   SNAP_ID

For local indexes, DROP PARTITION doesn’t require UPDATE INDEXES, because ALTER TABLE DROP PARTITION drops the related index partition as well. It is precisely the reason why this problem doesn’t occur on a new 18c database.

In my opinion, it’s a bug in the upgrade script, which doesn’t replace the global index with the local one. Until there’s an official procedure to fix this problem you can safely ignore it, unless your queries on WRH$_SYSMETRIC_HISTORY get slow. In this case, you can rebuild the index, which usually remains VALID for a couple of days:

alter index SYS.WRH$_SYSMETRIC_HISTORY_INDEX rebuild online ;

Since the problem recurs, I automatically rebuild this index whenever it becomes UNUSABLE.

Lastly, I dropped the global and created the local index on a test database:

drop index sys.wrh$_sysmetric_history_index ;

create index sys.wrh$_sysmetric_history_index on sys.wrh$_sysmetric_history (
  dbid, snap_id, instance_number, group_id, metric_id, begin_time, con_dbid
) local tablespace sysaux ;

The index haven’t become UNUSABLE any more and I haven’t observed any side-effects. Mind, however, that this is unsupported and, therefore, should be only done after getting clearance from the Oracle support.

References:

[McDonald] Conor McDonald. (September 20, 2017). Updating indexes with partition maintenance