Oracle 统计信息

【统计信息和DBMS_STATS包】

统计信息的重要性：仅知道要处理的SQL语句和它引用的对象结构，优化器还无法提供高效的执行计划，它还需要量化待处理的数据量，而统计信息就是用来提供这样的信息的。CBO会根据以上信息来估算多种路径访问的COST，并选择较低COST的执行计划，但是COST只是估算，很有可能出现较大偏差导致CBO选择实际性能更差的计划。
统计信息的分类：系统统计信息、对象统计信息。
DBMS_STATS包用来收集统计信息，简单的例子如下：
exec dbms_stats.gather_table_stats(user,’TBL’,cascade=>true)
9i之前使用analyze命令来收集统计信息，为了保持向后兼容，现在还可使用，但不推荐。

 

一。  Statistic 说明
    Statistic 对Oracle 是非常重要的。 它会收集数据库中对象的详细信息，并存储在相应的数据字典里。 根据这些统计信息， optimizer 可以对每个SQL 去选择最好的执行计划。
    在9i 及之前的版本，在选择执行计划的时候会根据RBO（Rule-BasedOptimization）或者CBO来分析。 10g及以后版本只支持CBO（Cost-BasedOptimization）。 这部分内容，参考第二节。

    优化器收集的统计信息包括如下内容：
    1）Table statistics
     Number of rows
     Number of blocks
     Average row length

    2）Column statistics
     Number of distinct values （NDV） in column
     Number of nulls in column
     Data distribution （histogram）
    3）Index statistics
     Number of leaf blocks
     Levels
     Clustering factor

    4）System statistics
     I/O performance and utilization
     CPU performance and utilization

    Oracle Statistic 的收集，可以使用analyze 命令，也可以使用DBMS_STATS 包来收集，Oracle 建议使用DBMS_STATS包来收集统计信息，因为DBMS_STATS包收集的更广，并且更准确。 analyze 在以后的版本中可能会被移除。

    有关DBMS_STATS包的使用，参考如下Blog 的第三节：
    Oracle 分析及动态采样
    http://blog.csdn.net/tianlesoftware/archive/2010/08/28/5845028.aspx

    analyze 命令的语法如下：
     SQL>analyze table tablename compute statistics;
     SQL>analyze table tablename compute statistics for all indexes;
     SQL>analyze table tablename delete statistics

    Oracle 的Statistic 信息的收集分两种：自动收集和手工收集。　在这里，我们看一下自动收集的部分。　其他内容参考Oracle 的联机文档。
    Oracle 的Automatic Statistics Gathering 是通过Scheduler 来实现收集和维护的。 Job 名称是GATHER_STATS_JOB, 该Job收集数据库所有对象的2种统计信息：
     （1）Missing statistics（统计信息缺失）
     （2）Stale statistics（统计信息陈旧）
    该Job 是在数据库创建的时候自动创建，并由Scheduler来管理。Scheduler 在maintenance windows open时运行gather job。 默认情况下，job 会在每天晚上10到早上6点和周末全天开启。该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级，再开始进行统计信息。
    Scheduler Job的 stop_on_window_close 属性控制GATHER_STATS_JOB 是否继续。该属性默认值为True. 如果该值设置为False，那么GATHER_STATS_JOB 会中断， 而没有收集完的对象将在下次启动时继续收集。

    Gather_stats_job 调用dbms_stats.gather_database_stats_job_proc过程来收集statistics 的信息。 该过程收集对象statistics的条件如下：
     （1）对象的统计信息之前没有收集过。
     （2）当对象有超过10%的rows 被修改，此时对象的统计信息也称为stale statistics。

    查看该Job 信息：
    SQL> select job_name, program_name,enabled,stop_on_window_close  from dba_scheduler_jobs where job_name = 'gather_stats_job';

     job_name                  program_name   enabl  stop_on_window_close
     -------------------- --------------------  ----- --------------------
     gather_stats_job  gather_stats_prog    true  true

    监控参数 STATISTICS_LEVEL：
    为了决定是否对对象进行监控，Oracle 提供了一个参数STATISTICS_LEVEL。
    通过设置初始化参数 STATISTIC_LEVEL 为 TYPICAL 或 ALL，就可以自动收集统计信息（默认值为 TYPICAL，因此可以随即启用自动收集统计信息的功能）。STATISTIC_LEVEL 参数的值可以激活GATHER_STATS_JOB。

    在10g中表监控默认是激活的，如果STATISTICS_LEVEL设置为basic，不仅不能监控表，而且将禁掉如下一些10g的新功能：
     （1）ASH（Active Session History）
     （2）ASSM（Automatic Shared Memory Management）
     （3）AWR（Automatic Workload Repository）
     （4）ADDM（Automatic Database Diagnostic Monitor）

      SQL> show parameter statistics_level
      NAME            TYPE        VALUE
      ------------------------- ----------- ------------------------------
      statistics_level      string      TYPICAL

    当启动对象的监控后，从上次统计信息收集之后的的信息，如inserts，updates，deletes 等，这些改变的信息会记录到 user_tab_modifications 视图。
    当对象的数据发生改变之后， 经过几分钟的延时，这些信息写入到user_tab_modifications视图，然后dbms_stats.flush_database_monitoring_info过程就会发现这些信息，并讲这些信息保存在内存中。
    当监控的对象被修改的部分超过10%时， gather_database_stats 或者 gather_schema_stats 过程就会去收集这些stale statistics.

    停用该Job：
    默认情况下，该Job是enable的。统计信息的收集是资源相当密集的工作，所以在某些情况下，就不希望它自动去收集，而考虑选择手动来收集。 这中情况下，我们可以设置statistics_level 为Basic，禁用对对象的监控，但是这样也会禁用AWR等信息的收集，这种情况下，我们就可以使用可以使用如下语句：
     BEGIN
     DBMS_SCHEDULER.DISABLE（'GATHER_STATS_JOB'）；
     END;
     /

    系统用户统计信息的收集：
    如果想收集system schemas的统计信息，可以使用dbms_stats.gather_dictionary_stats过程。 该过程会收集所有system schemas，包括SYS和SYSTEM，和一些其他的chemas，如CTXSYS和 DRSYS.
    DBMS_STATS 包里的statistics 过程：
     Procedure
     Collects
     GATHER_INDEX_STATS
     Index statistics
     GATHER_TABLE_STATS
     Table, column, and index statistics
     GATHER_SCHEMA_STATS
     Statistics for all objects in a schema
     GATHER_DICTIONARY_STATS
     Statistics for all dictionary objects
     GATHER_DATABASE_STATS
     Statistics for all objects in a database

    有关统计信息的其他内容，比如统计信息的删除，锁定，还原等参考Blog：
    Oracle 分析及动态采样
    http://blog.csdn.net/tianlesoftware/archive/2010/08/28/5845028.aspx

 

    二。  CBO 与 RBO
    有关CBO 与 RBO 的内容，参考：
    Oracle Optimizer CBO RBO
    http://blog.csdn.net/tianlesoftware/archive/2010/08/19/5824886.aspx

    RBO 在Oracle 9i 及之前的版本使用。
    CBO 在Oracle 10g及以后的版本中使用。

    在这里提一下几点注意的地方：
    （1） 执行计划中的 Cost的计算方式默认为CPU+I/O两者之和。 所以一般我们看执行计划是，cost 越低，SQL 的性能就越好。
    （2）Oracle使用Optimizer_mode参数来控制优化器的偏好：

    9i常用的参数有：first_rows,all_rows,first_rows_n,rule,choose。
    10g和11g: 就只有first_rows,all_rows,first_rows_n，少了rule和choose。

    Oracle 在10g及以后的版本已经不支持RBO了。所以这里RBO对应的模式也取消了。

    各种Mode 说明：
    Rule
 基于规则的方式
    Choose
 指的是当一个表或或索引有统计信息，则走CBO的方式，如果表或索引没统计信息，表又不是特别的小，而且相应的列有索引时，那么就走索引，走RBO的方式。
    First Rows
 The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

    All Rows
 10g和11g中的默认值，The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput （minimum resource use to complete the entire statement）。

    first_rows_n
 The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows （where n = 1, 10, 100, 1000）。

    修改optimizer_mode：
 sql>alter session set optimizer_mode=first_rows/all_rows

    （3）OPTIMIZER_INDEX_COST_ADJ参数
 参数OPTIMIZER_INDEX_COST_ADJ可以理解为Oracle执行多块（MultiBlock）I/O（比如全表扫描）的代价与执行单块（Single-block）I/O代价的相对比例。
    OPTIMIZER_INDEX_COST_ADJ通过指明索引I/O代价与扫描全表I/O代价的相对比值来影响CBO的行为，取值越小，CBO越倾向于使用索引，取值越大，越倾向于全表扫描。而缺省值100，指明缺省下，二者的代价是相等。

    该参数从某种意义上来说可以决定使用使用索引，如果统计信息准确，但是执行计划又没走索引，可以将该值调小一点。

    OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.
    The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.

    注意：
    9i中，如果相关的segment（表段，索引段等）没有做统计分析的时候，会选择走RBO。
    10g以后版本，如果segment 没有统计信息，那么Oracle 会使用动态采样来收集统计的信息。 这个信息不是很准确，但也可以提供一定的参考。

    有关动态采样的信息参考：
    Oracle 分析及动态采样
    http://blog.csdn.net/tianlesoftware/archive/2010/08/28/5845028.aspx

    三。  统计信息的存储位置
    统计信息收集如下数据：
    （1）表自身的分析： 包括表中的行数，数据块数，行长等信息。
    （2）列的分析：包括列值的重复数，列上的空值，数据在列上的分布情况。
    （3）索引的分析： 包括索引叶块的数量，索引的深度，索引的聚合因子等。

    这些统计信息存放在数据字典里，如：
    （1）。  DBA_TABLES
    （2）。  DBA_OBJECT_TABLES
    （3）。  DBA_TAB_STATISTICS
    （4）。  DBA_TAB_COL_STATISTICS
    （5）。  DBA_TAB_HISTOGRAMS
    （6）。  DBA_INDEXES
    （7）。  DBA_IND_STATISTICS
    （8）。  DBA_CLUSTERS
    （9）。  DBA_TAB_PARTITIONS
    （10）。 DBA_TAB_SUBPARTITIONS
    （11）。 DBA_IND_PARTITIONS
    （12）。 DBA_IND_SUBPARTITIONS
    （13）。 DBA_PART_COL_STATISTICS
    （14）。 DBA_PART_HISTOGRAMS
    （15）。 DBA_SUBPART_COL_STATISTICS
    （16）。 DBA_SUBPART_HISTOGRAMS

    3.1    表的统计信息：
    包含表行数，使用的块数，空的块数，块的使用率，行迁移和链接的数量，pctfree，pctused的数据，行的平均大小：
    SELECT  NUM_ROWS, --表中的记录数
     BLOCKS, --表中数据所占的数据块数
     EMPTY_BLOCKS, --表中的空块数
     AVG_SPACE, --数据块中平均的使用空间
     CHAIN_CNT, --表中行连接和行迁移的数量
     AVG_ROW_LEN --每条记录的平均长度
    FROM USER_TABLES

    3.2    索引列的统计信息
    包含索引的深度（B-Tree的级别），索引叶级的块数量，集群因子（clustering_factor）， 唯一值的个数。
    SELECT  BLEVEL, --索引的层数
     LEAF_BLOCKS, --叶子结点的个数
     DISTINCT_KEYS, --唯一值的个数
     AVG_LEAF_BLOCKS_PER_KEY, --每个KEY的平均叶块个数
     AVG_DATA_BLOCKS_PER_KEY, --每个KEY的平均数据块个数
     CLUSTERING_FACTOR --群集因子
    FROM USER_INDEXES

    3.3    列的统计信息
    包含 唯一的值个数，列最大小值，密度（选择率），数据分布（直方图信息），NUll值个数
    SELECT  NUM_DISTINCT, --唯一值的个数
     LOW_VALUE, --列上的最小值
     HIGH_VALUE, --列上的最大值
     DENSITY, --选择率因子（密度）
     NUM_NULLS, --空值的个数
     NUM_BUCKETS, --直方图的BUCKET个数
     HISTOGRAM --直方图的类型
    FROM USER_TAB_COLUMNS

    3.4 测试：表只有分析了之后，num_rows才会有值
    SQL> create table dba as select * from dba_objects;
 Table created.

    SQL> select count（*） from dba;
     COUNT（*）
     ----------
     50123

    SQL> select table_name,num_rows from dba_tables where table_name='DBA';
     TABLE_NAME          NUM_ROWS
     ------------------------------ ----------
     DBA
    --注意，这里为的num_rows 为空。 分析之后就有了

    SQL> exec dbms_stats.gather_table_stats（'SYS','DBA'）；
 PL/SQL procedure successfully completed.

    SQL> select table_name,num_rows from dba_tables where table_name='DBA';
     TABLE_NAME         NUM_ROWS
     ------------------------------ ----------
     DBA                  50123

    所以，判断统计信息是否准确，只需要比较一下表记录的count 和 num_rows 值就知道了。 如果是一致的，就说明是最新的，如果不一致，可能就需要分析了。

    四。  直方图（histograms）
    DBMS_STATS 包对段表的分析有三个层次：
     （1）表自身的分析： 包括表中的行数，数据块数，行长等信息。
     （2）列的分析：包括列值的重复数，列上的空值，数据在列上的分布情况。
     （3）索引的分析： 包括索引叶块的数量，索引的深度，索引的聚合因子等。
    默认情况下，在收集表的统计信息信息时，对列信息的收集是FOR ALL COLUMNS SIZE AUTO，这种情况下直方图的信息可能没有收集到，所以可以手工指定收集直方图的信息：
 exec DBMS_STATS.GATHER_table_STATS（OWNNAME => 'ICD', TABNAME => 'TAGENTOPRINFO',  METHOD_OPT =>'FOR all COLUMNS'）；

    4.1 直方图上列的信息说明
    直方图就是列分析中 数据在列上的分布情况。可以使用如下SQL 查看表列上的直方图信息。
    [sql] view plaincopyprint?
    /* Formatted on 2011/11/25 12:51:33（QP5 v5.185.11230.41888） */
    SELECT column_name AS "NAME",
     num_distinct AS "#DST",
     low_value,
     high_value,
     density AS "DENS",
     num_nulls AS "#NULL",
     avg_col_len AS "AVGLEN",
     histogram,
     num_buckets AS "#BKT"
    FROM user_tab_col_statistics
    WHERE table_name = 'T';

    /* Formatted on 2011/11/25 12:51:33（QP5 v5.185.11230.41888） */ SELECT column_name AS "NAME", num_distinct AS "#DST", low_value, high_value, density AS "DENS", num_nulls AS "#NULL", avg_col_len AS "AVGLEN", histogram, num_buckets AS "#BKT" FROM user_tab_col_statistics WHERE table_name = 'T';

    相关字段的解释如下：
    num_distinct：该列中唯一值的数量
    low_value：该列的最小值，显示内部存储格式
    high_value：该列的最大值，显示内部存储格式
    num_nulls：该列中存储的null的总数
    avg_col_len：平均列大小，以字节表示
    histogram：表明是否有直方图统计信息，如果有，是哪种类型。
    NONE表示没有，
    frequency表示频率类型，
    height balanced表示平均分布类型，此列在10g以后提供
    num_buckets：直方图里的桶数，统计信息中所谓的桶或类，就是一组同类的数值放在一起。直方图至少由一个桶组成。如果没有直方图，桶数为1.最大桶数是254

    这里重点看一些density列，在asktom论坛上有一篇帖子说明。
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2969235095639
    Density is acolumn statistic and provides selectivity estimates for equi-join predicates（e.g. and A.COL1 = B.COL1） and equality predicates （e.g. COL1 = 5）。

    --Density列的统计信息用来估计   equi-join predicates和 equality predicates的选择性。
    The density is expressed as a decimalnumber between 0 and 1.
    Values close to 1 indicate that this columnis unselective
    Values close to 0 indicate that this columnis highly selective

    --density 用0到1之间的一个小数来表达。 当其值越接近与1，代表的该列的选择性越差，越接近与0，代表该列的选择性越高。
    The more selective a column, the less rowsare likely to be returned by a query referencing this column in its predicatelist.

    --当列的选择性越高，那么谓词查询时返回的rows 越少，这样查询的效率就高。
    The column selectivity is part of theequation used to decide on the best path for a query to take to retrieve thedata required in the most effective manner and hence impacts the final costvalue for the query.

    --列的选择性是用来决定执行计划的一个重要部分。
    Density is calculated as follows:
    Density 的计算方式有2种：
    Pre 7.3
    ~~~~~~~

    Oracle 7.3 之前的计算公式如下：
    Density = 1 / Number of distinct NON null values
   
    --注意这里是非空的distinct
    The number ofdistinct NON-null values for a column （COL1） on table TABLE1 can be obtained as follows:
   
    --非空值可以通过如下SQL 查询：
    [sql] view plaincopyprint?
    select distinct count（COL1）
    from  TABLE1
    where COL1 is not null;
    select distinct count（COL1） from TABLE1 where COL1 is not null;
    7.3+
    ~~~~

    Oracle 7.3 之后的density计算方式：
    The Density calculation has been refined bythe use of histograms. If you have created histograms on your columns we cannow use the histogram information to give more accurate information. Otherwisethe Density is calculated as before. With histograms we can use information on popularand non-popular values to determine the selectivity.

    --在7.3 之后的density 计算方法有所改变，当列上已经建立了直方图，俺么可以根据直方图的信息，得到更准确的信息，如果没有直方图，还按照Oracle 7.3 之前的计算方法。

    根据选择性，可以使用popular 和 non-popular 值来表示density值。
    A non-popular value is one that does notspan multiple bucket end points.
    A popular value is one that spans multipleend points.
    （Refer to <Note:50750.1> for detailson histograms）
    For non-popularvalues the density is calculated as the number of non-popular
    values divided by the total number ofvalues. Formula:
   
    --non-popular 的计算方法如下：
    Density =  Number of non-popularvalues
    ----------------------------
    total number of values
    We only use the density statistic fornon-popular values.

    Popular values calculate the selectivity ofa particular column values by using histograms as follows:
   
    --popular 值的计算方法如下：
    The Selectivityfor popular values is calculated as the number of end points spanned by thatvalue divided by the total number of end points. Formula:

    Selectivity = Number of end points spanned by this value
    ------------------------------------------
    total number of endpoints

    4.2 直方图类型说明
    当Oracle 做直方图分析时，会将要分析的列上的数据分成很多数量相同的部分，每一部分称为一个bucket,这样CBO就可以非常容易地知道这个列上的数的分布情况，这种数据的分布将作为一个非常重要的因素纳入到执行计划成本的计算当中。
    Oracle 有两种类型的直方图： height-balanced histograms and frequency histograms.
    直方图的信息存储在DBA_TAB_COL_STATISTICS和USER_ TAB_COL_STATISTICS 视图中。
    视图中的histogram列有如下三种值： HEIGHT BALANCED, FREQUENCY, NONE.

    4.2.1  Height-Balanced Histograms
    在高度平衡的直方图中， 列的值被分入一些bands中，每个band 包含差不多的rows数。这个band 也称为bucket。 即在一个bucket内，记录基本上是一样的。
    假如一个列ID的值是1到100，histogram 有10个buckets。
    如果数据是均匀分布的，那么它的直方图就类似与：
    每个bucket中的记录数都是表中总数的十分之一。
    如果数据是非均匀分布的，那么它的直方图就类似与：
    在这个实例中，绝大多数的记录值都是5. 只有1/10的值在60到100之间。

    测试：
    SQL> create table bhh（id number）；
    SQL> declare
     2  i number;
     3  begin
     4  for i in 1100 loop
     5     insert into bhh values（i）；
     6  end loop;
     7  end;
     8  /
    PL/SQL procedure successfully completed.

    SQL> commit;
    Commit complete.

    SQL> select count（*） from bhh;
     COUNT（*）
     ----------
     100

    --默认情况下，在收集表的统计信息信息时，是FOR ALL COLUMNS SIZE AUTO

    这里为了显示效果，我们手工指定收集列的信息
    exec  DBMS_STATS.GATHER_table_STATS （OWNNAME => 'SYS', TABNAME => 'BHH', METHOD_OPT => 'FOR COLUMNS SIZE 10 ID'）；

    --10是我们buckets的数量，ID 是我们要收集列的名称。
    SQL> SELECT column_name, num_distinct, num_buckets, histogram
    2    FROM DBA_TAB_COL_STATISTICS  WHERE table_name = 'BHH' AND column_name = 'ID';

    COLUMN_NAME     NUM_DISTINCT           NUM_BUCKETS     HISTOGRAM
    ----------------------------- ------------ ----------- ---------------
    ID                   100       10     HEIGHT BALANCED

    SQL> SELECT endpoint_number, endpoint_value
    2    FROM USER_HISTOGRAMS
    3   WHERE table_name = 'BHH' and column_name = 'ID'
    ORDER BY endpoint_number;

    ENDPOINT_NUMBER ENDPOINT_VALUE
    --------------- --------------
    0              1
    1             11
    2 rows selected.

    4.2.2  Frequency Histograms

    在Frequency Histograms 里，每个bucket 只包含一个记录。 因此当表记录中distinct values 小于等于histograms buckets时，会创建Frequency histograms 会自动创建。

    示例：
    SQL> create table fh（id number）；
    Table created.

    SQL> declare
     2  i number;
     3  j number;
     4  begin
     5  for i in 110 loop
     6  for j in 110 loop
     7  insert into fh values（j）；
     8  end loop;
     9  end loop;
     10  end;
     11  /
    PL/SQL procedure successfully completed.

    SQL> select count（*） from fh;
    COUNT（*）
    ----------
    100

    SQL> BEGIN
    DBMS_STATS.GATHER_table_STATS （OWNNAME => 'SYS', TABNAME => 'FH',  METHOD_OPT => 'FOR COLUMNS SIZE 100 ID'）；
    END;
    /

    --创建100个bucket，这样每个bucket 就只有一个row了。
    SQL> SELECT column_name, num_distinct, num_buckets, histogram
     2    FROM USER_TAB_COL_STATISTICS
     3   WHERE table_name = 'FH' AND column_name = 'ID';

    COLUMN_NAME     NUM_DISTINCT            NUM_BUCKETS   HISTOGRAM
    ------------------------------ ------------ ----------- ---------------
    ID                   10          10  FREQUENCY
   
    SQL> SELECT endpoint_number, endpoint_value
     2    FROM USER_HISTOGRAMS
     3   WHERE table_name = 'FH' and column_name = 'ID'
    ORDER BY endpoint_number;

    直方图有时对于CBO非常重要，特别是对于有字段数据非常倾斜的表，做直方图分析尤为重要。 可以用dbms_stats包来分析。 默认情况下，dbms_stats 包会对所有的列做直方图分析。

    如果一个列上的数据有比较严重的倾斜，对这个列做直方图是必要的，但是，Oracle 对数据分析是需要消耗资源的，特别是对于一些很大的段对象，分析的时间尤其长。对于OLAP系统，可能需要几个小时才能完成。

    所以做不做分析就需要DBA 权衡好了。 但有一点要注意， 不要在生产环境中随便修改分析方案，除非你有十足的把握。 否则可能导致非常严重的后果。

    通常情况下当BUCTET < 表的NUM_DISTINCT值得到的是HEIGHT BALANCED（高度平衡）直方图，而当BUCTET = 表的NUM_DISTINCT值的时候得到的是FREQUENCY（频率）直方图。

    由于满足BUCTET = 表的NUM_DISTINCT值概率较低，所以在Oracle中生成的直方图大部分是HEIGHT BALANCED（高度平衡）直方图。

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

