ORACLE 统计分析

ORACLE 统计分析

统计分析主要包括产生表及索引的统计信息

表的统计信息主要包括表的行数，每行的平均长度（字节），空闲块，统计时间等信息
索引的统计信息主要包括行数、层数、叶块数、统计时间等信息。
另外ORACLE还可以统计列及数据不对称信息，9i还可以统计系统信息（CPU，I/O）

ORACLE执行成本分析时首先取出所应用表及索引的统计数据进行分析，其中数据行数是一个重要的参数，因为ORACLE 在分析表大小时行数为主要参数，如果进行两个表联合时，ORACLE会通过分析表的大小，决定应用小表进行全表查询，而大表执行联合查询，这种性能明显高 于先大表进行全表扫描。索引的统计信息对分析也产生比较大的影响，如ORACLE通过统计可以分析产生多个索引的优先级及索引的实用性来确定最优的索引策 略。ORACLE还可以统计列及数据对称信息以产生更精确的分析。如一个表有A字段的索引，其中A共有两种值1和0，共10000条记录，为0的记录有 10条，为1的记录有9990条，这时如果没有进行列数据不对称的统计信息，那么ORACLE对A=0及A=1条件查询都会进行索引，但实际应用对A=0 的索引性能得到了很大的提高，而A=1的索引反而使性能下降。所以说索引特征值分析信息对应用索引产生重大影响，精确的信息使ORACLE不会使用不应该 用的索引。

实际分析:
zl_cbqc和zl_yhjbqk都没有建立统计信息，执行如下两个SQL ORACLE将产生不同的执行计划。

1. select * from dlyx.zl_cbqc  b, dlyx.zl_yhjbqk  a where a.qc_bh=b.qc_bh

执行计划:

SELECT STATEMENT, GOAL = CHOOSE                             

NESTED LOOPS                            

TABLE ACCESS FULL   DLYX     ZL_YHJBQK  

TABLE ACCESS BY INDEX ROWID      DLYX     ZL_CBQC                   

INDEX UNIQUE SCAN DLYX                         

2. select * from dlyx.zl_yhjbqk a,dlyx.zl_cbqc b where a.qc_bh=b.qc_bh

执行计划：

SELECT STATEMENT, GOAL = CHOOSE                             

NESTED LOOPS                            

TABLE ACCESS FULL    DLYX     ZL_CBQC                  

TABLE ACCESS BY INDEX ROWID      DLYX     ZL_YHJBQK                

 INDEX RANGE SCAN    DLYX                        

在对两个表进行了统计分析后

3. select * from dlyx.zl_yhjbqk a,dlyx.zl_cbqc b where a.qc_bh=b.qc_bh

执行计划：

SELECT STATEMENT, GOAL = CHOOSE                159 72853     9689449

HASH JOIN                159 72853     9689449

TABLE ACCESS FULL      DLYX     ZL_CBQC      1     426 19596

TABLE ACCESS FULL      DLYX     ZL_YHJBQK   157 72853     6338211

4. select * from dlyx.zl_cbqc b,dlyx.zl_yhjbqk a where a.qc_bh=b.qc_bh

执行计划：

SELECT STATEMENT, GOAL = CHOOSE                159 72853     9689449

HASH JOIN                159 72853     9689449

TABLE ACCESS FULL      DLYX     ZL_CBQC      1     426 19596

TABLE ACCESS FULL      DLYX     ZL_YHJBQK   157 72853     6338211

从以上测试可以明显看出ORACLE的分析结果

第1条SQL与第2条SQL在没有统计分析的信息下进行，ORACLE无法进行判断，只能以规则方法进行分析，根据表的出现先后顺序有明显关系。

