Note
This view is introduced in V3.2.1.
Purpose
This view displays the optimizer statistics of tables accessible to the current user.
Applicability
This view applies only to OceanBase Database in Oracle mode.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | NO | The user to which the entity belongs. |
| 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 entity. |
| NUM_ROWS | NUMBER | YES | The number of rows in the entity. |
| BLOCKS | NUMBER | YES | This field is not supported. Its value is NULL by default. |
| EMPTY_BLOCKS | NUMBER | YES | This field is not supported. Its value is NULL by default. |
| AVG_SPACE | NUMBER | YES | This field is not supported. Its value is NULL by default. |
| CHAIN_CNT | NUMBER | YES | This field is not supported. Its value is NULL by default. |
| AVG_ROW_LEN | NUMBER | YES | The average row length of the entity. |
| AVG_SPACE_FREELIST_BLOCKS | NUMBER | YES | This field is not supported. Its value is NULL by default. |
| NUM_FREELIST_BLOCKS | NUMBER | YES | This field is not supported. Its value is NULL by default. |
| AVG_CACHED_BLOCKS | NUMBER | YES | This field is not supported. Its value is NULL by default. |
| AVG_CACHE_HIT_RATIO | NUMBER | YES | This field is not supported. Its value is NULL by default. |
| IM_IMCU_COUNT | NUMBER | YES | This field is not supported. Its value is NULL by default. |
| IM_BLOCK_COUNT | NUMBER | YES | This field is not supported. Its value is NULL by default. |
| IM_STAT_UPDATE_TIME | TIMESTAMP(9) | YES | This field is not supported. Its value is NULL by default. |
| SCAN_RATE | NUMBER | YES | This field is not supported. Its value is NULL by default. |
| 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 were 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 outdated. |
| SCOPE | VARCHAR2(7) | YES | The scope of validity of the statistics. |
Sample query
View the optimizer statistics for the T_SUBPART table among the tables that the current user can access.
obclient [SYS]> SELECT OWNER, TABLE_NAME, OBJECT_TYPE, NUM_ROWS, AVG_ROW_LEN FROM SYS.ALL_TAB_STATISTICS WHERE TABLE_NAME = 'T_SUBPART' ORDER BY 1, 2, 3;
The query result is as follows:
+-------+------------+--------------+----------+-------------+
| OWNER | TABLE_NAME | OBJECT_TYPE | NUM_ROWS | AVG_ROW_LEN |
+-------+------------+--------------+----------+-------------+
| SYS | T_SUBPART | PARTITION | 2530 | 60 |
| SYS | T_SUBPART | PARTITION | 4966 | 60 |
| SYS | T_SUBPART | PARTITION | 2504 | 60 |
| SYS | T_SUBPART | SUBPARTITION | 829 | 60 |
| SYS | T_SUBPART | SUBPARTITION | 1141 | 60 |
| SYS | T_SUBPART | SUBPARTITION | 560 | 60 |
| SYS | T_SUBPART | SUBPARTITION | 1122 | 60 |
| SYS | T_SUBPART | SUBPARTITION | 2714 | 60 |
| SYS | T_SUBPART | SUBPARTITION | 1130 | 60 |
| SYS | T_SUBPART | SUBPARTITION | 549 | 60 |
| SYS | T_SUBPART | SUBPARTITION | 1125 | 60 |
| SYS | T_SUBPART | SUBPARTITION | 830 | 60 |
| SYS | T_SUBPART | TABLE | 10000 | 60 |
+-------+------------+--------------+----------+-------------+
13 rows in set
References
View the optimizer statistics of all tables in the current tenant: DBA_TAB_STATISTICS
View the optimizer statistics of tables owned by the current user: USER_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 information about how to collect statistics, see the following topics: