不同统计方式导致分区表的统计信息不同

最新推荐文章于 2020-12-18 10:21:24 发布
转载 最新推荐文章于 2020-12-18 10:21:24 发布 · 261 阅读 · 0
文章标签：

#数据库

本文探讨了在Oracle数据库中使用不同方法收集分区表统计信息的效果。通过对比analyze命令、dbms_ddl及dbms_stats包的功能，揭示了它们对dba_tables和分区表统计信息更新的不同影响。

问题的起因：最近发现一张分区表：从dba_table得到的统计信息跟dba_tab_stats/dba_tab_partitions查得的信息出入很大,后来查得的原因是: 脚本里调用dbms_ddl来收集统计信息，而dbms_ddl实际上是调用analyze命令，analyze基本上不会用来收集统计信息了。我们通常是调用dbms_stats来统计的。


下面是实验证明：num_rows和last_analyzed来自dba_tables/dba_tab_stats/dba_tab_partitions

[@more@]

SQL> create table test( a number, b varchar2(30)) partition by hash(a) partitions 8;
Table created.
SQL> insert into test select object_id,substr(object_name,1,30) from dba_objects;
43042 rows created.
SQL> select sysdate from dual;
SYSDATE
-------------------
2010-03-10 14:59:46
SQL> exec dbms_stats.gather_table_stats('SYS','TEST',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select owner,table_name,num_rows,last_analyzed from dba_tables where table_name='TEST' and owner='SYS';
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
SYS TEST 43042 2010-03-10 15:00:12
SQL> select owner,table_name,partition_name,last_analyzed,num_rows from dba_tab_statistics where table_name='TEST' and owner='SYS';

OWNER TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ------------------- ----------
SYS TEST 2010-03-10 15:00:12 43042
SYS TEST SYS_P101 2010-03-10 15:00:12 5496
SYS TEST SYS_P102 2010-03-10 15:00:12 5424
SYS TEST SYS_P103 2010-03-10 15:00:12 5427
SYS TEST SYS_P104 2010-03-10 15:00:12 5363
SYS TEST SYS_P105 2010-03-10 15:00:12 5324
SYS TEST SYS_P106 2010-03-10 15:00:12 5393
SYS TEST SYS_P107 2010-03-10 15:00:12 5351
SYS TEST SYS_P108 2010-03-10 15:00:12 5264
9 rows selected.
SQL> select table_owner,table_name,partition_name,last_analyzed,num_rows from dba_tab_partitions where table_name='TEST' and table_owner='SYS';

TABLE_OWNER TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ------------------- ----------
SYS TEST SYS_P101 2010-03-10 15:00:12 5496
SYS TEST SYS_P102 2010-03-10 15:00:12 5424
SYS TEST SYS_P103 2010-03-10 15:00:12 5427
SYS TEST SYS_P104 2010-03-10 15:00:12 5363
SYS TEST SYS_P105 2010-03-10 15:00:12 5324
SYS TEST SYS_P106 2010-03-10 15:00:12 5393
SYS TEST SYS_P107 2010-03-10 15:00:12 5351
SYS TEST SYS_P108 2010-03-10 15:00:12 5264
8 rows selected.
SQL> select sysdate from dual;
SYSDATE
-------------------
2010-03-10 15:00:48
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select owner,table_name,num_rows,last_analyzed from dba_tables where table_name='TEST' and owner='SYS';
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
SYS TEST 43042 2010-03-10 15:00:12

SQL> select owner,table_name,partition_name,last_analyzed,num_rows from dba_tab_statistics where table_name='TEST' and owner='SYS';
OWNER TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ------------------- ----------
SYS TEST 2010-03-10 15:00:12 43042
SYS TEST SYS_P101 2010-03-10 15:01:16 5496
SYS TEST SYS_P102 2010-03-10 15:01:16 5424
SYS TEST SYS_P103 2010-03-10 15:01:16 5427
SYS TEST SYS_P104 2010-03-10 15:01:16 5363
SYS TEST SYS_P105 2010-03-10 15:01:16 5324
SYS TEST SYS_P106 2010-03-10 15:01:16 5393
SYS TEST SYS_P107 2010-03-10 15:01:16 5351
SYS TEST SYS_P108 2010-03-10 15:01:16 5264
9 rows selected.
SQL> select table_owner,table_name,partition_name,last_analyzed,num_rows from dba_tab_partitions where table_name='TEST' and table_owner='SYS';

TABLE_OWNER TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ------------------- ----------
SYS TEST SYS_P101 2010-03-10 15:01:16 5496
SYS TEST SYS_P102 2010-03-10 15:01:16 5424
SYS TEST SYS_P103 2010-03-10 15:01:16 5427
SYS TEST SYS_P104 2010-03-10 15:01:16 5363
SYS TEST SYS_P105 2010-03-10 15:01:16 5324
SYS TEST SYS_P106 2010-03-10 15:01:16 5393
SYS TEST SYS_P107 2010-03-10 15:01:16 5351
SYS TEST SYS_P108 2010-03-10 15:01:16 5264
8 rows selected.

这里可以看到通过analyze来收集统计信息的话，dba_tables的信息是不是更新的，只会更新下面的分区表信息。

SQL> exec dbms_ddl.analyze_object('TABLE','SYS','TEST','COMPUTE');
PL/SQL procedure successfully completed.
SQL> select owner,table_name,num_rows,last_analyzed from dba_tables where table_name='TEST' and owner='SYS';
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
SYS TEST 43042 2010-03-10 15:00:12
SQL> select owner,table_name,partition_name,last_analyzed,num_rows from dba_tab_statistics where table_name='TEST' and owner='SYS';
OWNER TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ------------------- ----------
SYS TEST 2010-03-10 15:00:12 43042
SYS TEST SYS_P101 2010-03-10 15:03:39 5496
SYS TEST SYS_P102 2010-03-10 15:03:39 5424
SYS TEST SYS_P103 2010-03-10 15:03:39 5427
SYS TEST SYS_P104 2010-03-10 15:03:39 5363
SYS TEST SYS_P105 2010-03-10 15:03:39 5324
SYS TEST SYS_P106 2010-03-10 15:03:39 5393
SYS TEST SYS_P107 2010-03-10 15:03:39 5351
SYS TEST SYS_P108 2010-03-10 15:03:39 5264
9 rows selected.
SQL> select table_owner,table_name,partition_name,last_analyzed,num_rows from dba_tab_partitions where table_name='TEST' and table_owner='SYS';
TABLE_OWNER TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ------------------- ----------
SYS TEST SYS_P101 2010-03-10 15:03:39 5496
SYS TEST SYS_P102 2010-03-10 15:03:39 5424
SYS TEST SYS_P103 2010-03-10 15:03:39 5427
SYS TEST SYS_P104 2010-03-10 15:03:39 5363
SYS TEST SYS_P105 2010-03-10 15:03:39 5324
SYS TEST SYS_P106 2010-03-10 15:03:39 5393
SYS TEST SYS_P107 2010-03-10 15:03:39 5351
SYS TEST SYS_P108 2010-03-10 15:03:39 5264
8 rows selected.

这里也可以看到调用dbms_ddl.analyze_object也只会更新分区表里的统计信息，而不会更新dba_tables的信息。

SQL> exec dbms_stats.gather_table_stats('SYS','TEST',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select owner,table_name,num_rows,last_analyzed from dba_tables where table_name='TEST' and owner='SYS';
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
SYS TEST 43042 2010-03-10 15:07:17
SQL> select owner,table_name,partition_name,last_analyzed,num_rows from dba_tab_statistics where table_name='TEST' and owner='SYS';
OWNER TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ------------------- ----------
SYS TEST 2010-03-10 15:07:17 43042
SYS TEST SYS_P101 2010-03-10 15:07:17 5496
SYS TEST SYS_P102 2010-03-10 15:07:17 5424
SYS TEST SYS_P103 2010-03-10 15:07:17 5427
SYS TEST SYS_P104 2010-03-10 15:07:17 5363
SYS TEST SYS_P105 2010-03-10 15:07:17 5324
SYS TEST SYS_P106 2010-03-10 15:07:17 5393
SYS TEST SYS_P107 2010-03-10 15:07:17 5351
SYS TEST SYS_P108 2010-03-10 15:07:17 5264
9 rows selected.
SQL> select table_owner,table_name,partition_name,last_analyzed,num_rows from dba_tab_partitions where table_name='TEST' and table_owner='SYS';

TABLE_OWNER TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ------------------- ----------
SYS TEST SYS_P101 2010-03-10 15:07:17 5496
SYS TEST SYS_P102 2010-03-10 15:07:17 5424
SYS TEST SYS_P103 2010-03-10 15:07:17 5427
SYS TEST SYS_P104 2010-03-10 15:07:17 5363
SYS TEST SYS_P105 2010-03-10 15:07:17 5324
SYS TEST SYS_P106 2010-03-10 15:07:17 5393
SYS TEST SYS_P107 2010-03-10 15:07:17 5351
SYS TEST SYS_P108 2010-03-10 15:07:17 5264
8 rows selected.

只有dbms_stats在更新分区表统计信息的同时，更新dba_tables.

SQL> insert into test select object_id,substr(object_name,1,30) from dba_objects;
43042 rows created.
SQL> exec dbms_ddl.analyze_object('TABLE','SYS','TEST','COMPUTE');
PL/SQL procedure successfully completed.
SQL> select owner,table_name,num_rows,last_analyzed from dba_tables where table_name='TEST' and owner='SYS';

OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
SYS TEST 43042 2010-03-10 15:07:17
SQL> select owner,table_name,partition_name,last_analyzed,num_rows from dba_tab_statistics where table_name='TEST' and owner='SYS';
OWNER TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ------------------- ----------
SYS TEST 2010-03-10 15:07:17 43042
SYS TEST SYS_P101 2010-03-10 15:08:54 10992
SYS TEST SYS_P102 2010-03-10 15:08:54 10848
SYS TEST SYS_P103 2010-03-10 15:08:54 10854
SYS TEST SYS_P104 2010-03-10 15:08:54 10726
SYS TEST SYS_P105 2010-03-10 15:08:54 10648
SYS TEST SYS_P106 2010-03-10 15:08:54 10786
SYS TEST SYS_P107 2010-03-10 15:08:54 10702
SYS TEST SYS_P108 2010-03-10 15:08:55 10528
9 rows selected.
SQL> select table_owner,table_name,partition_name,last_analyzed,num_rows from dba_tab_partitions where table_name='TEST' and table_owner='SYS';
TABLE_OWNER TABLE_NAME PARTITION_NAME LAST_ANALYZED NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ------------------- ----------
SYS TEST SYS_P101 2010-03-10 15:08:54 10992
SYS TEST SYS_P102 2010-03-10 15:08:54 10848
SYS TEST SYS_P103 2010-03-10 15:08:54 10854
SYS TEST SYS_P104 2010-03-10 15:08:54 10726
SYS TEST SYS_P105 2010-03-10 15:08:54 10648
SYS TEST SYS_P106 2010-03-10 15:08:54 10786
SYS TEST SYS_P107 2010-03-10 15:08:54 10702
SYS TEST SYS_P108 2010-03-10 15:08:55 10528
8 rows selected.

即使数据量出现大的变动，analyze也不会反映到dba_tables,这就再次说明用analyze来收集统计信息是存在问题的，正当途径还是用dbms_stats来收集。这个脚本也不知是哪位大佬写的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/45188/viewspace-1031729/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/45188/viewspace-1031729/

数据库统计信息过期导致执行计划错误的大数据解决方案
百态老人的博客
05-16 1142
统计信息是数据库优化器生成高效执行计划的核心依据，主要包含列值分布、数据密度、索引基数等元数据。当数据分布发生显著变化（如大规模DML操作或ETL过程）而统计信息未及时更新时，优化器会基于过时信息做出错误决策。典型场景影响：索引误判：中Oracle案例显示，当某字段重复率从0.1%暴增至50%时，优化器仍选择索引扫描而非全表扫描，导致I/O负载激增。连接顺序错误：多表关联时错误估算中间结果集大小，导致嵌套循环替代哈希连接。资源浪费：显示表扫描成本被高估100%，引发不必要的磁盘I/O和CPU消耗。
架构师之路--达梦数据库分区表详解及使用方式
最新发布
shine_du的博客
12-27 1309
分区表是将一个逻辑表按照一定的规则（如范围、列表、哈希等）划分为多个物理子表（分区）的表。这些分区在物理存储上是相互独立的，但在逻辑上仍然被视为一个整体表。例如，对于一个存储销售订单数据的表，可以按照订单日期将其划分为不同的分区，每个分区包含特定时间段内的订单数据。
ArcGIS教程：以表格显示分区几何统计
03-02
为数据集中的各个区域计算几何测量值（面积、周长、厚度和椭圆的特征值）并以表的形式来显示结果。
11gr2中DBA_TAB_MODIFICATIONS视图返回结果异常
ckawt40802的专栏
05-26 202
今天在PUB上看到了一个帖子，查询DBA_TAB_MODIFICATIONS视图的时可能会丢失记录，原贴参考：http://www.itpub.net/thread-1306609-1-2.html 简单测试了...
SQL SERVER 分区表的总结--一些疑问的总结
baicongli9053的博客
12-02 215
在使用的分区表中，遇到一些问题，也想到一些问题。就一起总结起来。 1. 像主表--从表，这种结构才应用同样的分区架构和分区函数，如订单主表--订单详细表。 这样可以存储对齐，于IO和联接查询性能都有提升。 但是如果两张不相关的表，最好不要用同样分区架构和分区函数。因为在对其中一张表做分区结构调整时，会同样作用到另一张表。 2. 每个分区对应一个不同的文件组，并置于...
ORACLE统计信息
清水雨潭
01-08 3626
1. 什么是统计信息 统计信息主要是描述数据库中表，索引的大小，规模，数据分布状况等的一类信息。比如，表的行数，块数，平均每行的大小，索引的leaf blocks，索引字段的行数，不同值的大小等，都属于统计信息。CBO正是根据这些统计信息数据，计算出不同访问路径下，不同join 方式下，各种计划的成本，最后选择出成本最小的计划。 统计信息是存放在数据字段表中的，如tab$。一般我们从数据字段视图中
oracle-学习笔记-5
勐萌博文
12-17 230
接 oracle-学习笔记-4 -------------------------------------     16.Oracle 小问题 Oracle Package有哪些作用呢？ 简化应用设计、提高应用性能、实现信息隐藏、子程序重载。  ORACLE中的function   、package、package   bodies、procedure的有什么区别和相同的地方？ ...
oracle 表信息统计+表信息分析
10-11
通过上述两种方式，Oracle数据库管理工具提供了灵活而强大的方法来收集和分析表统计信息，从而优化数据库性能。数据库管理员可以根据实际的数据使用模式和业务需求，选择适当的时间和方法来执行统计信息的收集和分析...
Oracle甲骨文原厂分区表最佳实践
05-08
分区表可以根据不同的策略进行设计，例如范围分区、散列分区、列表分区和复合分区等，每种方式都有其特定的应用场景和优势。 ### 分区指导 **选择分区键**：选择合适的分区键至关重要，它应具有高度的区分度，以...
【MySQL进阶学习】优化索引与分区表
12-14
【MySQL进阶学习】优化索引与分区表 在MySQL数据库管理中，索引和分区是两个关键的优化策略，用于提升查询性能和管理大规模数据。这篇文章将深入探讨这两个主题，帮助你理解如何有效地利用它们。 优化索引是提高...
oracle分区表常用统计信息
hayata的专栏
12-27 4735
---显示数据库所有分区表的信息：DBA_PART_TABLES ---显示表分区信息 显示数据库所有分区表的详细分区信息：DBA_TAB_PARTITIONS ---显示子分区信息 显示数据库所有组合分区表的子分区信息：DBA_TAB_SUBPARTITIONS ---显示分区列 显示数据库所有分区表的分区列信息：DBA_PART_KEY_C
DBA_Oracle Table Partition表分区概念汇总（概念）
weixin_33734785的博客
11-16 452
2014-06-20 Created By BaoXinjian 一、摘要 有关表分区的一些维护性操作： 注：分区根据具体情况选择。 表分区有以下优点： 1、数据查询：数据被存储到多个文件上，减少了I/O负载，查询速度提高。 2、数据修剪：保存历史数据非常的理想。 3、备份：将大表的数据分成多个文件，方便备份和恢复。 4、并行性：可以同时向表中进行DML操作，并行性性能提高。 ...
oracle 删除大量数据后整理表（analyze table xxx compute statistics）
写博客只为学习
04-11 2万+
1. DELETE 后 TRUNCATE TABLE ; 然后重新分析一下 analyze table tablename compute statistics 查看表信息 select NUM_ROWS,BLOCKS,AVG_SPACE,AVG_ROW_LEN from user_tables where table_name=table_name; 2.Oracle 表删除大量数据后，
arcgis中分区统计中常见的错误
热门推荐
qq_34950486的博客
06-12 2万+
在arcgis分区统计中，会出现什么不存在表名99999错误问题。有几点注意事项： 1.区域字段选择FID 2.输入赋值栅格的类型其实无论是整型或者小数栅格都可以的，与这个无关 3.如果分区统计不以表格显示的话，则命名没有要求。如果以表格显示再连接到shp中的话，注意表名一定不要以数字开头，以数字开头就会出现错误。 ...
分区表的统计信息收集策略
weixin_34029949的博客
06-17 882
#####1 如果每天产生一个分区， 1.10g库如果是一个日分区表，每天产生20到30万 笔数据，可以考虑采用分区复制的方式来缓解10g 晚上22点的统计信息造成的I/O 高峰期的 分区复制方法参考（https://orahow.com/how-to-gather-statistics-on-large-partitioned-tables-in-oracle/） 2.1...
oracle统计信息不准确导致sql执行慢的解决步骤
weixin_41597351的博客
12-18 2004
1.查询统计信息系统表，检查统计信息收集是否被锁定，Stattype_Locked是all表示被锁定，需要解锁。 Select * From All_Tab_Statistics t Where t.Owner = '用户名' And t.Stattype_Locked = 'ALL'; 2.解锁统计信息: begin dbms_stats.unlock_table_stats(ownname => '用户名',tabname => '表名'); end; 3.先收集统计信息后清
Oracle常用
Behind You！
02-22 375
++++++++++++++++++++++++++++++++++++++++++++++++ 表备份： 1.create tablename_bak_date as select * from tablename;         --create .. as select * from .. 2.select * into tablename_bak_date from tab
基于Oracle分区表的百万级政府数据统计优化
描述中明确指出，使用Oracle分区表进行统计操作时性能表现极为出色，说明分区表已成为应对大数据量查询与分析的关键技术支撑。结合标签内容可知，整个技术体系涵盖了Oracle数据库管理中的多个关键领域，包括但不限于...
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值