Monitor indexes

OceanBase Database provides the DBA_INDEX_USAGE view for you to monitor indexes and query index monitoring results to better understand index usage. OceanBase Database periodically clears the monitoring records of deleted index tables in the background. You do not need to manually clear such data.

Considerations

• If you set the tenant-level parameter _iut_stat_collection_type to ALL, the index query performance may degrade. Proceed with caution.

• The tenant-level parameter _iut_max_entries specifies the maximum number of index tables that can be monitored in a tenant. A large value of this parameter may result in a high tenant memory usage. We recommend that you set the parameter to a value no greater than 30,000.

Syntax

• By default, index monitoring is enabled. In the default index statistics collection mode, namely, SAMPLED, the impact of index monitoring is little and can be ignored.

Parameters

OceanBase Database provides the following tenant-level parameters for you to control index monitoring.

Collection modes of index monitoring statistics

In the default index statistics collection mode (SAMPLED), only sampled index usage data is recorded. Therefore, a query of the DBA_INDEX_USAGE view may return an empty result. To obtain accurate index usage data of a tenant, set the index statistics collection mode to ALL for the tenant.

You can use the following SQL statement to view the value of the _iut_stat_collection_type parameter:

SELECT name, data_type, value FROM sys.GV$OB_PARAMETERS WHERE name LIKE '%iut_stat_collection%';

You can use the following SQL statement to set the index statistics collection mode:

ALTER SYSTEM SET "_iut_stat_collection_type"='ALL';

Example

1. Create a table named tbl1.

   CREATE TABLE tbl1 (col1 NUMBER PRIMARY KEY, col2 VARCHAR(50) NOT NULL, col3 NUMBER);
   

2. On the col3 column of the tbl1 table, create an index named idx1_tbl1.

   CREATE INDEX idx1_tbl1 ON tbl1(col3);
   

3. Insert five records into the tbl1 table.

   INSERT INTO tbl1 VALUES (1, 'name1', 20),(2, 'name2', 19),(3, 'name3', 20),(4, 'name4', 29),(5, 'name5', 26);
   

   The return result is as follows:

   Query OK, 5 rows affected
   Records: 5  Duplicates: 0  Warnings: 0
   

4. Execute the COMMIT; statement.

5. Execute a SELECT query.

   SELECT col1, col2 FROM tbl1 WHERE col3 >= 25;
   

   The return result is as follows:

   +------+-------+
   | COL1 | COL2  |
   +------+-------+
   |    5 | name5 |
   |    4 | name4 |
   +------+-------+
   2 rows in set
   

6. Query the DBA_INDEX_USAGE view.

   SELECT OBJECT_ID, NAME, OWNER, TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT, LAST_USED FROM sys.DBA_INDEX_USAGE;
   

   The return result is as follows:

   Empty set
   

   In the default SAMPLED mode, not all index usage data is recorded in the internal table. As a result, the expected monitoring statistics may not exist in the view. You can change the statistics collection mode to collect all index usage data. Here is an example:

   1. Use the following SQL statement to set the index statistics collection mode to ALL.

      ALTER SYSTEM SET "_iut_stat_collection_type"='ALL';
      

   2. Execute the SELECT query again.

      SELECT col1, col2 FROM tbl1 WHERE col3 >= 25;
      

   3. Query the DBA_INDEX_USAGE view again.

      Note

      OceanBase Database may take up to 15 minutes to flush the collected index monitoring statistics to the internal table in the background by using scheduled tasks. After that, the monitoring results of index usage can be obtained by querying the view.

      SELECT OBJECT_ID, NAME, OWNER, TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT, LAST_USED FROM sys.DBA_INDEX_USAGE;
      

      The return result is as follows:

      +-----------+------------------------+-------+--------------------+------------------+------------------------------+
      | OBJECT_ID | NAME                   | OWNER | TOTAL_ACCESS_COUNT | TOTAL_EXEC_COUNT | LAST_USED                    |
      +-----------+------------------------+-------+--------------------+------------------+------------------------------+
      |    500269 | __idx_500266_IDX1_TBL1 | SYS   |                  1 |                1 | 24-JAN-24 02.28.38.264523 PM |
      +-----------+------------------------+-------+--------------------+------------------+------------------------------+
      1 row in set
      

      The query result shows the following information:

      The TOTAL_ACCESS_COUNT field indicates that during the SELECT operation, the idx1_tbl1 index in the SYS schema was accessed 1 time at 24-JAN-24 02.28.38.264523 PM.

      The fields are described as follows:

      • OBJECT_ID: the ID of the index table.
      • NAME: the name of the index table.
      • OWNER: the name of the database.
      • TOTAL_ACCESS_COUNT: the total number of accesses.
      • TOTAL_EXEC_COUNT: the total number of executions.
      • LAST_USED: the time when the index table was last accessed.

      For more information about the columns in the DBA_INDEX_USAGE view, see DBA_INDEX_USAGE.

References

• Create a table
• Create an index
• Drop an index