---
title: 手动统计信息收集命令使用手册-OceanBase数据库使用指南
description: 了解OceanBase数据库在实际应用中关于 手动统计信息收集命令使用手册相关的常见问题和使用技巧，帮助您快速解决 手动统计信息收集命令使用手册的难题。
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

划线反馈

# 手动统计信息收集命令使用手册

更新时间：2025-11-24 08:36

适用版本： V3.2.x、V4.0.x、V4.1.x、V4.2.x、V4.3.x、V4.4.x 内容类型：TechNote  

优化器统计信息是一个描述数据库中表和列信息的数据集合，是选取最优执行计划非常关键的部分。OceanBase 数据库 V4.x 之前版本的统计信息收集主要依靠每日合并过程中完成，但是由于每日合并是增量合并，会导致统计信息并不是一直准确的，同时每日合并也没法收集直方图信息。因此，**从 OceanBase 数据库 V4.x 版本开始，实现了全新的统计信息收集，将统计信息收集和每日合并解耦，每日合并不再收集统计信息**。所以在使用 OceanBase 数据库 V4.x 版本的时候需要特别关注统计信息的收集情况。本文将结合一些实际应用场景针对性的推荐一些手动统计信息收集的命令。

## 表级统计信息收集

如果需要显示收集某个表的统计信息，当前主要提供了两种方式进行统计信息：**DBMS_STATS 系统包和 ANALYZE 命令行**。不同版本的差异如下。

| **内核版本** | **DBMS_STATS 系统包** | **ANALYZE 命令行** |
| --- | --- | --- |
| 内核 V4.x 版本 Oracle 模式 | ✅ | ✅ |
| 内核 V4.x 版本 MySQL 模式 | ✅ | ✅ |
| 内核 V3.2.3/V3.2.x 版本 Oracle 模式 | ✅ | ✅ |
| 内核 V3.2.3/V3.2.x 版本 MySQL 模式 | ❌ | ✅ |
| 内核其他版本 | ❌ | ❌ |

### 非分区表的统计信息收集

- **当表的数据量和列的个数的乘积不高于 1 千万时**，推荐使用如下命令收集，比如如下 TEST 用户的表 T1 有 10 个列，同时数据量在一百万行时。

  ```shell
  OceanBase(TEST@TEST)>create table test.t1(c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int);

  OceanBase(TEST@TEST)>insert /*+append*/ into t1 select level,level,level,level,level,level,level,level,level,level from dual connect by level<=1000000;

  ```

     - OceanBase 数据库 V4.x 版本。

      ```shell
      re1.不收集直方图
      call dbms_stats.gather_table_stats('test', 't1', method_opt=>'for all columns size 1');

      re2.直方图收集使用默认策略
      call dbms_stats.gather_table_stats('test', 't1');、
      -- 收集时间约 2 秒左右

      ```
     - OceanBase 数据库 V3.2.3/V3.2.x 版本。

      不推荐使用，实验特性。
     - OceanBase 数据库 V4.x 版本（使用并行度）。

      ```shell
      re1.不收集直方图
      call dbms_stats.gather_table_stats('test', 't1', degree=>8, method_opt=>'for all columns size 1');

      re2.直方图收集使用默认策略
      call dbms_stats.gather_table_stats('test', 't1', degree=>8);
      -- 收集时间约 4 秒左右

      不推荐使用，实验特性。

### 分区表的统计信息收集

相比较于非分区表，统计信息收集需要考虑分区表的分区统计信息收集，因此收集策略配置时需要将其考虑进去。

- **在系统资源允许的情况下，推荐在上述收集非分区表的并行度情况下再额外增加一倍的并行度**，相同上述场景中的 TEST 用户 T1 表改为 128 分区的 T_PART 表，10 列，100 万行数据，由于多了一个分区统计信息的收集，因此加了并行度为 2。

  ```shell
  OceanBase(TEST@TEST)>create table t_part(c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int) partition by hash(c1) partitions 128;

  OceanBase(TEST@TEST)>insert /*+append*/ into t_part select level,level,level,level,level,level,level,level,level,level from dual connect by level<=1000000;

  ```

     - OceanBase 数据库 V4.x 版本（使用并行度）。

      ```shell
      使用适当的并行度：
      re1.不收集直方图
      call dbms_stats.gather_table_stats('test', 't_part', degree=>2, method_opt=>'for all columns size 1');

      re2.直方图收集使用默认策略
      call dbms_stats.gather_table_stats('test', 't_part', degree=>2);

      -- 收集时间约 4 秒左右