第3条SQL与第4条SQL在有统计分析的信息下进行，ORACLE分析与表的出现先后顺序无关，因为它已经知道了 表的数据量并且已经确定返回的数据量基本上是两个表所有的数据，所以对表两个表进行了HASH JOIN （同时取出两个表的数据然后在内存中进行联合产生返回结果）。

    ORACLE数据库的PL/SQL语句执行的优化器，有基于代价的优化器（CBO）和基于规则的优化器（RBO）。

    RBO的优化方式，依赖于一套严格的语法规则，只要按照规则写出的语句，不管数据表和索引的内容是否发生变化，不会影响PL/SQL语句的"执行计划"。

    CBO自ORACLE7版被引入，ORACLE自7版以来采用的许多新技术都是只基于CBO的，如星型连接排列查询，哈希连接查询，反向索引，索引表，分 区表和并行查询等。CBO计算各种可能"执行计划"的"代价"，即cost，从中选用cost最低的方案，作为实际运行方案。各"执行计划"的cost的 计算根据，依赖于数据表中数据的统计分布，ORACLE数据库本身对该统计分布是不清楚的，须要分析表和相关的索引，才能搜集到CBO所需的数据。

    CBO是ORACLE推荐使用的优化方式，要想使用好CBO，使SQL语句发挥最大效能，必须保证统计数据的及时性。

    统计信息的生成可以有完全计算法和抽样估算法。SQL例句如下：

    完全计算法： analyze table abc compute statistics; 
    抽样估算法(抽样20%)： analyze table abc estimate statistics sample 20 percent;

    对表作完全计算所花的时间相当于做全表扫描，抽样估算法由于采用抽样，比完全计算法的生成统计速度要快，如果不是要求要有精确数据的话，尽量采用抽样分析法。建议对表分析采用抽样估算，对索引分析可以采用完全计算。

    我们可以采用以下两种方法，对数据库的表和索引及簇表定期分析生成统计信息，保证应用的正常性能。

    1. 在系统设置定时任务，执行分析脚本。

    在数据库服务器端，我们以UNIX用户oracle，运行脚本analyze，在analyze中，我们生成待执行sql脚本，并运行。（假设我们要分析scott用户下的所有表和索引）

    Analyze脚本内容如下：

sqlplus scott/tiger << EOF
    set pagesize 5000
    set heading off
    SPOOL ANALYTAB.SQL
    SELECT "ANALYZE TABLE SCOTT."||TABLE_NAME||" ESTIMATE STATISTICS SAMPLE 20 PERCENT ;" FROM USER_TABLES；
    SPOOL OFF
    SPOOL ANALYIND.SQL
    SELECT "ANALYZE TABLE SCOTT."||TABLE_NAME||" ESTIMATE STATISTICS SAMPLE 20 PERCENT FOR ALL INDEXES;" FROM USER_TABLES；
    SPOOL OFF
    SPOOL ANALYZE.LOG
    @ANALYTAB.SQL
    @ANALYIND.SQL
    SPOOL OFF
    EXIT

    在UNIX平台上crontab加入，以上文件，设置为每个月或合适的时间段运行。

或者将如下脚本保存成analyze.sql,然后在sqlplus里面执行：

set pagesize 5000
set linesize 300
set trims on
set heading off
set feedback off
SPOOL analyTab.sql
SELECT 'ANALYZE TABLE ZFMI.'||TABLE_NAME||' COMPUTE STATISTICS ;'

FROM USER_TABLES;
SPOOL OFF
SPOOL analyIdx.sql
SELECT 'ANALYZE TABLE ZFMI.'||TABLE_NAME||' COMPUTE STATISTICS

