`
ticojj
  • 浏览: 151372 次
  • 性别: Icon_minigender_1
  • 来自: 广州
社区版块
存档分类
最新评论

Oracle Statistic 统计信息 optimizer性能调优

 
阅读更多
 
 
imp $1/$2  file=/u_ora_exporti/restore_dmp/DWH/sgmt_ofr_csm_data_$3.dmp INDEXES=N COMMIT=Y IGNORE=y FROMUSER=$1 TOUSER=$1 TABLES=tbl_maprg_sgmt_ofr_csm_data grants=n statistics=none buffer=5400000
error msg:
  IMP-00017: following statement failed with ORACLE error 20000:"BEGIN DBMS_STATS.SET_TABLE_STATS(NULL,'"TBL_MAPRG_SGMT_OFR_CSM_DATA"','"TP""_MAPRG_SGMT_OFR_CSM_200706"',NULL,NULL,0,0,0,0); END;"IMP-00003: ORACLE error 20000 encounteredORA-20000: TP_MAPRG_SGMT_OFR_CSM_200706 invalid partition nameORA-06512: at "SYS.DBMS_STATS", line 3894ORA-06512: at "SYS.DBMS_STATS", line 10134ORA-06512: at line 1IMP-00017: following statement failed with ORACLE error 20000:"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := NULL; SREC.MAXVAL :="" NULL; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMAR""RAY(); SREC.BKVALS := DBMS_STATS.NUMARRAY(); SREC.EPC := 0; DBMS_STATS.SET_""COLUMN_STATS(NULL,'"TBL_MAPRG_SGMT_OFR_CSM_DATA"','"VER_DTE"','"TP_MAPRG_SG""MT_OFR_CSM_200706"',NULL,NULL,0,0,0,srec,0,0); END;"IMP-00003: ORACLE error 20000 encounteredORA-20000: TP_MAPRG_SGMT_OFR_CSM_200706 invalid partition nameORA-06512: at "SYS.DBMS_STATS", line 3894ORA-06512: at "SYS.DBMS_STATS", line 8915ORA-06512: at line 1IMP-00017: following statement failed with ORACLE error 20000:"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := NULL; SREC.MAXVAL :="" NULL; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMAR""RAY(); SREC.BKVALS := DBMS_STATS.NUMARRAY(); SREC.EPC := 0; DBMS_STATS.SET_""COLUMN_STATS(NULL,'"TBL_MAPRG_SGMT_OFR_CSM_DATA"','"REC_DTE"','"TP_MAPRG_SG""MT_OFR_CSM_200706"',NULL,NULL,0,0,0,srec,0,0); END;"IMP-00003: ORACLE error 20000 encounteredORA-20000: TP_MAPRG_SGMT_OFR_CSM_200706 invalid partition nameORA-06512: at "SYS.DBMS_STATS", line 3894ORA-06512: at "SYS.DBMS_STATS", line 8915ORA-06512: at line 1IMP-00017: following statement failed with ORACLE error 20000:"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := NULL; SREC.MAXVAL :="" NULL; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMAR""RAY(); SREC.BKVALS := DBMS_STATS.NUMARRAY(); SREC.EPC := 0; DBMS_STATS.SET_""COLUMN_STATS(NULL,'"TBL_MAPRG_SGMT_OFR_CSM_DATA"','"HUB_AC_NO"','"TP_MAPRG_""SGMT_OFR_CSM_200706"',NULL,NULL,0,0,0,srec,0,0); END;"IMP-00003: ORACLE error 20000 encounteredORA-20000: TP_MAPRG_SGMT_OFR_CSM_200706 invalid partition nameORA-06512: at "SYS.DBMS_STATS", line 3894ORA-06512: at "SYS.DBMS_STATS", line 8915ORA-06512: at line 1IMP-00017: following statement failed with ORACLE error 20000:"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := NULL; SREC.MAXVAL :="" NULL; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMAR""RAY(); SREC.BKVALS := DBMS_STATS.NUMARRAY(); SREC.EPC := 0; DBMS_STATS.SET_""COLUMN_STATS(NULL,'"TBL_MAPRG_SGMT_OFR_CSM_DATA"','"CUS_ID_TYP"','"TP_MAPRG""_SGMT_OFR_CSM_200706"',NULL,NULL,0,0,0,srec,0,0); END;"IMP-00003: ORACLE error 20000 encounteredORA-20000: TP_MAPRG_SGMT_OFR_CSM_200706 invalid partition nameORA-06512: at "SYS.DBMS_STATS", line 3894ORA-06512: at "SYS.DBMS_STATS", line 8915ORA-06512: at line 1IMP-00017: following statement failed with ORACLE error 20000:"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := NULL; SREC.MAXVAL :="" NULL; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMAR""RAY(); SREC.BKVALS := DBMS_STATS.NUMARRAY(); SREC.EPC := 0; DBMS_STATS.SET_""COLUMN_STATS(NULL,'"TBL_MAPRG_SGMT_OFR_CSM_DATA"','"CUS_ID"','"TP_MAPRG_SGM""T_OFR_CSM_200706"',NULL,NULL,0,0,0,srec,0,0); END;"IMP-00003: ORACLE error 20000 encounteredORA-20000: TP_MAPRG_SGMT_OFR_CSM_200706 invalid partition nameORA-06512: at "SYS.DBMS_STATS", line 3894ORA-06512: at "SYS.DBMS_STATS", line 8915ORA-06512: at line 1IMP-00017: following statement failed with ORACLE error 20000:"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := NULL; SREC.MAXVAL :="" NULL; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMAR""RAY(); SREC.BKVALS := DBMS_STATS.NUMARRAY(); SREC.EPC := 0; DBMS_STATS.SET_""COLUMN_STATS(NULL,'"TBL_MAPRG_SGMT_OFR_CSM_DATA"','"BEST_INCOME"','"TP_MAPR""G_SGMT_OFR_CSM_200706"',NULL,NULL,0,0,0,srec,0,0); END;"IMP-00003: ORACLE error 20000 encounteredORA-20000: TP_MAPRG_SGMT_OFR_CSM_200706 invalid partition nameORA-06512: at "SYS.DBMS_STATS", line 3894ORA-06512: at "SYS.DBMS_STATS", line 8915ORA-06512: at line 1IMP-00017: following statement failed with ORACLE error 20000:"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := NULL; SREC.MAXVAL :="" NULL; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMAR""RAY(); SREC.BKVALS := DBMS_STATS.NUMARRAY(); SREC.EPC := 0; DBMS_STATS.SET_""COLUMN_STATS(NULL,'"TBL_MAPRG_SGMT_OFR_CSM_DATA"','"CD_BKRU_SCOR"','"TP_MAP""RG_SGMT_OFR_CSM_200706"',NULL,NULL,0,0,0,srec,0,0); END;"IMP-00003: ORACLE error 20000 encounteredORA-20000: TP_MAPRG_SGMT_OFR_CSM_200706 invalid partition nameORA-06512: at "SYS.DBMS_STATS", line 3894ORA-06512: at "SYS.DBMS_STATS", line 8915ORA-06512: at line 1
 
 
 
您查询的关键词是:dbms_statsstatistic 。如果打开速度慢,可以尝试快速版;如果想保存快照,可以添加到搜藏;如果想更新或删除快照,可以投诉快照
(百度和网页http://www.codesky.net/article/201201/166720.html的作者无关,不对其内容负责。百度快照谨为网络故障时之索引,不代表被搜索网站的即时页面。)
 
<!-- 顶部 --> <!-- 头部 --> <!-- 头部 -->
 
 
 
 
 

Oracle Statistic 统计信息 小结

阅读:658次   时间:2012-01-23 19:59:56   字体:[ ]
 
 

    Oracle Statistic 统计信息 小结

    分类: Oracle 性能调优 2009-10-14 12:18 2738人阅读 评论(4) 收藏 举报

    一。  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             21

    3             31

    4             41

    5             51

    6             61

    7             71

    8             81

    9             91

    10            100

    11 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;

    4

    ENDPOINT_NUMBER ENDPOINT_VALUE

    --------------- --------------

    10              1

    20              2

    30              3

    40              4

    50              5

    60              6

    70              7

    80              8

    90              9

    100             10

    10 rows selected.

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

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

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

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

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

 
<!-- <div class="break"> 上一篇:<a href=""><$lasttitle$></a> <br />下一篇:<a href=""><$nexttitle$></a> </div> -->
<!--cr-->
 
<!-- Baidu Button BEGIN --><!-- Baidu Button END -->
 
Copyright ©2003-2014 源码天空 All Rights Reserved
 
分享到:
评论

相关推荐

    Oracle Statistic 统计信息

    Statistic 对Oracle 是非常重要的。 它会收集数据库中对象的详细信息,并存储在相应的数据字典里。 根据这些统计信息, optimizer 可以对每个SQL 去选择最好的执行计划

    IDEA统计代码量Statistic插件

    IDEA统计代码量Statistic插件,可适配IDEA2019.1版本,解决旧版IDEA工具栏找不到Statistic问题。 安装步骤:下载后打开IDEA,选择File -&gt; Settings -&gt; Plugins -&gt; 点击顶部齿轮按钮 -&gt; Install Plugins from Disk......

    oracle动态性能表

    oracle动态性能表 学习动态性能表 第一篇--v$sysstat 2007.5.23  按照OracleDocument中的描述,v$sysstat存储自数据库实例运行那刻起就开始累计全实例(instance-wide)的资源使用情况。 类似于v$sesstat,该视图...

    IDEA统计代码量Statistic插件(解决旧版IDEA工具栏找不到Statistic问题)

    IDEA统计代码量Statistic插件,可适配IDEA2019.1版本,解决旧版IDEA工具栏找不到Statistic问题。 安装步骤:下载后打开IDEA,选择File -&gt; Settings -&gt; Plugins -&gt; 点击顶部齿轮按钮 -&gt; Install Plugins from Disk......

    Statistic 统计类

    Statistic class: Statistic Sample StatAccumulative StatGrup NOTE: VC++ 6.0

    idea :插件Statistic 统计代码数量和行数

    显示项目统计数量。 这个插件显示的是按扩展名排序的文件,以及大小、行数、LOC等。 用户可以选择统计(Project/Module/Package/File)范围,选择范围后需要点击Refresh,。 (这个插件需要Java1.8)

    idea统计代码行数插件Statistic-3.8.1版本

    idea统计代码行数可以用到插件:Statistic。解决老版本idea不显示该插件问题 Statistic-3.8.1.jar 详见:https://blog.csdn.net/Sun__Kings/article/details/109285389

    idea :插件Statistic 统计代码数量和行数.zip

    安装离线包 选中插件即可 安装过程不再赘述 可执行百度解决 1. Overview参数说明(如java) Count : java文件数量 Size SUM :Java所有文件总占用磁盘大小 Size MIN : java文件的最小文件占用硬盘大小 ...

    学习ORACLE动态性能表.doc

    类似于v$sesstat,该视图存储下列的统计信息: 1&gt;.事件发生次数的统计(如:user commits) 2&gt;.数据产生,存取或者操作的total列(如:redo size) 3&gt;.如果TIMED_STATISTICS值为true,则统计花费在执行操作上的总时间(如...

    IDEA代码统计插件Statistic最新版本4.1.2.rar

    Statistic插件可以统计代码的行数,如果安装后仍不起作用,可能是IDEA版本不匹配。IDEA2019及之前版本使用不了这个最新的Statistics版本。

    Gap Statistic算法的Matlab源码

    自己写的Gap Statistic算法Matlab代码,完整可运行,测试平台Matlab 2010b。

    Best Practices for Gathering Optimizer Statistics with Oralce 18c

    Best Practices for Gathering Optimizer Statistics with oracle 18c. 此文档研究18c优化器必看.

    Statistic3.4.1.zip

    Statistic-3.4.1 idea插件,可兼容idea2018版本,用可于统计代码量

    Gap Statistic算法研究

    Gap Statistic算法研究,详细的代码以及分析过程。

    idea插件statistic4.1.7版本.rar

    idea统计代码插件Statistic-4.1.7.jar

    ORACLE_优化设计与系统调整(doc)

    第一部分 ORACLE系统优化基本知识 42 第1章 ORACLE结构回顾 42...表B-7 STATISTIC列中的统计数据名 491 表B-10 RESOURCE_LIMIT列的值 501 表B-11 COMMAND列的命令数字 507 附录B 初始化参数 531 附录C 基本概念解释 532

    Statistic-3.5.jar

    Idea插件,可用于统计当前代码行数,代码量,大小,以及细分jsp,css,java等不同文件类型,安装方法:打开idea&gt;&gt;settings&gt;&gt;plugins&gt;&gt;install plugin from disk。安装完毕后,重启idea,打开左下角statistic工具,...

    Statistic-3.4.1.jar

    idea 2018 Statistic插件下载

    Significance of a T Statistic

    Significance of a T Statistic

    Statistic Word

    Statistic word's appearence Frequnency.

Global site tag (gtag.js) - Google Analytics