综上，**针对分区表的统计信息收集，可以考虑增加合适的并行度以及选择分区推导的方式进行统计信息收集**。

## SCHEMA 级别的统计信息收集

除了手动的对单表的统计信息收集以外，基于 DBMS_STATS 系统包还提供了对整个用户下的所有表进行统计信息，**因此该功能仅仅只有在 OceanBase 数据库 V4.x 版本的所有模式和 V3.2.3/V3.2.x 版本的 Oracle 模式支持。**

在收集某个用户下的所有表统计信息时，很显然这是一个比较耗时的操作；因此，该功能建议在业务低峰期使用。

- 如果该用户下**所有表的数据量都是一些小表（数据量不超过 1 百万行）**，可以直接使用类似于如下收集 TEST 用户的统计信息命令。

  ```shell
  re1.不收集直方图
  call dbms_stats.gather_schema_stats('TEST', method_opt=>'for all columns size 1');

  re2.直方图收集使用默认策略
  call dbms_stats.gather_schema_stats('TEST');

  ```
 - 当收集的用户下**存在一些大表（行数在千万级别）**时，可以在业务低峰期增大并行度来收集。

  ```shell
  re1.不收集直方图
  call dbms_stats.gather_schema_stats('TEST', degree=>'16', method_opt=>'for all columns size 1');

  re2.直方图收集使用默认策略
  call dbms_stats.gather_schema_stats('TEST', degree=>'16');

  ```
 - 如果**用户下存在超大表（行数超过 1 亿）时，可以选择针对超大表开大并行单独收集**，然后锁定超大表的统计信息再使用上述命令收集整个用户的，收集完成后在解锁超大表的统计信息，后续按照增量模式收集。如下示例命令。

  ```shell
  call dbms_stats.gather_table_stats('test', 'big_table', degree=>128, method_opt=>'for all columns size 1');

  call dbms_stats.lock_table_stats('test','big_table');

  call dbms_stats.gather_schema_stats('TEST', degree=>'16', method_opt=>'for all columns size 1');

  call dbms_stats.unlock_table_stats('test','big_table');

  ```

## 统计信息过期查询

### 内核 V4.1 版本

