Histogram與統計信息

    什麽是Histogram?為什麼需要？
    數據庫軟件版本10.2.0.3.0，統計表信息的job:DBMS_STATS.GATHER_TABLE_STATS是默認開啟的，搜集列的信息建立列的histogram。該列上建有索引，就會建有histgoram.
histogram 描述列數據的分佈情況，一个列上数值的大致分布的密度(density)和范围(range)，CBO使用histgrams来更加准确的判断按照某个条件对每一列查询能返回多少记录

     Histogram有哪些值?
    NONE:沒有直方圖
    Frequency:頻率直方圖，列的distinct值數量<=bucket數量，
    Height Balanced:高度平行直方圖，列的distinct值數量>bucket數量，
    在這裡解析一下bucket,存儲桶，取值範圍0~254，個人理解存儲里有rowid等信息，每个桶中的rowid数并不相等，它会将高频出现的记录集中在

某一些桶中，低频记录会存放在少量桶中，如果低频记录存入高频桶中，影响优化器生成的执行计划不夠準確。
    疑問?
    bucket存放什麼信息？
    為什麼低频记录存入高频桶中，會影响优化器生成的执行计划不夠準確

     如何查看Histogram?
   首先查看列的統計信息：
    SELECT * FROM DBA_TAB_COL_STATISTICS  WHERE TABLE_NAME ='LOT'
    AND OWNER='HBLCMPD'
    AND COLUMN_NAME='LOTNAME'
   再來查看列的直方圖信息：
    SELECT *
    FROM DBA_HISTOGRAMS
    WHERE TABLE_NAME ='LOT'
    AND OWNER='HBLCMPD'
    AND COLUMN_NAME='PRODUCTIONTYPE'
    ORDER BY ENDPOINT_NUMBER;  

   什麽情況使用Histogram？
    主要是在字段上的数据有高度的数据分布的不均匀，即有部分数据占整个数据量的比例很大，这样特征的表应该建立Histogram。
    如果查詢的字段值是數據量少的值，採用索引訪問；如果是數據量大的值，採用全表訪問會更快。
    如果where字句不引用該列，那麼該列的histogram完全沒有意義
    
    什麽情況避免使用Histogram？
1) 这部分字段不会在where字句中使用；
2) 字段是唯一的，只是在等值使用；
3) 字段用于绑定变量,說明數據是分佈均勻的；
4) 字段中的数据是被均匀分配的
    在使用绑定变量的时候，Oracle的优化器并不知道这个字段上将绑定的变量的确切的值。所以并不能用到这个Histogram统计

数据。
    字段的值是唯一的，说明在这个字段上所有的值将是均匀分布的。
    条件是等值使用的时候，Histogram统计数据是没有任何意义的，所以Histogram只在範圍查詢時用到。

   如何手動搜集某列的 Histogram
   exec dbms_stats.gather_table_stats(ownname=>‘HBLCMPD’,tabname=>' LOTHISTORY',method_opt=>'for column EVENTNAME size auto');

Oracle調動scheduler job “GATHER_STATS_JOB”收集統計信息（Oracle defined automatic optimizer statistics collection program）
    查看scheduler job是否開啟
  select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'
    該job 默認開啟時間22:00~06:00，周末週日全日開啟；
    目的：收集對象的詳細信息，用於優化器選擇最好的執行計劃
    收集的過濾條件：
      對象的統計信息之前沒有收集過；
      對象超過10%的rows被修改； 

    收集哪些統計信息？
    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
   
 

修改參數 statistics_level 為Basic,同時也會禁用AWR等的信息收集，所以通常使用下列語句僅僅對job停用：
    如何停用與開啟該JOB ?
     exec DBMS_SCHEDULER.DISABLE(‘GATER_STATS_JOB’);
     exec DBMS_SCHEDULER.ENABLE(‘GATER_STATS_JOB’);

    如何不統計所有表？
    --鎖定表統計
    Exec dbms_stats.lock_table_stats(‘HBLCMPD’,’ LOTHISTORY’);
    --解除鎖定：
    Exec dbms_stats.unlock_table_stats(‘HBLCMPD’,’ LOTHISTORY’);
    --查看表統計是否被鎖定
    SELECT STATTYPE_LOCKED FROM dba_tab_statistics where TABLE_NAME='LOTHISTORY'

    統計信息會引起的問題？
    1)統計會掃描整張表，產生大量的物理IO與邏輯IO,導致數據庫緩慢，表越大，情況越嚴重，通常將表進行分區就可解決;
    2)可能會產生latch 競爭，觀察某個數據庫一段時間，每隔12天左右，AP就反映DB晚上某時段緩慢，查看該時間段的AWR，發現GATHER_STATS_JOB佔用大量的物理IO與邏輯IO,且某schema下有個大表(超過100G)，AP每次反映緩慢的時候，oracle都有統計該表,看Instance Efficiency Percentages的Latch Hit% 都大於99%說明沒有Latch競爭,所以應該是表太大導致大量物理IO引起DB緩慢。  

