【DB笔试面试628】Oracle的统计信息包括哪几种类型？

小麦苗DBA宝典

发布于 2019-09-29 16:18:30

6850

发布于 2019-09-29 16:18:30

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

♣

题目部分

Oracle的统计信息包括哪几种类型？

♣

答案部分

Oracle数据库里的统计信息是一组存储在数据字典里，且从多个维度描述了数据库里对象的详细信息的一组数据。当Oracle数据库工作在CBO（Cost Based Optimization，基于代价的优化器）模式下时，优化器会根据数据字典中记录的对象的统计信息来评估SQL语句的不同执行计划的成本，从而找到最优或者是相对最优的执行计划。所以，可以说，SQL语句的执行计划由统计信息来决定，若没有统计信息则会采取动态采样的方式来生成执行计划。统计信息决定着SQL的执行计划的正确性，属于SQL执行的指导思想。若统计信息不准确，则会导致表的访问方式（例如应该使用索引，但是选择了全表扫描）、表与表的连接方式出现问题（例如应该使用HJ，但是使用了NL连接），从而导致CBO选择错误的执行计划。

统计信息主要包括6种类型，其中表、列和索引的统计信息也可以统称为普通对象的统计信息，如下所示：

查询表统计信息的SQL如下所示：

SELECT D.NUM_ROWS, --表中的记录数
       D.BLOCKS, --轰中数据所占的数据块数
       D.EMPTY_BLOCKS, --表中的空块数
       D.AVG_SPACE, --数据块中平均的，使用空间
       D.CHAIN_CNT, --表中行连接和行迁移的数量
       D.AVG_ROW_LEN, --每条记录的平均长度
       D.STALE_STATS, --统计信息是否过期
       D.LAST_ANALYZED --最近一次搜集统计信息的时间
  FROM DBA_TAB_STATISTICS D  --DBA_TAB_STATISTICS DBA_TABLES
 WHERE D.TABLE_NAME = 'CUSTOMERS';

查询表上列的统计信息的SQL如下所示：

SELECT D.COLUMN_NAME,
       D.NUM_DISTINCT, --唯一值的个数
       D.LOW_VALUE, --列上的最小值
       D.HIGH_VALUE, --列上的最大值
       D.DENSITY, --若不存在柱状图的话,则表示选择率因子（密度）=1/(NDV)
       D.NUM_NULLS, --空值的个数
       D.NUM_BUCKETS, --直方图的BUCKETS个数
       D.HISTOGRAM --直方图的类型
  FROM DBA_TAB_COLUMNS D  --DBA_TAB_COL_STATISTICS
 WHERE TABLE_NAME = 'CUSTOMERS';

关于上表中需要注意的几点：

（一）索引统计信息

BLEVEL存储的就是目标索引的层级，它表示的是从根节点到叶子块的深度，BLEVEL被CBO用于计算访问索引叶子块的成本。BLEVEL的值越大，则从根节点到叶子块所需要访问的数据块的数量就会越多，耗费的I/O就会越多，访问索引的成本就会越大。BLEVEL的值从0开始算起，当BLEVEL的值为0时，表示该B树索引只有一层，且根节点和叶子块就是同一个块。在Oracle数据库里，如果要降低目标B树索引的层级，那么只能通过REBUILD该索引的方式来实现。

（二）列的统计信息

列的统计信息用于描述Oracle数据库里列的详细信息，包含了列的DISTINCT值的数量、列的NULL值的数量、列的最小值、列的最大值等一些典型维度。这些列统计信息实际上是存储在数据字典基表SYS.HIST_HEAD$中，可以通过数据字典DBA_TAB_COL_STATISTICS、DBA_PART_COL_STATISTICS和DBA_SUBPART_COL_STATISTICS来分别查看表、分区表的分区和分区表的子分区的列统计信息。在这些数据字典中的字段NUM_DISTINCT存储的就是目标列的DISTINCT值的数量。CBO用NUM_DISTINCT的值来评估用目标列做等值查询的可选择率（Selectivity）。CBO会用NUM_NULLS的值来调整对有NULL值的目标列做等值查询的可选择率。

数据字典中的字段DENSITY和NUM_BUCKETS分别存储的是目标列的密度和所用桶的数量，这两个维度仅和直方图有关。在没有直方图统计信息时，DENSITY的值就等于I/NUM_DISTINCT；在有频率直方图的时，DENSITY的值就等于1/(2*(NUM_ROWS-NUM_NULLS))。示例如下：

CREATE TABLE T_MD_20170606_LHR AS SELECT ROWNUM ID,ROWNUM SAL FROM DUAL CONNECT BY LEVEL<=10000;
UPDATE T_MD_20170606_LHR SET SAL=5000 WHERE SAL BETWEEN 6 AND 9995;  --9990 
UPDATE T_MD_20170606_LHR SET SAL='' WHERE SAL BETWEEN 2 AND 3;  --2

在无直方图的情况下：

LHR@orclasm > EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_MD_20170606_LHR',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

LHR@orclasm > SET LINESIZE 120
LHR@orclasm > SELECT D.COLUMN_NAME,D.NUM_DISTINCT,D.NUM_NULLS,D.NUM_BUCKETS,D.HISTOGRAM,D.DENSITY FROM DBA_TAB_COLUMNS D WHERE D.TABLE_NAME = 'T_MD_20170606_LHR';


COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS NUM_BUCKETS HISTOGRAM          DENSITY
------------------------------ ------------ ---------- ----------- --------------- ----------
SAL                                       9          2           1 NONE            .111111111
ID                                    10000          0           1 NONE                 .0001

LHR@orclasm > SELECT 1/9,1/10000 FROM DUAL; 

       1/9    1/10000
---------- ----------
.111111111      .0001

在有直方图的情况下：

LHR@orclasm > EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_MD_20170606_LHR',CASCADE=>TRUE,METHOD_OPT=>'FOR COLUMNS SAL SIZE 9'); 

PL/SQL procedure successfully completed.

LHR@orclasm > SELECT D.COLUMN_NAME,D.NUM_DISTINCT,D.NUM_NULLS,D.NUM_BUCKETS,D.HISTOGRAM,D.DENSITY FROM DBA_TAB_COLUMNS D WHERE D.TABLE_NAME = 'T_MD_20170606_LHR';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS NUM_BUCKETS HISTOGRAM          DENSITY
------------------------------ ------------ ---------- ----------- --------------- ----------
SAL                                       9          2           9 FREQUENCY        .00005001
ID                                    10000          0           1 NONE                 .0001

LHR@orclasm > SELECT 1/(2*(10000-2)) FROM DUAL;

1/(2*(10000-2))
---------------
      .00005001

数据字典中的字段LOW_VALUE和HIGH_VALUE分别存储的就是目标列的最小值和最大值，CBO用LOW_VALUE和HIGH_VALUE来评估对目标列做范围查询时的可选择率。不过这两个字段的返回值是RAW类型的，需要转换后才能识别。可以使用UTL_RAW.CAST_TO_NUMBER、UTL_RAW.CAST_TO_VARCHAR2等函数来转换，也可以使用存储过程DBMS_STATS.CONVERT_RAW_VALUE来转换，下面给出示例：

CREATE OR REPLACE FUNCTION FUN_DISPLAY_RAW_LHR(P_RAWVAL RAW,P_TYPE   VARCHAR2)
  RETURN VARCHAR2 IS
  V_NUMBER    NUMBER;
  V_VARCHAR2  VARCHAR2(32);
  V_DATE      DATE;
  V_NVARCHAR2 NVARCHAR2(32);
  V_ROWID     ROWID;
  V_CHAR      CHAR(32);
BEGIN
  IF (P_TYPE = 'NUMBER' OR P_TYPE = 'FLOAT') THEN
    DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_NUMBER);
    RETURN TO_CHAR(V_NUMBER);
  ELSIF (P_TYPE = 'VARCHAR2') THEN
    DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_VARCHAR2);
    RETURN TO_CHAR(V_VARCHAR2);
  ELSIF (P_TYPE = 'DATE' OR P_TYPE LIKE 'TIMESTAMP%') THEN
    DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_DATE);
    RETURN TO_CHAR(V_DATE);
  ELSIF (P_TYPE = 'NVARCHAR2') THEN
    DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_NVARCHAR2);
    RETURN TO_CHAR(V_NVARCHAR2);
  ELSIF (P_TYPE = 'ROWID') THEN
    DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_ROWID);
    RETURN TO_CHAR(V_ROWID);
  ELSIF (P_TYPE = 'CHAR') THEN
    DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_CHAR);
    RETURN TO_CHAR(V_CHAR);
  ELSIF (P_TYPE = 'RAW') THEN
    RETURN TO_CHAR(P_RAWVAL);
  ELSE
    RETURN 'UNKNOWN DATATYPE!';
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'ERRORS!';
END FUN_DISPLAY_RAW_LHR;

使用该函数查询：

CREATE TABLE T_AA_20170606_LHR AS SELECT * FROM DBA_OBJECTS;
EXEC DBMS_STATS.gather_table_stats(USER,'T_AA_20170606_LHR');
SELECT D.COLUMN_NAME,
       D.LOW_VALUE,
       D.HIGH_VALUE,
       D.DENSITY,
       D.NUM_DISTINCT,
       D.NUM_NULLS,
       D.NUM_BUCKETS,
       D.HISTOGRAM,
       D.DATA_TYPE,
       FUN_DISPLAY_RAW_LHR(D.LOW_VALUE, D.DATA_TYPE) LOW_VALUE1,
       FUN_DISPLAY_RAW_LHR(D.HIGH_VALUE, D.DATA_TYPE) HIGH_VALUE1--,
       --UTL_RAW.CAST_TO_NUMBER(D.LOW_VALUE) LOW_VALUE2,
       --UTL_RAW.CAST_TO_NUMBER(D.HIGH_VALUE) HIGH_VALUE2,
  FROM USER_TAB_COLS D
 WHERE D.TABLE_NAME = 'T_AA_20170606_LHR';

& 说明：

有关转换的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140335/

（三）系统统计信息

系统统计信息主要包括目标数据库服务器CPU的主频、单块读的平均耗费时间、多块读的平均耗费时间和单次多块读所能读取的数据块的平均值等。收集系统统计信息的方法主要是使用系统存储过程：

EXEC DBMS_STATS.GATHER_SYSTEM_STATS('start');
系统正常负载运行一段时间
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('stop');

或：

EXEC DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE => 'INTERVAL',INTERVAL =>1);--INTERVAL为间隔时长，单位为分钟

系统统计信息主要存储在SYS.AUX_STATS$表中，也可以使用DBMS_STATS.GET_SYSTEM_STATS获取系统统计信息的内容，修改系统统计信息可以使用DBMS_STATS.SET_SYSTEM_STATS，删除系统统计信息可以使用DBMS_STATS.DELETE_SYSTEM_STATS。

在未引入系统统计信息之前，CBO所计算的成本值全部是基于I/O来计算的；在Oracle引入了系统统计信息之后，实际上就额外地引入了CPU成本计算模型（CPU Cost model），从此以后，CBO所计算的成本值就包括I/O Cost和CPU Cost这两个部分。CBO在计算成本的时候就会分别对它们各自计算，并将算出来的I/O Cost和CPU Cost值的总和作为目标SQL新的成本值。

从Oracle 9i开始，Oracle通过一个隐含参数“_OPTIMIZER_COST_MODEL”来控制是否开启CPU Cost model。该参数的默认值为CHOOSE，意思是如果SYS.AUX_STATS$表里有相关记录，那么表示开启CPU Cost model，否则就还是沿用以前的成本计算模型（即计算的成本全部是I/O Cost）。

SYS@orclasm > set pagesize 9999
SYS@orclasm > set line 9999
SYS@orclasm > col NAME format a40
SYS@orclasm > col KSPPDESC format a50
SYS@orclasm > col KSPPSTVL format a20
SYS@orclasm > SELECT a.INDX,
  2         a.KSPPINM NAME,
  3         a.KSPPDESC,
  4         b.KSPPSTVL 
  5  FROM   x$ksppi  a,
  6         x$ksppcv b
  7  WHERE  a.INDX = b.INDX
  8  and lower(a.KSPPINM) like  lower('%&parameter%');
Enter value for parameter: _OPTIMIZER_COST_MODEL
old   8: and lower(a.KSPPINM) like  lower('%&parameter%')
new   8: and lower(a.KSPPINM) like  lower('%_OPTIMIZER_COST_MODEL%')

      INDX NAME                                     KSPPDESC                                           KSPPSTVL
---------- ---------------------------------------- -------------------------------------------------- --------------------
      1917 _optimizer_cost_model                    optimizer cost model                               CHOOSE

SYS@orclasm > SET LINESIZE 9999
SYS@orclasm > COL PVAL1 FOR 999999999
SYS@orclasm > COL PVAL2 FOR A30
SYS@orclasm > COL SNAME FOR A15
SYS@orclasm > SELECT * FROM SYS.AUX_STATS$;

SNAME           PNAME                               PVAL1 PVAL2
--------------- ------------------------------ ---------- ------------------------------
SYSSTATS_INFO   STATUS                                    COMPLETED
SYSSTATS_INFO   DSTART                                    06-02-2017 13:54
SYSSTATS_INFO   DSTOP                                     06-02-2017 13:55
SYSSTATS_INFO   FLAGS                                   0
SYSSTATS_MAIN   CPUSPEEDNW                           1752
SYSSTATS_MAIN   IOSEEKTIM                              10
SYSSTATS_MAIN   IOTFRSPEED                           4096
SYSSTATS_MAIN   SREADTIM                                4
SYSSTATS_MAIN   MREADTIM
SYSSTATS_MAIN   CPUSPEED                             2099
SYSSTATS_MAIN   MBRC
SYSSTATS_MAIN   MAXTHR
SYSSTATS_MAIN   SLAVETHR

结果含义如下所示：

l CPUSPEEDNW：非工作量统计模式下CPU主频，即每秒可以完成的机器指命数据，直接来自硬件。

l IOSEEKTIM：I/O寻址时间（毫秒），默认值为10，直接来自硬件。

l IOTFRSPEED：I/O传输速率（字节/毫秒），默认为4096。

l SREADTIM：读取单个数据块的平均时间，单位是毫秒（ms）。

l MREADTIM：读取多个数据块的平均时间，单位是毫秒（ms）。

l CPUSPEED：工作量统计模式下CPU主频，根据当前工作量评估出一个合理值。

l MBRC：Oracle收集完统计信息后评估出的一次多块读可以读几个数据块（DB_FILE_MULTIBLOCK_READ_COUNT）。

l MAXTHR：最大I/O吞吐量（字节/秒）。

l SLAVETHR：单个并行进程的最大吞吐量（字节/秒）。

l SYSSTATS_INFO：系统统计信息的状态和收集时间。

l SYSSTATS_MAIN：系统统计信息的结果集。

l SYSSTATS_TEMP：只有当收集系统统计信息时才可用。

以下是10053事件的trace关于系统统计数据的部分内容：

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats
  CPUSPEEDNW: 3097 millions instructions/sec (default is 100)
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 16 milliseconds (default is 10)
  MBRC: -1 blocks (default is 16)

（四）内部对象统计信息

数据字典基表SYS.TAB_STATS$中会存储X$表的表对象统计信息。默认情况下（包括默认的自动统计信息收集作业在内），Oracle不会对X$系列表收集内部对象统计信息，所以默认情况下SYS.TAB_STATS$中没有任何记录。

需要注意的是，X$表虽然只是内存结构，不占用数据库的物理存储空间，但X$系列表的内部对象统计信息实际上已经被Oracle存储在了数据字典里，这些统计信息是占用了实际的物理存储空间的，这意味着X$表的统计信息已经被持久化了，并不会随着数据库的起停而消失。所以，实际上并不需要随着数据库的起停而对X$表反复收集内部对象统计信息，除非系统的负载发生了很大的变化，之前收集的内部对象统计信息已经不再具备代表性。

即使相关的X$表没有内部对象统计信息，Oracle也不会在访问这些X$表时使用动态采样。在明确诊断出系统已有的性能问题是因为X$表的内部对象统计信息不准引起的，这个时候就应该收集X$表的内部对象统计信息，其它情形就不要收集了。因为X$表实际上就是内存结构，所以在RAC环境下收集内部对象统计信息时需要在每个节点都进行收集统计信息。