```shell
-- 在 SYS 用户中执行
select distinct DATABASE_NAME,TABLE_NAME from (
WITH V AS
(SELECT
  NVL(T.TENANT_ID, 0) AS TENANT_ID,
  NVL(T.TABLE_ID, VT.TABLE_ID) AS TABLE_ID,
  NVL(T.TABLET_ID, VT.TABLET_ID) AS TABLET_ID,
  NVL(T.INSERTS, 0) + NVL(VT.INSERT_ROW_COUNT, 0) - NVL(T.LAST_INSERTS, 0) AS INSERTS,
  NVL(T.UPDATES, 0) + NVL(VT.UPDATE_ROW_COUNT, 0) - NVL(T.LAST_UPDATES, 0) AS UPDATES,
  NVL(T.DELETES, 0) + NVL(VT.DELETE_ROW_COUNT, 0) - NVL(T.LAST_DELETES, 0) AS DELETES
  FROM
  OCEANBASE.__ALL_MONITOR_MODIFIED T
  FULL JOIN
  OCEANBASE.__ALL_VIRTUAL_DML_STATS VT
  ON T.TABLE_ID = VT.TABLE_ID
  AND T.TABLET_ID = VT.TABLET_ID
  AND VT.TENANT_ID = EFFECTIVE_TENANT_ID()
)
SELECT
  CAST(TM.DATABASE_NAME AS CHAR(128)) AS DATABASE_NAME,
  CAST(TM.TABLE_NAME AS CHAR(128)) AS TABLE_NAME,
  CAST(TM.PART_NAME AS CHAR(128)) AS PARTITION_NAME,
  CAST(TM.SUB_PART_NAME AS CHAR(128)) AS SUBPARTITION_NAME,
  CAST(TS.ROW_CNT AS SIGNED) AS LAST_ANALYZED_ROWS,
  TS.LAST_ANALYZED AS LAST_ANALYZED_TIME,
  CAST(TM.INSERTS AS SIGNED) AS INSERTS,
  CAST(TM.UPDATES AS SIGNED) AS UPDATES,
  CAST(TM.DELETES AS SIGNED) AS DELETES,
  CAST(NVL(CAST(UP.VALCHAR AS SIGNED), CAST(GP.SPARE4 AS SIGNED)) AS SIGNED) STALE_PERCENT,
  CAST(CASE NVL((TM.INSERTS + TM.UPDATES + TM.DELETES) > TS.ROW_CNT * NVL(CAST(UP.VALCHAR AS SIGNED), CAST(GP.SPARE4 AS SIGNED)) / 100,
                (TM.INSERTS + TM.UPDATES + TM.DELETES) > 0)
        WHEN 0 THEN 'NO'
        WHEN 1 THEN 'YES'
       END AS CHAR(3)) AS IS_STALE
FROM
(SELECT
  T.TENANT_ID,
  T.TABLE_ID,
  CASE T.PART_LEVEL WHEN 0 THEN T.TABLE_ID WHEN 1 THEN P.PART_ID WHEN 2 THEN SP.SUB_PART_ID END AS PARTITION_ID,
  DB.DATABASE_NAME,
  T.TABLE_NAME,
  P.PART_NAME,
  SP.SUB_PART_NAME,
  NVL(V.INSERTS, 0) AS INSERTS,
  NVL(V.UPDATES, 0) AS UPDATES,
  NVL(V.DELETES, 0) AS DELETES
FROM OCEANBASE.__ALL_TABLE T
JOIN OCEANBASE.__ALL_DATABASE DB
  ON T.TENANT_ID = DB.TENANT_ID AND DB.DATABASE_ID = T.DATABASE_ID
LEFT JOIN OCEANBASE.__ALL_PART P
  ON T.TENANT_ID = P.TENANT_ID AND T.TABLE_ID = P.TABLE_ID
LEFT JOIN OCEANBASE.__ALL_SUB_PART SP
  ON T.TENANT_ID = SP.TENANT_ID AND T.TABLE_ID = SP.TABLE_ID AND P.PART_ID = SP.PART_ID
LEFT JOIN V
ON T.TENANT_ID = V.TENANT_ID AND T.TABLE_ID = V.TABLE_ID
AND V.TABLET_ID = CASE T.PART_LEVEL WHEN 0 THEN T.TABLET_ID WHEN 1 THEN P.TABLET_ID WHEN 2 THEN SP.TABLET_ID END
WHERE T.TABLE_TYPE IN (0, 3, 6)
UNION ALL
SELECT
  MIN(T.TENANT_ID),
  MIN(T.TABLE_ID),
  -1 AS PARTITION_ID,
  DB.DATABASE_NAME,
  T.TABLE_NAME,
  NULL AS PART_NAME,
  NULL AS SUB_PART_NAME,
  SUM(NVL(V.INSERTS, 0)) AS INSERTS,
  SUM(NVL(V.UPDATES, 0)) AS UPDATES,
  SUM(NVL(V.DELETES, 0)) AS DELETES
FROM OCEANBASE.__ALL_TABLE T
JOIN OCEANBASE.__ALL_DATABASE DB
  ON T.TENANT_ID = DB.TENANT_ID AND DB.DATABASE_ID = T.DATABASE_ID
JOIN OCEANBASE.__ALL_PART P
  ON T.TENANT_ID = P.TENANT_ID AND T.TABLE_ID = P.TABLE_ID
LEFT JOIN V
ON T.TENANT_ID = V.TENANT_ID AND T.TABLE_ID = V.TABLE_ID AND V.TABLET_ID = P.TABLET_ID
WHERE T.TABLE_TYPE IN (0, 3, 6) AND T.PART_LEVEL = 1
GROUP BY DB.DATABASE_NAME,
         T.TABLE_NAME
UNION ALL
SELECT
  MIN(T.TENANT_ID),
  MIN(T.TABLE_ID),
  MIN(P.PART_ID) AS PARTITION_ID,
  DB.DATABASE_NAME,
  T.TABLE_NAME,
  P.PART_NAME,
  NULL AS SUB_PART_NAME,
  SUM(NVL(V.INSERTS, 0)) AS INSERTS,
  SUM(NVL(V.UPDATES, 0)) AS UPDATES,
  SUM(NVL(V.DELETES, 0)) AS DELETES
FROM OCEANBASE.__ALL_TABLE T
JOIN OCEANBASE.__ALL_DATABASE DB
  ON T.TENANT_ID = DB.TENANT_ID AND DB.DATABASE_ID = T.DATABASE_ID
JOIN OCEANBASE.__ALL_PART P
  ON T.TENANT_ID = P.TENANT_ID AND T.TABLE_ID = P.TABLE_ID
JOIN OCEANBASE.__ALL_SUB_PART SP
  ON T.TENANT_ID = SP.TENANT_ID AND T.TABLE_ID = SP.TABLE_ID AND P.PART_ID = SP.PART_ID
LEFT JOIN V
ON T.TENANT_ID = V.TENANT_ID AND T.TABLE_ID = V.TABLE_ID AND V.TABLET_ID = SP.TABLET_ID
WHERE T.TABLE_TYPE IN (0, 3, 6) AND T.PART_LEVEL = 2
GROUP BY DB.DATABASE_NAME,
        T.TABLE_NAME,
        P.PART_NAME
UNION ALL
SELECT
  MIN(T.TENANT_ID),
  MIN(T.TABLE_ID),
  -1 AS PARTITION_ID,
  DB.DATABASE_NAME,
  T.TABLE_NAME,
  NULL AS PART_NAME,
  NULL AS SUB_PART_NAME,
  SUM(NVL(V.INSERTS, 0)) AS INSERTS,
  SUM(NVL(V.UPDATES, 0)) AS UPDATES,
  SUM(NVL(V.DELETES, 0)) AS DELETES
FROM OCEANBASE.__ALL_TABLE T
JOIN OCEANBASE.__ALL_DATABASE DB
  ON T.TENANT_ID = DB.TENANT_ID AND DB.DATABASE_ID = T.DATABASE_ID
JOIN OCEANBASE.__ALL_PART P
  ON T.TENANT_ID = P.TENANT_ID AND T.TABLE_ID = P.TABLE_ID
JOIN OCEANBASE.__ALL_SUB_PART SP
  ON T.TENANT_ID = SP.TENANT_ID AND T.TABLE_ID = SP.TABLE_ID AND P.PART_ID = SP.PART_ID
LEFT JOIN V
ON T.TENANT_ID = V.TENANT_ID AND T.TABLE_ID = V.TABLE_ID AND V.TABLET_ID = SP.TABLET_ID
WHERE T.TABLE_TYPE IN (0, 3, 6) AND T.PART_LEVEL = 2
GROUP BY DB.DATABASE_NAME,
        T.TABLE_NAME
) TM
LEFT JOIN OCEANBASE.__ALL_TABLE_STAT TS
  ON TM.TENANT_ID = TS.TENANT_ID AND TM.TABLE_ID = TS.TABLE_ID AND TM.PARTITION_ID = TS.PARTITION_ID
LEFT JOIN OCEANBASE.__ALL_OPTSTAT_USER_PREFS UP
  ON TM.TENANT_ID = UP.TENANT_ID AND TM.TABLE_ID = UP.TABLE_ID AND UP.PNAME = 'STALE_PERCENT'
JOIN OCEANBASE.__ALL_OPTSTAT_GLOBAL_PREFS GP
  ON GP.SNAME = 'STALE_PERCENT') where DATABASE_NAME not in ('oceanbase','mysql', '__recyclebin') and (IS_STALE = 'YES' or LAST_ANALYZED_TIME is null);

```

