oracle统计信息概要

最新推荐文章于 2024-08-01 23:39:22 发布

魂醉

最新推荐文章于 2024-08-01 23:39:22 发布

阅读量566

点赞数 1

分类专栏： # oracle admin

本文链接：https://blog.csdn.net/dazuiba008/article/details/101699122

版权

oracle admin 专栏收录该内容

57 篇文章 3 订阅

订阅专栏

每个级别收集的信息：
表级别：行数，块数，每行的长度
列级别：列中去除重复值的数量，空值的数量，数据分布(直方图)，扩展统计
索引统计信息：叶子块的数量，levels,聚簇因子
系统统计信息：I/O性能，CPU性能

如果收集的统计信息不准确，那么会影响执行计划，影响了执行计划，可能就影响到了业务的响应速度，如果select，update,delete都很慢的情况。oracle是基于cost生产的执行计划，所以收集统计信息非常重要。

1.oracle建议打开自动统计信息收集
11g查看是否开启：

SQL> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED
auto space advisor                                               ENABLED
sql tuning advisor                                               ENABLED

打开和关闭自动统计信息收集,前提条件还要设置STATISTICS_LEVEL为all或者为typical，默认为typical

SQL> show parameter STATISTICS_LEVEL;

NAME                                 TYPE           VALUE
------------------------------------ -------------- ------------------------------
statistics_level                     string         TYPICAL

打开：
BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
     client_name => 'auto optimizer stats collection' 
,    operation   => NULL
,    window_name => NULL
);
END;
/

关闭：
BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
     client_name => 'auto optimizer stats collection'
,    operation   => NULL 
,    window_name => NULL
);
END;
/

2.什么时候手动收集统计信息

大多数情况下，开启自动的统计信息收集已经足够了，但是在维护窗口的时候，有时候大量修改的表会出现统计信息过时的情况。

(1)重复删除，或者truncate和重建的表会变为不稳定的表
(2)大的批量载入的的对象，超过对象本身的10%以上

对于高度不稳定的表，可以使用以下两种方法：

当表没有统计信息的时候，数据库会动态的收集该表必要的统计信息，参数OPTIMIZER_DYNAMIC_SAMPLING控制动态收集的特性，可以设置为2或者更高，默认2，那么你可以通过以下命令删除统计信息然后锁定统计信息设置为NULL

BEGIN
  DBMS_STATS.DELETE_TABLE_STATS('OE','ORDERS');
  DBMS_STATS.LOCK_TABLE_STATS('OE','ORDERS');
END;
/

这些表的统计信息可以设置为typical状态的值。当表具有了典型的的行数时，就会在表上收集统计信息，然后锁定统计信息。
对于批量加载的表，在加载过程之后立即在表上运行统计信息收集过程。最好将这些过程作为运行批量加载的同一脚本或作业的一部分来运行。

数据库可以通过以下方式为外部表收集统计信息

GATHER_TABLE_STATS procedure

GATHER_SCHEMA_STATS procedure

GATHER_DATABASE_STATS procedure

Automatic optimizer statistics collection processing

如果要使用GATHER_TABLE_STATS，那么需要设置ESTIMATE_PERCENT为NULL, 100或者AUTO_SAMPLE。因为在外部表上不支持采样，因为数据库不允许对外部表进行数据操作，所以数据库绝不会将外部表的统计信息标记为过时。例如，如果外部表需要新的统计信息，则是因为基础数据文件发生了变化，那么请删除现有的统计信息并重新收集它们。

系统统计信息必须手动收集。数据库不会自动收集这些统计信息。
必须使用GATHER_FIXED_OBJECTS_STATS过程手动收集有关固定对象（例如动态性能表）的统计信息。

恢复旧的统计信息
每当修改字典中的统计信息时，旧版本的统计信息都会自动保存以供将来还原。您可以使用DBMS_STATS包的RESTORE过程来还原统计信息。

锁定统计信息
有时候，可能需要防止DBMS_STATS_JOB进程在表或模式上收集新的统计信息，如之前所说的不稳定的表。在这些情况下，DBMS_STATS包提供了用于锁定表或模式的统计信息。

3.手动收集统计信息
如果没有开启自动统计信息收集，可以手动使用DBMS_STATS收集统计信息。
建议不要使用analyze命令的COMPUTE和ESTIMATE来收集统计信息，这样命令在将来可能会被移除。DBMS_STATS收集的统计信息更加广泛，准确有效。

DBMS_STATS包可以收集有关表和索引以及表的各个列和分区的统计信息。它不收集cluster统计信息。但是，可以使用DBMS_STATS来收集cluster单个表的的统计信息。

收集有关系统模式的统计信息时，可以使用过程DBMS_STATS.GATHER_DICTIONARY_STATS。此过程收集所有系统模式（包括SYS和SYSTEM）以及其他可选模式（例如CTXSYS和DRSYS）的统计信息。
更新数据库对象的统计信息时，Oracle数据库会使访问该对象的任何当前已解析的SQL语句无效。下次执行该语句时，将重新解析该语句，优化器将根据新统计信息自动选择新的执行计划。使用远程数据库上的新统计信息访问对象的分布式语句有效。新的统计信息将在下次解析SQL语句时生效。
在这里插入图片描述
3.1统计信息收集采样
如果不进行采样，那么就是是全表收集，要对全表进行扫描，排序，会比较资源，所以采样是很重要的收集统计信息的技术。
使用DBMS_STATS过程的ESTIMATE_PERCENT参数指定采样。尽管可以将采样百分比设置为任何值，但是Oracle建议将DBMS_STATS收集过程的ESTIMATE_PERCENT参数设置为DBMS_STATS.AUTO_SAMPLE_SIZE，以最大程度地提高性能，同时获得必要的统计准确性。 AUTO_SAMPLE_SIZE使Oracle数据库可以根据对象的统计属性来确定良好统计所需的最佳样本量。由于每种类型的统计信息都有不同的要求，因此跨表，列或索引的实际样本大小可能不相同。例如，要通过自动采样收集OE模式中所有表的表和列统计信息，可以使用：
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('OE',DBMS_STATS.AUTO_SAMPLE_SIZE);

手动指定ESTIMATE_PERCENT参数时，如果指定的百分比不能产生足够大的样本，则DBMS_STATS收集过程可能会自动增加采样百分比。这样可以通过减少波动来确保估计值的稳定性。

3.2并行统计信息收集
统计信息收集操作可以串行或并行运行。可以使用DBMS_STATS收集过程的DEGREE参数指定并行度。数据库可以将并行统计信息收集与采样结合使用。 Oracle建议将DEGREE参数设置为DBMS_STATS.AUTO_DEGREE。此设置使Oracle数据库可以根据对象的大小以及与并行相关的init.ora参数的设置来选择适当的并行度。

3.3 统计信息比较
DBMS_STATS能够比较来自两个不同来源的表的统计信息。下表列出了DBMS_STATS包中用于比较统计信息的功能。
在这里插入图片描述

4.系统统计信息
系统统计信息向查询优化器描述了系统的硬件特性，例如I / O以及CPU性能和利用率。选择执行计划时，优化器会估算每个查询所需的I / O和CPU资源。系统统计信息使查询优化器可以更准确地估计I / O和CPU成本，从而使查询优化器可以选择更好的执行计划。
使用DBMS_STATS.GATHER_SYSTEM_STATS来收集系统统计信息

当然官方文档还介绍了很多相关统计信息相关信息，这里不再一一描述。

下面简单介绍一下，统计信息收集的命令
表级别统计信息收集
GATHER_TABLE_STATS语法：

DBMS_STATS.GATHER_TABLE_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')), 
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT  to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   stattype         VARCHAR2 DEFAULT 'DATA',
   force            BOOLEAN  DEFAULT FALSE);

例子：
DBMS_STATS.GATHER_TABLE_STATS( 'SH', 'SALES', method_opt => 'FOR COLUMNS (empno, deptno)');

索引统计信息收集
GATHER_INDEX_STATS语法：

DBMS_STATS.GATHER_INDEX_STATS (
   ownname          VARCHAR2, 
   indname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (GET_PARAM('ESTIMATE_PERCENT')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type 
                                               (GET_PARAM('NO_INVALIDATE')),
   force            BOOLEAN DEFAULT FALSE);

例子：
execute dbms_stats.gather_index_stats('USERNAME','INDEXNAME');

系统统计信息收集
GATHER_SYSTEM_STATS语法：

DBMS_STATS.GATHER_SYSTEM_STATS (
   gathering_mode   VARCHAR2 DEFAULT 'NOWORKLOAD',
   interval         INTEGER  DEFAULT NULL,
   stattab          VARCHAR2 DEFAULT NULL,
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL);

例子：
execute dbms_stats.gather_system_stats('NOWORKLOAD',10);

收集动态新能视图统计信息
GATHER_FIXED_OBJECTS_STATS语法

DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (
   stattab        VARCHAR2 DEFAULT NULL,
   statid         VARCHAR2 DEFAULT NULL,
   statown        VARCHAR2 DEFAULT NULL, 
   no_invalidate  BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')));

例子：
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();

收集数据字典表统计信息
GATHER_DICTIONARY_STATS语法

DBMS_STATS.GATHER_DICTIONARY_STATS (
   comp_id          VARCHAR2 DEFAULT NULL, 
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')),
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   options          VARCHAR2 DEFAULT 'GATHER AUTO', 
   objlist    OUT   ObjectTab,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   obj_filter_list ObjectTab DEFAULT NULL);

DBMS_STATS.GATHER_DICTIONARY_STATS (
   comp_id           VARCHAR2 DEFAULT NULL,
   estimate_percent  NUMBER DEFAULT 
                          to_estimate_percent_type(GET_PARAM('ESTIMATE_PERCENT')),
   block_sample      BOOLEAN DEFAULT FALSE,
   method_opt        VARCHAR2 DEFAULT GET_PARAM('METHOD_OPT'),
   degree            NUMBER DEFAULT to_degree_type(GET_PARAM('DEGREE')),
   granularity       VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
   cascade           BOOLEAN DEFAULT to_cascade_type(GET_PARAM('CASCADE')),
   stattab           VARCHAR2 DEFAULT NULL, 
   statid            VARCHAR2 DEFAULT NULL,
   options           VARCHAR2 DEFAULT 'GATHER AUTO', 
   statown           VARCHAR2 DEFAULT NULL,
   no_invalidate     BOOLEAN DEFAULT
                               to_no_invalidate_type(get_param('NO_INVALIDATE')),
   obj_filter_list   ObjectTab DEFAULT NULL);

例子：
exec dbms_stats.gather_dictionary_stats();

命令参考如下链接
https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS059