Note
This view is introduced in V3.2.1.
Purpose
This view displays the optimizer statistics of all tables belonging to the current user.
Applicability
This view applies only to OceanBase Database 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 | This column is not supported. The default value is NULL. |
| EMPTY_BLOCKS | NUMBER | YES | This column is not supported. The default value is NULL. |
| AVG_SPACE | NUMBER | YES | This column is not supported. The default value is NULL. |
| CHAIN_CNT | NUMBER | YES | This column is not supported. The default value is NULL. |
| AVG_ROW_LEN | NUMBER | YES | The average row length of the object. |
| AVG_SPACE_FREELIST_BLOCKS | NUMBER | YES | This column is not supported. The default value is NULL. |
| NUM_FREELIST_BLOCKS | NUMBER | YES | This column is not supported. The default value is NULL. |
| AVG_CACHED_BLOCKS | NUMBER | YES | This column is not supported. The default value is NULL. |
| AVG_CACHE_HIT_RATIO | NUMBER | YES | This column is not supported. The default value is NULL. |
| IM_IMCU_COUNT | NUMBER | YES | This column is not supported. The default value is NULL. |
| IM_BLOCK_COUNT | NUMBER | YES | This column is not supported. The default value is NULL. |
| IM_STAT_UPDATE_TIME | TIMESTAMP(9) | YES | This column is not supported. The default value is NULL. |
| SCAN_RATE | NUMBER | YES | This column is not supported. The default value is NULL. |
| SAMPLE_SIZE | NUMBER | YES | The sample size when statistics are collected. |
| LAST_ANALYZED | DATE | YES | The time when the last analysis was performed. |
| GLOBAL_STATS | VARCHAR2(3) | YES | YES: indicates that the statistics are directly collected or incrementally maintained.NO: indicates other cases. |
| 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 validity of the statistics. |
Sample query
View the optimizer statistics of the T_SUBPART table among all tables that the current user has access to.
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
View the optimizer statistics of tables accessible to the current user: ALL_TAB_STATISTICS
View the optimizer statistics of all tables in the current tenant: DBA_TAB_STATISTICS
View global column statistics by querying the following views:
View partition-level column statistics by querying the following views:
View subpartition-level column statistics by querying the following views:
View table-level histogram statistics by querying the following views:
View partition-level histogram statistics by querying the following views:
View subpartition-level histogram statistics by querying the following views:
View index statistics by querying the following views:
For more information about how to collect statistics, see the following topics:
