Note
This view is available starting with V3.2.1.
Purpose
This view displays the optimizer statistics of all tables that belong to the current user.
Applicability
This view is available only in Oracle mode of OceanBase Database.
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 | This column is not supported. The value of this column is NULL by default. |
| EMPTY_BLOCKS | NUMBER | YES | This column is not supported. The value of this column is NULL by default. |
| AVG_SPACE | NUMBER | YES | This column is not supported. The value of this column is NULL by default. |
| CHAIN_CNT | NUMBER | YES | This column is not supported. The value of this column is NULL by default. |
| AVG_ROW_LEN | NUMBER | YES | The average row length of the object. |
| AVG_SPACE_FREELIST_BLOCKS | NUMBER | YES | This column is not supported. The value of this column is NULL by default. |
| NUM_FREELIST_BLOCKS | NUMBER | YES | This column is not supported. The value of this column is NULL by default. |
| AVG_CACHED_BLOCKS | NUMBER | YES | This column is not supported. The value of this column is NULL by default. |
| AVG_CACHE_HIT_RATIO | NUMBER | YES | This column is not supported. The value of this column is NULL by default. |
| IM_IMCU_COUNT | NUMBER | YES | This column is not supported. The value of this column is NULL by default. |
| IM_BLOCK_COUNT | NUMBER | YES | This column is not supported. The value of this column is NULL by default. |
| IM_STAT_UPDATE_TIME | TIMESTAMP(9) | YES | This column is not supported. The value of this column is NULL by default. |
| SCAN_RATE | NUMBER | YES | This column is not supported. The value of this column is NULL by default. |
| 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 are user-defined. |
| 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 in all tables 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
Query the optimizer statistics of tables accessible to the current user: ALL_TAB_STATISTICS
Query the optimizer statistics of all tables in the current tenant: DBA_TAB_STATISTICS
Query global-level column statistics from the following views:
Query partition-level column statistics from the following views:
Query subpartition-level column statistics from the following views:
Query table-level histogram statistics from the following views:
Query partition-level histogram statistics from the following views:
Query subpartition-level histogram statistics from the following views:
Query index statistics from the following views:
For more information about how to collect statistics, see the following topics: