ORACLE分区表统计信息收集的正确姿势

经常碰到一些分区表里的分区统计信息num_rows=0,从而导致执行计划不准，特别是笛卡尔积。这个主要是灌入了数据，未及时更新分区统计信息。

另外，没有数据的分区，不要收集统计信息，也就不会导致num_rows=0的问题，就算有数据，未收集分区统计信息，内部也会采样，比较准确。

为什么导致num_rows=0,这个主要和收集统计信息的语句有关：
1）一般的收集语句，没有指定pname,会导致num_rows=0问题
exec dbms_stats.gather_table_stats(ownname => user,tabname=>'part_tab',no_invalidate => false);

2)指定pname,会收集指定分区统计信息，也会更新全局表统计信息，不会有num_rows=0问题， 未收集的分区如果有数据，内部会采样，比较准确，比num_rows好。
exec dbms_stats.delete_table_stats(ownname => user,tabname => 'part_tab',partname => 'p202402',no_invalidate => false);


另外12c还有分区表增量统计信息收集。


下面就研究下分区表上述收集统计信息不同之处。  


建表语句如下：

create table part_tab
(
id number,
insert_date date
)
partition by range(insert_date)
(
partition p202401 values less than (to_date('2024-2-1','yyyy-mm-dd')),
partition p202402 values less than (to_date('2024-3-1','yyyy-mm-dd')),
partition p202403 values less than (to_date('2024-4-1','yyyy-mm-dd')),
partition p202404 values less than (to_date('2024-5-1','yyyy-mm-dd')),
partition p202405 values less than (to_date('2024-6-1','yyyy-mm-dd'))
);


insert into part_tab
select level,date'2024-1-1'+ dbms_random.value(1,100)
from dual
connect by level < 10000;
commit;

目前表和分区统计信息都为空：

select table_name,partition_name,num_rows,sample_size,last_analyzed
from dba_tab_statistics
where table_name='PART_TAB';
TABLE_NAME    PARTITION_NAME    NUM_ROWS    SAMPLE_SIZE    LAST_ANALYZED
PART_TAB
PART_TAB    P202403
PART_TAB    P202402
PART_TAB    P202404
PART_TAB    P202401
PART_TAB    P202405

数据分布如下，P202405分区无数据：

select trunc(insert_date,'mm'),count(*) from part_tab
group by trunc(insert_date,'mm');

TRUNC(INSERT_DATE,'MM')    COUNT(*)
2024/1/1    2930
2024/2/1    2932
2024/3/1    3237
2024/4/1    900

一般的统计信息收集数据，对于分区表，没有指定pname,则可能有问题，没有数据的num_rows=0：

exec dbms_stats.gather_table_stats(ownname => user,tabname=>'part_tab',no_invalidate => false);

采用上面的收集，所有分区不管有没有数据都收集，这样P202405收集的num_rows=0,如果下次这个分区灌入了数据，不及时收集，则统计信息不准。

TABLE_NAME    PARTITION_NAME    NUM_ROWS    SAMPLE_SIZE    LAST_ANALYZED
PART_TAB    P202401    2930    2930    2024/5/8 16:17:42
PART_TAB    P202402    2932    2932    2024/5/8 16:17:42
PART_TAB    P202403    3237    3237    2024/5/8 16:17:42
PART_TAB    P202404    900    900    2024/5/8 16:17:42
PART_TAB    P202405    0        2024/5/8 16:17:42
PART_TAB        9999    9999    2024/5/8 16:17:42

删除2月统计信息，看查询是否能正确估算P202402的行数：

