Note
This view is available starting with V3.2.1.
Purpose
This view displays the optimizer statistics of all tables owned by the current user.
Applicability
This view is available only in Oracle mode.
Columns
Column |
Type |
Nullable |
Description |
|---|---|---|---|
| TABLE_NAME | VARCHAR2(128) | NO | The name of the table. |
| PARTITION_NAME | VARCHAR2(128) | NO | The name of the partition. |
| PARTITION_POSITION | NUMBER | NO | The position of the partition in the table. |
| SUBPARTITION_NAME | VARCHAR2(128) | NO | The name of the subpartition. |
| SUBPARTITION_POSITION | NUMBER | NO | The position of the subpartition in the partition. |
| OBJECT_TYPE | VARCHAR2(12) | NO | The type of the object. |
| NUM_ROWS | NUMBER | YES | The number of rows in the object. |
| BLOCKS | NUMBER | YES | NULL |
| EMPTY_BLOCKS | NUMBER | YES | NULL |
| AVG_SPACE | NUMBER | YES | NULL |
| CHAIN_CNT | NUMBER | YES | NULL |
| AVG_ROW_LEN | NUMBER | YES | The average row length of the object. |
| AVG_SPACE_FREELIST_BLOCKS | NUMBER | YES | NULL |
| NUM_FREELIST_BLOCKS | NUMBER | YES | NULL |
| AVG_CACHED_BLOCKS | NUMBER | YES | NULL |
| AVG_CACHE_HIT_RATIO | NUMBER | YES | NULL |
| IM_IMCU_COUNT | NUMBER | YES | NULL |
| IM_BLOCK_COUNT | NUMBER | YES | NULL |
| IM_STAT_UPDATE_TIME | TIMESTAMP(9) | YES | NULL |
| SCAN_RATE | NUMBER | YES | NULL |
| SAMPLE_SIZE | NUMBER | YES | The sampling size when statistics were collected. |
| LAST_ANALYZED | DATE | YES | The time when the statistics were last collected. |
| GLOBAL_STATS | VARCHAR2(3) | YES |
|
| USER_STATS | VARCHAR2(3) | YES | Indicates whether the partition statistics were set by the user. |
| STATTYPE_LOCKED | VARCHAR2(5) | YES | The type of statistics lock. |
| STALE_STATS | VARCHAR2(3) | YES | Indicates whether the statistics are stale. |
| SCOPE | VARCHAR2(7) | YES | The scope of the statistics. |
Sample query
Query the optimizer statistics of the T_SUBPART table owned by the current user.
obclient [SYS]> SELECT TABLE_NAME, OBJECT_TYPE, NUM_ROWS, AVG_ROW_LEN FROM SYS.USER_TAB_STATISTICS WHERE TABLE_NAME = 'T_SUBPART' ORDER BY 1, 2, 3;
The query result is as follows:
+------------+--------------+----------+-------------+
| TABLE_NAME | OBJECT_TYPE | NUM_ROWS | AVG_ROW_LEN |
+------------+--------------+----------+-------------+
| T_SUBPART | PARTITION | 2504 | 60 |
| T_SUBPART | PARTITION | 2530 | 60 |
| T_SUBPART | PARTITION | 4966 | 60 |
| T_SUBPART | SUBPARTITION | 549 | 60 |
| T_SUBPART | SUBPARTITION | 560 | 60 |
| T_SUBPART | SUBPARTITION | 829 | 60 |
| T_SUBPART | SUBPARTITION | 830 | 60 |
| T_SUBPART | SUBPARTITION | 1122 | 60 |
| T_SUBPART | SUBPARTITION | 1125 | 60 |
| T_SUBPART | SUBPARTITION | 1130 | 60 |
| T_SUBPART | SUBPARTITION | 1141 | 60 |
| T_SUBPART | SUBPARTITION | 2714 | 60 |
| T_SUBPART | TABLE | 10000 | 60 |
+------------+--------------+----------+-------------+
13 rows in set
References
To view the optimizer statistics of tables that the current user can access, see ALL_TAB_STATISTICS.
To view the optimizer statistics of all tables in the current tenant, see DBA_TAB_STATISTICS.
To view column-level statistics at the global level, query the following views:
To view column-level statistics at the partition level, query the following views:
To view column-level statistics at the subpartition level, query the following views:
To view table-level histogram statistics, query the following views:
To view partition-level histogram statistics, query the following views:
To view subpartition-level histogram statistics, query the following views:
To view index statistics, query the following views:
For information about how to collect statistics, see the following topics: