Oracle收集表与列统计信息

原创 2013年12月04日 10:04:23

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

一、表统计信息

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

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

Oracle 查看收集统计信息

-- Start 统计信息相当于情报,对 Oracle 至关重要,如果统计信息不准确,Oracle 就会做出错误的判断。通常,Oracle 会在每天固定时间段自动维护统计信息。但是对于某些表,这是远远...
  • shangboerds
  • shangboerds
  • 2015年07月29日 17:27
  • 13433

Oracle 11G统计信息自动收集及调整

从Oracle 11G开始,数据库统计信息的自动收集被整合到自动维护任务中,满足大多数情形下的运行需求。但对于在线商城,交易系统而言,可能需要调整其执行时间或者自行指定收集窗口。本文描述了如何查看以及...
  • robinson_0612
  • robinson_0612
  • 2017年03月25日 11:05
  • 3653

oracle的统计信息的查看与收集

查看某个表的统计信息 SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; Session altered. SQL> s...
  • u010692693
  • u010692693
  • 2017年07月16日 15:39
  • 282

手工收集oracle统计信息

手工收集oracle某用户的统计信息 1.exec DBMS_STATS.GATHER_SCHEMA_STATS(‘NC57’) 2.execute dbms_stats.GATHER_SCHEM...
  • luckchang
  • luckchang
  • 2013年09月15日 10:20
  • 1110

Oracle收集表的数据与统计信息差异

有时候有这样的烦恼,由于dmp增量到数据库中,或是大批量数据操作后没有收集表的统计信息,导致数据库性能慢。要手工写脚本检查。 drop table gather_tcount; create tabl...
  • guogang83
  • guogang83
  • 2016年03月28日 15:46
  • 658

Oracle11g 多列收集统计信息--直方图

最近发现有一条SQL要执行几百秒,是执行计划走错了,为什么走错呢?来看一下: SELECT *   FROM (SELECT *           FROM (SELECT INNER_TABLE....
  • guogang83
  • guogang83
  • 2017年01月06日 10:39
  • 728

如何 找出未收集统计信息,以及统计信息过期的表

 下面这个查询可以找到从未收集过统计信息或者统计信息过期的表。 EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; SELECT OWNER,TA...
  • xu1314
  • xu1314
  • 2016年02月26日 11:00
  • 1006

Oracle收集统计信息和重建索引

1.统计信息介绍: Statistic对Oracle是非常重要的。它会收集数据库中对象的详细信息,并存储在相应的数据字典里。根据这些统计信息,optimizer可以对每个SQL去选择最好的执行计划。...
  • csdsxsx
  • csdsxsx
  • 2013年04月20日 16:15
  • 2111

分区表统计信息用法

我们在收集普通表的时候,经常使用Analyze来收集一下表的统计信息,但是在分区表这样做是有问题的,所以我们要用dbms_stats.gather_table_stats来收集分区信息,这样做的好处1...
  • suyishuai
  • suyishuai
  • 2013年12月27日 09:10
  • 2056

Oracle 手动搜集统计信息

--禁用自动收集 exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operatio...
  • zhaoyangjian724
  • zhaoyangjian724
  • 2015年08月29日 09:46
  • 953
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle收集表与列统计信息
举报原因:
原因补充:

(最多只允许输入30个字)