Oracle10g的在线文档《Managing Optimizer Statistics》这一章写的比较经典，虽然关键词、知识点很多，但整个文章结构条理化非常清晰，特别是没有复杂的英文单词，总结一下以备后用。

1. 统计信息
Oracle10g里的统计信息按照object和system分类如下:
•Object statistics
 ◦Table statistics
 ◦Column statistics
 ◦Index statistics
•System statistics
 ◦I/O performance and utilization
 ◦CPU performance and utilization

2. Object的统计信息
Oracle里每个object的统计信息类型如下：
•Table statistics
 ◦Number of rows
 ◦Number of blocks
 ◦Average row length
•Column statistics
 ◦Number of distinct values (NDV) in column
 ◦Number of nulls in column
 ◦Data distribution (histogram)
•Index statistics
 ◦Number of leaf blocks
 ◦Levels
 ◦Clustering factor

系统级别的统计信息主要是性能相关的信息，如下：
•System statistics
 ◦I/O performance and utilization
 ◦CPU performance and utilization

3. 获得统计信息的途径
可以通过以下表或视图查询统计信息
 •DBA_TABLES
 •DBA_OBJECT_TABLES
 •DBA_TAB_STATISTICS
 •DBA_TAB_COL_STATISTICS
 •DBA_TAB_HISTOGRAMS
 •DBA_INDEXES
 •DBA_IND_STATISTICS
 •DBA_CLUSTERS
 •DBA_TAB_PARTITIONS
 •DBA_TAB_SUBPARTITIONS
 •DBA_IND_PARTITIONS
 •DBA_IND_SUBPARTITIONS
 •DBA_PART_COL_STATISTICS
 •DBA_PART_HISTOGRAMS
 •DBA_SUBPART_COL_STATISTICS
 •DBA_SUBPART_HISTOGRAMS
