我们在分析某些语句的性能时,会分析一些信息。像表、列、索引、直方图等等,本篇主要讲表与列的统计信息收集与分析。
一、表统计信息
首先创建一个测试表,更新一些数据,加入一些约束:
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