ORACLE统计信息包-DBMS_STATS
1.什么是统计信息
统计信息主要是描述数据库中表，索引的大小，规模，数据分布状况等的一类信息。比如，表的行数，块数，平均每行的大小，索引的leaf blocks，索引字段的行数，不同值的大小等，都属于统计信息。CBO正是根据这些统计信息数据，计算出不同访问路径下，不同join 方式下，各种计划的成本，最后选择出成本最小的计划。
统计信息是存放在数据字段表中的，如tab$。一般我们从数据字段视图中察看统计信息状况，如DBA_TABLES,DBA_INDEXES,DBA_TAB_COL_STATISTICS, DBA_TAB_HISTOGRAMS 等。
列举下DBA_TABLES,DBA_INDEXES 视图中表示统计信息的一些字段。这些字段只有搜集过统计信息之后才有值，否则是空的。这些字段中last_analyzed 字段表示上次统计信息搜集的时间，大家可以根据这个字段，快速的了解最近一次统计信息搜集的时间。
统计信息主要是描述数据库中表，索引的大小，规模，数据分布状况等的一类信息。比如，表的行数，块数，平均每行的大小，索引的leaf blocks，索引字段的行数，不同值的大小等，都属于统计信息。CBO正是根据这些统计信息数据，计算出不同访问路径下，不同join 方式下，各种计划的成本，最后选择出成本最小的计划。
统计信息是存放在数据字段表中的，如tab$。一般我们从数据字段视图中察看统计信息状况，如DBA_TABLES,DBA_INDEXES,DBA_TAB_COL_STATISTICS, DBA_TAB_HISTOGRAMS 等。
列举下DBA_TABLES,DBA_INDEXES 视图中表示统计信息的一些字段。这些字段只有搜集过统计信息之后才有值，否则是空的。这些字段中last_analyzed 字段表示上次统计信息搜集的时间，大家可以根据这个字段，快速的了解最近一次统计信息搜集的时间。
--dba_tables
NUM_ROWS* NUMBER Number of rows in the table
BLOCKS* NUMBER Number of used data blocks in the table
EMPTY_BLOCKS* NUMBER Number of empty (never used) data blocks in the table
AVG_SPACE* NUMBER Average amount of free space, in bytes, in a data block allocated to the table
CHAIN_CNT* NUMBER Number of rows in the table that are chained from one data block to another, or which have migrated to a new block, requiring a link to preserve the old ROWID
AVG_ROW_LEN* NUMBER Average length of a row in the table in bytes
LAST_ANALYZED DATE Date on which this table was most recently analyzed
NUM_ROWS* NUMBER Number of rows in the table
BLOCKS* NUMBER Number of used data blocks in the table
EMPTY_BLOCKS* NUMBER Number of empty (never used) data blocks in the table
AVG_SPACE* NUMBER Average amount of free space, in bytes, in a data block allocated to the table
CHAIN_CNT* NUMBER Number of rows in the table that are chained from one data block to another, or which have migrated to a new block, requiring a link to preserve the old ROWID
AVG_ROW_LEN* NUMBER Average length of a row in the table in bytes
LAST_ANALYZED DATE Date on which this table was most recently analyzed
--dba_indexes
BLEVEL* NUMBER B*-Tree level: depth of the index from its root block to its leaf blocks. A depth of 0 indicates that the root block and leaf block are the same.
LEAF_BLOCKS* NUMBER Number of leaf blocks in the index
DISTINCT_KEYS* NUMBER Number of distinct indexed values. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is the same as the number of rows in the table (USER_TABLES.NUM_ROWS)
AVG_LEAF_BLOCKS_PER_KEY* NUMBER Average number of leaf blocks in which each distinct value in the index appears, rounded to the nearest integer. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is always 1.
AVG_DATA_BLOCKS_PER_KEY* NUMBER Average number of data blocks in the table that are pointed to by a distinct value in the index rounded to the nearest integer. This statistic is the average number of data blocks that contain rows that contain a given value for the indexed columns.
CLUSTERING_FACTOR* NUMBER Indicates the amount of order of the rows in the table based on the values of the index.
? If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend to point to rows in the same data blocks.
? If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks
NUM_ROWS NUMBER Number of rows in the index
SAMPLE_SIZE NUMBER Size of the sample used to analyze the index
LAST_ANALYZED DATE Date on which this index was most recently analyzed
2. 如何搜集统计信息
统计信息搜集也是有多种方法，推荐大家使用DBMS_STATS 表来进行统计信息搜集及进行一般的统计信息维护工作。
DBMS-STATS 包，主要提供了搜集，删除，导出，导入，修改统计信息的方法，分别对应于gather系列，delete系列，export 系列，import系列，set系列的子过程。一般可能主要是使用统计信息的搜集，以及导出导入这样的功能。具体来说，主要会使用到如下几个子过程：
GATHER_INDEX_STATS Procedure
Gathers index statistics.
统计信息搜集也是有多种方法，推荐大家使用DBMS_STATS 表来进行统计信息搜集及进行一般的统计信息维护工作。
DBMS-STATS 包，主要提供了搜集，删除，导出，导入，修改统计信息的方法，分别对应于gather系列，delete系列，export 系列，import系列，set系列的子过程。一般可能主要是使用统计信息的搜集，以及导出导入这样的功能。具体来说，主要会使用到如下几个子过程：
GATHER_INDEX_STATS Procedure
Gathers index statistics.
GATHER_TABLE_STATS Procedure
Gathers table and column (and index) statistics.
Gathers table and column (and index) statistics.
CREATE_STAT_TABLE Procedure
Creates a table with name stattab in ownname's schema which is capable of holding statistics.
Creates a table with name stattab in ownname's schema which is capable of holding statistics.
EXPORT_TABLE_STATS Procedure
Retrieves statistics for a particular table and stores them in the user stat table.
Retrieves statistics for a particular table and stores them in the user stat table.
EXPORT_SCHEMA_STATS Procedure
Retrieves statistics for all objects in the schema identified by ownname and stores them in the user stat table identified by stattab.
Retrieves statistics for all objects in the schema identified by ownname and stores them in the user stat table identified by stattab.
IMPORT_INDEX_STATS Procedure
Retrieves statistics for a particular index from the user stat table identified by stattab and stores them in the dictionary.
Retrieves statistics for a particular index from the user stat table identified by stattab and stores them in the dictionary.
IMPORT_TABLE_STATS Procedure
Retrieves statistics for a particular table from the user stat table identified by stattab and stores them in the dictionary.
Retrieves statistics for a particular table from the user stat table identified by stattab and stores them in the dictionary.
IMPORT_SCHEMA_STATS Procedure
Retrieves statistics for all objects in the schema identified by ownname from the user stat table and stores them in the dictionary.
Retrieves statistics for all objects in the schema identified by ownname from the user stat table and stores them in the dictionary.
对于统计信息的搜集，谈谈个人的几点理解：
? 统计信息默认是存放在数据字典表中的，也只有数据字典中的统计信息，才会影响到CBO。
? DBMS_STATS 提供的CREATE_STAT_TABLE 过程，只是生成一个用户自定义的特定格式的表，用来存放统计信息罢了，这个表中的统计信息是不会影响到统计信息的。
? GATHER 系列过程中，如果指定stattab，statid，statown 参数（也可以不指定），则是搜集的统计信息除了更新到数据字典外，还在statown 用户下的stattab 表中存放一份，标示为 statid;
? EXPORT和IMPORT 系列的过程中,stattab,statid,statown 参数不能为空，分别表示把数据字典中的当前统计信息导出到用户自定义的表中，以及把用户表中的统计信息导入到数据字典中，很明显可以看出，这里的导入操作和上面GATHER 操作会改变统计信息，可能会引起执行执行计划的改变，因此要慎重操作。
? 每次统计信息搜集前，将旧的统计信息备份起来是很有必要的；特别是保留一份或多份系统在稳定时期的统计信息也是很有必要的。
? 多长时间搜集一次统计信息，对于统计信息如何备份和保留，搜集统计信息时如何选择合适的采样，并行，直方图设置等都比较重要，需要设计一个较好的统计信息搜集策略。
? 统计信息默认是存放在数据字典表中的，也只有数据字典中的统计信息，才会影响到CBO。
? DBMS_STATS 提供的CREATE_STAT_TABLE 过程，只是生成一个用户自定义的特定格式的表，用来存放统计信息罢了，这个表中的统计信息是不会影响到统计信息的。
? GATHER 系列过程中，如果指定stattab，statid，statown 参数（也可以不指定），则是搜集的统计信息除了更新到数据字典外，还在statown 用户下的stattab 表中存放一份，标示为 statid;
? EXPORT和IMPORT 系列的过程中,stattab,statid,statown 参数不能为空，分别表示把数据字典中的当前统计信息导出到用户自定义的表中，以及把用户表中的统计信息导入到数据字典中，很明显可以看出，这里的导入操作和上面GATHER 操作会改变统计信息，可能会引起执行执行计划的改变，因此要慎重操作。
? 每次统计信息搜集前，将旧的统计信息备份起来是很有必要的；特别是保留一份或多份系统在稳定时期的统计信息也是很有必要的。
? 多长时间搜集一次统计信息，对于统计信息如何备份和保留，搜集统计信息时如何选择合适的采样，并行，直方图设置等都比较重要，需要设计一个较好的统计信息搜集策略。
dbms_stats包问世以后，Oracle专家可通过一种简单的方式来为CBO收集统计数据。目前，已经不再推荐你使用老式的分析表和dbms_utility方法来生成CBO统计数据。那些古老的方式甚至有可能危及SQL的性能，因为它们并非总是能够捕捉到有关表和索引的高质量信息。CBO使用对象统计，为所有SQL语句选择最佳的执行计划。
dbms_stats能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。
清单A展示了dbms_stats的一次示范执行情况，其中使用了options子句。
execdbms_stats.gather_schema_stats(
ownname => 'SCOTT',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 15 )
dbms_stats能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。
清单A展示了dbms_stats的一次示范执行情况，其中使用了options子句。
execdbms_stats.gather_schema_stats(
ownname => 'SCOTT',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 15 )
为了充分认识dbms_stats的好处，你需要仔细体会每一条主要的预编译指令（directive）。下面让我们研究每一条指令，并体会如何用它为基于代价的SQL优化器收集最高质量的统计数据。
options参数
使用4个预设的方法之一，这个选项能控制Oracle统计的刷新方式：
gather——重新分析整个架构（Schema）。
gather empty——只分析目前还没有统计的表。
gather stale——只重新分析修改量超过10%的表（这些修改包括插入、更新和删除）。
gather auto——重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。注意，使用gather auto类似于组合使用gather stale和gather empty。
注意，无论gather stale还是gather auto，都要求进行监视。如果你执行一个alter table xxx monitoring命令，Oracle会用dba_tab_modifications视图来跟踪发生变动的表。这样一来，你就确切地知道，自从上一次分析统计数据以来，发生了多少次插入、更新和删除操作。
gather empty——只分析目前还没有统计的表。
gather stale——只重新分析修改量超过10%的表（这些修改包括插入、更新和删除）。
gather auto——重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。注意，使用gather auto类似于组合使用gather stale和gather empty。
注意，无论gather stale还是gather auto，都要求进行监视。如果你执行一个alter table xxx monitoring命令，Oracle会用dba_tab_modifications视图来跟踪发生变动的表。这样一来，你就确切地知道，自从上一次分析统计数据以来，发生了多少次插入、更新和删除操作。
estimate_percent选项
以下estimate_percent参数是一种比较新的设计，它允许Oracle的dbms_stats在收集统计数据时，自动估计要采样的一个segment的最佳百分比：
estimate_percent => dbms_stats.auto_sample_size
要验证自动统计采样的准确性，你可检视dba_tables sample_size列。一个有趣的地方是，在使用自动采样时，Oracle会为一个样本尺寸选择5到20的百分比。记住，统计数据质量越好，CBO做出的决定越好。
method_opt选项
dbms_stats的method_opt参数尤其适合在表和索引数据发生变化时刷新统计数据。method_opt参数也适合用于判断哪些列需要直方图（histograms）。
dbms_stats的method_opt参数尤其适合在表和索引数据发生变化时刷新统计数据。method_opt参数也适合用于判断哪些列需要直方图（histograms）。
某些情况下，索引内的各个值的分布会影响CBO是使用一个索引还是执行一次全表扫描的决策。例如，假如在where子句中指定的值的数量不对称，全表扫描就显得比索引访问更经济。
如果你有一个高度倾斜的索引（某些值的行数不对称），就可创建Oracle直方图统计。但在现实世界中，出现这种情况的机率相当小。使用CBO时，最常见的错误之一就是在CBO统计中不必要地引入直方图。根据经验，只有在列值要求必须修改执行计划时，才应使用直方图。
为了智能地生成直方图，Oracle为dbms_stats准备了method_opt参数。在method_opt子句中，还有一些重要的新选项，包括skewonly，repeat和auto：method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto'
skewonly选项会耗费大量处理时间，因为它要检查每个索引中的每个列的值的分布情况。
假如dbms_stat发现一个索引的各个列分布得不均匀，就会为那个索引创建直方图，帮助基于代价的SQL优化器决定是进行索引访问，还是进行全表扫描访问。例如，在一个索引中，假定有一个列在50%的行中，如清单B所示，那么为了检索这些行，全表扫描的速度会快于索引扫描。
--*************************************************************
-- SKEWONLY option—Detailed analysis
--
-- Use this method for a first-time analysis for skewed indexes
-- This runs a long time because all indexes are examined
--*************************************************************
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
degree => 7
);
end;
-- SKEWONLY option—Detailed analysis
--
-- Use this method for a first-time analysis for skewed indexes
-- This runs a long time because all indexes are examined
--*************************************************************
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
degree => 7
);
end;
重新分析统计数据时，使用repeat选项，重新分析任务所消耗的资源就会少一些。使用repeat选项（清单C）时，只会为现有的直方图重新分析索引，不再搜索其他直方图机会。定期重新分析统计数据时，你应该采取这种方式。
--**************************************************************
-- REPEAT OPTION - Only reanalyze histograms for indexes
-- that have histograms
--
-- Following the initial analysis, the weekly analysis
-- job will use the “repeat” option. The repeat option
-- tells dbms_stats that no indexes have changed, and
-- it will only reanalyze histograms for
-- indexes that have histograms.
--**************************************************************
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 7);
end;
-- REPEAT OPTION - Only reanalyze histograms for indexes
-- that have histograms
--
-- Following the initial analysis, the weekly analysis
-- job will use the “repeat” option. The repeat option
-- tells dbms_stats that no indexes have changed, and
-- it will only reanalyze histograms for
-- indexes that have histograms.
--**************************************************************
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 7);
end;
使用alter table xxx monitoring;命令来实现Oracle表监视时，需要使用dbms_stats中的auto选项。如清单D所示，auto选项根据数据分布以及应用程序访问列的方式（例如通过监视而确定的一个列的工作量）来创建直方图。使用method_opt=>’auto’类似于在dbms_stats的option参数中使用gather auto。begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7);
end;
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7);
end;
并行收集
Oracle允许以并行方式来收集CBO统计数据，这就显著提高了收集统计数据的速度。但是，要想并行收集统计数据，你需要一台安装了多个CPU的SMP服务器。
更快的执行速度
dbms_stats是提高SQL执行速度的一种出色机制。通过使用dbms_stats来收集最高质量的统计数据，CBO能够正确判断执行任何SQL查询时的最快途径。dbms_stats还在不断地改进。目前，它的一些令人激动的新特性（自动样本大小和自动直方图生成）已经显著简化了Oracle专家的工作。
Oracle允许以并行方式来收集CBO统计数据，这就显著提高了收集统计数据的速度。但是，要想并行收集统计数据，你需要一台安装了多个CPU的SMP服务器。
更快的执行速度
dbms_stats是提高SQL执行速度的一种出色机制。通过使用dbms_stats来收集最高质量的统计数据，CBO能够正确判断执行任何SQL查询时的最快途径。dbms_stats还在不断地改进。目前，它的一些令人激动的新特性（自动样本大小和自动直方图生成）已经显著简化了Oracle专家的工作。
在使用DBMS_STATS分析表的时候，我们经常要保存之前的分析，以防分析后导致系统性能低下然后进行快速恢复。
首先创建一个分析表，该表是用来保存之前的分析值：
SQL> begin
2 dbms_stats.create_stat_table(ownname => 'TEST',stattab => 'STAT_TABLE');
3 end;
4 /
2 dbms_stats.create_stat_table(ownname => 'TEST',stattab => 'STAT_TABLE');
3 end;
4 /
PL/SQL 过程已成功完成。
分析表信息
SQL> BEGIN
2 --DBMS_STATS.delete_table_stats(ownname => 'TEST',tabname => 'A');
3 DBMS_STATS.gather_table_stats(ownname => 'TEST',tabname => 'A');
4 END;
5 /
2 --DBMS_STATS.delete_table_stats(ownname => 'TEST',tabname => 'A');
3 DBMS_STATS.gather_table_stats(ownname => 'TEST',tabname => 'A');
4 END;
5 /
PL/SQL 过程已成功完成。
导出表分析信息到stat_table中。
SQL> BEGIN
2 dbms_stats.export_table_stats(ownname => 'TEST',tabname => 'A',stattab => 'STAT_TABLE');
3 END;
4 /
2 dbms_stats.export_table_stats(ownname => 'TEST',tabname => 'A',stattab => 'STAT_TABLE');
3 END;
4 /
PL/SQL 过程已成功完成。
SQL>
同理也有
EXPORT_COLUMN_STATS：导出列的分析信息
EXPORT_INDEX_STATS：导出索引分析信息
EXPORT_SYSTEM_STATS：导出系统分析信息
EXPORT_TABLE_STATS：导出表分析信息
EXPORT_SCHEMA_STATS：导出方案分析信息
EXPORT_DATABASE_STATS：导出数据库分析信息
IMPORT_COLUMN_STATS：导入列分析信息
IMPORT_INDEX_STATS：导入索引分析信息
IMPORT_SYSTEM_STATS：导入系统分析信息
IMPORT_TABLE_STATS：导入表分析信息
IMPORT_SCHEMA_STATS：导入方案分析信息
IMPORT_DATABASE_STATS：导入数据库分析信息
GATHER_INDEX_STATS：分析索引信息
GATHER_TABLE_STATS：分析表信息，当cascade为true时，分析表、列(索引)信息
GATHER_SCHEMA_STATS：分析方案信息
GATHER_DATABASE_STATS：分析数据库信息
GATHER_SYSTEM_STATS：分析系统信息
SQL> select count(*) from stat_table;
COUNT(*)
----------
1
删除分析信息
SQL> BEGIN
2 DBMS_STATS.delete_table_stats(ownname => 'TEST',tabname => 'A');
3 END;
4 /
2 DBMS_STATS.delete_table_stats(ownname => 'TEST',tabname => 'A');
3 END;
4 /
PL/SQL 过程已成功完成。
导入分析信息
SQL> BEGIN
2 DBMS_STATS.import_table_stats(ownname => 'TEST',tabname => 'A',stattab => 'STAT_TABLE');
3 END;
4 /
2 DBMS_STATS.import_table_stats(ownname => 'TEST',tabname => 'A',stattab => 'STAT_TABLE');
3 END;
4 /
PL/SQL 过程已成功完成。
SQL>
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
oracle统计信息 使用dbms_stats包收集统计数据
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
oracle统计信息 使用dbms_stats包收集统计数据
统计信息包括下面几类：
表统计：包括记录数、block数和记录平均长度。
列统计：列中不同值的数量（NVD）、空值的数量和数据分布（HISTOGRAM）。
索引统计：索引叶块的数量、索引的层数和聚集因子（CLUSTERING FACTOR）。
系统统计：I/O性能和利用率和CPU性能和利用率。
表统计：包括记录数、block数和记录平均长度。
列统计：列中不同值的数量（NVD）、空值的数量和数据分布（HISTOGRAM）。
索引统计：索引叶块的数量、索引的层数和聚集因子（CLUSTERING FACTOR）。
系统统计：I/O性能和利用率和CPU性能和利用率。
生成统计信息：
统计信息生成技术包括三种：
基于数据采样的估计方式；
精确计算方式；
用户自定义的统计信息收集方式；
基于数据采样的估计方式；
精确计算方式；
用户自定义的统计信息收集方式；
其中采用估算方式可以指定总记录数的估算百分比或者总块数的估算百分比。
分区表的统计信息分为几级：分区表的整体信息、分区的统计信息和子分区的统计信息。
最常用的收集统计信息的方式包括：DBMS_STATS包和ANALYZE语句，Oracle推荐使用DBMS_STATS包来收集统计信息。
DBMS_STATS包中用于收集统计信息的过程包括：
dbms_stats.gather_table_stats 收集表、列和索引的统计信息；
dbms_stats.gather_schema_stats 收集SCHEMA下所有对象的统计信息；
dbms_stats.gather_index_stats 收集索引的统计信息；
dbms_stats.gather_system_stats 收集系统统计信息。
dbms_stats.delete_table_stats 删除表的统计信息
dbms_stats.export_table_stats 输出表的统计信息
dbms_stats.create_state_table
dbms_stats.set_table_stats 设置 表的统计
dbms_stats.auto_sample_size
dbms_stats.gather_database_stats：收集数据库中所有对象的统计信息；
dbms_stats.gather_schema_stats 收集SCHEMA下所有对象的统计信息；
dbms_stats.gather_index_stats 收集索引的统计信息；
dbms_stats.gather_system_stats 收集系统统计信息。
dbms_stats.delete_table_stats 删除表的统计信息
dbms_stats.export_table_stats 输出表的统计信息
dbms_stats.create_state_table
dbms_stats.set_table_stats 设置 表的统计
dbms_stats.auto_sample_size
dbms_stats.gather_database_stats：收集数据库中所有对象的统计信息；
在CREATE INDEX和ALTER INDEX REBUILD时可以指定COMPUTE STATISTICS语句，对于非分区表重建索引时会收集表、列和索引的统计信息。对于分区表，只收集索引信息，不会收集表和列信息。
可以在将METHOD_OPT参数设置为“FOR ALL HIDDEN COLUMNS SIZE N”来收集函数索引的索引表达式信息。
Oracle根据下列条件来决定使用哪些索引：
索引中的记录数；
索引中不同键值的数量；
索引的层数；
索引中的叶块数；
聚集因子；
每个键值平均叶块数；
如果两个索引的选择性、查询代价和集势都相同，那么优化器会根据索引名称的字母顺序选
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
oracle查看和更新统计表的信息
oracle查看和更新统计表的信息
在OEM中查看数据库的会话时，发现有一个会话的SQL要10多秒
在PL SQL中执行 SQL按F5查看这个SQL的计划，发现别的地方有 调用索引,
oracle统计值查看
select * from dba_tables where table_name = TABLE
检查一下字段last_analyzed 的值，其值就是最后一次统计更新的日期
更新查看
全表分析
完全计算法： analyze table abc compute statistics;
抽样估算法(抽样20%)： analyze table abc estimate statistics sample 20 percent;
对表作完全计算所花的时间相当于做全表扫描，抽样估算法由于采用抽样，比完全计算法的生成统计速度要快，如果不是要求要有精确数据的话，尽量采用抽样分析法。建议对表分析采用抽样估算，对索引分析可以采用完全计算。
完全计算法： analyze table abc compute statistics;
抽样估算法(抽样20%)： analyze table abc estimate statistics sample 20 percent;
对表作完全计算所花的时间相当于做全表扫描，抽样估算法由于采用抽样，比完全计算法的生成统计速度要快，如果不是要求要有精确数据的话，尽量采用抽样分析法。建议对表分析采用抽样估算，对索引分析可以采用完全计算。
使用dbms_stats
exec dbms_stats.gather_table_stats(OWNNAME=>'tccs',tabname=>'emp',cascade=>true);
在我们经常做表的分析时会看到使用dbms_stats和analyze在这个方面也会有点不同。
就是当使用dbms_stats来分析表的时候global_stats是YES，而用analyze来分析表的时候就是NO.
而且num_rows结果也会有些不同：
==================================================
在导入时加上statistics=none，说明不再导入统计信息：
imp c/c full=n file=H.dmp tables=(hcl) buffer=12800000 statistics=none log=a.log commit=y
这样导入的用户中的表或索引没有做统计分析。
就是当使用dbms_stats来分析表的时候global_stats是YES，而用analyze来分析表的时候就是NO.
而且num_rows结果也会有些不同：
==================================================
在导入时加上statistics=none，说明不再导入统计信息：
imp c/c full=n file=H.dmp tables=(hcl) buffer=12800000 statistics=none log=a.log commit=y
这样导入的用户中的表或索引没有做统计分析。
如果在导入完成后要对该用户下的所有表和索引进行统计分析，可执行如下过程：
sql>set serverout on
sql>exec dbms_output.enable(2000000) --设置较大的输出缓冲
sql>exec p_anayze_schema(username); --username改成实际的shcema名
统计过程如下：
create or replace procedure p_anayze_schema(as_username in varchar2) is
/*
该存储过程主要是对表及索引进行分析,
对于包含有子分区sub-partition的表需要注意一下granularity参数。具体参考：
granularity - the granularity of statistics to collect (only pertinent
if the table is partitioned)
default' - gather global- and partition-level statistics
'subpartition' - gather subpartition-level statistics
'partition' - gather partition-level statistics
'global' - gather global statistics
all' - gather all (subpartition, partition, and global) statistics
*/
vi_per number(3) := 100;
vs_username varchar2(64);
vi_start number := dbms_utility.get_time;
create or replace procedure p_anayze_schema(as_username in varchar2) is
/*
该存储过程主要是对表及索引进行分析,
对于包含有子分区sub-partition的表需要注意一下granularity参数。具体参考：
granularity - the granularity of statistics to collect (only pertinent
if the table is partitioned)
default' - gather global- and partition-level statistics
'subpartition' - gather subpartition-level statistics
'partition' - gather partition-level statistics
'global' - gather global statistics
all' - gather all (subpartition, partition, and global) statistics
*/
vi_per number(3) := 100;
vs_username varchar2(64);
vi_start number := dbms_utility.get_time;
begin
vs_username := upper(as_username);
for vcr_rec in (select segment_name,
segment_type,
ceil(sum(bytes) / 1024 / 1024) segment_size
from user_segments
where segment_name not like 'TMP_%'
and segment_name not like 'BIN$%'
group by segment_name, segment_type) loop
case
when vcr_rec.segment_type = 'INDEX' then
case
when vcr_rec.segment_size <= 100 then
vi_per := 100;
when vcr_rec.segment_size <= 300 then
vi_per := 50;
else
vi_per := 20;
end case;
begin
--delete old schema index statistics;
dbms_stats.delete_index_stats(ownname => vs_username,
indname => vcr_rec.segment_name);
exception
when others then
null;
end;
begin
--analyze index compute statistics;
dbms_stats.gather_index_stats(ownname => vs_username,
indname => vcr_rec.segment_name,
estimate_percent => vi_per,
degree => 2);
exception
when others then
null;
end;
dbms_output.put_line(vcr_rec.segment_name||'---size:'||vcr_rec.segment_size||'m, took '||ceil((dbms_utility.get_time - vi_start)/100)||'s');
vi_start := dbms_utility.get_time;
when vcr_rec.segment_type = 'TABLE' then
--
case
when vcr_rec.segment_size <= 150 then
vi_per := 100;
when vcr_rec.segment_size <= 500 then
vi_per := 50;
else
vi_per := 20;
end case;
begin
--delete table analyze statistics
dbms_stats.delete_table_stats(ownname => vs_username,
tabname => vcr_rec.segment_name);
exception
when others then
null;
end;
begin
--analyze table compute statistics;
dbms_stats.gather_table_stats(ownname => vs_username,
tabname => vcr_rec.segment_name,
estimate_percent => vi_per,
cascade => true,
granularity => 'all',
degree => 2,
method_opt => 'for all indexed columns');
exception
when others then
null;
end;
dbms_output.put_line(vcr_rec.segment_name||'---size:'||vcr_rec.segment_size||'m, took '||ceil((dbms_utility.get_time - vi_start)/100)||'s');
vi_start := dbms_utility.get_time;
when vcr_rec.segment_type = 'TABLE PARTITION' then
case
when vcr_rec.segment_size <= 150 then
vi_per := 100;
when vcr_rec.segment_size <= 500 then
vi_per := 50;
else
vi_per := 20;
end case;
begin
--delete table analyze statistics
dbms_stats.delete_table_stats(ownname => vs_username,
tabname => vcr_rec.segment_name);
exception
when others then
null;
end;
begin
--analyze table compute statistics;
dbms_stats.gather_table_stats(ownname => vs_username,
tabname => vcr_rec.segment_name,
estimate_percent => vi_per,
cascade => true,
granularity => 'all',
degree => dbms_stats.default_degree,
method_opt => 'for all indexed columns');
exception
when others then
null;
end;
when vcr_rec.segment_type = 'INDEX PARTITION' then
case
when vcr_rec.segment_size <= 100 then
vi_per := 100;
when vcr_rec.segment_size <= 300 then
vi_per := 50;
else
vi_per := 20;
end case;
begin
--delete old schema index statistics;
dbms_stats.delete_index_stats(ownname => vs_username,
indname => vcr_rec.segment_name);
exception
when others then
null;
end;
begin
--analyze index compute statistics;
dbms_stats.gather_index_stats(ownname => vs_username,
indname => vcr_rec.segment_name,
estimate_percent => vi_per,
degree => dbms_stats.default_degree);
exception
when others then
null;
end;
dbms_output.put_line(vcr_rec.segment_name||'---size:'||vcr_rec.segment_size||'m, took '||ceil((dbms_utility.get_time - vi_start)/100)||'s');
vi_start := dbms_utility.get_time;
/** when vcr_rec.segment_type = 'lobindex' then
vi_start := dbms_utility.get_time;
when vcr_rec.segment_type = 'lobsegment' then
vi_start := dbms_utility.get_time;**/
else
dbms_output.put_line(vcr_rec.segment_type);
end case;
end loop;
end;
for vcr_rec in (select segment_name,
segment_type,
ceil(sum(bytes) / 1024 / 1024) segment_size
from user_segments
where segment_name not like 'TMP_%'
and segment_name not like 'BIN$%'
group by segment_name, segment_type) loop
case
when vcr_rec.segment_type = 'INDEX' then
case
when vcr_rec.segment_size <= 100 then
vi_per := 100;
when vcr_rec.segment_size <= 300 then
vi_per := 50;
else
vi_per := 20;
end case;
begin
--delete old schema index statistics;
dbms_stats.delete_index_stats(ownname => vs_username,
indname => vcr_rec.segment_name);
exception
when others then
null;
end;
begin
--analyze index compute statistics;
dbms_stats.gather_index_stats(ownname => vs_username,
indname => vcr_rec.segment_name,
estimate_percent => vi_per,
degree => 2);
exception
when others then
null;
end;
dbms_output.put_line(vcr_rec.segment_name||'---size:'||vcr_rec.segment_size||'m, took '||ceil((dbms_utility.get_time - vi_start)/100)||'s');
vi_start := dbms_utility.get_time;
when vcr_rec.segment_type = 'TABLE' then
--
case
when vcr_rec.segment_size <= 150 then
vi_per := 100;
when vcr_rec.segment_size <= 500 then
vi_per := 50;
else
vi_per := 20;
end case;
begin
--delete table analyze statistics
dbms_stats.delete_table_stats(ownname => vs_username,
tabname => vcr_rec.segment_name);
exception
when others then
null;
end;
begin
--analyze table compute statistics;
dbms_stats.gather_table_stats(ownname => vs_username,
tabname => vcr_rec.segment_name,
estimate_percent => vi_per,
cascade => true,
granularity => 'all',
degree => 2,
method_opt => 'for all indexed columns');
exception
when others then
null;
end;
dbms_output.put_line(vcr_rec.segment_name||'---size:'||vcr_rec.segment_size||'m, took '||ceil((dbms_utility.get_time - vi_start)/100)||'s');
vi_start := dbms_utility.get_time;
when vcr_rec.segment_type = 'TABLE PARTITION' then
case
when vcr_rec.segment_size <= 150 then
vi_per := 100;
when vcr_rec.segment_size <= 500 then
vi_per := 50;
else
vi_per := 20;
end case;
begin
--delete table analyze statistics
dbms_stats.delete_table_stats(ownname => vs_username,
tabname => vcr_rec.segment_name);
exception
when others then
null;
end;
begin
--analyze table compute statistics;
dbms_stats.gather_table_stats(ownname => vs_username,
tabname => vcr_rec.segment_name,
estimate_percent => vi_per,
cascade => true,
granularity => 'all',
degree => dbms_stats.default_degree,
method_opt => 'for all indexed columns');
exception
when others then
null;
end;
when vcr_rec.segment_type = 'INDEX PARTITION' then
case
when vcr_rec.segment_size <= 100 then
vi_per := 100;
when vcr_rec.segment_size <= 300 then
vi_per := 50;
else
vi_per := 20;
end case;
begin
--delete old schema index statistics;
dbms_stats.delete_index_stats(ownname => vs_username,
indname => vcr_rec.segment_name);
exception
when others then
null;
end;
begin
--analyze index compute statistics;
dbms_stats.gather_index_stats(ownname => vs_username,
indname => vcr_rec.segment_name,
estimate_percent => vi_per,
degree => dbms_stats.default_degree);
exception
when others then
null;
end;
dbms_output.put_line(vcr_rec.segment_name||'---size:'||vcr_rec.segment_size||'m, took '||ceil((dbms_utility.get_time - vi_start)/100)||'s');
vi_start := dbms_utility.get_time;
/** when vcr_rec.segment_type = 'lobindex' then
vi_start := dbms_utility.get_time;
when vcr_rec.segment_type = 'lobsegment' then
vi_start := dbms_utility.get_time;**/
else
dbms_output.put_line(vcr_rec.segment_type);
end case;
end loop;
end;
===============================================
收集优化器统计信息——性能调整手册和参考
统计信息包括下面几类：
表统计：包括记录数、block数和记录平均长度。
列统计：列中不同值的数量（NVD）、空值的数量和数据分布（HISTOGRAM）。
索引统计：索引叶块的数量、索引的层数和聚集因子（CLUSTERING FACTOR）。
系统统计：I/O性能和利用率和CPU性能和利用率。
表统计：包括记录数、block数和记录平均长度。
列统计：列中不同值的数量（NVD）、空值的数量和数据分布（HISTOGRAM）。
索引统计：索引叶块的数量、索引的层数和聚集因子（CLUSTERING FACTOR）。
系统统计：I/O性能和利用率和CPU性能和利用率。
生成统计信息：
统计信息生成技术包括三种：
基于数据采样的估计方式；
精确计算方式；
用户自定义的统计信息收集方式；
统计信息生成技术包括三种：
基于数据采样的估计方式；
精确计算方式；
用户自定义的统计信息收集方式；
其中采用估算方式可以指定总记录数的估算百分比或者总块数的估算百分比。
分区表的统计信息分为几级：分区表的整体信息、分区的统计信息和子分区的统计信息。
最常用的收集统计信息的方式包括：DBMS_STATS包和ANALYZE语句，Oracle推荐使用DBMS_STATS包来收集统计信息。
DBMS_STATS包中用于收集统计信息的过程包括：
GATHER_TABLE_STATS：收集表、列和索引的统计信息；
GATHER_INDEX_STATS：收集索引的统计信息；
GATHER_SCHEMA_STATS：收集SCHEMA下所有对象的统计信息；
GATHER_DATABASE_STATS：收集数据库中所有对象的统计信息；
GATHER_SYSTEM_STATS：收集系统统计信息。
在CREATE INDEX和ALTER INDEX REBUILD时可以指定COMPUTE STATISTICS语句，对于非分区表重建索引时会收集表、列和索引的统计信息。对于分区表，只收集索引信息，不会收集表和列信息。
可以在将METHOD_OPT参数设置为“FOR ALL HIDDEN COLUMNS SIZE N”来收集函数索引的索引表达式信息。
可以在将METHOD_OPT参数设置为“FOR ALL HIDDEN COLUMNS SIZE N”来收集函数索引的索引表达式信息。
Oracle根据下列条件来决定使用哪些索引：
索引中的记录数；
索引中不同键值的数量；
索引的层数；
索引中的叶块数；
聚集因子；
每个键值平均叶块数；
如果两个索引的选择性、查询代价和集势都相同，那么优化器会根据索引名称的字母顺序选择索引。
直方图（histograms）：
对经常在WHERE语句中使用的且数据的分别十分不均匀的列使用直方图。
当下面三种情况，直方图没有意义：
对这个列所有的判定条件都使用了绑定变量；
列中数据均匀分布；
列中数据唯一且只使用相等作为判定条件。
对这个列所有的判定条件都使用了绑定变量；
列中数据均匀分布；
列中数据唯一且只使用相等作为判定条件。
建立直方图的方式是在收集信息时METHOD_OPT参数给出BUCKETS的个数。例如：exec dbms_stats.gather_table_stats(user, ‘emp’, method_opt => ‘FOR COLUMN SIZE 10 sal’)
直方图有两种类型：
基于高度的直方图：每个范围包括相同数量的值，根据每个范围的终点的列值来判断数据的分布。
基于数值的直方图：当列中不同的值的数量少于或等于直方图的BUCKET数量时，建立数值的直方图。这种直方图列中每个值都有对应的BUCKET，根据每个值对应的BUCKET的个数来判断数据的分布。
------------------------------
DBMS_STATS.GATHER_TABLE_STATS的语法
基于高度的直方图：每个范围包括相同数量的值，根据每个范围的终点的列值来判断数据的分布。
基于数值的直方图：当列中不同的值的数量少于或等于直方图的BUCKET数量时，建立数值的直方图。这种直方图列中每个值都有对应的BUCKET，根据每个值对应的BUCKET的个数来判断数据的分布。
------------------------------
DBMS_STATS.GATHER_TABLE_STATS的语法
DBMS_STATS.GATHER_TABLE_STATS的语法如下:
DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_percent NUMBER, block_sample BOOLEAN, method_opt VARCHAR2, degree NUMBER, granularity VARCHAR2, cascade BOOLEAN, stattab VARCHAR2, statid VARCHAR2, statown VARCHAR2, no_invalidate BOOLEAN, force BOOLEAN);
参数说明:
ownname:要分析表的拥有者
tabname:要分析的表名.
partname:分区的名字,只对分区表或分区索引有用.
estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.
block_sapmple:是否用块采样代替行采样.
method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下:
for all columns:统计所有列的histograms.
for all indexed columns:统计所有indexed列的histograms.
for all hidden columns:统计你看不到列的histograms
for columns
DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_percent NUMBER, block_sample BOOLEAN, method_opt VARCHAR2, degree NUMBER, granularity VARCHAR2, cascade BOOLEAN, stattab VARCHAR2, statid VARCHAR2, statown VARCHAR2, no_invalidate BOOLEAN, force BOOLEAN);
参数说明:
ownname:要分析表的拥有者
tabname:要分析的表名.
partname:分区的名字,只对分区表或分区索引有用.
estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.
block_sapmple:是否用块采样代替行采样.
method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下:
for all columns:统计所有列的histograms.
for all indexed columns:统计所有indexed列的histograms.
for all hidden columns:统计你看不到列的histograms
for columns
- SIZE
degree:决定并行度.默认值为null.
granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.
cascace:是收集索引的信息.默认为falase.
stattab指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.statown存储统计信息表的拥有者.以上三个参数若不指定,统计信息会直接更新到数据字典.
no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.
force:即使表锁住了也收集统计信息.
例子:
execute dbms_stats.gather_table_stats(ownname => 'owner',tabname => 'table_name' ,estimate_percent => null ,method_opt => 'for all indexed columns' ,cascade => true);
-----------------------------------------
ORACLE 中dbms_stats的使用
dbms_stats能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。
exec dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 15
)
ownname => 'SCOTT',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 15
)
为了充分认识dbms_stats的好处，需要仔细体会每一条主要的预编译指令（directive）。下面让我们研究每一条指令，并体会如何用它为基于代价的SQL优化器收集最高质量的统计数据。
options参数
使用4个预设的方法之一，这个选项能控制Oracle统计的刷新方式：
gather——重新分析整个架构（Schema）。
gather empty——只分析目前还没有统计的表。
gather stale——只重新分析修改量超过10%的表（这些修改包括插入、更新和删除）。
gather auto——重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。注意，使用gather auto类似于组合使用gather stale和gather empty。
注意，无论gather stale还是gather auto，都要求进行监视。如果你执行一个alter table xxx monitoring命令，Oracle会用dba_tab_modifications视图来跟踪发生变动的表。这样一来，你就确切地知道，自从上一次分析统计数据以来，发生了多少次插入、更新和删除操作。
gather empty——只分析目前还没有统计的表。
gather stale——只重新分析修改量超过10%的表（这些修改包括插入、更新和删除）。
gather auto——重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。注意，使用gather auto类似于组合使用gather stale和gather empty。
注意，无论gather stale还是gather auto，都要求进行监视。如果你执行一个alter table xxx monitoring命令，Oracle会用dba_tab_modifications视图来跟踪发生变动的表。这样一来，你就确切地知道，自从上一次分析统计数据以来，发生了多少次插入、更新和删除操作。
estimate_percent选项
estimate_percent参数是一种比较新的设计，它允许Oracle的dbms_stats在收集统计数据时，自动估计要采样的一个segment的最佳百分比：
estimate_percent => dbms_stats.auto_sample_size
estimate_percent => dbms_stats.auto_sample_size
要验证自动统计采样的准确性，你可检视dba_tables sample_size列。一个有趣的地方是，在使用自动采样时，Oracle会为一个样本尺寸选择5到20的百分比。记住，统计数据质量越好，CBO做出的决定越好。
method_opt选项
method_opt：for table --只统计表
for all indexed columns --只统计有索引的表列
for all indexes --只分析统计相关索引
for all columns
method_opt：for table --只统计表
for all indexed columns --只统计有索引的表列
for all indexes --只分析统计相关索引
for all columns
dbms_stats的method_opt参数尤其适合在表和索引数据发生变化时刷新统计数据。method_opt参数也适合用于判断哪些列需要直方图（histograms）。
某些情况下，索引内的各个值的分布会影响CBO是使用一个索引还是执行一次全表扫描的决策。例如，假如在where子句中指定的值的数量不对称，全表扫描就显得比索引访问更经济。
如果你有一个高度倾斜的索引（某些值的行数不对称），就可创建Oracle直方图统计。但在现实世界中，出现这种情况的机率相当小。使用CBO时，最常见的错误之一就是在CBO统计中不必要地引入直方图。根据经验，只有在列值要求必须修改执行计划时，才应使用直方图。
为了智能地生成直方图，Oracle为dbms_stats准备了method_opt参数。在method_opt子句中，还有一些重要的新选项，包括skewonly，repeat和auto：
method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto'
method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto'
skewonly选项会耗费大量处理时间，因为它要检查每个索引中的每个列的值的分布情况。
假如dbms_stat发现一个索引的各个列分布得不均匀，就会为那个索引创建直方图，帮助基于代价的SQL优化器决定是进行索引访问，还是进行全表扫描访问。例如，在一个索引中，假定有一个列在50%的行中，如清单B所示，那么为了检索这些行，全表扫描的速度会快于索引扫描。
--*************************************************************
-- SKEWONLY option—Detailed analysis
--
-- Use this method for a first-time analysis for skewed indexes
-- This runs a long time because all indexes are examined
--*************************************************************
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
degree => 7
);
end;
重新分析统计数据时，使用repeat选项，重新分析任务所消耗的资源就会少一些。使用repeat选项（清单C）时，只会为现有的直方图重新分析索引，不再搜索其他直方图机会。定期重新分析统计数据时，你应该采取这种方式。
--**************************************************************
-- REPEAT OPTION - Only reanalyze histograms for indexes
-- that have histograms
--
-- Following the initial analysis, the weekly analysis
-- job will use the “repeat” option. The repeat option
-- tells dbms_stats that no indexes have changed, and
-- it will only reanalyze histograms for
-- indexes that have histograms.
--**************************************************************
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 7
);
end;
使用alter table xxx monitoring;命令来实现Oracle表监视时，需要使用dbms_stats中的auto选项。如清单D所示，auto选项根据数据分布以及应用程序访问列的方式（例如通过监视而确定的一个列的工作量）来创建直方图。使用method_opt=>’auto’类似于在dbms_stats的option参数中使用gather auto。
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7
);
end;
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7
);
end;
并行统计收集degree参数
Oracle推荐设置DBMS_STATS的DEGREE参数为DBMS_STATS.AUTO_DEGREE，该参数允许Oracle根据对象的大小和并行性初始化参数的设置选择恰当的并行度。
聚簇索引，域索引，位图连接索引不能并行收集。
Oracle推荐设置DBMS_STATS的DEGREE参数为DBMS_STATS.AUTO_DEGREE，该参数允许Oracle根据对象的大小和并行性初始化参数的设置选择恰当的并行度。
聚簇索引，域索引，位图连接索引不能并行收集。
如何使用dbms_stats分析统计信息？
--分析库
begin
dbms_stats.gather_database_stats(
options=> 'GATHER AUTO');
begin
dbms_stats.gather_database_stats(
options=> 'GATHER AUTO');
end;
--创建统计信息历史保留表
sql> exec dbms_stats.create_stat_table(ownname => 'scott',stattab => 'stat_table') ;
--导出整个scheme的统计信息
sql> exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => 'stat_table') ;
sql> exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => 'stat_table') ;
--分析scheme
Exec dbms_stats.gather_schema_stats(
ownname => 'scott',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns ',
degree => 6 )
Exec dbms_stats.gather_schema_stats(
ownname => 'scott',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns ',
degree => 6 )
--分析表
sql> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'work_list',estimate_percent => 10,method_opt=> 'for all indexed columns') ;
sql> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'work_list',estimate_percent => 10,method_opt=> 'for all indexed columns') ;
--分析索引
SQL> exec dbms_stats.gather_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',estimate_percent => '10',degree => '4') ;
SQL> exec dbms_stats.gather_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',estimate_percent => '10',degree => '4') ;
--如果发现执行计划走错，删除表的统计信息
SQL>dbms_stats.delete_table_stats(ownname => 'scott',tabname => 'work_list') ;
SQL>dbms_stats.delete_table_stats(ownname => 'scott',tabname => 'work_list') ;
--导入表的历史统计信息
sql> exec dbms_stats.import_table_stats(ownname => 'scott',tabname => 'work_list',stattab => 'stat_table') ;
sql> exec dbms_stats.import_table_stats(ownname => 'scott',tabname => 'work_list',stattab => 'stat_table') ;
--如果进行分析后，大部分表的执行计划都走错，需要导回整个scheme的统计信息
sql> exec dbms_stats.import_schema_stats(ownname => 'scott',stattab => 'stat_table');
sql> exec dbms_stats.import_schema_stats(ownname => 'scott',stattab => 'stat_table');
--导入索引的统计信息
SQL> exec dbms_stats.import_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',stattab => 'stat_table')
SQL> exec dbms_stats.import_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',stattab => 'stat_table')
--检查是否导入成功
SQL> select table_name,num_rows,a.blocks,a.last_analyzed from all_tables a where a.table_name='WORK_LIST';
SQL> select table_name,num_rows,a.blocks,a.last_analyzed from all_tables a where a.table_name='WORK_LIST';
分析数据库（包括所有的用户对象和系统对象）：gather_database_stats
分析用户所有的对象（包括表、索引、簇）：gather_schema_stats
分析表：gather_table_stats
分析索引：gather_index_stats
删除数据库统计信息：delete_database_stats
删除用户方案统计信息：delete_schema_stats
删除表统计信息：delete_table_stats
删除索引统计信息：delete_index_stats
删除列统计信息：delete_column_stats
设置表统计信息：set_table_stats
设置索引统计信息：set_index_stats
设置列统计信息：set_column_stats
分析用户所有的对象（包括表、索引、簇）：gather_schema_stats
分析表：gather_table_stats
分析索引：gather_index_stats
删除数据库统计信息：delete_database_stats
删除用户方案统计信息：delete_schema_stats
删除表统计信息：delete_table_stats
删除索引统计信息：delete_index_stats
删除列统计信息：delete_column_stats
设置表统计信息：set_table_stats
设置索引统计信息：set_index_stats
设置列统计信息：set_column_stats
从Oracle Database 10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO的统计信息。
这个自动任务默认情况下在工作日晚上10：00-6：00和周末全天开启。调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息。
该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级，再开始进行统计信息。
该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级，再开始进行统计信息。
可以通过以下查询这个JOB的运行情况：
select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'
select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'
其实同在10点运行的Job还有一个AUTO_SPACE_ADVISOR_JOB：
SQL> select JOB_NAME,LAST_START_DATE from dba_scheduler_jobs;
SQL> select JOB_NAME,LAST_START_DATE from dba_scheduler_jobs;
JOB_NAME LAST_START_DATE
------------------------------ ----------------------------------------
AUTO_SPACE_ADVISOR_JOB 04-DEC-07 10.00.00.692269 PM +08:00
GATHER_STATS_JOB 04-DEC-07 10.00.00.701152 PM +08:00
FGR$AUTOPURGE_JOB
PURGE_LOG 05-DEC-07 03.00.00.169059 AM PRC
------------------------------ ----------------------------------------
AUTO_SPACE_ADVISOR_JOB 04-DEC-07 10.00.00.692269 PM +08:00
GATHER_STATS_JOB 04-DEC-07 10.00.00.701152 PM +08:00
FGR$AUTOPURGE_JOB
PURGE_LOG 05-DEC-07 03.00.00.169059 AM PRC
然而这个自动化功能已经影响了很多系统的正常运行，晚上10点对于大部分生产系统也并非空闲时段。
而自动分析可能导致极为严重的闩锁竞争，进而可能导致数据库Hang或者Crash。
所以建议最好关闭这个自动统计信息收集功能
方法之一:
exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
恢复自动分析:
exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');
exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
恢复自动分析:
exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');
方法二：
alter system set "_optimizer_autostats_job"=false scope=spfile;
alter system set "_optimizer_autostats_job"=true scope=spfile;
Pfile可以直接修改初始化参数文件，重新启动数据库。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10527166/viewspace-683270/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Oracle之PL/SQL的变量小结
下一篇：
ORACLE导入导出 之 IMP/EXP
请登录后发表评论
登录
全部评论