```shell
-- 在 Oracle 模式租户下执行
select distinct OWNER,TABLE_NAME from (
WITH V AS
(SELECT
  NVL(T.TABLE_ID, VT.OBJN) AS TABLE_ID,
  NVL(T.TABLET_ID, VT.PAROBJN) AS TABLET_ID,
  NVL(T.INSERTS, 0) + NVL(VT.INS, 0) - NVL(T.LAST_INSERTS, 0) AS INSERTS,
  NVL(T.UPDATES, 0) + NVL(VT.UPD, 0) - NVL(T.LAST_UPDATES, 0) AS UPDATES,
  NVL(T.DELETES, 0) + NVL(VT.DEL, 0) - NVL(T.LAST_DELETES, 0) AS DELETES
  FROM
  SYS.ALL_VIRTUAL_MONITOR_MODIFIED_REAL_AGENT T
  FULL JOIN
  SYS.GV$DML_STATS VT
  ON T.TABLE_ID = VT.OBJN
  AND T.TABLET_ID = VT.PAROBJN
  AND T.TENANT_ID = EFFECTIVE_TENANT_ID()
  AND VT.INST_ID = EFFECTIVE_TENANT_ID()
)
SELECT
  CAST(TM.DATABASE_NAME AS VARCHAR2(128)) AS OWNER,
  CAST(TM.TABLE_NAME AS VARCHAR2(128)) AS TABLE_NAME,
  CAST(TM.PART_NAME AS VARCHAR2(128)) AS PARTITION_NAME,
  CAST(TM.SUB_PART_NAME AS VARCHAR2(128)) AS SUBPARTITION_NAME,
  CAST(TS.ROW_CNT AS NUMBER) AS LAST_ANALYZED_ROWS,
  TS.LAST_ANALYZED AS LAST_ANALYZED_TIME,
  CAST(TM.INSERTS AS NUMBER) AS INSERTS,
  CAST(TM.UPDATES AS NUMBER) AS UPDATES,
  CAST(TM.DELETES AS NUMBER) AS DELETES,
  CAST(NVL(CAST(UP.VALCHAR AS NUMBER), CAST(GP.SPARE4 AS NUMBER)) AS NUMBER) STALE_PERCENT,
  CAST(CASE WHEN TS.ROW_CNT IS NOT NULL
       THEN CASE WHEN (TM.INSERTS + TM.UPDATES + TM.DELETES) > TS.ROW_CNT * NVL(CAST(UP.VALCHAR AS NUMBER), CAST(GP.SPARE4 AS NUMBER)) / 100
            THEN 'YES' ELSE 'NO' END
       ELSE CASE WHEN (TM.INSERTS + TM.UPDATES + TM.DELETES) > 0
            THEN 'YES' ELSE 'NO' END
       END AS VARCHAR2(3)) AS IS_STALE
FROM
(SELECT
  T.TENANT_ID,
  T.TABLE_ID,
  CASE T.PART_LEVEL WHEN 0 THEN T.TABLE_ID WHEN 1 THEN P.PART_ID WHEN 2 THEN SP.SUB_PART_ID END AS PARTITION_ID,
  DB.DATABASE_NAME,
  T.TABLE_NAME,
  P.PART_NAME,
  SP.SUB_PART_NAME,
  NVL(V.INSERTS, 0) AS INSERTS,
  NVL(V.UPDATES, 0) AS UPDATES,
  NVL(V.DELETES, 0) AS DELETES
FROM SYS.ALL_VIRTUAL_TABLE_REAL_AGENT T
JOIN SYS.ALL_VIRTUAL_DATABASE_REAL_AGENT DB
  ON DB.DATABASE_ID = T.DATABASE_ID
  AND T.TENANT_ID = EFFECTIVE_TENANT_ID()
  AND DB.TENANT_ID = EFFECTIVE_TENANT_ID()
LEFT JOIN SYS.ALL_VIRTUAL_PART_REAL_AGENT P
  ON T.TABLE_ID = P.TABLE_ID
  AND P.TENANT_ID = EFFECTIVE_TENANT_ID()
LEFT JOIN SYS.ALL_VIRTUAL_SUB_PART_REAL_AGENT SP
  ON T.TABLE_ID = SP.TABLE_ID
  AND P.PART_ID = SP.PART_ID
  AND SP.TENANT_ID = EFFECTIVE_TENANT_ID()
LEFT JOIN V
ON T.TABLE_ID = V.TABLE_ID
AND V.TABLET_ID = CASE T.PART_LEVEL WHEN 0 THEN T.TABLET_ID WHEN 1 THEN P.TABLET_ID WHEN 2 THEN SP.TABLET_ID END
WHERE T.TABLE_TYPE IN (0, 3, 8, 9)
UNION ALL
SELECT
  MIN(T.TENANT_ID),
  MIN(T.TABLE_ID),
  -1 AS PARTITION_ID,
  DB.DATABASE_NAME,
  T.TABLE_NAME,
  NULL AS PART_NAME,
  NULL AS SUB_PART_NAME,
  SUM(NVL(V.INSERTS, 0)) AS INSERTS,
  SUM(NVL(V.UPDATES, 0)) AS UPDATES,
  SUM(NVL(V.DELETES, 0)) AS DELETES
FROM SYS.ALL_VIRTUAL_TABLE_REAL_AGENT T
JOIN SYS.ALL_VIRTUAL_DATABASE_REAL_AGENT DB
  ON DB.DATABASE_ID = T.DATABASE_ID
  AND T.TENANT_ID = EFFECTIVE_TENANT_ID()
  AND DB.TENANT_ID = EFFECTIVE_TENANT_ID()
JOIN SYS.ALL_VIRTUAL_PART_REAL_AGENT P
  ON T.TABLE_ID = P.TABLE_ID
  AND P.TENANT_ID = EFFECTIVE_TENANT_ID()
LEFT JOIN V
ON T.TABLE_ID = V.TABLE_ID AND V.TABLET_ID = P.TABLET_ID
WHERE T.TABLE_TYPE IN (0, 3, 8, 9) AND T.PART_LEVEL = 1
GROUP BY DB.DATABASE_NAME,
         T.TABLE_NAME
UNION ALL
SELECT
  MIN(T.TENANT_ID),
  MIN(T.TABLE_ID),
  MIN(P.PART_ID) AS PARTITION_ID,
  DB.DATABASE_NAME,
  T.TABLE_NAME,
  P.PART_NAME,
  NULL AS SUB_PART_NAME,
  SUM(NVL(V.INSERTS, 0)) AS INSERTS,
  SUM(NVL(V.UPDATES, 0)) AS UPDATES,
  SUM(NVL(V.DELETES, 0)) AS DELETES
FROM SYS.ALL_VIRTUAL_TABLE_REAL_AGENT T
JOIN SYS.ALL_VIRTUAL_DATABASE_REAL_AGENT DB
  ON DB.DATABASE_ID = T.DATABASE_ID
  AND T.TENANT_ID = EFFECTIVE_TENANT_ID()
  AND DB.TENANT_ID = EFFECTIVE_TENANT_ID()
JOIN SYS.ALL_VIRTUAL_PART_REAL_AGENT P
  ON T.TENANT_ID = P.TENANT_ID AND T.TABLE_ID = P.TABLE_ID
JOIN SYS.ALL_VIRTUAL_SUB_PART_REAL_AGENT SP
  ON T.TENANT_ID = SP.TENANT_ID AND T.TABLE_ID = SP.TABLE_ID AND P.PART_ID = SP.PART_ID
LEFT JOIN V
ON T.TABLE_ID = V.TABLE_ID AND V.TABLET_ID = SP.TABLET_ID
WHERE T.TABLE_TYPE IN (0, 3, 8, 9) AND T.PART_LEVEL = 2
GROUP BY DB.DATABASE_NAME,
        T.TABLE_NAME,
        P.PART_NAME
UNION ALL
SELECT
  MIN(T.TENANT_ID),
  MIN(T.TABLE_ID),
  -1 AS PARTITION_ID,
  DB.DATABASE_NAME,
  T.TABLE_NAME,
  NULL AS PART_NAME,
  NULL AS SUB_PART_NAME,
  SUM(NVL(V.INSERTS, 0)) AS INSERTS,
  SUM(NVL(V.UPDATES, 0)) AS UPDATES,
  SUM(NVL(V.DELETES, 0)) AS DELETES
FROM SYS.ALL_VIRTUAL_TABLE_REAL_AGENT T
JOIN SYS.ALL_VIRTUAL_DATABASE_REAL_AGENT DB
  ON DB.DATABASE_ID = T.DATABASE_ID
  AND T.TENANT_ID = EFFECTIVE_TENANT_ID()
  AND DB.TENANT_ID = EFFECTIVE_TENANT_ID()
JOIN SYS.ALL_VIRTUAL_PART_REAL_AGENT P
  ON T.TABLE_ID = P.TABLE_ID
  AND P.TENANT_ID = EFFECTIVE_TENANT_ID()
JOIN SYS.ALL_VIRTUAL_SUB_PART_REAL_AGENT SP
  ON T.TABLE_ID = SP.TABLE_ID
  AND P.PART_ID = SP.PART_ID
  AND SP.TENANT_ID = EFFECTIVE_TENANT_ID()
LEFT JOIN V
ON T.TABLE_ID = V.TABLE_ID AND V.TABLET_ID = SP.TABLET_ID
WHERE T.TABLE_TYPE IN (0, 3, 8, 9) AND T.PART_LEVEL = 2
GROUP BY DB.DATABASE_NAME,
        T.TABLE_NAME
) TM
LEFT JOIN SYS.ALL_VIRTUAL_TABLE_STAT_REAL_AGENT TS
  ON TM.TABLE_ID = TS.TABLE_ID
  AND TM.PARTITION_ID = TS.PARTITION_ID
  AND TM.TENANT_ID = EFFECTIVE_TENANT_ID()
LEFT JOIN SYS.ALL_VIRTUAL_OPTSTAT_USER_PREFS_REAL_AGENT UP
  ON TM.TABLE_ID = UP.TABLE_ID
  AND UP.PNAME = 'STALE_PERCENT'
  AND UP.TENANT_ID = EFFECTIVE_TENANT_ID()
JOIN SYS.ALL_VIRTUAL_OPTSTAT_GLOBAL_PREFS_REAL_AGENT GP
  ON GP.SNAME = 'STALE_PERCENT') where OWNER != 'oceanbase' and OWNER != '__recyclebin' and (IS_STALE = 'YES' or LAST_ANALYZED_TIME is null);

```

### 内核 V4.2 及其之后版本

```shell
-- 在 SYS 租户中执行
select distinct DATABASE_NAME,TABLE_NAME from oceanbase.DBA_OB_TABLE_STAT_STALE_INFO where DATABASE_NAME not in('oceanbase','mysql', '__recyclebin') and (IS_STALE = 'YES' or LAST_ANALYZED_TIME is null);

```

```shell
-- 在 Oracle 模式租户下执行
select distinct OWNER,TABLE_NAME from sys.DBA_OB_TABLE_STAT_STALE_INFO where OWNER != 'oceanbase' and OWNER != '__recyclebin' and (IS_STALE = 'YES' or LAST_ANALYZED_TIME is null);

```

## 适用版本

OceanBase 数据库 V3.2.x、V4.x 版本。

上一篇

[查询 __all_table_history 表的 inner sql 由于没有走上索引，执行缓慢](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000005413007)

下一篇

[专有云环境中 UPDATE 语句执行效率低下，影响业务运行的原因和解决方法](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000003699323) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线