FOR ALL INDEXES;' FROM USER_TABLES;
SPOOL OFF
SPOOL analyLog.log
@@analyTab.sql
@@analyIdx.sql
SPOOL OFF

    2. 利用ORACLE提供的程序包（PACKAGE）对相关的数据库对象进行分析。

    有以下的程序包可以对表，索引，簇表进行分析。

    包中的存储过程的相关参数解释如下：

    TYPE可以是：TABLE，INDEX，CLUSTER中其一。 
    SCHEMA为：TABLE，INDEX，CLUSTER的所有者，NULL为当前用户。 
    NAME为：相关对象的名称。 
    METHOD是：ESTIMATE，COMPUTE，DELETE中其一，当选用ESTIMATE， 
    下面两项，ESTIMATE_ROWS和ESTIMATE_PERCENT不能同时为空值。 
    ESTIMATE_ROWS是：估算的抽样行数。 
    ESTIMATE_PERCENT是：估算的抽样百分比。 
    METHOD_OPT是：有以下选项， 
    FOR TABLE /*只统计表*/ 
    [FOR ALL [INDEXED] COLUMNS] [SIZE N] /*只统计有索引的表列*/ 
    FOR ALL INDEXES /*只分析统计相关索引*/ 
    PARTNAME是：指定要分析的分区名称。

    1)

    DBMS_DDL.ANALYZE_OBJECT( 
    TYPE VARCHAR2, 
    SCHEMA VARCHAR2, 
    NAME VARCHAR2, 
    METHOD VARCHAR2, 
    ESTIMATE_ROWS NUMBER DEFAULT NULL, 
    ESTIMATE_PERCENT NUMBER DEFAULT NULL, 
    METHOD_OPT VARCHAR2 DEFAULT NULL, 
    PARTNAME VARCHAR2 DEFAULT NULL ) ;

    该存储过程可对特定的表，索引和簇表进行分析。例如，对SCOTT用户的EMP表，进行50%的抽样分析，参数如下：

    DBMS_DDL.ANALYZE_OBJECT("TABLE", "SCOTT", "EMP", "ESTIMATE", NULL,50);

    2)

    DBMS_UTILITY.ANALYZE_SCHEMA ( 
    SCHEMA VARCHAR2, 
    METHOD VARCHAR2, 
    ESTIMATE_ROWS NUMBER DEFAULT NULL, 
    ESTIMATE_PERCENT NUMBER DEFAULT NULL, 
    METHOD_OPT VARCHAR2 DEFAULT NULL ) ; 
    DBMS_UTILITY.ANALYZE_DATABASE ( 
    METHOD VARCHAR2, 
    ESTIMATE_ROWS NUMBER DEFAULT NULL, 
    ESTIMATE_PERCENT NUMBER DEFAULT NULL, 
    METHOD_OPT VARCHAR2 DEFAULT NULL );

    其中，ANALYZE_SCHEMA用于对某个用户拥有的所有TABLE，INDEX和CLUSTER的分析统计。ANALYZE_DATABASE用于对整个数据库进行分析统计。

    3) DBMS_STATS是在ORACLE8I中新增的程序包，它使统计数据的生成和处理更加灵活方便，并且可以并行方式生成统计数据。在程序包中的以下过程分别分析统计TABLE，INDEX，SCHEMA，DATABASE级别的信息。

    DBMS_STATS.GATHER_TABLE_STATS 
    DBMS_STATS.GATHER_INDEX_STATS 
    DBMS_STATS.GATHER_SCHEMA_STATS 
    DBMS_STATS.GATHER_DATABASE_STATS

    在这里，我们以数据库JOB的方式，定时对数据库中SCOTT模式下所有的表和索引进行分析：

    在SQL*PLUS下运行：

    VARIABLE jobno number;
    BEGIN
    DBMS_JOBS.SUBMIT ( :jobno ,
    " dbms_utility.analyze_schema ( "scott", "estimate", NULL, 20) ; ",
    sysdate, "sysdate+30");
    commit;
    end;
    /
    Statement processed.
    Print jobno
    JOBNO
    ------------- 
    16

    以上作业，每隔一个月用DBMS_UTILITY.ANALYZE_SCHEMA对用户SCOTT的所有表，簇表和索引作统计分析。

dbms_stats包问世以后，Oracle专家可通过一种简单的方式来为CBO收集统计数据。目前，已经不再推荐你使用老式的分析表和 dbms_utility方法来生成CBO统计数据。那些古老的方式甚至有可能危及SQL的性能，因为它们并非总是能够捕捉到有关表和索引的高质量信息。 CBO使用对象统计，为所有SQL语句选择最佳的执行计划。

dbms_stats能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。

下边给出了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视图来跟踪发生变动的表。这样一来，你就确切地知道，自从上一次 分析统计数据以来，发生了多少次插入、更新和删除操作。

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）。

某些情况下，索引内的各个值的分布会影响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'

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;

并行收集
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 /
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 /
PL/SQL 过程已成功完成。
导出表分析信息到stat_table中。
SQL> BEGIN
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 /
PL/SQL 过程已成功完成。
导入分析信息
SQL> BEGIN
2 DBMS_STATS.import_table_stats(ownname => 'TEST',tabname => 'A',stattab => 'STAT_TABLE');
3 END;
4 /
PL/SQL 过程已成功完成。

Oracel dbms_jobs包可以提交作业到作业队列中，这个包中还提供其它一些函数管理以前提交的作业，如对作业进行修改、禁止或删除等操作。

常用函数：

submit()，提交作业到作业队列。

isumbit()，

remove()，从作业队列中删除作业。

change()，改变已有作业的参数。

what()，改变作业要执行的代码。

next_date()，改变作业下一次执行时间。

interval()，改变作业执行的时间间隔。

broken()，暂停执行作业。

run()，强制执行作业。

最常用的submit()函数：

dbms_jobs.submit(

      job out binary_integer,

      what in varchar2,

      next_date in date default sysdate,

      interval in varchar default null,

      no_parse in boolean default false,

      instance in binary_integer default any_instance,

      force in boolean default false

);

submit函数参数：

job，作业的作业号，是个输出参数，所以调用submit函数时，这个参数要指定一个已存在的变量。

what，作业要执行的代码，一般是存储过程。

next_date，下一次作业运行的日期。

interval，作业执行的间隔时间，这个参数默认值是null，也就是what指定的代码只在next_date这一时间执行一次。

no_parse，默认值是false，当参数值是false，提交作业时就对what指定的代码进行语法分析；当参数值是true，第一次运行what指定代码时才进行语法分析。

instance，指明运行作业的数据库实例。

force，如果是true,instance可以是任何正数；如果是false，instance指定的实例必须正在运行。

示例：

var jobno number;

begin

dbms_job.submit(:jobno,'p_xxx_taxis;',trunc(sysdate)+(18/24),'trunc(sysdate+1,''hh'')');

--每天18点执行

commit;

end;

上面这段话要在 命令窗口 下运行,如在pl/sql developer里面的-->新键-->命令窗口

这样就会在每天的18点执行存储过程p_xxx_taxis。

 

 [总结] Oracle表的分析统计

===========================================================

作者: tolywang(http://tolywang.itpub.net)
发表于:2008.09.03 15:19
分类: Oracle数据库管理 
出处：http://tolywang.itpub.net/post/48/470117
--------------------------------------------------------------- 

讨论一： 使用dbms_stats 还是analyze

自从Oracle8.1.5引入dbms_stats包， Oracle及专家们就推荐使用dbms_stats取代analyze。 理由如下：

1. dbms_stats可以并行分析

2. dbms_stats有自动分析的功能(alter table monitor )

3. analyze 分析统计信息的有些时候不准确

 

第1,2比较好理解，且第2点实际上在VLDB(Very Large Database)中是最吸引人的；3以前比较模糊，看了metalink236935.1 解释，analyze在分析Partition表的时候，有时候会计算出不准确的Global statistics 。 原因是dbms_stats会实在的去分析表全局统计信息（当指定参数）；而analyze是将表分区（局部）的statistics 汇总计算成表全局statistics ，可能导致误差。 没有分区表的情况下两个都可以使用(看个人习惯，当然也可以分区表使用dbms_stats, 其他使用analyze )。

 

不过在一些论坛上也有看到dbms_stats 分析之后出现统计数据不准确的情况，而且确实有bug 在dbms_stats 上(可能和版本有关，有待查明)，应该是少数情况，需要我们注意。 还有，一般不建议analyze 和dbms_stats 混用。 实验： 如果在分区表上用dbms_stats统计后，再使用 analyze table 来统计，就会出现表信息不被更新的问题。 删除统计信息后再分析就更新了，或者直接用dbms_stats分析。 dbms_stats 目前有遇到的bug例子如下： http://www.itpub.net/viewthread.php?tid=959290&highlight=dbms%5C_stats

 

dbms_stats包可以分析table、Index或者整个用户(schema)，数据库，可以并行分析。

不同版本包有些不一样， dbms_utility (8i以前的工具包)，dbms_stats (8i或以后提供的工具包) ，具体的dbms_stats 包的众多功能介绍见后面。

 

对命令与工具包的一些总结:

1、对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。

a) 可以并行进行，对多个用户，多个Table