上面这些记忆性的东西是OCP认证考试喜欢挑选的一些知识点！死记硬背确实有点多，平时用的时候查一下资料就行了。

4.自动收集统计信息
Oracle的GATHER_STATS_JOB会自动收集所有object的统计信息，创建数据库的时候自动创建GATHER_STATS_JOB。
Oracle是否自动收集统计信息涉及到一个重要参数STATISTICS_LEVEL，需要注意：
 •STATISTICS_LEVEL=TYPICAL/ALL，Oracle自动检测统计信息过期并收集
 •STATISTICS_LEVEL=BASIC，Oracle不再自动收集统计信息
 •默认设置为TYPICAL，设置ALL收集所有相关的统计信息
设置statistics_level参数后，当object一下状态时，GATHER_STATS_JOB会自动收集统计信息:
 •object没有统计信息
 •object统计信息过旧
默认，GATHER_STATS_JOB定制每天晚上22点至凌晨6点运行，周末2天全天运行。stop_on_window_close属性控制JOB的窗口运行状态，默认关闭运行窗口，JOB停止收集统计信息。

常用命令：
---------------------------------------------------------------------------------------------------
查看GATHER_STATS_JOB
 SQL> SELECT owner, job_name, enabled, state FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
停止GATHER_STATS_JOB
 SQL>EXEC DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
打开GATHER_STATS_JOB
 SQL>EXEC DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');
---------------------------------------------------------------------------------------------------

5. 手工收集统计信息
一般来说，对于温和增长的数据变化，Oracle的自动收集统计信息功能足以满足需要。什么时候需要收手动集统计信息呢？当出现以下情况时，都需要重新收集statistics:
 •某些表在自动统计后被delete、truncate或是rebuild
 •数据增量超过10%，旧的统计信息将不再有效
简单地说就是当数据发生明显改变时，不建议使用automagic statistics gathering，需要根据业务压力和需要手工进行统计信息的更新。

常用命令。使用DBMS_STATS包，实现各个Object统计信息的update、delete、export、import。
 Procedure Collects Statistics
 GATHER_INDEX_STATS Index statistics
 GATHER_TABLE_STATS Table, column, and index statistics
 GATHER_SCHEMA_STATS Statistics for all objects in a schema
 GATHER_DICTIONARY_STATS Statistics for all dictionary objects
 GATHER_DATABASE_STATS Statistics for all objects in a database

6. USER_TAB_MODIFICATIONS
USER_TAB_MODIFICATIONS表记录了数据库里被监控表里的DML操作次数，记录更新不够及时，可以使用Oracle提供的存储过程进行刷新。
---------------------------------------------------------------------------------------------------
查看table的DML操作次数
 sql>select table_name,inserts,updates,deletes from user_tab_modifications;
刷新user_tab_modifications的统计信息
 sql>exec dbms_stats.flush_database_monitoring_info;
清空user_tab_modifications里表zhangp的统计信息
 sql>analyze table zhangp compute statistics;
---------------------------------------------------------------------------------------------------

