Note
This view is introduced since OceanBase Database V4.2.0.
Purpose
The 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 |
|---|---|---|---|
| OWNER | VARCHAR2(128) | NO | The owner of the table. |
| TABLE_NAME | VARCHAR2(128) | NO | The name of the table. |
| PARTITION_NAME | VARCHAR2(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 | VARCHAR2(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 | NUMBER | 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) WITH LOCAL TIME ZONE | YES | The time when statistics were last collected. The value NULL indicates that no statistics have been collected. |
| INSERTS | NUMBER | NO | The number of rows inserted since statistics were last collected. |
| UPDATES | NUMBER | NO | The number of rows updated since statistics were last collected. |
| DELETES | NUMBER | NO | The number of rows deleted since statistics were last collected. |
| STALE_PERCENT | NUMBER | NO | The percentage for determining whether statistics are outdated. |
| IS_STALE | VARCHAR2(3) | NO | Indicates whether statistics are outdated. Valid values:
|
Sample query
Check the number of DDL operations performed on the T_SUBPART table since statistics were last collected and whether the statistics are outdated.
obclient [SYS]> SELECT * FROM SYS.DBA_OB_TABLE_STAT_STALE_INFO WHERE TABLE_NAME='T_SUBPART';
The query result is as follows:
+-------+------------+----------------+-------------------+--------------------+------------------------------+---------+---------+---------+---------------+----------+
| OWNER | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | LAST_ANALYZED_ROWS | LAST_ANALYZED_TIME | INSERTS | UPDATES | DELETES | STALE_PERCENT | IS_STALE |
+-------+------------+----------------+-------------------+--------------------+------------------------------+---------+---------+---------+---------------+----------+
| SYS | T_SUBPART | NULL | NULL | 1000 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P1 | NULL | 500 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P2 | NULL | 300 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P3 | NULL | 200 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P1 | P1SP0 | 150 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P1 | P1SP1 | 150 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P1 | P1SP2 | 100 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P1 | P1SP3 | 100 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P2 | P2SP0 | 90 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P2 | P2SP1 | 90 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P2 | P2SP2 | 60 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P2 | P2SP3 | 60 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P3 | P3SP0 | 60 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P3 | P3SP1 | 60 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P3 | P3SP2 | 40 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
| SYS | T_SUBPART | P3 | P3SP3 | 40 | 21-MAR-25 04.21.31.118193 PM | 0 | 0 | 0 | 10 | NO |
+-------+------------+----------------+-------------------+--------------------+------------------------------+---------+---------+---------+---------------+----------+
16 rows in set