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

凯发app官方网站-凯发k8官网下载客户端中心 | | 凯发app官方网站-凯发k8官网下载客户端中心
  • 博客访问: 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

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. );


  14. insert into part_tab
  15. select level,date'2024-1-1' dbms_random.value(1,100)
  16. from dual
  17. connect by level < 10000;
  18. 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');

  3. trunc(insert_date,'mm')    count(*)
  4. 2024/1/1    2930
  5. 2024/2/1    2932
  6. 2024/3/1    3237
  7. 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);

  2. pl/sql procedure successfully completed.

  3. elapsed: 00:00:00.67

  4. table_name    partition_name    num_rows    sample_size    last_analyzed
  5. part_tab    p202401    2930    2930    2024/5/8 16:17:42
  6. part_tab    p202402            
  7. part_tab    p202403    3237    3237    2024/5/8 16:17:42
  8. part_tab    p202404    900    900    2024/5/8 16:17:42
  9. part_tab    p202405    0        2024/5/8 16:17:42
  10. 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

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

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

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

  17.    2 - filter("insert_date"<to_date(' 2024-02-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss') and
  18.               "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

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

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

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

  15.    2 - filter("insert_date"<to_date(' 2024-02-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss') and
  16.               "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

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

  12. predicate information (identified by operation id):
  13. ---------------------------------------------------

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

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






阅读(2771) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
")); function link(t){ var href= $(t).attr('href'); href ="?url=" encodeuricomponent(location.href); $(t).attr('href',href); //setcookie("returnouturl", location.href, 60, "/"); }
网站地图