40.Oracle的统计信息

1.什么是统计信息?

  oracle数据库中的统计信息存储在数据字典中,从多个维度描述了oracle数据库里的详细信息

2.统计信息作用

  oracle的 CBO优化器会利用统计信息计算目标SQL各种可能、不同执行路径的成本,并从中选择一条最小的路径来作为目标sql的执行计划。(如果统计信息不准确,sql的执行计划会走错,sql会出现性能问题)

3.统计信息的分类

  3.1 表统计信息:主要包含表的总行数(num_rows),表块数(blocks) 以及平均长度(avg_row_len)

  3.2 索引的统计信息:索引的统计信息描述了索引的详细信息,所以索引的层级、叶子块的数量、聚簇因子等

  3.3 列的统计信息:列的统计记录了列的distinct值的数量、null的数量、列的最小值和列的最大值

  3.4 系统统计信息:系统统计信息是描述了oracle数据库服务器系统的处理能力,包含cpu和I/O两个方面,可以通过这两个方面来知道数据库服务器的实际处理能力

  3.5 数据字典统计信息:描述了字典基表(tab, i n d ,ind,ind等),数据字典基表上的索引

  3.6 内部对象统计信息:记录了一些内部表(x系 统 表 ) 的 详 细 信 息 , 它 的 维 度 和 普 通 表 的 统 计 信 息 类 似 , 但 是 其 表 块 数 为 0 , x 系统表)的详细信息,它的维度和普通表的统计信息类似,但是其表块数为0,x系统表)的详细信息,它的维度和普通表的统计信息类似,但是其表块数为0,x实际上只是oracle自定义的内存结构,不占用实际物理空间

4.统计信息收集的方式:两种

  • analyze(不推荐)
  • dbms_stats(推荐)  

5. analyze命令收集

  oracle7开始,通过analyze命令来收集表、索引、列的统计信息。以下是一些典型的用法

  5.1 根据采用比为15%,对test表搜集统计信息  

analyze table test estimate statistics smaple 15 percent for table;

  5.2 计算模式:对test表收集统计信息,只有对test表有统计信息,test的列和索引都没有统计信息,且收集的统计信息和实际情况一致的

analyze table test compute statistics for table;

   5.3 计算模式: 对test表的列1和列2收集统计信息 

analyze table test compute statistics for cloumns 列1,列2;

   5.4 收集所以的统计信息

analyze index idx_1 statistics;

  5.5  删除统计信息

analyze table test delete statistics;

  5.6 删除test表、列、所有索引的统计信息

analyze index idx_1 delete statistics;

6. dbms_stats包收集统计信息

  oracle8.1.5开始,dbms_stats被广泛应用于统计信息的收集,也是oracle官方推荐的方式

  dbms_stats有4个存储过程

  6.1 gather_table_stats:用于收集目标表、列和索引的统计信息 (示例:收集test表的统计信息)

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                tabname          => 'TEST',
                                estimate_percent => 15, --采样比是15%
                                method_opt       => 'for table',
                                cascade          => false);  --默认是true,级联收集
END;
/

  6.2 gather_index_stats:用于收集指定统计信息 (示例:收集索引idx_text的统计信息)

BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(ownname          => 'SCOTT',
                                indname          => 'IDX_TEST',
                                estimate_percent => 100,
                                );
END;
/

  6.3 gather_schema_stats:用于收集指定schema下的所有对象统计信息(示例:收集scott用户下的所有对象)

BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(ownname          => 'SCOTT',
                                indname          => 'IDX_TEST',
                                estimate_percent => 100,
                                );
END;
/

  6.4 gather_database_stats:用于收集全库所有的统计信息 (收集全库的统计信息)

BEGIN
  DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent=>100,
                                   degree=>8,
                                   cascade=>true,
                                   granularity=>'ALL', --收集分区表
                                );
END;
/

7.DBMS_stats重要参数详解

  • ownname:表示表的拥有者,不区分大小写
  • tabname:表示表名字,不区分大小写。
  • granularity:表示收集统计信息的粒度,该选项只对分区表生效,默认为 AUTO,表示让Oracle根据表的分区类型自己判断如何收集分区表的统计信息。对于该选项,我们一般采用AUTO 方式,也就是数据库默认方式,因此在后面的脚本中,省略该选项
  • estimate_percent“”表示采样率,范围是0.000 001~100
