OceanBase AI Database provides the DBA_INDEX_USAGE view for you to monitor indexes and query index monitoring results to better understand index usage. OceanBase AI Database periodically clears the monitoring records of deleted index tables in the background. You do not need to manually clear such data.
Note
The DBA_INDEX_USAGE view displays only index statistics of user tenants.
Considerations
If you set the tenant-level parameter
_iut_stat_collection_typetoALL, the index query performance may degrade. Proceed with caution.The
_iut_max_entriesparameter 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.
Usage instructions
- 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
To control how indexes are monitored, OceanBase AI Database provides the following tenant-level parameters for you to modify.
Note
The parameters and statistics of a tenant are independent of those of other tenants.
Parameter |
Default value |
Description |
|---|---|---|
| _iut_enable | true | Specifies whether to enable index monitoring. The default value is true, which indicates that index monitoring is enabled. false indicates that index monitoring is disabled. |
| _iut_max_entries | 30000 | The maximum number of index tables that can be monitored. The default value is 30000. The value range is [0,+∞).
Note
|
| _iut_stat_collection_type | SAMPLED | The collection mode of index monitoring statistics. Valid values:
|
Index monitoring information collection modes
In the default index statistics collection mode (SAMPLED), not all index usage data is recorded in the internal table. 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 set it:
ALTER SYSTEM SET _iut_stat_collection_type="ALL";
Examples
Create a table named
test_tbl1.obclient [test_db]> CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(50) NOT NULL, col3 INT);Create an index named
idx1_test_tbl1on columncol3of tabletest_tbl1.obclient [test_db]> CREATE INDEX idx1_test_tbl1 ON test_tbl1(col3);Insert
5rows of data into tabletest_tbl1.obclient [test_db]> INSERT INTO test_tbl1 VALUES (1, 'name1', 20),(2, 'name2', 19),(3, 'name3', 20),(4, 'name4', 29),(5, 'name5', 26);The result is as follows:
Query OK, 5 rows affected Records: 5 Duplicates: 0 Warnings: 0Execute a
SELECTquery.obclient [test_db]> SELECT col1, col2 FROM test_tbl1 WHERE col3 >= 25;The query result is as follows:
+------+-------+ | col1 | col2 | +------+-------+ | 5 | name5 | | 4 | name4 | +------+-------+ 2 rows in setQuery the
DBA_INDEX_USAGEview.Note
OceanBase AI 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 query result is as follows:
Empty setBy default, not all index usage data is recorded in the internal table in sampled mode (
SAMPLED). As a result, you may not obtain the expected monitoring results in the view. You can change the collection mode to non-sampled mode by using SQL statements to collect data. For example, perform the following operations:Use the following SQL statement to set the index usage monitoring collection mode to
ALL.ALTER SYSTEM SET _iut_stat_collection_type="ALL";Execute the
SELECTquery again.SELECT col1, col2 FROM test_tbl1 WHERE col3 >= 25;Query the
DBA_INDEX_USAGEview again.Note
OceanBase AI 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 query 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 setQuery result description:
The
TOTAL_ACCESS_COUNTfield indicates that the index namedidx1_test_tbl1(__idx_500126_idx1_test_tbl1) in thetest_dbdatabase was accessed1time during theSELECToperation. The last access time was2024-01-24 11:09:30.928232.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 used.
For more information about the fields in the
DBA_INDEX_USAGEview, see DBA_INDEX_USAGE.