分类： Oracle
2013-09-04 14:35:31
在Oracle9i数据库以来，收集统计信息有两种统计数据：
n 数据库统计
作为数据库管理员，需要收集数据库统计信息，这些数据可以量化数据的分布和表、索引和簇存储的特征。统计数据是基于成本优化器的基础，如何收集统计数据是实现成本优化器的根本。可以使用Analyze和Dbms_stats进行数据库统计的收集。
n 系统统计
只能使用Dbms_stats进行数据库统计的收集。
这些信息被存放在数据字典中。
2007-3-19新添
统计信息收集的一些建议：
使用dbms_stats包来收集而不是使用analyze命令收集。
在Oracle 10g统计信息的收集是自动的。
什么时间收集统计信息哪？有以下几种情况需要收集统计信息：
大的数据的改变的情况下，例如loads,purges,bulk updates。
新的high/low键值的产生的时候。
新添加表后
更新CPU，IO子系统的时候（主要是系统统计的收集）
RBO到CBO迁移的时候。
新的数据库添加的时候。
数据字典ALL_TAB_MODIFICATIONS的使用：
这个视图可以看到某个表的插入，更新删除数据的大概次数，这样就可以根据这些情况考虑这个表是否需要更新统计数据了。
end
1.1 表的统计信息内容
n 行数
n 数据块数（一般是准确的）
n 空的数据块数（一般是准确的）
n 平均可利用空闲空间
n 链接或者迁移行数
n 平均行的长度
n 最后analyze时间和事例的大小
查看的视图：
User_tables
All_tables
Dba_tables dba_tab_[sub]paratition
1.2 索引的统计信息内容
n 索引层数
n 页块的数目
n Distinct键值的数目
n 每一个键的页块的平均数目
n 每一个键的数据块的平均数目
n 索引entries的数目
n Clustering factor
n 最后analyze时间和事例的大小
查看的视图：
User_indexes
All_indexes
1.3 字段统计信息的内容
n Distinct值的数目
n 最小的值
n 最大的值
n 最后一次analyze日期和例子的大小
查看的视图：
User_tab_col_statistics
All_tab_col_statistics
1.4 系统统计信息的内容
n I/O性能和使用
n CPU性能和使用
1.5 收集直方图统计信息
2. 收集统计信息通常有三种方式进行收集：
2.1 Analyze
2.2 Dbms_stats
2.3 Dbms_utility
通常我们使用前两种方式进行统计数据的收集，oracle推荐使用dbms_stats进行统计数据的收集。
3. 统计信息收集的间隔时间
在添加和重建索引得时候需要分析索引。可以使用下面的语句来实现
|
Create index ... compute statistics;
Alter index ... rebuild compute statistics; |
统计数据收集的频率和应用程序和环境相关，数据库中数据的变化和数量变化的大小相关。不是有一个固定的间隔时间能够适合所有的数据库。要根据特定数据库的使用来确定分析数据的时间，一个目的就是要能够确保字典中的统计数据和表中的实际行数和分布相一致，至少是较为符合。例如，如果有一个数据库有大量的DML操作，而另外一个数据库只是多用于查询，报表，后一个数据库就没有必要经常分析。
4．需要多少样本
当然对于收集统计数据的时候，样本的数目越打越好，这样就能够保证正确的可信度，可信度的大小极大依赖样本的大小。对于估计运算来说，经常使用20％的样本尺寸，这样的范围对于一部分是足够保障优化器作出正确的执行计划。当然如果统计所有的数据，则统计数据的可信度为100％，对于分析一个样本数据大于49％的时候，Oracle会分析导致统计这个表的所有数据。
1、如果数据是规律分布的，只需要5％的样本
2、如果唯一值大于10％的总行数的时候，需要更多的样本大小。
3、在使用分析直方图统计数据的时候，样本的大小必须至少是bucket数目的100倍。
下面就收集信息的种类来分别对这几种方式进行讨论：
5．统计数据的收集
5.1 使用analyze收集统计信息
使用analyze命令的功能为：
n 收集或者删除表，索引，簇的统计信息。
n 确认索引或者分区索引、表或者分区表、索引组织表、簇或者对象的结构。
n 确定表或者簇的行链接和行迁移
收集表的统计信息
|
全样本收集 SQL> analyze table t compute statistics;
Table analyzed
SQL> select NUM_ROWS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS,NUM_FREELIST_BLOCKS from user_tables where table_name = 'T';
NUM_ROWS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS ---------- ---------- ---------- ----------- ------------------------- ------------------- 19 7837 0 6 0 0
SQL> 缺省样本，在这里没有指定样本的数目，Oracle会使用1064行作为样本数目 SQL> analyze table t estimate statistics;
Table analyzed |
删除统计信息数据
|
SQL> analyze table t delete statistics;
Table analyzed
SQL> select NUM_ROWS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS,NUM_FREELIST_BLOCKS from user_tables where table_name = 'T';
NUM_ROWS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS ---------- ---------- ---------- ----------- ------------------------- -------------------
SQL> |
收集直方图统计数据
|
SQL> SELECT NUM_DISTINCT, NUM_BUCKETS, SAMPLE_SIZE 2 FROM USER_TAB_COLUMNS 3 WHERE TABLE_NAME = 'T' AND COLUMN_NAME = 'A';
NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE ------------ ----------- -----------
SQL> SQL> ANALYZE TABLE T 2 COMPUTE STATISTICS FOR COLUMNS a SIZE 10;
Table analyzed
SQL> SQL> SELECT NUM_DISTINCT, NUM_BUCKETS, SAMPLE_SIZE 2 FROM USER_TAB_COLUMNS 3 WHERE TABLE_NAME = 'T' AND COLUMN_NAME = 'A';
NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE ------------ ----------- ----------- 19 10 19 |
5.1.2 收集索引得统计信息
5.1.3 收集字段的统计信息
5.1.4 收集模式的统计信息
5.1.5 收集系统的统计信息
5.1.6 收集直方图的统计信息
5.2 使用dbms_stats收集统计信息
包dbms_stats是Oracle推荐使用收集统计数据，
收集表的统计信息
|
SQL> exec dbms_stats.gather_table_stats('TEST','T');
PL/SQL procedure successfully completed
SQL> select NUM_ROWS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from user_tables where table_name = 'T';
NUM_ROWS AVG_SPACE CHAIN_CNT AVG_ROW_LEN ---------- ---------- ---------- ----------- 19 0 0 3 |
删除表的统计数据
|
SQL> exec dbms_stats.delete_table_stats('TEST','T');
PL/SQL procedure successfully completed
SQL> select NUM_ROWS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from user_tables where table_name = 'T';
NUM_ROWS AVG_SPACE CHAIN_CNT AVG_ROW_LEN ---------- ---------- ---------- -----------
SQL> |
收集索引统计信息
两种收集方式的区别
我一直在收集关于这两个收集方式的区别，在看到资料上只是说，Oracle推荐使用包DBMS_STATS收集统计数据。但是analyze命令有包DBMS_STATS不能替代的功能就是这个命令可以确认索引或者分区索引、表或者分区表、索引组织表、簇或者对象的结构。
确定表或者簇的行链接和行迁移。这是包DBMS_STATS没有的功能。但是这些信息不和成本优化器的解析相关。
Oracle之所以推荐使用包DBMS_STATS收集统计数据是因为这个包能够并行收集统计数据，收集分区表全局的统计和能够优化统计数据的收集。
6. 统计数据的导出导入
1、使用dbms_stats.create_stat_table过程建立一个用户定义的统计表
|
SQL> exec dbms_stats.create_stat_table(ownname => 'TEST',stattab => 'mystats');
PL/SQL procedure successfully completed
SQL> desc mystats; Name Type Nullable Default Comments ------- -------------- -------- ------- -------- STATID VARCHAR2(30) Y TYPE CHAR(1) Y VERSION NUMBER Y FLAGS NUMBER Y C1 VARCHAR2(30) Y C2 VARCHAR2(30) Y C3 VARCHAR2(30) Y C4 VARCHAR2(30) Y C5 VARCHAR2(30) Y N1 NUMBER Y N2 NUMBER Y N3 NUMBER Y N4 NUMBER Y N5 NUMBER Y N6 NUMBER Y N7 NUMBER Y N8 NUMBER Y N9 NUMBER Y N10 NUMBER Y N11 NUMBER Y N12 NUMBER Y D1 DATE Y R1 RAW(32) Y R2 RAW(32) Y CH1 VARCHAR2(1000) Y
SQL> |
2、使用dbms_stats.export_table_stats过程将统计数据从数据字典拷贝到用户定义的统计表中。
|
SQL> select NUM_ROWS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from user_tables where table_name = 'T';
NUM_ROWS AVG_SPACE CHAIN_CNT AVG_ROW_LEN ---------- ---------- ---------- ----------- 19 0 0 3
SQL> select count(*) from mystats;
COUNT(*) ---------- 0
SQL> exec dbms_stats.export_table_stats(ownname => 'test',tabname => 'T',stattab => 'mystats');
PL/SQL procedure successfully completed
SQL> select count(*) from mystats;
COUNT(*) ---------- 2 |
3使用export和import工具将统计信息导出，导入到相应的需要的数据库中的统计表。
4 使用过程将导入的统计信息导入到数据字典表中，在这里步骤3、4做一个模拟。
|
SQL> exec dbms_stats.delete_table_stats(ownname => 'TEST',tabname => 'T');
PL/SQL procedure successfully completed
SQL> select NUM_ROWS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from user_tables where table_name = 'T';
NUM_ROWS AVG_SPACE CHAIN_CNT AVG_ROW_LEN ---------- ---------- ---------- -----------
SQL> exec dbms_stats.import_table_stats(ownname => 'test',tabname => 't',stattab => 'mystats');
PL/SQL procedure successfully completed
SQL> select NUM_ROWS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from user_tables where table_name = 'T';
NUM_ROWS AVG_SPACE CHAIN_CNT AVG_ROW_LEN ---------- ---------- ---------- ----------- 19 0 0 3
SQL> |
7 系统统计
这是在Oracle9i上新增的一项功能。在
系统统计是由包dbms_stats来收集，这些数据被存放在aux_stats$表中。
n CPU速度（MHz）
n 单一块读的时间（毫秒）
n 一毫秒读多少块
n Typical achieved multiblock read
系统统计收集的过程
手动收集系统统计
n 开始收集execute dbms_stats.gather_system_stats('Start');
n 运行批量负荷
n 停止收集execute dbms_stats.gather_system_stats('Stop');
附：
了解系统统计 作者：Jonathan Lewis这个地址上有一片介绍系统统计的文章，可以参考一下。以下部分内容是节选自这篇文章。
这是可以通过查询视图aux_stats$ 的表（由 SYS 模式所有）。在收集了系统统计数据之后，这个表将包含一些由新的优化器算法用来计算成本的关键数字。
|
select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN'; |
这时候会有一下的结果出现：
|
PNAME PVAL1 ------------------------------ ---------- CPUSPEED 564 MAXTHR 13899776 MBRC 6 MREADTIM 10.496 SLAVETHR 182272 SREADTIM 1.468
10g 还引进了一些额外的行，这些行的值在数据库启动时设置：
CPUSPEEDNW 904.86697 IOSEEKTIM 10 IOTFRSPEED 4096
|
dbms_stats 程序包中的 get_system_stats 过程是收集系统信息。如果您想“调整”这些值（没有正确地收集它们），那么还有一个 set_system_stats 过程。
系统统计的使用内幕
表扫描
当提供系统统计时，在优化程序成本计算上有两个显著的变化。首先您将注意到sys.aux_stats$ 保存了以下参数的值：
n sreadtim：一次单块读请求的平均时间（以毫秒为单位）
n mreadtim：一次多块读请求的平均时间（以毫秒为单位）
n MBRC： 一次多块读操作中的平均块数。
利用这些信息，Oracle 可以估计执行一次表扫描（或索引快速全扫描）要花多长时间。计算很容易：它就是执行扫描所需的多块读操作的数量乘以执行一次多块读操作的平均时间。忽略因自动段空间管理造成的小的变化，我们只注意高水线标记，并从那里开始工作：
完成时间＝ mreadtim * HWM / MBRC。
Oracle 根据等价的单块读操作数重新声明了“完成时间”，而不是将“完成时间”报告为查询的成本。为此，只需将完成时间除以一次单块读操作的平均时间。
成本＝完成时间/ sreadtim
或者，将这两个公式放在一起并重新排列项目：
表扫描的成本＝ (HWM / MBRC) * (mreadtim / sreadtim)
从这个例子中您可以看到，查询的成本是查询的完成时间，但用单块读操作为单位进行表示而不是使用正常的时间单位。
当您开始使用系统统计时，优化程序在表扫描和索引访问路径之间作选择时自动开始变得更“明智”，因为用于表扫描的多块读操作的成本将包含一个正确和适当的时间组成部分。
过去，表扫描的成本很简单：
表扫描的成本＝ HWM /（修改后的 db_file_multiblock_read_count）。
您为参数 db_file_multiblock_read_count 选择的值可能不实际，而这个公式几乎没有考虑这一事实，也没有考虑与单块读操作相比，一个极大的 db_file_multiblock_read_count 将花费的额外时间。
这个缺点是 Oracle 在 8.1.6 中创建 optimizer_index_cost_adj 参数的主要原因，这个参数使您能够引进实质上类似于您在系统统计中收集的 mreadtim 的一个因子。（您可能已经发现了新的成本公式中的 mreadtim/sreadtim 部分和估计一个切合实际的optimizer_index_cost_adj 的常见方法之间的相似性。）但使用optimizer_index_cost_adj 参数存在一些可能产生问题的意外副作用，而在您开始使用系统统计时开始起作用的一些机制变得更强健得多。
顺便提一下，甚至在使用系统统计时，使用 optimizer_index_cost_adj 作为表高速缓存效果（特别是，多少百分比的单块表读操作可能转变成实际的读请求）的一个线索仍然很有意义。不过，在 10g 中会提供一些信息，使得甚至这种线索在不远的将来也将变得不必要。
注：
在这里有两个比较复杂的参数：db_file_multiblock_read_count和参数optimizer_index_cost_adj的使用问题，在我们的实际应用中慧出现这样的情况，在你设置db_file_multiblock_read_count参数是根据操作系统的实际IO吞吐能力来确定的，但是如果设置这个参数比较高的话，优化器就会认为全表扫描来代替索引扫描，所以后来（8.1.6）引入了一个参数optimizer_index_cost_adj，使用这个参数来调整使用索引的花费，缺省是100，设置的范围是1－10000，100的意思就是索引和全表扫描的花费是一致的，通常是设置一个小于100的值，这样就告诉Oracle使用索引来替代全表扫描。但是我们在设置这个参数的时候是没有一个能够定量的参考，系统统计就给我们提供了这个参考。优化器利用系统统计来确定是否使用索引或者全表扫描。
CPU 成本
系统统计不仅能够修正单块读操作和多块读操作之间的 I/O 和时间权衡。它们还考虑到了对成本计算的两个进一步的增强（或修正）：首先，Oracle 甚至能够更好地根据索引访问路径平衡表扫描；其次，Oracle 能够智能地重新安排预测顺序。
注意统计如何包含表面的 CPU 速度（名义上以 MHz 为单位）。如果这与您的系统的实际 CPU速度一点也不相同，请不用大惊小怪 — 这个数字可能只是一个基准线操作的内部校准，Oracle使用这种基准操作来计算其它操作的相对 CPU 成本。在以 2.8GHz 的速度运行的计算机上，我一般得到的表面 CPU 速度是几百 MHz。（记住您看到的是单个 CPU 的名义速度，而不是一个多 CPU 系统中的所有 CPU 的总和。）
那么为什么它帮助优化程序了解您的 CPU 的（表面）速度？考虑一个例子，其中您能够选择：
选项 1：使用一个简单的日期列上的一个索引来在表中查找 20 个分散的行。
选项 2：使用表扫描来检查表中的每一行，查看表中每一行，看看日期列是否落在正确的范围之内。
Oracle 可能纯粹根据单块和多块读取的数量和速度决定表扫描将更快。但如果表扫描需要在10,000 行上执行类似以下的一个测试，那么这将花费多少 CPU 时间：
date_col between to_date('01-Jan-2004') and to_date('02-Jan-2004);
CPU 操作也花费时间，并且如果必须在表扫描上执行的测试的数量和特性需要许多 CPU 时间，那么 Oracle 将把这个成本转换成公式中的一个因子，并且可能将一个查询从 CPU 密集型的表扫描转变成索引范围扫描。您可以从 Oracle9i 数据库性能调整指南和参考中的公式 (A96533 p. 9-22) 中看到这些：
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim
这个公式中的 #CPUCycles 值可以在 Explain Plan 程序使用的更新版本 plan_table 的cpu_cost 列中看到。（下面是优化程序增强的另一个小但却重要的细节：始终查看 Explain Plan 程序如何发展。）
实际上，如果您在成本公式中使用该列，那么该公式需要引进一个谎言因素 (fudge factor) —cpuspeed 以 MHz 为单位记录，其它的计时参量以毫秒为单位给出，因此该公式的 CPU 部分看起来就像用一个因子 1000 进行了调整一样：
#CPUCycles / (cpuspeed * 1000)
预测顺序
除高水平的选择之外，对 CPU 操作的了解和预测的复杂性可以允许 Oracle 去做一些您在手工调整实践中可能从来不会去考虑的事情。对这一原则的最佳演示来自一个（稍微经过设计的）工作示例。为了可重复测试的目的，以下代码在
by:http://litterbaby.itpub.net/post/16841/276335