这个参数主要是用于CBO估算表的总行数,采样率越高,CBO估算的表行数越接近于真实值,执行计划越能走正确。
估算总行数=样本大小(DBA_TAB_STATISTICS.SAMPLE_SIZE)*100/采样率(estimate_percent)
这个参数可能对于很多新手来说都不知道怎么设置:
一般对小于 1GB 的表进行100%采样,因为表很小,即使100%采样速度也比较快。有时候小表有可能数据分布不均衡,如果没有100%采样,可能会导致统计信息不准。
因此建议对小表 100%采样。我们一般对表大小在1GB~5GB 的表采样50%,对大于5GB的表采样30%。如果表特别大,有几十甚至上百 GB,我们建议应该先对表进行分区,然后分别对每个分区收集统计信息。一般情况下,为了确保统计信息比较准确,我们建议采样率不要低于30%<1GB 建议采样比100%
1GB~5GB 建议采样比50%
>5GB 建议采样比30%
  • method_opt:用于控制收集直方图策略
直方图简单来说就是数据库了解表中某列的数据分布,从而更正确的走更优的执行计划
method_opt =>for all columns size 1’ 表示所有列都不收集直方图
method_opt =>for all columns size skewonly’ 表示对表中所有列收集自动判断是否收集直方图。选择率非常高的列和null的列不会收集(谨慎使用)
method_opt =>for all columns size auto’ 表示对出现在 where 条件中的列自动判断是否收集直方图。
method_opt =>for all columns size repeat’ 表示当前有哪些列收集了直方图,现在就对哪些列收集直方图。
在实际工作中,当系统趋于稳定之后,使用 REPEAT 方式收集直方图。
  • no_invalidate :表示共享池中涉及到该表的游标是否立即失效,默认值为 DBMS_STATS.AUTO_INVALIDATE,表示让 Oracle 自己决定是否立即失效。建议将 no_invalidate 参数设置为 FALSE,立即失效。因为发现有时候 SQL 执行缓慢是因为统计信息过期导致,重新收集了统计信息之后执行计划还是没有更改,原因就在于没有将这个参数设置为 false
  • degree: 表示收集统计信息的并行度,默认为 NULL。如果表没有设置 degree。如果表没有设置 degree,收集统计信息的时候后就不开并行;如果表设置了 degree,收集统计信息的时候就按照表的 degree 来开并行。可以查询 DBA_TABLES.degree 来查看表的 degree,一般情况下,表的 degree 都为 1。我们建议可以根据当时系统的负载、系统中 CPU 的个数以及表大小来综合判断设置并行度。
  • cascade :表示在收集表的统计信息的时候,是否级联收集索引的统计信息,默认值为DBMS_STATS.AUTO_CASCADE,表示让 Oracle 自己判断是否级联收集索引的统计信息。

8.analyze和dbms_stats的区别

  • analyze命令不能正确的手机分区表的统计信息,而dbms_stats包却可以。
  • analyze命令不能并行收集统计信息,而dbms_stats包可以。
  • analyze命令不能收集x$的统计信息,所以选择推荐使用dbms_stats来对表进行统计信息收集

9.收集统计信息脚本(示例)

复制代码
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                tabname          => 'TEST',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size repeat',
                                no_invalidate    => FALSE,
                                degree           => 8,
                                granularity      => 'ALL',
                                cascade          => TRUE);
END;
/
复制代码

10.补充

唯一需要注意选择的地方是:
estimate_percent :
  <1GB 建议采样比100%
  1GB~5GB 建议采样比50%
  >5GB 建议采样比30%
(朋友们可以自己尝试用存储过程去判断表大小,自定义收集统计信息脚本)
degree :
  根据服务器的资源,和业务负载来指定
method_opt :
  系统刚上线使用auto,业务系统稳定后使用repeat。

  

这边再补充一篇别人的博客:

https://mp.weixin.qq.com/s/32rHGDa1WSpp7rIVn3qC7A  

  

posted on   太白金星有点烦  阅读(345)  评论(0编辑  收藏  举报

相关博文:
阅读排行:
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库

导航

< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8
点击右上角即可分享
微信分享提示