如何手動收集統計信息?

    exec dbms_stats.gather_table_stats(ownname=>‘HBLCMPD’,tabname=>' LOTHISTORY');

介紹一下dbms_stats.gather_table_stats

统计表,列,索引的统计信息（默认参数下是对表进行直方图信息收集，包含该表的自身-表的行数、数据块数、行长等信息；列的分析--列值的重复数、列上的空值、数据在列上的分布情况；索引的分析-索引页块的数量、索引的深度、索引聚合因子

DBMS_STATS.GATHER_TABLE_STATS (

ownname    VARCHAR2, 分析表的擁有者

tabname     VARCHAR2,  分析表名

partname    VARCHAR2,  分區的名字

estimate_percent   NUMBER, 采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.

block_sample  BOOLEAN,   是否用块采样代替行采样

method_opt    VARCHAR2, 决定histograms信息是怎样被统计的,取值如下：
     for all columns:统计所有列的histograms.

for all indexed columns:统计所有indexed列的histograms.

for all hidden columns:统计你看不到列的histograms

    for columns SIZE | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围[1,254]，N指定存儲桶的個數;
       REPEAT 只更新上次统计过的histograms，不新增bucket;
       AUTO由oracle决定N的大小;
       SKEWONLY multiple end-points with the same value which is what we define by "there is skew in thedata

    默認值：for all columns size auto

degree       NUMBER,  并行度，默認NULL

granularity    VARCHAR2, 分區表統計的粒度，默認值為AUTO，取值如下：

    all：对表达全局，分区，子分区的数据都做分析

auto：Oracle根据分区的类型，自动决定做哪一种粒度的分析

global：只做全局级别的分析

global and partition：只对全局和分区级别做分析，对子分区不做分析，这是和all的一个区别

partition：只做分区级别做分析

subpartition：只做子分区做分析

cascade   BOOLEAN, 是否收集索引的信息，默認值為FALSE

stattab    VARCHAR2, 指定要存储统计信息的表

statid     VARCHAR2, 如果多个表的统计信息存储在同一个stattab中,statid用于进行区分

statown   VARCHAR2, 存儲統計信息表的擁用者,

         stattab,statid,statown都不指定，統計信息會直接更新到數據字典

no_invalidate  BOOLEAN, share pool中的共享游標是否仍然有效，TRUE, 原有的共享游標不会被失效，可以支持共享。只有当被age out或者flush out出shared pool之后，新执行计划才能生成；FALSE, 原有的共享游标被失效，下一次在执行SQL的时候，Oracle会重新为其生成执行计划，也就是一次hard parse过程

force    BOOLEAN 即使表鎖住，也進行統計，默認值為true

);

--查看表的統計信息：

Select * from dba_tab_statistics where table_name=
--查看表的歷史統計記錄
select * from dba_tab_stats_history where

--查看索引的統計信息：

Select * from dba_ind_statistics where table_name=

--查看表列的統計信息：

Select * from dba_tab_col_statistics where table_name=
--查看列的直方圖信息：
    SELECT *
    FROM DBA_HISTOGRAMS
    WHERE TABLE_NAME ='LOT'
    AND OWNER='HBLCMPD'
    AND COLUMN_NAME='PRODUCTIONTYPE'
    ORDER BY ENDPOINT_NUMBER;

其他相關的統計函數：

--创建统计信息历史保留表

sql> exec dbms_stats.create_stat_table(ownname => 'scott',stattab => 'stat_table') ;

--导出整个scheme的统计信息

sql> exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => 'stat_table') ;

--表較多，統計整個scheme

Exec dbms_stats.gather_schema_stats(

ownname => 'scott',

options => 'GATHER AUTO',

estimate_percent => dbms_stats.auto_sample_size,

method_opt => 'for all indexed columns ',

degree => 6 )

--統計表

sql> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'work_list',estimate_percent => 10,method_opt=> 'for all indexed columns') ;

--統計索引

SQL> exec dbms_stats.gather_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',estimate_percent => '10',degree => '4') ;

--如果发现执行计划走错，删除表的统计信息

SQL>dbms_stats.delete_table_stats(ownname => 'scott',tabname => 'work_list') ;

--导入表的历史统计信息

sql> exec dbms_stats.import_table_stats(ownname => 'scott',tabname => 'work_list',stattab => 'stat_table') ;

--如果进行分析后，大部分表的执行计划都走错，需要导回整个scheme的统计信息

sql> exec dbms_stats.import_schema_stats(ownname => 'scott',stattab => 'stat_table');

--导入索引的统计信息

SQL> exec dbms_stats.import_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',stattab => 'stat_table')

--检查是否导入成功

SQL> select table_name,num_rows,a.blocks,a.last_analyzed from all_tables a where a.table_name='WORK_LIST';

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29564184/viewspace-1132073/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29564184/viewspace-1132073/