PLSQL_统计信息系列01_统计信息之概念以及要紧

2014-12-18 Created By
BaoXinjian

图片 1一、摘要


Statistic 对Oracle 是特别重大的。

它见面征集数据库中目标的详细信息,并蕴藏在相应的数目字典里。
根据这些统计信息, optimizer 可以本着每个SQL 去挑选最好之执行计划。

Statistic 对Oracle
是很关键之,它会采集数据库被目标的详细信息,并储存在对应的数字典里。

根据这些统计信息, optimizer
可以对每个SQL 去挑最为好的实行计划。

Oracle Statistic 的采访,可以下analyze
命令,也堪下DBMS_STATS 包来集。

Oracle
建议使用DBMS_STATS包来搜集统计信息,因为DBMS_STATS包收集之更广大,并且再标准,Analyze
于事后的版本中恐会见于移除。

 

图片 2第二、统计信息内容和层次


  1. Table statistics

(1). 行数,块数,行平均长度

(2).
DBA_TBALES:NUM_ROWS,BLOCKS,AVG_ROW_LEN;

  • Number of rows
  • Number of blocks
  • Average row length

  • Column statistics

(1).
列中绝无仅有值的数目(NDV),NULL值的数目,数据分布;

(2).
DBA_TAB_COLUMNS:NUM_DISTINCT,NUM_NULLS,HISTOGRAM;

  • Number of distinct values (NDV) in
    column
  • Number of nulls in column
  • Data distribution (histogram)

  • Index statistics

(1). 叶块数量,等级,聚簇因子;

(2).
DBA_INDEXES:LEAF_BLOCKS,CLUSTERING_FACTOR,BLEVEL;

  • Number of leaf blocks
  • Levels
  • Clustering factor

  • System statistics

(1).
存储在aux_stats$中,需要用dbms_stats收集,I/O统计在X$KCFIO中;

  • I/O performance and
    utilization
  • CPU performance and
    utilization

 

图片 3其三、统计信息语法


  1. analyze

要采取ANALYZE统计的统计:使用LIST
CHAINED ROWS和VALIDATE子句收集空闲列表块的统计;

analyze 不适合做分区表的剖析

  • 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]

  • 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_dictioinary_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

 

图片 4季、统计信息语法


4.1 统计信息收集如下数据:

(1)表自身的分析:
包括表中的行数,数据块数,行长等信息。

(2)列的解析:包括列值的重复数,列上的空值,数据在排列上之布状况。

(3)索引的解析:
包括索引叶块的数码,索引的深,索引的集因子等。

4.2
这些统计信息寄存于数量字典里,如:

(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

4.3 表的统计信息:

含表行数,使用的片数,空的块数,块的使用率,行迁移和链接的数额,pctfree,pctused的数额,行之平均大小:

SELECT NUM_ROWS, --表中的记录数
        BLOCKS, --表中数据所占的数据块数
        EMPTY_BLOCKS, --表中的空块数
        AVG_SPACE, --数据块中平均的使用空间
        CHAIN_CNT, --表中行连接和行迁移的数量
        AVG_ROW_LEN --每条记录的平均长度
FROM USER_TABLES 

4.4 索引列的统计信息   

饱含索引的吃水(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

4.5 列的统计信息  

带有
唯一的值个数,列最酷小值,密度(选择率),数据分布(直方图信息),NUll值个数

SELECT NUM_DISTINCT, --唯一值的个数
    LOW_VALUE, --列上的最小值
    HIGH_VALUE, --列上的最大值
    DENSITY, --选择率因子(密度)
    NUM_NULLS, --空值的个数
    NUM_BUCKETS, --直方图的BUCKET个数
    HISTOGRAM --直方图的类型
FROM USER_TAB_COLUMNS

 

图片 5五、案例


案例: 查询表时,解析计划回去结果集Rows完全无得法,表经过大气之DML后,未进行剖析导致统计信息过久

Step1. 树测试SQL

图片 6

Step2. 查看结果集的Cardinality

图片 7

Step3.
查看表的统计计划,最后分析时过久

图片 8

Step4. 分析表

BEGIN
   DBMS_STATS.gather_table_stats ('SH', 'SALES');
END;

Step5. 分析表后统计信息变更为最新

图片 9

Step6.
解析计划Cardinality变更更为精确

图片 10

 

Thanks and Regards

参考: 一江水 –
http://www.cnblogs.com/rootq/archive/2010/02/04/1663622.html

参考: David –
http://blog.csdn.net/tianlesoftware/article/details/4668723

参考: Edwardking888 –
http://blog.itpub.net/8183550/viewspace-666335/

图片 11

相关文章