Oracle10g的统计信息功能并不是很稳定。我们的生产数据库从9i升级到10g后，就是没有充分考虑到gather statistics的功能和策略上的变化，导致OLTP系统晚上做报表分析时速度巨慢，sql语句的执行计划异常，影响到正常的数据分析。所以对于采用Oracle10g版本的数据库用户，无论采用哪种方式统计信息时一定要慎重测试。

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

优化器统计范围：
 表统计； --行数，块数，行平均长度；all_tables：NUM_ROWS，BLOCKS，AVG_ROW_LEN；
 列统计； --列中唯一值的数量（NDV），NULL值的数量，数据分布；
       --DBA_TAB_COLUMNS：NUM_DISTINCT，NUM_NULLS，HISTOGRAM；
 索引统计；--叶块数量，等级，聚簇因子；
       --DBA_INDEXES：LEAF_BLOCKS，CLUSTERING_FACTOR，BLEVEL；
 系统统计；--I/O性能与使用率；
       --CPU性能与使用率；
       --存储在aux_stats$中，需要使用dbms_stats收集，I/O统计在X$KCFIO中；

analyze命令：
需要使用ANALYZE统计的统计：
使用LIST CHAINED ROWS和VALIDATE子句；
收集空闲列表块的统计；
 Analyze table tablename compute statistics;
 Analyze index|cluster indexname estimate statistics;
 ANALYZE TABLE tablename COMPUTE STATISTICS
 FOR TABLE
 FOR ALL [LOCAL] INDEXES
 FOR ALL [INDEXED] COLUMNS;
 ANALYZE TABLE tablename DELETE STATISTICS
 ANALYZE TABLE tablename VALIDATE REF UPDATE
 ANALYZE TABLE tablename VALIDATE STRUCTURE [CASCADE]|[INTO TableName]
 ANALYZE TABLE tablename LIST CHAINED ROWS [INTO TableName]
 ANALYZE 不适合做分区表的分析

dbms_stats包：
dbms_stats能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。
这个包的下面四个存储过程分别收集index、table、schema、database的统计信息:
 dbms_stats.gather_table_stats     收集表、列和索引的统计信息；
 dbms_stats.gather_schema_stats    收集SCHEMA下所有对象的统计信息；
 dbms_stats.gather_index_stats     收集索引的统计信息；
 dbms_stats.gather_system_stats    收集系统统计信息
 dbms_stats.GATHER_DICTIONARY_STATS： 所有字典对象的统计；
 DBMS_STATS.GATHER_DICTIONARY_STATS 其收集所有系统模式的统计

 dbms_stats.delete_table_stats     删除表的统计信息
 dbms_stats.delete_index_stats     删除索引的统计信息
 dbms_stats.export_table_stats     输出表的统计信息
 dbms_stats.create_state_table
 dbms_stats.set_table_stats     设置表的统计
 dbms_stats.auto_sample_size

统计收集的权限
必须授予普通用户权限
 sys@ORADB> grant execute_catalog_role to hr;
 sys@ORADB> grant connect,resource,analyze any to hr;

统计收集的时间考虑
当参数STATISTICS_LEVEL设置为TYPICAL或者ALL，系统会在夜间自动收集统计信息。
查看系统自动收集统计信息的job：
 SELECT * FROM dba_scheduler_jobs WHERE job_name = 'GATHER_STATS_JOB';
也可以disable自动收集统计信息：
 BEGIN
 dbms_scheduler.disable('GATHER_STATS_JOB');
 END;

使用手工统计
对所有更改活动中等的对象自动统计应该足够充分，由于自动统计收集在夜间进行，因此对于一些更新频繁的对象其统计可能已经过期。两种典型的对象：
 高度变化的表在白天的活动期间被TRUNCATE/DROP并重建；
 块加载超过本身总大小10%的对象；

对于第一种对象可以使用以下两种方法：
 1 将这些表上的统计设置为NULL，当Oracle遇到没有统计的表时，将动态收集必要的统计作为查询优化的一部分；
 动态收集特征由OPTIMIZER_DYNAMIC_SAMPLING控制，这个参数应该设置为大于等于2，默认为2。可以通过删除并锁住统计将统计设置为NULL：
  DBMS_STATS.DELETE_TABLE_STATS('SCHEMA','TABLE');
  DBMS_STATS.LOCK_TABLE_STATS('SCHEMA','TABLE');

 2 将这些表上的统计设置为代表表典型状态的值。在表具有某个有代表性的值时收集统计，然后锁住统计；
  由于夜间收集的统计未必适合于白天的负载，因此这些情况下使用手工收集比GATHER_STATS_JOB更有效。
  对于块加载，统计应该在加载后立刻收集，通常合并在加载语句的后面防止遗忘。
  对于外部表，统计不能通过GATHER_DATABASE_STATS，GATHER_SCHEMA_STATS以及自动统计收集收集。因此需要使用GATHER_TABLE_STATS在单个表上收集统计，并且在外部表上不支持取样，ESTIMATE_PERCENT应该被显示设置为NULL。
  如果STATISTICS_LEVEL设置为BASIC禁用了监控特征，自动统计收集将不会检测过期的统计，此时需要手工收集。

 3 需要手工收集的另一个地方是系统统计，其不会自动收集。
  对于固定表，如动态性能表，需要使用GATHER_FIXED_OBJECTS_STATS收集，这些表上的统计应该在数据库具有有代表性的活动后收集。

统计收集考虑
1 统计收集使用取样
 不使用抽样的统计收集需要全表扫描并且排序整个表，抽样最小化收集统计的必要资源。
 Oracle推荐设置DBMS_STATS的ESTIMATE_PERCENT参数为DBMS_STATS.AUTO_SAMPLE_SIZE在达到必要的统计精确性的同时最大化性能。
2 并行统计收集
 Oracle推荐设置DBMS_STATS的DEGREE参数为DBMS_STATS.AUTO_DEGREE，该参数允许Oracle根据对象的大小和并行性初始化参数的设置选择恰当的并行度。
 聚簇索引，域索引，位图连接索引不能并行收集。
3 分区对象的统计收集
 对于分区表和索引，DBMS_STATS可以收集单独分区的统计和全局分区，对于组合分区，可以收集子分区，分区，表/索引上的统计，分区统计的收集可以通过声明参数GRANULARITY。根据将优化的SQL语句，优化器可以选择使用分区统计或全局统计，对于大多数系统这两种统计都是很重要的，Oracle推荐将GRANULARITY设置为AUTO同时收集全部信息。
4 列统计和直方图
 当在表上收集统计时，DBMS_STATS收集表中列的数据分布的信息，数据分布最基本的信息是最大值和最小值，但是如果数据分布是倾斜的，这种级别的统计对于优化器来说不够的，对于倾斜的数据分布，直方图通常用来作为列统计的一部分。
 直方图通过METHOD_OPT参数声明，Oracle推荐设置METHOD_OPT为FOR ALL COLUMNS SIZE AUTO，使用该值时Oracle自动决定需要直方图的列以及每个直方图的桶数。也可以手工设置需要直方图的列以及桶数。
 如果在使用DBMS_STATS的时候需要删除表中的所有行，需要使用TRUNCATE代替drop/create，否则自动统计收集特征使用的负载信息以及RESTORE_*_STATS使用的保存的统计历史将丢失。这些特征将无法正常发挥作用。