b) 可以得到整个分区表的数据和单个分区的数据。

c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区

d) 可以导出统计信息

e) 可以用户自动收集统计信息(alter table monitor )

2、DBMS_STATS的缺点：

a) 不能Validate Structure (注意：validate structure 主要在于校验对象的有效性. compute statistics在于统计相关的信息) 。

b) 不能收集CHAINED ROWS(行链接), 不能收集CLUSTER TABLE(簇表)的信息，这两个仍旧需要使用Analyze语句。

c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True 。即GATHER_TABLE_STATS：分析表信息，当cascade为true时，分析表、列（索引）信息。

Analyze是同时更新表和索引的统计信息，而dbms_stats会先更新表的统计信息，然后再更新索引的统计信息(默认Cascade是False)，这里就有一个问题，就是当表的统计信息更新后，而索引的统计信息没有被更新，这时候cbo就有可能选择错误的plan 。

3、对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息。

 

Analyze 命令语法如下 ：

ANALYZE

{ TABLE [ schema.]table

[ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ]

| INDEX [ schema. ]index

[ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ]

| CLUSTER [ schema. ]cluster

}

{ COMPUTE [ SYSTEM ] STATISTICS [for_clause]

| ESTIMATE [ SYSTEM ] STATISTICS [for_clause][SAMPLE integer { ROWS | PERCENT }]

| validation_clauses

| LIST CHAINED ROWS [ into_clause ]

| DELETE [ SYSTEM ] STATISTICS

} ;

 

dbms_stats所有的功能包如下：

GATHER_INDEX_STATS：分析索引信息

GATHER_TABLE_STATS：分析表信息，当cascade为true时，分析表、列（索引）信息

GATHER_SCHEMA_STATS：分析方案信息

GATHER_DATABASE_STATS：分析数据库信息

GATHER_SYSTEM_STATS：分析系统信息

 

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：导入数据库分析信息

 

 

讨论二： analyze 的使用方法 (分区表建议使用dbms_stats)

 

可以参考 http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_46a.htm#SQLRF01105

 

Analyze 的三大功能：

搜集和删除索引、表和簇的统计信息 验证表、索引和簇的结构 鉴定表和簇的行迁移(migrated rows)和行链接(chained rows)

CBO是Oracle推荐使用的优化方式，要想使用好CBO，使SQL语句发挥最大效能，必须保证统计数据的及时性。统计信息的生成可以有完全计算法和抽样估算法。SQL例句如下：

完全计算法： analyze table abc compute statistics; 
抽样估算法(抽样20%)： analyze table abc estimate statistics sample 20 percent;

对表作完全计算所花的时间相当于做全表扫描，抽样估算法由于采用抽样，比完全计算法的生成统计速度要快，如果不是要求要有非常精确的数据的话，尽量采用抽样分析法。建议对表分析采用抽样估算，对索引分析可以采用完全计算。

Analyze 分析table, index等需要的权限： 必须在你自己的Schema(方案)中或者有ANALYZE ANY 系统权限 。

比如： grant analyze any to tolywang ;

revoke analyze any from tolywang ;

Analyze 使用的局限及改善：

