精华内容
下载资源
问答
  • Oracle统计信息

    千次阅读 2018-11-20 20:23:38
    Oracle统计信息是存储在数据字典里的一组数据,从多个维度描述了oracle数据库里对象的详细信息。CBO会利用这些统计信息来计算目标SQL各种可能的、不同的执行路径成本,并从中选择一条成本值最小的执行路径来作为目标...

    统计信息概念及分类

    Oracle统计信息是存储在数据字典里的一组数据,从多个维度描述了oracle数据库里对象的详细信息。CBO会利用这些统计信息来计算目标SQL各种可能的、不同的执行路径成本,并从中选择一条成本值最小的执行路径来作为目标SQL的执行计划

    oracle数据库里的统计信息分为以下6种类型:

    • 表的统计信息:描述表的详细信息,如记录数、表块(表里的数据块数量)的数量、平均行长度等
    • 索引的统计信息:描述索引的详细信息,如索引的层级、叶子块的数量、聚簇因子等
    • 列的统计信息:描述列的详细信息,如列的distinct值的数量、列的null值数量、列的最小最大值及直方图
    • 系统统计信息:描述数据库服务器的系统处理能力,包含了CPU和I/O这两个维度,借助于系统统计信息,oracle可以更清楚地知道目标数据库服务器的实际处理能力
    • 数据字典统计信息:描述数据字典基表(如TAB$、IND$等)、数据字典基表上的索引,以及这些数据字典基表的列的详细信息,描述上述数据字典基表的统计信息与描述普通表、索引、列的统计信息没有本质区别
    • 内部对象统计信息:描述oracle数据库里的一些内部表(如X$系列表)的详细信息,它的维度和普通表的统计信息的维度类似,只不过其表块的数量为0,因为X$系列表实际上只是oracle自定义的内存结构,并不占用实际的物理存储空间

     

    收集与查看方法

    1. 推荐使用DBMS_STATS包收集统计信息

    dbms_stats.gather_table_stats
    dbms_stats.gether_index_stats
    dbms_stats.gather_schema_stats
    dbms_stats.gather_database_stats
    
    exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',estimate_percent=>15,method_opt=>'FOR TABLE',cascade=>false);--估算模式,采样比例15%

    2. DBMS_STATS包相对于ANALYZE的优势

    • ANALYZE命令不能正确地收集分区表的统计信息,而DBMS_STATS包可以。ANALYZE只会收集最低层次对象的统计信息,然后推到和汇总出高一级的统计信息,比如对于有子分区的分区表而言,它只会先收集子分区统计信息,然后再汇总、推导出分区和表级的统计信息。有的统计信息是可以从当前对象的下一级对象进行汇总后得到的,比如表的总行数,可以由各分区的行数相加得到。但有的统计信息则不能从下一级对象得到,比如列上的distinct值数量NUM_DISTINCT以及DENSITY等。
    • ANALYZE命令不能并行收集统计信息,而DBMS_STATS包可以。精确收集数据量很大的表的统计信息时,并行更省时。

    3. 查看统计信息:表级别DBA_TABLES、分区级别DBA_TAB_PARTITIONS、子分区级别DBA_TAB_SUBPARTITIONS统计信息

    或者执行脚本sosi.txt  http://www.dbsnake.net/books

     

    表的统计信息

     

    展开全文
  • 详解Oracle统计信息

    千次阅读 2019-04-19 09:53:00
    优化器统计范围: 表统计; --行数,块数,行平均长度;all_tables:NUM_ROWS,BLOCKS,AVG_ROW_LEN; 列统计; --列中唯一值的数量(NDV),NULL值的数量,数据分布; --DBA_TAB_COLUMNS:NUM_DISTINCT,NUM_...

    优化器统计范围:

    表统计; --行数,块数,行平均长度;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想要得要一个高效的执行计划需要如此多的统计信息.

    展开全文
  • 收集oracle统计信息

    千次阅读 2018-07-27 12:57:58
    优化器统计范围: 表统计; --行数,块数,行平均长度;all_tables:NUM_ROWS,BLOCKS,AVG_ROW_LEN;列统计; --列中唯一值的数量(NDV),NULL值的数量,数据分
    原文地址为:
    收集oracle统计信息
    

    优化器统计范围:

    表统计; --行数,块数,行平均长度;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想要得要一个高效的执行计划需要如此多的统计信息.

     


    转载请注明本文地址: 收集oracle统计信息
    展开全文
  • oracle统计信息被锁定处理

    千次阅读 2019-08-01 10:12:29
    oracle统计信息被锁定处理 基本现象 ORA-20005: object statistics are locked (stattype = ALL) 在进行SQL时,发现一个表的统计信息过旧,想要收集一个表的统计信息 然而信息报错如下: SQL> begin 2 dbms_stats...

    oracle统计信息被锁定处理

    基本现象

    ORA-20005: object statistics are locked (stattype = ALL)
    在进行SQL时,发现一个表的统计信息过旧,想要收集一个表的统计信息
    然而信息报错如下:
    SQL> begin
    2 dbms_stats.gather_table_stats(ownname => ‘DB’, tabname => ‘tbhrc0’,cascade => true);
    3 end;
    4 /
    begin
    *
    ERROR at line 1:
    ORA-20005: object statistics are locked (stattype = ALL)
    ORA-06512: at “SYS.DBMS_STATS”, line 24281
    ORA-06512: at “SYS.DBMS_STATS”, line 24332
    ORA-06512: at line 2

    分析

    此表的统计信息被锁定

    解锁统计信息:
    SQL> exec dbms_stats.unlock_table_stats(‘db’,‘tb0’);

    重新收集:

    重新收集统计信息:
    SQL> begin
    2 dbms_stats.gather_table_stats(ownname => ‘DB’, tabname => ‘tbh0’,cascade => true);
    3 end;
    4 /

    PL/SQL procedure successfully completed.

    再次查看表的统计 信息:
    SQL> select table_name,t.LAST_ANALYZED,t.STATTYPE_LOCKED from dba_tab_statistics t where t.table_name = ‘TBHRC0’;

    TABLE_NAME LAST_ANALYZE STATT


    TB0 31-JUL-19

    至此处理正常

    总结

    自动统计信息收集

    oracle 11g会自动收集统计信息
    自动收集的频率为:

    SQL> select t1.window_name, t1.repeat_interval, t1.duration
      2    from dba_scheduler_windows t1, dba_scheduler_wingroup_members t2
      3   where t1.window_name = t2.window_name
      4     and t2.window_group_name in
      5         ('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');
    
    WINDOW_NAME                    REPEAT_INTERVAL
    ------------------------------ --------------------------------------------------------------------------------
    DURATION
    ---------------------------------------------------------------------------
    MONDAY_WINDOW                  freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0
    +000 04:00:00
    
    TUESDAY_WINDOW                 freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0
    +000 04:00:00
    
    WEDNESDAY_WINDOW               freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0
    +000 04:00:00
    
    THURSDAY_WINDOW                freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0
    +000 04:00:00
    
    FRIDAY_WINDOW                  freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0
    +000 04:00:00
    
    SATURDAY_WINDOW                freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0
    +000 20:00:00
    
    SUNDAY_WINDOW                  freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0
    +000 20:00:00
    
    
    7 rows selected.
    

    周一到周五,每天22开始,持续时间为4h,所以
    周六至周日,每天6点

    查看开启自动统计信息收集

    SQL> select client_name,status from dba_autotask_client;
    
    CLIENT_NAME                                                      STATUS
    ---------------------------------------------------------------- --------
    auto optimizer stats collection                                  ENABLED
    auto space advisor                                               ENABLED
    sql tuning advisor                                               ENABLED
    

    auto optimizer stats collection 是自动统计信息收集任务,当前为开户状态

    禁止和启动自动统计信息收集
    禁止:

    exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
    
    

    启用:

     exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
    

    统计信息管理

    解锁一个用户的统计信息
    DBMS_STATS.UNLOCK_schema_STATS(user);

    解锁单个对象的统计信息
    1)被锁定统计信息的表
    select table_name from user_tab_statistics where stattype_locked is not null;
    然后再解锁对象
    exec dbms_stats.unlock_table_stats(user,‘表名’);
    2)直接生成sql脚本批量执行
    select ‘exec dbms_stats.unlock_table_stats(’’’||user||’’’,’’’||table_name||’’’);’ from user_tab_statistics where stattype_locked is not null;
    这里不在生成的sql中用动态的user是为了让执行者明确知道到底是解锁哪个schema下的表,防止误操作

    当统计信息收集不准确以及执行计划走偏的时候,有时候类似的需求,我们需要固定统计信息,在这种场景下,需要锁定统计信息的。
    敷衍dbms_stats.lock_table_stats来锁定统计信息

    删除统计信息
    exec dbms_stats.delete_table_stats(‘user’,‘t1’,cascade => TRUE);

    锁定统计信息
    exec dbms_stats.lock_table_stats(‘user’,‘t1’,cascade=>TRUE);

    改变统计信息的估算百分比
    exec dbms_stats.set_table_stats(‘TEST’,‘T1’,numrows=>10);

    收集表和索引统计信息
    exec dbms_stats.gather_table_stats(‘DB’, ‘T1’);
    exec dbms_stats.gather_index_stats(‘DB’, ‘T1_IDX1’);

    统计信息表
    ALL_TAB_STATISTICS displays optimizer statistics for the tables accessible to the current user.
    DBA_TAB_STATISTICS displays optimizer statistics for all tables in the database.
    USER_TAB_STATISTICS displays optimizer statistics for the tables owned by the current user. This view does not display the OWNER column.

    可以根据不同的schema粒度选择对应的schema来访问

    展开全文
  • oracle统计信息高可靠性保障技术;oracle统计信息高可靠性保障技术
  • 主要给大家介绍了关于Oracle统计信息的导出导入测试的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
  • Oracle 查看收集统计信息

    万次阅读 2015-07-29 17:27:00
    统计信息相当于情报,对 Oracle 至关重要,如果统计信息不准确,Oracle 就会做出错误的判断。通常,Oracle 会在每天固定时间段自动维护统计信息。但是对于某些表,这是远远不够的,如:某些表每天都需要清空,...
  • ORACLE统计信息

    千次阅读 2010-01-08 09:17:00
    1. 什么是统计信息 统计信息主要是描述数据库中表,索引的大小,规模,数据分布状况等的一类信息。比如,表的行数,块数,平均每行的大小,索引的leaf blocks,索引字段的行数,不同值的大小等,都属于统计信息。CBO...
  • Statistic 对Oracle 是非常重要的。 它会收集数据库中对象的详细信息,并存储在相应的数据字典里。 根据这些统计信息, optimizer 可以对每个SQL 去选择最好的执行计划
  • 自动更新Oracle统计信息

    千次阅读 2017-02-27 10:37:32
    1.需求描述:Oracle不定时更新数据表的统计信息,当数据量增多时,更新频率逐渐变慢; 当编写某些大的查询sql时,如访问最近一个月的销售汇总,调优后的评估值在1000左右,执行时间在10几分钟;但是一两个月后,...
  • oracle系统统计信息

    千次阅读 2013-12-27 11:20:51
    这些信息对于查询优化器来说是非常重要的,在选择执行计划的过程中,oracle优化器会利用系统统计信息来评估执行计划的成本,因此,准确无误的系统统计信息可以帮助优化器做出正确的选择。 我们可以通过DBMS_STATS....
  • oracle统计信息查看与收集

    万次阅读 2017-07-16 15:39:50
    查看某个表的统计信息 SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; Session altered. SQL> select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED from user_tables t where ...
  • 查看当前库中统计信息过期的表: set linesize 150 set pagesize 1000 EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; SELECT OWNER, TABLE_NAME, PARTITION_NAME, OBJECT_TYPE, STALE_STATS, LAST_...
  • ORACLE 收集统计信息

    千次阅读 2014-08-07 23:56:46
      今天网上看到一篇关于收集统计...优化器统计信息就是一个更加详细描述数据库和数据库对象的集合,这些统计信息被用于查询优化器,让其为每条SQL语句选择最佳的执行计划。优化器统计信息包括: · 表的统计信息...
  • 查看Oracle中表的统计信息

    千次阅读 2013-11-06 17:42:44
    oracle数据库在执行sql语句的时候,会根据统计信息计算怎样执行性能更优,当执行性能比自己想象的慢时,就有可能是统计信息不新导致,查看统计信息常用的方式有如下三种: 1 查看当前用户下的统计信息 select * from...
  • Oracle统计信息的更新

    千次阅读 2014-12-23 00:24:08
    Oracle有个自动更新统计信息的Job,大约在每天晚上执行。所以在白天统计信息不会被更新掉; 2. 统计信息对CBO的影响有个阀值,尤其是对范围查找来说,超过这个阀值,就使用索引,否则就全表扫描; 3. 白天发现查
  • oracle统计信息收集

    千次阅读 2009-11-19 11:47:00
    http://www.51testing.com/?uid-16403-action-viewspace-itemid-101604 统计信息包括下面几类: 表统计:包括记录数、block数和记录平均长度。列统计:列中不同值的数量(NVD)、空值的数量和数据分布(HISTOGRAM...
  • Oracle数据库里的统计信息是这样的一组数据:它存储在数据字典里,且从多个维度描述了Oracle数据库里对象的详细信息。CBO会利用这些统计信息来计算目标SQL各种可能的、不同的执行路径的成本,并从中选择一条成本值...
  • oracle统计分析表信息

    千次阅读 2018-10-09 19:33:28
    Oracle数据库中的优化器又叫查询优化器(Query Optimizer)。它是SQL分析和执行的优化工具,它负责生成、制定SQL的执行计划。Oracle的优化器有两种,基于规则的优化器(RBO)与基于代价的优化器(CBO)  RBO: Rule-...
  • Oracle统计信息的锁定

    千次阅读 2016-06-22 16:58:18
    场景1、锁定统计信息,即禁止搜集表的统计信息: exec dbms_stats.lock_table_stats('','T1',cascade=>TRUE); select table_name from user_tab_statistics where stattype_locked is not null;  如需 场景...
  • 手工收集oracle统计信息

    千次阅读 2013-09-15 10:20:43
    手工收集oracle某用户的统计信息 1.exec DBMS_STATS.GATHER_SCHEMA_STATS(‘NC57’) 2.execute dbms_stats.GATHER_SCHEMA_STATS('nc57',DBMS_STATS.AUTO_SAMPLE_SIZE)
  • oracle11g查看自动收集统计信息是否开启SQL> select client_name,status from dba_autotask_client;CLIENT_NAME Status --------------------------------------------------
  • oracle 11g 统计信息

    2014-06-02 16:11:47
    oracle 11g自动收集统计信息的时间
  • 转载 查看Oracle中表的统计信息

    千次阅读 2019-07-03 08:41:10
    oracle数据库在执行sql语句的时候,会根据统计信息计算怎样执行性能更优,当执行性能比自己想象的慢时,就有可能是统计信息不新导致,查看统计信息常用的方式有如下三种: 1 查看当前用户下的统计信息 select * ...
  • oracle 统计信息是什么

    千次阅读 2009-11-19 11:48:00
    什么是统计信息统计信息主要是描述数据库中表,索引的大小,规模,数据分布状况等的一类信息。比如,表的行数,块数,平均每行的大小,索引的leaf blocks,索引字段的行数,不同值的大小等,都属于统计信息。CBO...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 147,463
精华内容 58,985
关键字:

oracle统计信息查看