度量快速开发平台-业界最好的全功能软件开发平台

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
热搜: 部件 流程 SQL
查看: 84|回复: 7

[分享] ORACLE表统计信息与列统计信息、索引统计信息

[复制链接]

542

主题

5919

帖子

1万

积分

作者

Rank: 7Rank: 7Rank: 7

积分
13589
发表于 2018-2-9 14:02:30 | 显示全部楼层 |阅读模式
ORACLE表统计信息与列统计信息
我们在分析某些语句的性能时,会分析一些信息。像表、列、索引、直方图等等,本篇主要讲表与列、索引的统计信息收集与分析。

一、表统计信息
  • 1. 表已创建,需要收集统计信息

                   
  •                         BEGIN
                   
  •                         DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '',
                   
  •                                                         TABNAME => '',
                   
  •                                                         ESTIMATE_PERCENT => 100,
                   
  •                                                         METHOD_OPT => 'for all columns size skewonly',
                   
  •                                                         CASCADE => TRUE);
                   
  •                         END;
                   

  •        
       

                 2. 查看表的统计信息用dba_tab_statistics。        

       


                   
  •                         col OWNER for a16
                   
  •                         col table_name for a20
                   
  •                         col PARTITION_NAME for a18
                   
  •                         col OBJECT_TYPE for a12
                   
  •                         col LAST_ANALYZED for a20
                   
  •                         SELECT OWNER,TABLE_NAME,PARTITION_NAME,OBJECT_TYPE,NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN,to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED,USER_STATS
                   
  •                           FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = 'MSTB_ORDER_HEADER' ;
                   

  •                
  •                         OWNER            TABLE_NAME  PARTITION_NAME OBJECT_TYPE    NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN LAST_ANALYZED USER_S
    ---------------- ----------- -------------- ------------ ---------- ---------- ------------ ---------- ---------- ----------- ------------- ------
    POS_ORDER        MSTB_ORDER_                TABLE          40445748    1928239            0          0          0         317 2016-01-07 17 NO
    POS_ORDER        MSTB_ORDER_ P_MAXVALUE     PARTITION             1         19            0          0          0         197 2016-01-07 16 NO
    POS_ORDER        MSTB_ORDER_ P_MOH_2009_10  PARTITION             0          0            0          0          0           0 2016-01-07 16 NO
    POS_ORDER        MSTB_ORDER_ P_MOH_2009_11  PARTITION             0          0            0          0          0           0 2016-01-07 16 NO
    POS_ORDER        MSTB_ORDER_ P_MOH_2009_12  PARTITION             0          0            0          0          0           0 2016-01-07 16 NO
    POS_ORDER        MSTB_ORDER_ P_MOH_2012_1   PARTITION             0          0            0          0          0           0 2016-01-07 16 NO
    POS_ORDER        MSTB_ORDER_ P_MOH_2012_10  PARTITION         73496       3299            0          0          0         260 2016-01-07 16 NO
    POS_ORDER        MSTB_ORDER_ P_MOH_2012_11  PARTITION         69908       3138            0          0          0         259 2016-01-07 16 NO
    POS_ORDER        MSTB_ORDER_ P_MOH_2012_12  PARTITION         66918       3018            0          0          0         260 2016-01-07 16 NO
    POS_ORDER        MSTB_ORDER_ P_MOH_2012_6   PARTITION         45502       1940            0          0          0         256 2016-01-07 16 NO
    POS_ORDER        MSTB_ORDER_ P_MOH_2012_7   PARTITION         53236       2279            0          0          0         256 2016-01-07 16 NO
    POS_ORDER        MSTB_ORDER_ P_MOH_2012_8   PARTITION         52568       2279            0          0          0         254 2016-01-07 16 NO
    POS_ORDER        MSTB_ORDER_ P_MOH_2012_9   PARTITION         71614       3219            0          0          0         260 2016-01-07 16 NO
    POS_ORDER        MSTB_ORDER_ P_MOH_2013_1   PARTITION         72958       3293            0          0          0         260 2016-01-07 16 NO
    POS_ORDER        MSTB_ORDER_ P_MOH_2013_10  PARTITION         75330       3449            0          0          0         261 2016-01-07 16 NO
                           

                   
  •                        
                   
  •                         关于每一列的解释联机文档上都有(http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_2114.htm#REFRN20376)
                   
  •                         这里blocks是高水位以下的数据块数,empty_blocks是高水位以上的数据块数。
                   
  •                         Dbms_stats不计算EMPTY_BLOCKS、AVG_SPACE、CHAIN_CNT。可以使用ANALYZE收取(ANALYZE TABLE <tabname> COMPUTE STATISTICS)</tabname<>
                   
  •                         chain_cnt字段表示行迁移和行链接的数量信息.
二、列统计信息
  • 查看列的统计信息用DBA_TAB_COL_STATISTICS
                   
  •                        

                   
  •                         col COLUMN_NAME for a30

                   
  •                         col LOW_VALUE for a18
                   
  •                         col HIGH_VALUE for a24
                   
  •                         col HISTOGRAM for a20
                   
  •                         SELECT COLUMN_NAME,
                   
  •                               NUM_DISTINCT,
                   
  •                               LOW_VALUE,
                   
  •                               HIGH_VALUE,
                   
  •                               DENSITY ,
                   
  •                               NUM_NULLS ,
                   
  •                               AVG_COL_LEN ,
                   
  •                               HISTOGRAM,
                   
  •                               NUM_BUCKETS
                   
  •                           FROM DBA_TAB_COL_STATISTICS
                   
  •                          WHERE TABLE_NAME = '&TABLE_NAME';
                   
  •                          
                   

                             
    •                                 COLUMN_NAME         NUM_DISTINCT LOW_VALUE       HIGH_VALUE          DENSITY  NUM_NULLS AVG_COL_LEN HISTOGRAM       NUM_BUCKETS
      ------------------- ------------ --------------- ---------------- ---------- ---------- ----------- --------------- -----------
      ORIGINAL_ORDER_PV           3707 80              C24C5F19          .00026976   40419026           2 NONE                      1
      RSVST3                         4 4230363235      4230363238              .25   40194414           2 NONE                      1
      INVOICE_FLAG                   2 80              C102             4.7902E-08   30012940           2 FREQUENCY                 2
      INVOICE_REPRINT                2 80              C102             1.7177E-07   37531990           2 FREQUENCY                 2
      POSWTO_FLAG                    1 80              80                        1          0           2 NONE                      1
      INVOICE_SALES_DATE           705 78720106010101  78730A1A010101   .002169197   32955696           3 HEIGHT BALANCED         254
      ORDER_SUM_QUANTITY          1400 C102            C4025A645B       .000714286    9823640           3 NONE                      1
      RECIEVABLE_AMOUNT          81810 3C1E2E5B66      C50A1C143415     .000844595          0           4 HEIGHT BALANCED         254
      ORDER_BV                  158632 3C640B66        C508590C3B35     6.3039E-06          0           4 NONE                      1
      ORDER_PV                  167806 3D582C5166      C4400707450B     5.9593E-06          0           4 NONE                      1
      SALE_DATE                   1293 78700206010101  78C70C04010101   .001996008          0           8 HEIGHT BALANCED         254
      REGION_CODE                    2 303530          333630           1.2352E-08          0           4 FREQUENCY                 2
      SHOP_CODE                    318 3031            6E756C6C          .00304878          0           5 HEIGHT BALANCED         254
      SHIPPING_WAREHOUSE           389 3031            5443305A         .002808989          0           5 HEIGHT BALANCED         254
                     
           
       
               
字段解释,可参考联机文档(http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_2101.htm#REFRN20275

统计信息解释一下:
NUM_DISTINCT:该列中唯一值的数量。
LOW_VALUE:该列的最小值。显示为内部存储格式。对于字符串列,只存储前32字节。
HIGH_VALUE:该列的最大值。显示为内部存储格式。对于字符串列,只存储前32字节。
DENSITY:0到1之间的一个小数。接近0表示对于该列的过滤操作能去掉大多数的行。接近1表示对于该列的过滤操作起不到什么作用。如果没有直方图,DENSITY=1/NUM_DISTINCT。
HISTOGRAM:表明是否有直方图信息,如果有,是什么类型?FREQUENCY表示频率类型,HEIGHT BALANCED表示平均分布类型;如果没有,则为NONE。
NUM_BUCKETS:直方图里的桶数。它表示一组同类的数值放在一起。直方图最少由一个桶组成。如果没有直方图,则为1,最大桶数为254。

这里的LOW_VALUE和HIGH_VALUE都是内部格式,所以必须转换为可读懂的格式,有两种方法。

1)使用工具包utl_raw提供的函数cast_to_binary_double、cast_to_binary_float、cast_to_binary_integer、cast_to_number、cast_to_nvarchar2、cast_to_raw和cast_to_varchar2。这些函数就是把内部存储格式转换为实际值。

SELECT UTL_RAW.CAST_TO_NUMBER(LOW_VALUE), UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE)
   FROM DBA_TAB_COL_STATISTICS
  WHERE TABLE_NAME = 'MSTB_ORDER_HEADER'
   AND COLUMN_NAME = 'GROUP_ORDER_ID';

UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE)
--------------------------------- ----------------------------------
                                 0                            7821636

2) 使用dbms_stats提供的过程convert_raw_value、convert_raw_value_nvarchar和convert_raw_value_rowid。该过程不能直接在SQL语句中使用,通常只用于PL/SQL程序中。

DECLARE
   L_LOW_VALUE  DBA_TAB_COL_STATISTICS.LOW_VALUE%TYPE;
   L_HIGH_VALUE DBA_TAB_COL_STATISTICS.HIGH_VALUE%TYPE;
   L_VAL1       T.VAL1%TYPE;
BEGIN
   SELECT LOW_VALUE, HIGH_VALUE
     INTO L_LOW_VALUE, L_HIGH_VALUE
     FROM DBA_TAB_COL_STATISTICS
  WHERE TABLE_NAME = 'MSTB_ORDER_HEADER'
   AND COLUMN_NAME = 'GROUP_ORDER_ID';

  DBMS_STATS.CONVERT_RAW_VALUE(L_LOW_VALUE, L_VAL1);
   DBMS_OUTPUT.PUT_LINE('low_value: ' || L_VAL1);
  DBMS_STATS.CONVERT_RAW_VALUE(L_HIGH_VALUE, L_VAL1);
   DBMS_OUTPUT.PUT_LINE('high_value: ' || L_VAL1);
END;
/

UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE)
--------------------------------- ----------------------------------
                                 0                            7821636



                   


回复

使用道具 举报

542

主题

5919

帖子

1万

积分

作者

Rank: 7Rank: 7Rank: 7

积分
13589
 楼主| 发表于 2018-2-9 14:03:33 | 显示全部楼层
回复 支持 反对

使用道具 举报

227

主题

2497

帖子

5707

积分

论坛元老

Rank: 8Rank: 8

积分
5707
发表于 2018-2-9 17:20:02 | 显示全部楼层
回复 支持 反对

使用道具 举报

542

主题

5919

帖子

1万

积分

作者

Rank: 7Rank: 7Rank: 7

积分
13589
 楼主| 发表于 2018-2-11 14:28:52 | 显示全部楼层
回复 支持 反对

使用道具 举报

227

主题

2497

帖子

5707

积分

论坛元老

Rank: 8Rank: 8

积分
5707
发表于 2018-2-11 17:37:00 | 显示全部楼层
回复 支持 反对

使用道具 举报

141

主题

1551

帖子

3573

积分

论坛元老

Rank: 8Rank: 8

积分
3573
发表于 2018-2-12 16:32:01 | 显示全部楼层
回复 支持 反对

使用道具 举报

141

主题

1551

帖子

3573

积分

论坛元老

Rank: 8Rank: 8

积分
3573
发表于 2018-2-12 16:59:51 | 显示全部楼层
回复 支持 反对

使用道具 举报

542

主题

5919

帖子

1万

积分

作者

Rank: 7Rank: 7Rank: 7

积分
13589
 楼主| 发表于 2018-2-23 14:09:16 | 显示全部楼层
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

手机版|小黑屋|重庆度量科技  本站关键词:快速开发平台

GMT+8, 2018-4-22 16:21 , Processed in 0.392928 second(s), 25 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表