5 确定过期的统计
 对于那些随着时间更改的对象必须周期性收集统计，为了确定过期的统计，Oracle提供了一个表监控这些更改，这些监控默认情况下在STATISTICS_LEVEL为TYPICAL/ALL时启用，该表为USER_TAB_MODIFICATIONS。使用DBMS_STATS.FLUSH_DATABASE _MONITORING_INFO可以立刻反映内存中超过监控的信息。在OPTIONS参数设置为GATHER STALE or GATHER AUTO时，DBMS_STATS收集过期统计的对象的统计。
6 用户定义统计
 在创建了基于索引的统计后，应该在表上收集新的列统计，这可以通过调用过程设置METHOD_OPT的FOR ALL HIDDEN COLUMNS。
7 何时收集统计
 对于增量更改的表，可能每个月/每周只需要收集一次，而对于加载后表，通常在加载脚本中增加收集统计的脚本。对于分区表，如果仅仅是一个分区有了较大改动，只需要收集一个分区的统计，但是收集整个表的分区也是必要的。

系统统计
系统统计描述系统硬件的特征，包括I/O和CPU。在选择执行计划时，优化器考虑查询所需的CPU和I/O代价。系统统计允许优化器更加精确的评价CPU和IO代价，选择更好的查询计划。
使用DBMS_STATS.GATHER_SYSTEM_STATS收集系统统计，Oracle推荐收集系统统计。收集系统统计需要DBA权限。
收集的优化器系统统计包括：
 cpuspeedNW：代表无负载CPU速度，CPU速度为每秒钟CPU周期数；通过设置gathering_mode = NOWORKLOAD或手工设置统计；单位Millions/sec。
 ioseektim：I/O查找时间=查找时间+延迟时间+OS负载时间；通过设置gathering_mode = NOWORKLOAD或手工设置统计；单位为ms。
 Iotfrspeed：I/O传输速度；通过设置gathering_mode = NOWORKLOAD或手工设置统计；单位为Bytes/ms.
 Cpuspeed：代表有负载CPU速度，CPU速度为每秒钟CPU周期数；通过设置gathering_mode =NOWORKLOAD,INTERVAL, START|STOP或手工设置统计；单位Millions/sec。
 Maxthr：最大I/O吞吐量；通过设置gathering_mode =NOWORKLOAD,INTERVAL, START|STOP或手工设置统计；单位Bytes/sec.
 Slavethr：服务I/O吞吐量是平均并行服务I/O吞吐量；通过设置gathering_mode = INTERVAL，START|STOP或手工设置统计；Bytes/sec.
 Sreadtim：随机读取单块的平均时间；通过设置gathering_mode =INTERVAL，START|STOP或手工设置统计；单位为ms。
 Mreadtim：顺序读取多块的平均时间，通过设置通过设置gathering_mode = INTERVAL，START|STOP或手工设置统计；单位为ms。
 Mbrc: 多块读平均每次读取的块数量；通过设置通过设置gathering_mode = INTERVAL，START|STOP或手工设置统计；单位为blocks。
系统统计的重新收集不会导致当前的SQL无效，只是所有的新SQL语句使用新的统计。

Oracle提供两个选项收集统计：负载统计；非负载统计。

负载统计
在负载窗口的开始运行dbms_stats.gather_system_stats(’start’)，然后运行dbms_stats.gather_system_stats(’stop’)结束负载窗口。
运行dbms_stats.gather_system_stats(’interval’, interval=>N)，N表示N分钟后系统统计收集结束。
运行dbms_stats.delete_system_stats()删除负载统计。

非负载统计
运行不带参数的dbms_stats.gather_system_stats()收集非负载统计，运行非负载统计时会有一定的I/O负载。在某些情况下，非负载统计的值可能会保持默认，此时需要使用dbms_stats.set_system_stats设置。

管理统计
转储先前版本的统计
使用RESTORE过程转储先前版本的统计，这些过程使用一个时间戳作为参数，包含统计时间的视图包括：
 1 DBA_OPTSTAT_OPERATIONS：其中包含了使用DBMS_STATS在模式/系统级别执行的统计操作；
 2 *_TAB_STATS_HISTORY：包含了表统计更改的历史。
旧的统计定期刷新，根据DBMS_STATS的ALTER_STATS_HISTORY_RETENTION过程设置而定，默认为31天。
默认情况下，如果STATISTICS_LEVEL为TYPICAL/ALL，自动刷新启用；否则需要使用PURGE_STAT手工刷新。

其他转储与刷新相关的信息包括：
 PURGE_STATS：     手工刷新超过某个时间戳的旧统计；
 GET_STATS_HISTORY_RENTENTION：   得到当前历史统计保留值；
 GET_STATS_HISTORY_AVAILABILTY： 得到可用的最旧的统计的时间戳。
转储的限制：
 1 不能转储用户定义统计；
 2 如果使用了ANALYZE收集，旧的统计将无法转储。

导入/导出统计
导出统计前需要使用DBMS_STATS.CREATE_STAT_TABLE创建一个统计表保留统计，在表创建后可以使用DBMS_STATS.EXPORT_*_STATS导出统计到自定义表，这些统计可以使用DBMS_STATS.IMPORT_*_STATS重新导入。
也可以使用IMP/EXP导到其他数据库。

转储统计与导入导出统计

使用转储的情况：
 1 恢复旧版本的统计；
 2 希望数据库管理统计历史的保留和刷新；
使用EXPORT/IMPORT_*_STATS的情况：
 1 实验各种值的不同情况；
 2 移动统计到不同数据库；
 3 保留统计数据更长的时间。

锁住表和模式的统计
一旦统计被锁住，将无法在更改这些统计直到被解锁。DBMS_STAT提供两个过程用于解锁，两个用于加锁：
 1 LOCK_SCHEMA_STATS；¡¤LOCK_TABLE_STATS；
 2 UNLOCK_SCHEMA_STATS；¡¤UNLOCK_TABLE_STATS；

设置统计
可以使用SET_*_STATISTICS设置表，索引，列，系统统计。

使用动态取样评价统计
动态取样的目的是通过为谓词选择性和表/索引统计确定更加精确的估计提高服务器性能，估计越精确产生的性能更好。
可以使用动态取样的情况：
 1 在收集的统计不能使用或会导致严重的估计错误时估计单表的谓词选择性；
 2 估计没有统计的表/索引的统计；
 3 估计统计过期的表和索引的统计；
动态取样特征由参数OPTIMIZER_DYNAMIC_SAMPLING控制，默认级别为2。

动态取样的工作机制
主要的性能特征是编译时，Oracle在编译时决定一个查询是否能通过取样获益，如果可以，将用递归SQL随机扫描一小部分表块，然后应用相关的单表谓词评价谓词选择性。

使用动态取样的时间
使用动态取样将获益的情况：
 1 可以发现更好的执行计划；
 2 取样时间仅占总时间的一小部分；
 3 查询将执行多次；

取样级别
范围从1..10

