ORACLE表統計資訊與列統計資訊、索引統計資訊

531968912發表於2018-03-25
Oracle索引
ORACLE表統計資訊與列統計資訊

我們在分析某些語句的效能時，會分析一些資訊。像表、列、索引、直方圖等等，本篇主要講表與列、索引的統計資訊收集與分析。

一、表統計資訊

  1. 1. 表已建立，需要收集統計資訊
  2. BEGIN
  3. DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '',
  4.                                 TABNAME => '',
  5.                                 ESTIMATE_PERCENT => 100,
  6.                                 METHOD_OPT => 'for all columns size skewonly',
  7.                                 CASCADE => TRUE);
  8. END;

 2. 檢視錶的統計資訊用dba_tab_statistics。
  1. col OWNER for a16
  2. col table_name for a20
  3. col PARTITION_NAME for a18
  4. col OBJECT_TYPE for a12
  5. col LAST_ANALYZED for a20
  6. 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
  7.   FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = 'MSTB_ORDER_HEADER' ;

  9. 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


  11. 關於每一列的解釋聯機文件上都有()
  12. 這裡blocks是高水位以下的資料塊數，empty_blocks是高水位以上的資料塊數。
  13. Dbms_stats不計算EMPTY_BLOCKS、AVG_SPACE、CHAIN_CNT。可以使用ANALYZE收取(ANALYZE TABLE <tabname> COMPUTE STATISTICS)</tabname<>
  14. chain_cnt欄位表示行遷移和行連結的數量資訊.


二、列統計資訊

  1. 檢視列的統計資訊用DBA_TAB_COL_STATISTICS

  3. col COLUMN_NAME for a30
  4. col LOW_VALUE for a18
  5. col HIGH_VALUE for a24
  6. col HISTOGRAM for a20
  7. SELECT COLUMN_NAME,
  8.       NUM_DISTINCT,
  9.       LOW_VALUE,
  10.       HIGH_VALUE,
  11.       DENSITY ,
  12.       NUM_NULLS ,
  13.       AVG_COL_LEN ,
  14.       HISTOGRAM,
  15.       NUM_BUCKETS
  16.   FROM DBA_TAB_COL_STATISTICS
  17.  WHERE TABLE_NAME = '&TABLE_NAME';
  18.  
    1. 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


欄位解釋，可參考聯機文件（）

統計資訊解釋一下：
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

詳細使用方法可參考 “ORACLE utl_raw函式與dbms_stats.convert_raw_value函式使用”
http://blog.itpub.net/17086096/viewspace-1983619/




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25462274/viewspace-2152207/，如需轉載，請註明出處，否則將追究法律責任。

相關文章