Analyze 命令每次仅仅能影响到一个table(或index), 如果想通过analyze为整个schema或整个数据库中的所有表生成统计数字。可以使用analyze的批处理方式(脚本)。

Analyze 分析命令解析：

ANALYZE

{ TABLE [ schema.]table

[ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ]

| INDEX [ schema. ]index

[ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ]

| CLUSTER [ schema. ]cluster

}

{ COMPUTE [ SYSTEM ] STATISTICS [for_clause]

| ESTIMATE [ SYSTEM ] STATISTICS [for_clause][SAMPLE integer { ROWS | PERCENT }]

| validation_clauses

| LIST CHAINED ROWS [ into_clause ]

| DELETE [ SYSTEM ] STATISTICS

} ;

INDEX index： 对索引进行分析，分析的结果会放在USER_INDEXES, ALL_INDEXES,或 DBA_INDEXES中 。一般仅需要对索引进行统计时用到。

分析的内容：

Depth of the index from its root block to its leaf blocks (BLEVEL) 从索引的根块到其叶块的索引的深度(级数)。

Number of leaf blocks (LEAF_BLOCKS) 叶块的数量，这些块包括了指向表中及索引中行的指针。

Number of distinct index values (DISTINCT_KEYS) 不同索引值的数量 。

Average number of leaf blocks for each index value (AVG_LEAF_BLOCKS_PER_KEY) 包括每一个值的记录的叶块的平均数。

Average number of data blocks for each index value (for an index on a table) (AVG_DATA_BLOCKS_PER_KEY) 被一个索引值指向的数据块的平均数量。

Clustering factor (how well ordered the rows are about the indexed values) (CLUSTERING_FACTOR) 一个簇因子，表明了表中的行的顺序和索引中的顺序相匹配的紧密程度。

LAST_ANALYZED 为索引生成的统计数字的日期。

TABLE table：对表进行分析，分析的结果会放在USER_TABLES, ALL_TABLES 和DBA_TABLES视图中，当为表收集统计数字时，除非以别的方式指明，否则Oracle也为那个表中的索引收集统计数字。还有，在分析表的时候，oracle也会分析基于函数的index所引用的表达式。

分析table产生的内容 (在上面的几个视图列中可以找到)：

Number of rows (NUM_ROWS) * 表中行的数量 。

Number of data blocks below the high water mark (that is, the number of data blocks that have been formatted to receive data, regardless whether they currently contain data or are empty) (BLOCKS) 高水位一下的数据块数量(不管是否现在有数据还是空的) 。

* Number of data blocks allocated to the table that have never been used (EMPTY_BLOCKS) 分配给表但未被数据使用的数据块的数量。

Average available free space in each data block in bytes (AVG_SPACE) 在每一块中自由空间数量的平均值(以字节表示)。

Number of chained rows (CHAIN_CNT) 链接行的数量。

Average row length, including the row's overhead, in bytes (AVG_ROW_LEN) 在表中行的平均长度，以字节表示。

LAST_ANALYZED : 为表生成统计数据的日期。

分析表的限制：

不可以分析数据字典表

不可以分析扩展表，但可以用DBMS_STATS来实现这个目的

不可以分析临时表

不可以计算或估计下列字段类型：REFs, varrays, nested tables, LOBs (LOBs are not analyzed, they are skipped), LONGs, or object types.

分析分区表最好使用DBMS_STATS来实现 。

PARTITION | SUBPARTITION：对分区表或索引进行分析

CLUSTER cluster: 对簇进行分析，分析的结果会放在ALL_CLUSTERS, USER_CLUSTERS and DBA_CLUSTERS.

compute_statistics_clause

语法：COMPUTE [ SYSTEM ] STATISTICS [for_clause]

对分析对像进行精确的统计，然后把信息存储的数据字典中。可以选择对表或对字段进行分析。computed和estimated这两种方式的统计数据都被优化器用来影响sql的执行计划，如果指定system选项就只统计系统产生的信息 

转自 kelin1314
 http://www.cnblogs.com/kelin1314/archive/2012/02/16/2355047.html