缺失统计处理
当Oracle遇到丢失统计时，优化器动态必要的统计。在某些情况下，Oracle无法执行动态取样，包括：远程表/外部表，此时将使用默认统计。
缺失统计时的表默认值：
 1 Cardinality：num_of_blocks * (block_size - cache_layer) / avg_row_len
 2 Average row length：100字节；
 3 Number of blocks：100或基于分区映射的实际值；
 4 Remote cardinality：2000行；
 5 Remote average row length：100字节；
缺失统计时的索引默认值：
 Levels：1
 Leaf blocks：25
 Leaf blocks/key：1
 Data blocks/key：1
 Distinct keys：100
 Clustering factor：800

gather_schema_stats
begin
dbms_stats.gather_schema_stats( wnname => 'SCOTT',
                                 ptions => 'GATHER AUTO',
                                 estimate_percent => dbms_stats.auto_sample_size,
                                 method_opt => 'for all columns size repeat',
                                 degree => 15 );
end;
 options参数使用4个预设的方法：
 gather——重新分析整个架构（Schema）。
 gather empty——只分析目前还没有统计的表。
 gather stale——只重新分析修改量超过10%的表（这些修改包括插入、更新和删除）。
 gather auto——重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。类似于组合使用gather stale和gather empty。

注意，无论gather stale还是gather auto，都要求进行监视。
如果你执行一个alter table xxx monitoring命令，Oracle会用dba_tab_modifications视图来跟踪发生变动的表。
这样一来，你就确切地知道，自从上一次分析统计数据以来，发生了多少次插入、更新和删除操作。
SELECT * FROM Sys.Dba_Tab_Modifications WHERE Table_Owner = 'SCOTT';
使用alter table xxx monitoring命令来实现Oracle表监视时，需要使用dbms_stats中的auto选项。
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;

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%的行中，那么为了检索这些行，全表扫描的速度会快于索引扫描。
--*************************************************************
-- 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选项时，只会为现有的直方图重新分析索引，不再搜索其他直方图机会。定期重新分析统计数据时，你应该采取这种方式。
--**************************************************************
-- 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;

Oracle中关于表的统计信息是在数据字典中的，可以下SQL查询到:
SELECT Table_Name,Num_Rows,Blocks,Empty_Blocks,Avg_Space,Chain_Cnt,Avg_Row_Len,Sample_Size,Last_Analyzed
FROM Dba_Tables WHERE wner = 'SCOTT' ;

这是对命令与工具包的一些总结
1、对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。
a) 可以并行进行，对多个用户，多个Table
b) 可以得到整个分区表的数据和单个分区的数据。
c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区 ,但不收集聚簇统计
d) 可以倒出统计信息
e) 可以用户自动收集统计信息
2、DBMS_STATS的缺点
a) 不能Validate Structure
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。
c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True
3、对于External Table，Analyze不能使用，只能使用DBMS_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);

参数说明:
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 <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:
                                              统计指定列的histograms.N的取值范围[1,254]; R
                                              EPEAT上次统计过的histograms;
                                              AUTO由oracle决定N的大小;
                                              SKEWONLY multiple end-points with the same value which is what we define by "there is skew in the data
degree:              设置收集统计信息的并行度.默认值为null.
granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.
cascade:       是收集索引的信息.默认为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);
GATHER_INDEX_STATS
==========================
BEGIN
SYS.DBMS_STATS.GATHER_INDEX_STATS (OwnName => 'ABC',
                                   IndName => 'IDX_FUNC_ABC',
                                   Estimate_Percent => 10,
                                   Degree => SYS.DBMS_STATS.DEFAULT_DEGREE,
                                   No_Invalidate => FALSE);
END;
 
---------------------------------------
10g自动收集统计信息
---------------------------------------
从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';
其实同在10点运行的Job还有一个AUTO_SPACE_ADVISOR_JOB：
SELECT Job_Name, Last_Start_Date FROM Dba_Scheduler_Jobs;

JOB_NAME                       LAST_START_DATE
------------------------------ ------------------------------------
AUTO_SPACE_ADVISOR_JOB         30-OCT-08 10.00.01.463000 PM +08:00
GATHER_STATS_JOB               30-OCT-08 10.00.01.463000 PM +08:00

然而这个自动化功能已经影响了很多系统的正常运行，晚上10点对于大部分生产系统也并非空闲时段。
而自动分析可能导致极为严重的闩锁竞争，进而可能导致数据库Hang或者Crash。
所以建议最好关闭这个自动统计信息收集功能：
关闭及开启自动搜集功能，有两种方法，分别如下：
方法一：
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;

---------------------------------------
查看统计
---------------------------------------
表/索引/列上的统计
DBA_TABLES
DBA_OBJECT_TABLES
DBA_TAB_STATISTICS
DBA_TAB_COL_STATISTICS
DBA_TAB_HISTOGRAMS
DBA_INDEXES
DBA_IND_STATISTICS
DBA_CLUSTERS
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_PART_COL_STATISTICS
DBA_PART_HISTOGRAMS
DBA_SUBPART_COL_STATISTICS
DBA_SUBPART_HISTOGRAMS
---------------------------------------
直方图统计
---------------------------------------
直方图的类型存储在*TAB_COL_STATISTICS视图的HISTOGRAM列上。

------------------------------------------------------------------------------
bde_last_analyzed.sql - Verifies CBO Statistics
------------------------------------------------------------------------------
bde_last_analyzed.sql verifies the CBO statistics in the data dictionary for all tables, indexes, and partitions. It also validates the statistics on tables and indexes owned by 'SYS'.

The 5 generated reports bde_last_analyzed_xxx.html, present the total of tables and indexes analyzed per module and per date.

Script. bde_last_analyzed.sql provided in this Note can be used on any 8i, 9i, 10g, 11g or higher database, including Oracle Apps 11i and R12 instances

如果是ERP数据库,则用APPS连接，否则用其他任何SYS权限用户连接都可以
#sqlplus <user>/<pwd>
     SQL> START bde_last_analyzed.sql

Review spool output files bde_last_analyzed_xxx.html files. Spool files get created on same directory from which this script. is executed. On NT, files may get created under $ORACLE_HOME/bin.

If some modules have not been analyzed, or they have but not recently, these Apps objects must be analyzed using FND_STATS or coe_stats.sql if belonging to Oracle Apps. Otherwise use DBMS_STATS.
If Oracle Apps, use corresponding concurrent program with an estimate of 10%, or execute equivalent FND_STATS procedure from SQL*Plus:
SQL> exec fnd_stats.gather_schema_statistics('APPLSYS'); Where 'APPLSYS' is the module (schema) that requires new statistics.

If only a few tables require to have their statistics gathered, use the corresponding concurrent program to gather stats by table, or execute equivalent FND_STATS procedure from SQL*Plus:
SQL> exec fnd_stats.gather_table_stats('MRP','MRP_FORECAST_DATES');
Where 'MRP' is the schema owner, and 'MRP_FORECAST_DATES' is the table name. This syntax is only for non-partitioned Tables.

If any Partitioned Table requires its Global Stats being rebuilt, it is because at some point you gathered Stats on the table using a granularity of PARTITION. See second method below:
begin
dbms_stats.delete_table_stats(ownname => 'APPLSYS', tabname => 'WF_ITEM_ACTIVITY_STATUSES');
fnd_stats.gather_table_stats (ownname => 'APPLSYS', tabname => 'WF_ITEM_ACTIVITY_STATUSES',
                                granularity => 'DEFAULT');
end;
/

Once you fix your stats, be sure to ALWAYS use the granularity of DEFAULT for partitioned tables.

If you want to execute this bde_last_analyzed.sql script. against only one schema, modify DEF SCHEMA code line.

---------------------------------------
分区表的统计信息实例
--------------------------------------- 
ORATEA ORACLE的统计信息在执行SQL的过程中扮演着非常重要的作用，而且ORACLE在表的各个层次都会有不同的统计信息，通过这些统计信息来描述表的，列的各种各样的统计信息。下面通过一个复合分区表来说明一些常见的和常见的统计信息。

SQL>
create table test
partition by range(object_id)
subpartition by hash(object_type) subpartitions 4
(partition p1 values less than(10000),
partition p2 values less than(20000),
partition p3 values less than(30000),
partition p4 values less than(maxvalue))
as
select * from dba_objects;

表已创建。
sql>
BEGIN
dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                tabname          => 'TEST',
                                estimate_percent => 100,
                                block_sample     => FALSE,
                                method_opt       => 'FOR ALL COLUMNS SIZE 10',
                                granularity      => 'ALL',
                                cascade          => TRUE);
END;

1,表级的统计信息

SQL> select table_name,num_rows,blocks,empty_blocks,avg_space from user_tables where table_name = 'TEST';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_SPACE
------------------------------ ---------- ---------- ------------ ----------
TEST                                50705        788            0          0

2,表上列的统计信息
SQL> select table_name,column_name,num_distinct,density from user_tab_columns where table_name = 'TEST';
TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT    DENSITY
------------------------------ ------------------------------ ------------ ----------
TEST                           OWNER                                    25 .365014295
TEST                           OBJECT_NAME                           30275 .000039205
TEST                           SUBOBJECT_NAME                          191 .015657993
TEST                           OBJECT_ID                             50705 .000019722
TEST                           DATA_OBJECT_ID                         4334 .000248075
TEST                           OBJECT_TYPE                              42 .271207855
TEST                           CREATED                                2305 .001608457
TEST                           LAST_DDL_TIME                          2369 .001566737
TEST                           TIMESTAMP                              2412 .001610251
TEST                           STATUS                                    2 .000009861
TEST                           TEMPORARY                                 2 .000009861
TEST                           GENERATED                                 2 .000009861
TEST                           SECONDARY                                 2 .000009861

13 rows selected.

3,表上列的直方图信息
SQL>
select table_name,column_name,endpoint_number,endpoint_value
from user_tab_histograms
where table_name = 'TEST'
and column_name = 'OBJECT_ID';

TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- ---------- --------------- --------------
TEST       OBJECT_ID                0              2
TEST       OBJECT_ID                1           5160
TEST       OBJECT_ID                2          10587
TEST       OBJECT_ID                3          15658
TEST       OBJECT_ID                4          20729
TEST       OBJECT_ID                5          25800
TEST       OBJECT_ID                6          30870
TEST       OBJECT_ID                7          35940
TEST       OBJECT_ID                8          41089
TEST       OBJECT_ID                9          46821
TEST       OBJECT_ID               10          53497

4,分区的统计信息

SQL>
select partition_name,num_rows,blocks,empty_blocks,avg_space
from user_tab_partitions
where table_name = 'TEST';

PARTITION_NAME    NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_SPACE
--------------- ---------- ---------- ------------ ----------
P1                    9581        140            0          0
P2                    9973        164            0          0
P3                   10000        158            0          0
P4                   21151        326            0          0

5,分区上列的统计信息

SQL> select column_name,num_distinct,density,num_nulls
from user_part_col_statistics
where table_name = 'TEST'
and partition_name = 'P1';

COLUMN_NAME     NUM_DISTINCT    DENSITY NUM_NULLS
--------------- ------------ ---------- ----------
OWNER                      7 .000052187          0
OBJECT_NAME             7412 .000156925          0
SUBOBJECT_NAME            26 .47017301       9496
OBJECT_ID               9581 .000104373          0
DATA_OBJECT_ID          1765 .000664385       7780
OBJECT_TYPE               34 .18494854          0
CREATED                  913 .001977449          0
LAST_DDL_TIME            994 .001882695          0
TIMESTAMP                982 .001928775          0
STATUS                     2 .000052187          0
TEMPORARY                  2 .000052187          0
GENERATED                  2 .000052187          0
SECONDARY                  1 .000052187          0

6,分区上列的直方图信息

SQL> select column_name,bucket_number,endpoint_value
from user_part_histograms
where table_name = 'TEST'
and partition_name = 'P1'
and column_name = 'OBJECT_ID';

COLUMN_NAME     BUCKET_NUMBER ENDPOINT_VALUE
--------------- ------------- --------------
OBJECT_ID                   0              2
OBJECT_ID                   1           1005
OBJECT_ID                   2           1963
OBJECT_ID                   3           2921
OBJECT_ID                   4           3888
OBJECT_ID                   5           4859
OBJECT_ID                   6           5941
OBJECT_ID                   7           6899
OBJECT_ID                   8           7885
OBJECT_ID                   9           8864
OBJECT_ID                  10           9999

7,子分区的统计信息

SQL> select subpartition_name,num_rows,blocks,empty_blocks
from user_tab_subpartitions
where table_name = 'TEST'
and partition_name = 'P1';

SUBPARTITION_NAME                NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
SYS_SUBP21                           3597         50            0
SYS_SUBP22                           3566         52            0
SYS_SUBP23                            637         11            0
SYS_SUBP24                           1781         27            0

8,子分区上的列的统计信息

SQL> select column_name,num_distinct,density
from user_subpart_col_statistics
where table_name = 'TEST'
and subpartition_name = 'SYS_SUBP21';
COLUMN_NAME     NUM_DISTINCT    DENSITY
--------------- ------------ ----------
OWNER                      6 .000139005
OBJECT_NAME             3595 .000278319
SUBOBJECT_NAME             4 .014285714
OBJECT_ID               3597 .000278009
DATA_OBJECT_ID           155 .006451613
OBJECT_TYPE                8 .000139005
CREATED                  751 .002392334
LAST_DDL_TIME            784 .002302524
TIMESTAMP                768 .00235539
STATUS                     1 .000139005
TEMPORARY                  2 .000139005
GENERATED                  2 .000139005
SECONDARY                  1 .000139005

9,子分区上的列的直方图信息

SQL> select column_name,bucket_number,endpoint_value
from user_subpart_histograms
where table_name = 'TEST'
and subpartition_name = 'SYS_SUBP21'
and column_name = 'OBJECT_ID';
COLUMN_NAME     BUCKET_NUMBER ENDPOINT_VALUE
--------------- ------------- --------------
OBJECT_ID                   0            208
OBJECT_ID                   1           1525
OBJECT_ID                   2           2244
OBJECT_ID                   3           2892
OBJECT_ID                   4           3252
OBJECT_ID                   5           4047
OBJECT_ID                   6           5238
OBJECT_ID                   7           6531
OBJECT_ID                   8           7661
OBJECT_ID                   9           8474
OBJECT_ID                  10           9998

我们对这个复合分区分析之后产生了上面这九种不同层次的统计信息。CBO想要得要一个高效的执行计划需要如此多的统计信息.

 

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