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 | 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 analyzed. |
| 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 tables.
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 tenant: DBA_TAB_STATISTICS
Query the optimizer statistics of tables owned by the current user: USER_TAB_STATISTICS
Query the column statistics at the global level from the following views:
Query the column statistics at the partition level from the following views:
Query the column statistics at the subpartition level from the following views:
Query the histogram statistics at the table level from the following views:
Query the histogram statistics at the partition level from the following views:
Query the histogram statistics at the subpartition level from the following views:
Query the index statistics from the following views:
For information about how to collect statistics, see the following topics: