Note
This view is available starting with V3.2.1.
Purpose
This view displays the optimizer statistics of tables accessible to the current user.
Applicability
This view is available only in Oracle mode of OceanBase Database.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | NO | The user who owns the object. |
| 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. It is NULL by default. |
| EMPTY_BLOCKS | NUMBER | YES | This column is not supported. It is NULL by default. |
| AVG_SPACE | NUMBER | YES | This column is not supported. It is NULL by default. |
| CHAIN_CNT | NUMBER | YES | This column is not supported. It 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. It is NULL by default. |
| NUM_FREELIST_BLOCKS | NUMBER | YES | This column is not supported. It is NULL by default. |
| AVG_CACHED_BLOCKS | NUMBER | YES | This column is not supported. It is NULL by default. |
| AVG_CACHE_HIT_RATIO | NUMBER | YES | This column is not supported. It is NULL by default. |
| IM_IMCU_COUNT | NUMBER | YES | This column is not supported. It is NULL by default. |
| IM_BLOCK_COUNT | NUMBER | YES | This column is not supported. It is NULL by default. |
| IM_STAT_UPDATE_TIME | TIMESTAMP(9) | YES | This column is not supported. It is NULL by default. |
| SCAN_RATE | NUMBER | YES | This column is not supported. It is NULL by default. |
| SAMPLE_SIZE | NUMBER | YES | The sample size used to collect statistics. |
| LAST_ANALYZED | DATE | YES | The time when the last statistics collection was performed. |
| 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 the current user's accessible schema.
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
Query the optimizer statistics of all tables in the current schema: DBA_TAB_STATISTICS
Query the optimizer statistics of tables owned by the current user: USER_TAB_STATISTICS
Query column statistics at the global level from the following views:
Query column statistics at the partition level from the following views:
Query column statistics at the subpartition level from the following views:
Query histogram statistics at the table level from the following views:
Query histogram statistics at the partition level from the following views:
Query histogram statistics at the subpartition level from the following views:
Query index statistics from the following views:
For information about how to collect statistics, see the following topics: