oracle分区表统计信息收集的正确姿势-凯发app官方网站

凯发app官方网站-凯发k8官网下载客户端中心 | | 凯发app官方网站-凯发k8官网下载客户端中心

dingjun123的blog

凯发app官方网站首页　| 　博文目录　| 　关于我

dingjun123

  • 博客访问： 1156148
  • 博文数量： 166
  • 博客积分： 0
  • 博客等级： 民兵
  • 技术积分： 3760
  • 用 户 组： 普通用户
  • 注册时间： 2011-03-30 13:00
个人简介

about me:oracle ace pro,optimistic,passionate and harmonious. focus on oracle,mysql and other database programming,peformance tuning,db design, j2ee,linux/aix,architecture tech,etc

文章分类

全部博文（166）

文章存档

2024年（21）

2023年（28）

2022年（43）

2020年（62）

2014年（3）

2013年（9）

我的朋友
最近访客
推荐博文
相关博文
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
oracle分区表统计信息收集的正确姿势

分类： oracle

2024-05-08 17:07:57

经常碰到一些分区表里的分区统计信息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还有分区表增量统计信息收集。


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


建表语句如下：

点击(此处)折叠或打开

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


  16. insert into part_tab
  17. select level,date'2024-1-1' dbms_random.value(1,100)
  18. from dual
  19. connect by level < 10000;
  20. commit;
目前表和分区统计信息都为空：

点击(此处)折叠或打开

  1. select table_name,partition_name,num_rows,sample_size,last_analyzed
  2. from dba_tab_statistics
  3. where table_name='part_tab';
  4. table_name    partition_name    num_rows    sample_size    last_analyzed
  5. part_tab                
  6. part_tab    p202403            
  7. part_tab    p202402            
  8. part_tab    p202404            
  9. part_tab    p202401            
  10. part_tab    p202405

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

点击(此处)折叠或打开

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

  4. trunc(insert_date,'mm')    count(*)
  5. 2024/1/1    2930
  6. 2024/2/1    2932
  7. 2024/3/1    3237
  8. 2024/4/1    900
一般的统计信息收集数据，对于分区表，没有指定pname,则可能有问题，没有数据的num_rows=0：

点击(此处)折叠或打开

  1. exec dbms_stats.gather_table_stats(ownname => user,tabname=>'part_tab',no_invalidate => false);
采用上面的收集，所有分区不管有没有数据都收集，这样p202405收集的num_rows=0,如果下次这个分区灌入了数据，不及时收集，则统计信息不准。

点击(此处)折叠或打开

  1. table_name    partition_name    num_rows    sample_size    last_analyzed
  2. part_tab    p202401    2930    2930    2024/5/8 16:17:42
  3. part_tab    p202402    2932    2932    2024/5/8 16:17:42
  4. part_tab    p202403    3237    3237    2024/5/8 16:17:42
  5. part_tab    p202404    900    900    2024/5/8 16:17:42
  6. part_tab    p202405    0        2024/5/8 16:17:42
  7. part_tab        9999    9999    2024/5/8 16:17:42


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

点击(此处)折叠或打开

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

  3. pl/sql procedure successfully completed.

  5. elapsed: 00:00:00.67

  7. table_name    partition_name    num_rows    sample_size    last_analyzed
  8. part_tab    p202401    2930    2930    2024/5/8 16:17:42
  9. part_tab    p202402            
  10. part_tab    p202403    3237    3237    2024/5/8 16:17:42
  11. part_tab    p202404    900    900    2024/5/8 16:17:42
  12. part_tab    p202405    0        2024/5/8 16:17:42
  13. part_tab        9999    9999    2024/5/8 16:17:42
可以看到，2月统计信息删除后，优化器内部还是会估算的比较准确(这种可能使用分区级动态采样，只不过执行计划没有显示），rows=2727行，真实的是2932行。

点击(此处)折叠或打开

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

  6. execution plan
  7. ----------------------------------------------------------
  8. plan hash value: 3614281979

  10. ---------------------------------------------------------------------------------------------------
  11. | id | operation | name | rows | bytes | cost (%cpu)| time | pstart| pstop |
  12. ---------------------------------------------------------------------------------------------------
  13. | 0 | select statement | | 2727 | 32724 | 215 (0)| 00:00:01 | | |
  14. | 1 | partition range single| | 2727 | 32724 | 215 (0)| 00:00:01 | 2 | 2 |
  15. |* 2 | table access full | part_tab | 2727 | 32724 | 215 (0)| 00:00:01 | 2 | 2 |
  16. ---------------------------------------------------------------------------------------------------

  18. predicate information (identified by operation id):
  19. ---------------------------------------------------

  21.    2 - filter("insert_date"<to_date(' 2024-02-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss') and
  22.               "insert_date">to_date(' 2024-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

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

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

清除统计信息

点击(此处)折叠或打开

  1. exec dbms_stats.delete_table_stats(ownname => user,tabname => 'part_tab',partname => 'p202402',no_invalidate => false);
下面收集p202401统计信息

点击(此处)折叠或打开

  1. exec dbms_stats.gather_table_stats(ownname => user,tabname=>'part_tab',partname => 'p202401',no_invalidate => false);
收集完后更新了p202401分区统计信息，而且全局统计信息也更新为9999行，全局的统计信息包括了未收集分区但是有数据的分区统计信息：

点击(此处)折叠或打开

  1. select table_name,partition_name,num_rows,sample_size,last_analyzed
  2. from dba_tab_statistics
  3. where table_name='part_tab'
  4. order by partition_name;
  5. table_name    partition_name    num_rows    sample_size    last_analyzed
  6. part_tab    p202401    2930    2930    2024/5/8 16:34:46
  7. part_tab    p202402            
  8. part_tab    p202403            
  9. part_tab    p202404            
  10. part_tab    p202405            
  11. part_tab        9999    9999    2024/5/8 16:34:46
p202402分区没有收集统计信息，也能比较准确估算行数。估算为2730行：

点击(此处)折叠或打开

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

  4. execution plan
  5. ----------------------------------------------------------
  6. plan hash value: 3614281979

  8. ---------------------------------------------------------------------------------------------------
  9. | id | operation | name | rows | bytes | cost (%cpu)| time | pstart| pstop |
  10. ---------------------------------------------------------------------------------------------------
  11. | 0 | select statement | | 2730 | 32760 | 275 (1)| 00:00:01 | | |
  12. | 1 | partition range single| | 2730 | 32760 | 275 (1)| 00:00:01 | 2 | 2 |
  13. |* 2 | table access full | part_tab | 2730 | 32760 | 275 (1)| 00:00:01 | 2 | 2 |
  14. ---------------------------------------------------------------------------------------------------

  16. predicate information (identified by operation id):
  17. ---------------------------------------------------

  19.    2 - filter("insert_date"<to_date(' 2024-02-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss') and
  20.               "insert_date">to_date(' 2024-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

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

点击(此处)折叠或打开

  1. select * from part_tab where insert_date > date'2024-5-1' and insert_date<date'2024-5-31';
  2. execution plan
  3. ----------------------------------------------------------
  4. plan hash value: 3614281979

  6. ---------------------------------------------------------------------------------------------------
  7. | id | operation | name | rows | bytes | cost (%cpu)| time | pstart| pstop |
  8. ---------------------------------------------------------------------------------------------------
  9. | 0 | select statement | | 1 | 12 | 172 (0)| 00:00:01 | | |
  10. | 1 | partition range single| | 1 | 12 | 172 (0)| 00:00:01 | 5 | 5 |
  11. |* 2 | table access full | part_tab | 1 | 12 | 172 (0)| 00:00:01 | 5 | 5 |
  12. ---------------------------------------------------------------------------------------------------

  14. predicate information (identified by operation id):
  15. ---------------------------------------------------

  17.    2 - filter("insert_date">to_date(' 2024-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') and
  18.               "insert_date"<to_date(' 2024-05-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

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






阅读(2771) | 评论(0) | 转发(0) |
0

上一篇：oracle组合索引选择率计算及如何选择组合索引的列顺序_part1

下一篇：oracle组合索引选择率计算及如何选择组合索引的列顺序_part2

给主人留下些什么吧！~~
| | | | |

凯发app官方网站 copyright 2001-2010 chinaunix.net all rights reserved 北京皓辰网域网络信息技术有限公司. 凯发k8官网下载客户端中心的版权所有

感谢所有关心和支持过chinaunix的朋友们
")); function link(t){ var href= $(t).attr('href'); href ="?url=" encodeuricomponent(location.href); $(t).attr('href',href); //setcookie("returnouturl", location.href, 60, "/"); }
网站地图