dingjun123@ORCLPDB> exec dbms_stats.delete_table_stats(ownname => user,tabname => 'part_tab',partname => 'p202402',no_invalidate => false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.67

TABLE_NAME    PARTITION_NAME    NUM_ROWS    SAMPLE_SIZE    LAST_ANALYZED
PART_TAB    P202401    2930    2930    2024/5/8 16:17:42
PART_TAB    P202402
PART_TAB    P202403    3237    3237    2024/5/8 16:17:42
PART_TAB    P202404    900    900    2024/5/8 16:17:42
PART_TAB    P202405    0        2024/5/8 16:17:42
PART_TAB        9999    9999    2024/5/8 16:17:42

可以看到，2月统计信息删除后，优化器内部还是会估算的比较准确(这种可能使用分区级动态采样，只不过执行计划没有显示），rows=2727行，真实的是2932行。

dingjun123@ORCLPDB> set line 200 pagesize 999
dingjun123@ORCLPDB> set autotrace traceonly exp
dingjun123@ORCLPDB> select * from part_tab where insert_date > date'2024-2-1' and insert_date<date'2024-2-28';
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3614281979

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2727 | 32724 | 215 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 2727 | 32724 | 215 (0)| 00:00:01 | 2 | 2 |
|* 2 | TABLE ACCESS FULL | PART_TAB | 2727 | 32724 | 215 (0)| 00:00:01 | 2 | 2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("INSERT_DATE"<TO_DATE(' 2024-02-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "INSERT_DATE">TO_DATE(' 2024-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

分区表统计信息收集的正确姿势：

使用指定pname收集分区统计信息，则也会更新全局表统计信息，这个全局表统计信息是估算所有有数据的分区，不仅仅是pname指定的分区。
而且未收集的分区表统计信息不收集，避免了num_rows=0问题。

清除统计信息

exec dbms_stats.delete_table_stats(ownname => user,tabname => 'part_tab',partname => 'p202402',no_invalidate => false);

下面收集p202401统计信息

exec dbms_stats.gather_table_stats(ownname => user,tabname=>'part_tab',partname => 'p202401',no_invalidate => false);

收集完后更新了P202401分区统计信息，而且全局统计信息也更新为9999行，全局的统计信息包括了未收集分区但是有数据的分区统计信息：

select table_name,partition_name,num_rows,sample_size,last_analyzed
from dba_tab_statistics
where table_name='PART_TAB'
order by partition_name;
TABLE_NAME    PARTITION_NAME    NUM_ROWS    SAMPLE_SIZE    LAST_ANALYZED
PART_TAB    P202401    2930    2930    2024/5/8 16:34:46
PART_TAB    P202402
PART_TAB    P202403
PART_TAB    P202404
PART_TAB    P202405
PART_TAB        9999    9999    2024/5/8 16:34:46

p202402分区没有收集统计信息，也能比较准确估算行数。估算为2730行：

select * from part_tab where insert_date > date'2024-2-1' and insert_date<date'2024-2-28';
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3614281979

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2730 | 32760 | 275 (1)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 2730 | 32760 | 275 (1)| 00:00:01 | 2 | 2 |
|* 2 | TABLE ACCESS FULL | PART_TAB | 2730 | 32760 | 275 (1)| 00:00:01 | 2 | 2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("INSERT_DATE"<TO_DATE(' 2024-02-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "INSERT_DATE">TO_DATE(' 2024-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

p202405分区没有数据，也能正确估算行数，rows=1:  

select * from part_tab where insert_date > date'2024-5-1' and insert_date<date'2024-5-31';
Execution Plan
----------------------------------------------------------
Plan hash value: 3614281979

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 172 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 12 | 172 (0)| 00:00:01 | 5 | 5 |
|* 2 | TABLE ACCESS FULL | PART_TAB | 1 | 12 | 172 (0)| 00:00:01 | 5 | 5 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("INSERT_DATE">TO_DATE(' 2024-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "INSERT_DATE"<TO_DATE(' 2024-05-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

总结：                   
对于分区表统计信息收集，{BANNED}最佳好指定pname,这样可以更新分区和全局表统计信息，对于未指定的分区（未灌入数据）则不会收集，从而避免了分区num_rows=0的问题，就算灌入了数据未及时收集，优化器也会内部采样，这样避免了统计信息不准导致执行计划的问题。