View indexes with abnormal status

In OceanBase Database V4.x, the index monitoring feature is enabled by default. You can query the index usage of a user tenant by using the CDB_INDEX_USAGE view (for the sys tenant) or the DBA_INDEX_USAGE view (for the user tenant).

By default, index monitoring is in the SAMPLED mode when the cluster is started. In this mode, not all index usage data is recorded in the internal table. Therefore, the CDB_INDEX_USAGE view (for the sys tenant) or the DBA_INDEX_USAGE view (for the user tenant) may return empty query results. If you need accurate information, you can change the index monitoring information statistics mode to ALL before using the index, and then query the index usage.

The statement to change the index monitoring information statistics mode is as follows:

obclient(root@mysql001)[(none)]> ALTER SYSTEM SET _iut_stat_collection_type='ALL';

obclient(SYS@oracle001)[SYS]> ALTER SYSTEM SET "_iut_stat_collection_type"='ALL';

Then use the following statements to query the index usage.

• Sys tenant

  In the sys tenant, you can use the following statement to query:

  obclient(root@sys)[(none)]> SELECT OBJECT_ID, NAME, OWNER, TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT, LAST_USED FROM oceanbase.CDB_INDEX_USAGE;
  

• User tenant

  In a MySQL-compatible user tenant, you can use the following statement to query:

  obclient(root@mysql001)[(none)]> SELECT OBJECT_ID, NAME, OWNER, TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT, LAST_USED FROM oceanbase.DBA_INDEX_USAGE;
  

  In an Oracle-compatible user tenant, you can use the following statement to query:

  obclient(SYS@oracle001)[SYS]> SELECT OBJECT_ID, NAME, OWNER, TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT, LAST_USED FROM SYS.DBA_INDEX_USAGE;