关闭

Oracle收集表与列统计信息

标签: Oraclesqlstatistics性能
1344人阅读 评论(0) 收藏 举报
分类:

             我们在分析某些语句的性能时,会分析一些信息。像表、列、索引、直方图等等,本篇主要讲表与列的统计信息收集与分析。

一、表统计信息

             首先创建一个测试表,更新一些数据,加入一些约束:

CREATE TABLE t
AS
SELECT rownum AS id,
       round(dbms_random.normal*1000) AS val1,
       100 + round(ln(rownum/3.25+2)) AS val2,
       100 + round(ln(rownum/3.25+2)) AS val3,
       dbms_random.string('p',250) AS pad
FROM All_Objects
WHERE ROWNUM<=1000
ORDER BY dbms_random.value;

UPDATE T SET VAL1 = NULL WHERE VAL1 < 0;

ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY(ID);

CREATE INDEX t_val1_i ON t(val1);
CREATE INDEX t_val2_i ON t(val2);

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

             此时表已经搜集了统计信息,查看表的统计信息用user_tab_statistics。

SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN
  FROM USER_TAB_STATISTICS
 WHERE TABLE_NAME = 'T';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
      1000         44            0          0          0         265

             关于每一列的解释联机文档上都有,这里blocks是高水位以下的数据块数,empty_blocks是高水位以上的数据块数。Dbms_stats不计算EMPTY_BLOCKS、AVG_SPACE、CHAIN_CNT。

二、列统计信息

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
  FROM USER_TAB_COL_STATISTICS
 WHERE TABLE_NAME = 'T';
NAME   #DST LOW_VALUE            HIGH_VALUE                 DENS #NULL AVGLEN HISTOGRAM       NUM_BUCKETS
----- ----- -------------------- -------------------- ---------- ----- ------ --------------- -----------
ID     1000 C102                 C20B                       .001     0      4 NONE                      1
VAL1    450 C102                 C22D4B               .002513356   481      3 HEIGHT BALANCED         234
VAL2      6 C20202               C20207                    .0005     0      4 FREQUENCY                 6
VAL3      6 C20202               C20207                    .0005     0      4 FREQUENCY                 6
PAD    1000 202523422F3B40542B2D 7E6664433E4F2C2E515C       .001     0    251 HEIGHT BALANCED         254
            7276252F3C7035494769 3570536E7D7B303D546B
            323F275E773236205A4A 4D50244C42367A76536F
            2638                 6B6A

             这里几列统计信息解释一下:

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都是内部格式,所以必须转换为可读懂的格式,有两种方法。

①使用工具包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) AS LOW_VALUE,
       UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE) AS HIGH_VALUE
  FROM USER_TAB_COL_STATISTICS
 WHERE TABLE_NAME = 'T'
   AND COLUMN_NAME = 'VAL1';

LOW_VALUE HIGH_VALUE
--------- ----------
        1       4474

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

-- Created on 2013/12/4 by LENOVO 
DECLARE
  L_LOW_VALUE  USER_TAB_COL_STATISTICS.LOW_VALUE%TYPE;
  L_HIGH_VALUE USER_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 USER_TAB_COL_STATISTICS
   WHERE TABLE_NAME = 'T'
     AND COLUMN_NAME = 'VAL1';

  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;

low_value: 1
high_value: 4474

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:28217次
    • 积分:579
    • 等级:
    • 排名:千里之外
    • 原创:28篇
    • 转载:9篇
    • 译文:1篇
    • 评论:6条