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 _iut_max_entries parameter 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 more than 30,000.

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 oceanbase.GV$OB_PARAMETERS WHERE name LIKE '%iut_stat_collection%';

You can use the following SQL statement to configure this parameter:

ALTER SYSTEM SET _iut_stat_collection_type="ALL";

Example

1. Create a table named test_tbl1.

   obclient [test_db]> CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(50) NOT NULL, col3 INT);
   

2. On the col3 column of the test_tbl1 table, create an index named idx1_test_tbl1.

   obclient [test_db]> CREATE INDEX idx1_test_tbl1 ON test_tbl1(col3);
   

3. Insert 5 data rows to the test_tbl1 table.

   obclient [test_db]> INSERT INTO test_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 SELECT query.

   obclient [test_db]> SELECT col1, col2 FROM test_tbl1 WHERE col3 >= 25;
   

   The return result is as follows:

   +------+-------+
   | col1 | col2  |
   +------+-------+
   |    5 | name5 |
   |    4 | name4 |
   +------+-------+
   2 rows in set
   

5. Query the DBA_INDEX_USAGE view.

   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.

   obclient [test_db]> SELECT OBJECT_ID, NAME, OWNER, TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT, LAST_USED FROM oceanbase.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 test_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 oceanbase.DBA_INDEX_USAGE;
      

      The return result is as follows:

      +-----------+-----------------------------+---------+--------------------+------------------+----------------------------+
      | OBJECT_ID | NAME                        | OWNER   | TOTAL_ACCESS_COUNT | TOTAL_EXEC_COUNT | LAST_USED                  |
      +-----------+-----------------------------+---------+--------------------+------------------+----------------------------+
      |    500127 | __idx_500126_idx1_test_tbl1 | test_db |                  1 |                1 | 2024-01-24 11:09:30.928232 |
      +-----------+-----------------------------+---------+--------------------+------------------+----------------------------+
      1 row in set
      

      In the query result:

      The TOTAL_ACCESS_COUNT field indicates that during the SELECT operation, the idx1_test_tbl1 index in the test_db database was accessed 1 time at 2024-01-24 11:09:30.928232.

      The fields are described as follows:

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

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

References

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