Note
This view is introduced since OceanBase Database V4.2.0.
Purpose
The oceanbase.DBA_OB_TABLE_STAT_STALE_INFO view displays the number of DDL operations performed on each table since the last statistics collection, and whether the current statistics are outdated.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| DATABASE_NAME | varchar(128) | NO | The name of the database where the table resides. |
| TABLE_NAME | varchar(128) | NO | The name of the table. |
| PARTITION_NAME | varchar(128) | YES | The name of the partition. If the value of this column in a partitioned table is NULL, the current record is summarized at the table level. |
| SUBPARTITION_NAME | varchar(128) | YES | The name of the subpartition. If the value of this column in a subpartitioned table is NULL, the current record is summarized at the table or partition level. |
| LAST_ANALYZED_ROWS | bigint(20) | YES | The number of rows when statistics were collected the last time. The value NULL indicates that no statistics have been collected. |
| LAST_ANALYZED_TIME | timestamp(6) | YES | The time when statistics were last collected. The value NULL indicates that no statistics have been collected. |
| INSERTS | BIGINT(21) | NO | The number of rows inserted since statistics were last collected. |
| UPDATES | BIGINT(21) | NO | The number of rows updated since statistics were last collected. |
| DELETES | BIGINT(21) | NO | The number of rows deleted since statistics were last collected. |
| STALE_PERCENT | BIGINT(21) | NO | The percentage for determining whether statistics are outdated. |
| IS_STALE | varchar(3) | NO | Indicates whether statistics are outdated. Valid values:
|
Sample query
View the number of DDL operations performed on t_subpart since the last time statistics were collected and the expiration status of statistics.
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_TABLE_STAT_STALE_INFO WHERE TABLE_NAME='t_subpart';
The query result is as follows:
+---------------+---------------+----------------+-------------------+--------------------+----------------------------+---------+---------+---------+---------------+----------+
| DATABASE_NAME | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | LAST_ANALYZED_ROWS | LAST_ANALYZED_TIME | INSERTS | UPDATES | DELETES | STALE_PERCENT | IS_STALE |
+---------------+---------------+----------------+-------------------+--------------------+----------------------------+---------+---------+---------+---------------+----------+
| test | t_subpart | NULL | NULL | 1000 | 2025-03-21 16:21:09.846859 | 0 | 0 | 0 | 10 | NO |
| test | t_subpart | p2023 | NULL | 635 | 2025-03-21 16:21:09.846859 | 0 | 0 | 0 | 10 | NO |
| test | t_subpart | p2024 | NULL | 365 | 2025-03-21 16:21:09.846859 | 0 | 0 | 0 | 10 | NO |
| test | t_subpart | p_max | NULL | 0 | 2025-03-21 16:21:09.846859 | 0 | 0 | 0 | 10 | NO |
| test | t_subpart | p2023 | p2023sp0 | 127 | 2025-03-21 16:21:09.846859 | 0 | 0 | 0 | 10 | NO |
| test | t_subpart | p2023 | p2023sp1 | 190 | 2025-03-21 16:21:09.846859 | 0 | 0 | 0 | 10 | NO |
| test | t_subpart | p2023 | p2023sp2 | 127 | 2025-03-21 16:21:09.846859 | 0 | 0 | 0 | 10 | NO |
| test | t_subpart | p2023 | p2023sp3 | 191 | 2025-03-21 16:21:09.846859 | 0 | 0 | 0 | 10 | NO |
| test | t_subpart | p2024 | p2024sp0 | 73 | 2025-03-21 16:21:09.846859 | 0 | 0 | 0 | 10 | NO |
| test | t_subpart | p2024 | p2024sp1 | 110 | 2025-03-21 16:21:09.846859 | 0 | 0 | 0 | 10 | NO |
| test | t_subpart | p2024 | p2024sp2 | 73 | 2025-03-21 16:21:09.846859 | 0 | 0 | 0 | 10 | NO |
| test | t_subpart | p2024 | p2024sp3 | 109 | 2025-03-21 16:21:09.846859 | 0 | 0 | 0 | 10 | NO |
| test | t_subpart | p_max | p_maxsp0 | 0 | 2025-03-21 16:21:09.846859 | 0 | 0 | 0 | 10 | NO |
| test | t_subpart | p_max | p_maxsp1 | 0 | 2025-03-21 16:21:09.846859 | 0 | 0 | 0 | 10 | NO |
| test | t_subpart | p_max | p_maxsp2 | 0 | 2025-03-21 16:21:09.846859 | 0 | 0 | 0 | 10 | NO |
| test | t_subpart | p_max | p_maxsp3 | 0 | 2025-03-21 16:21:09.846859 | 0 | 0 | 0 | 10 | NO |
+---------------+---------------+----------------+-------------------+--------------------+----------------------------+---------+---------+---------+---------------+----------+
16 rows in set