Oracle 11g使用GATHER_DATABASE_STATS收集指定分区统计信息失败问题

环境信息 #

select * from v$version
BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0  Production  
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

问题描述 #

需要通过DBMS_STATS.GATHER_DATABASE_STATS的obj_filter_list参数，手动指定特定表和分区收集统计信息（业务按需聚焦需求，已知自动收集为最优方案）。当前仅指定表时功能正常，但添加分区名称后，分区的统计信息未被收集。

使用的代码如下：

declare
    filter_list dbms_stats.objecttab := dbms_stats.objecttab();
begin
    filter_list.extend(2);
    filter_list(1).ownname := 'USER01';
    filter_list(1).objname := 'TABLEABC';
    filter_list(1).objtype := 'TABLE';
    filter_list(1).partname := 'PART_CURR';
    
    filter_list(2).ownname := 'USER02';
    filter_list(2).objname := 'TAB_MYTAB';
    filter_list(2).objtype := 'TABLE';
    filter_list(2).partname := 'PART2023'    ;
    
    dbms_stats.gather_database_stats(obj_filter_list => filter_list);
end;
/

问题原因 #

在Oracle 11g中，obj_filter_list条目里如果指定了partname，但objtype设为'TABLE'，数据库会将该条目视为表级过滤条件，直接忽略partname参数，导致分区统计无法被收集。必须明确指定objtype为'PARTITION'才能触发分区级的统计收集。

解决方案 #

1. 修正objtype参数 #

将需要收集统计的分区条目，objtype改为'PARTITION'。如果同时需要收集整个表的统计，需单独添加表级的过滤条目。

2. 修改后的示例代码 #

declare
    filter_list dbms_stats.objecttab := dbms_stats.objecttab();
begin
    -- 收集USER01.TABLEABC的PART_CURR分区统计
    filter_list.extend;
    filter_list(filter_list.last).ownname := 'USER01';
    filter_list(filter_list.last).objname := 'TABLEABC';
    filter_list(filter_list.last).objtype := 'PARTITION';
    filter_list(filter_list.last).partname := 'PART_CURR';
    
    -- 收集USER02.TAB_MYTAB的PART2023分区统计
    filter_list.extend;
    filter_list(filter_list.last).ownname := 'USER02';
    filter_list(filter_list.last).objname := 'TAB_MYTAB';
    filter_list(filter_list.last).objtype := 'PARTITION';
    filter_list(filter_list.last).partname := 'PART2023';
    
    -- 若需同时收集表级全局统计，取消下方注释
    -- filter_list.extend;
    -- filter_list(filter_list.last).ownname := 'USER01';
    -- filter_list(filter_list.last).objname := 'TABLEABC';
    -- filter_list(filter_list.last).objtype := 'TABLE';
    
    -- 显式指定收集分区统计（默认值为TRUE，可按需调整）
    dbms_stats.gather_database_stats(
        obj_filter_list => filter_list,
        gather_partition_stats => TRUE
    );
end;
/

3. 验证统计收集结果 #

执行代码后，通过以下查询验证分区统计是否更新：

SELECT table_name, partition_name, last_analyzed
FROM dba_tab_partitions
WHERE owner IN ('USER01', 'USER02')
AND table_name IN ('TABLEABC', 'TAB_MYTAB');

内容的提问来源于stack exchange，提问作者Jim