Note
- For V4.3.x, this parameter was introduced in V4.3.0.
- For V4.2.x, this parameter was introduced in V4.2.4.
Purpose
The DBA_INDEX_USAGE view displays the index usage data.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| OBJECT_ID | NUMBER | NO | ID of the index table. |
| NAME | VARCHAR2(128) | YES | Name of the index table. |
| OWNER | VARCHAR2(128) | YES | Name of the database. |
| TOTAL_ACCESS_COUNT | NUMBER | NO | Total number of accesses. |
| TOTAL_EXEC_COUNT | NUMBER | NO | Total number of executions. |
| TOTAL_ROWS_RETURNED | NUMBER | NO | Total number of returned rows. |
| BUCKET_0_ACCESS_COUNT | NUMBER | NO | Number of times the index table was used 0 times. |
| BUCKET_1_ACCESS_COUNT | NUMBER | NO | Number of times the index table was used 1 time. |
| BUCKET_2_10_ACCESS_COUNT | NUMBER | NO | Number of times the index table was used 2 to 10 times. |
| BUCKET_2_10_ROWS_RETURNED | NUMBER | NO | Number of rows returned when the index table was used 2 to 10 times. |
| BUCKET_11_100_ACCESS_COUNT | NUMBER | NO | Number of times the index table was accessed 11 to 100 times. |
| BUCKET_11_100_ROWS_RETURNED | NUMBER | NO | Number of rows returned when the index table was accessed 11 to 100 times. |
| BUCKET_101_1000_ACCESS_COUNT | NUMBER | NO | Number of times the index table was accessed 101 to 1000 times. |
| BUCKET_101_1000_ROWS_RETURNED | NUMBER | NO | Number of rows returned when the index table was accessed 101 to 1000 times. |
| BUCKET_1000_PLUS_ACCESS_COUNT | NUMBER | NO | Number of times the index table was used more than 1000 times. |
| BUCKET_1000_PLUS_ROWS_RETURNED | NUMBER | NO | Number of rows returned when the index table was used more than 1000 times. |
| LAST_USED | VARCHAR2(128) | NO | Time when the index table was last used. |
Sample query
obclient [SYS]> SELECT * FROM SYS.DBA_INDEX_USAGE;