话聊Oracle统计信息的那点事

杨磊

746

7页

11次

2020-01-15

5墨值下载

【前言】

在

Oracle

的

11g

版本中，统计信息为自动收集功能，在部署安装

11g Oracle

软件过程中，其中有一个步骤

便是提示是否启动这个功能

(

默认是启用这个功能

)

；且有时候在生产环境中，还会对一些对象做手动统计

信息的搜集，如果新搜集的统计信息产生的执行计划在实际生产中不符和生产要求，则需继续使用原有的

执行计划。

因平时接触此部分内容不是很频繁，但统计信息的搜集无疑又很重要，故本文对自动统计信息搜集及常规

操作做下介绍。

【大纲】

啥事统计信息

2. Oracle

的统计信息自动搜集策略；

手动搜集的常用命令；

一、

统计信息是个啥？

说统计信息前，先要说下

Oracle

的优化器

Oracle

数据库中的优化器又叫查询优化器（

Query Opmizer

）。它是

SQL

分析和执行的优化

工具，它负责生成、制定

SQL

的执行计划。

Oracle

的优化器有两种，基于规则的优化器

（

RBO

）与基于代价的优化器（

CBO)

RBO: Rule-Based Opmizaon

基于规则的优化器

CBO: Cost-Based Opmizaon

基于代价的优化器

RBO

自

ORACLE 6

以来被采用，一直沿用至

ORACLE 9i. ORACLE 10g

开始，

ORACLE

已经彻底

丢弃了

RBO

，它有着一套严格的使用规则，只要你按照它去写

SQL

语句，无论数据表中的

内容怎样，也不会影响到你的“执行计划”，也就是说

RBO

对数据不“敏感”；它根据

ORACLE

指定的优先顺序规则，对指定的表进行执行计划的选择。比如在规则中，索引的优先级大

于全表扫描；在

RBO

中，

SQL

的写法往往会影响执行计划，这就要求开发人员非常了解

RBO

的各项细则，菜鸟写出来的

SQL

脚本性能可能非常差。

CBO

是一种比

RBO

更加合理、可靠的优化器，它是从

ORACLE 8

中开始引入，但到

ORACLE

中才逐渐成熟，在

ORACLE 10g

中完全取代

RBO

，

CBO

是计算各种可能“执行计划”的“代

价”，即

COST

，从中选用

COST

最低的执行方案，作为实际运行方案。它依赖数据库对象的

统计信息，统计信息的准确与否会影响

CBO

做

出最优的选择。如果对一次执行

SQL

时发现涉及对

象（表、索引等）没有被分析、统计过，那么

ORACLE

会采用一种叫做动态采样的技术，动态的收集表

和索引上的一些数据信息。

好了，这里改说下优化器依赖哪些统计信息，优化器统计范围：

表统计：

行数，块数，行平均长度；

all_tables

：

NUM_ROWS

，

BLOCKS

，

AVG_ROW_LEN

；

列统计：

列中唯一值的数量（

NDV

），

NULL

值的数量，数据分布；

--DBA_TAB_COLUMNS

：

NUM_DISTINCT

，

NUM_NULLS

，

HISTOGRAM

；

索引统计：

叶块数量，等级，聚簇因子；

--DBA_INDEXES

：

LEAF_BLOCKS

，

CLUSTERING_FACTOR

，

BLEVEL

；

系统统计：

--I/O

性能与使用率；

--CPU

性能与使用率；

存储在

aux_stats$

中，需要使用

dbms_stats

收集，

I/O

统计在

X$KCFIO

中；

上一篇推文讲述了直方图，《【精品篇】

_Oracle

的直方图是个啥？》点击文章链接

简述直方图作用：

“当在表上收集统计时，

DBMS_STATS

收集表中列的数据分布的信息，数据分布最基本的信息是最大值和最

小值，但是如果数据分布是倾斜的，这种级别的统计对于优化器来说不够的，对于倾斜的数据分布，直方

图通常用来作为列统计的一部分。”

接下来讲一讲统计信息收集那点事

统计信息可分为自动收集和手动收集。

自动收集统计信息的情况：

自动统计收集在夜间进行，对所有更改活动中等的对象自动统计应该足够；使用如下命令查看；

SELECT WINDOW_NAME,

REPEAT_INTERVAL,

ENABLED,

ACTIVE,

duration

FROM dba_scheduler_windows;

WINDOW_NAME REPEAT_INTERVAL ENABLED ACTIVE duraon

MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 TRUE FALSE +00

04:00:00.000000

TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 TRUE FALSE +00

04:00:00.000000

WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 TRUE FALSE +00

04:00:00.000000

THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 TRUE FALSE +00

04:00:00.000000

FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 TRUE FALSE +00

04:00:00.000000

SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0TRUE FALSE +00

20:00:00.000000

SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 TRUE FALSE +00

20:00:00.000000

WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0

FALSE FALSE +00 08:00:00.000000

WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 FALSE FALSE +02

00:00:00.000000

可以看出每周一至周五

22:00

开始，历时

小时进行统计信息的自动收集；

每周六和周日早上

：

开始，历时

小时收集统计信息；

【前言】

在

Oracle

的

11g

版本中，统计信息为自动收集功能，在部署安装

11g Oracle

软件过程中，其中有一个步骤

便是提示是否启动这个功能

(

默认是启用这个功能

)

；且有时候在生产环境中，还会对一些对象做手动统计

信息的搜集，如果新搜集的统计信息产生的执行计划在实际生产中不符和生产要求，则需继续使用原有的

执行计划。

因平时接触此部分内容不是很频繁，但统计信息的搜集无疑又很重要，故本文对自动统计信息搜集及常规

操作做下介绍。

【大纲】

啥事统计信息

2. Oracle

的统计信息自动搜集策略；

手动搜集的常用命令；

一、

统计信息是个啥？

说统计信息前，先要说下

Oracle

的优化器

Oracle

数据库中的优化器又叫查询优化器（

Query Opmizer

）。它是

SQL

分析和执行的优化

工具，它负责生成、制定

SQL

的执行计划。

Oracle

的优化器有两种，基于规则的优化器

（

RBO

）与基于代价的优化器（

CBO)

RBO: Rule-Based Opmizaon

基于规则的优化器

CBO: Cost-Based Opmizaon

基于代价的优化器

RBO

自

ORACLE 6

以来被采用，一直沿用至

ORACLE 9i. ORACLE 10g

开始，

ORACLE

已经彻底

丢弃了

RBO

，它有着一套严格的使用规则，只要你按照它去写

SQL

语句，无论数据表中的

内容怎样，也不会影响到你的“执行计划”，也就是说

RBO

对数据不“敏感”；它根据

ORACLE

指定的优先顺序规则，对指定的表进行执行计划的选择。比如在规则中，索引的优先级大

于全表扫描；在

RBO

中，

SQL

的写法往往会影响执行计划，这就要求开发人员非常了解

RBO

的各项细则，菜鸟写出来的

SQL

脚本性能可能非常差。

CBO

是一种比

RBO

更加合理、可靠的优化器，它是从

ORACLE 8

中开始引入，但到

ORACLE

中才逐渐成熟，在

ORACLE 10g

中完全取代

RBO

，

CBO

是计算各种可能“执行计划”的“代

价”，即

COST

，从中选用

COST

最低的执行方案，作为实际运行方案。它依赖数据库对象的

统计信息，统计信息的准确与否会影响

CBO

做

出最优的选择。如果对一次执行

SQL

时发现涉及对

象（表、索引等）没有被分析、统计过，那么

ORACLE

会采用一种叫做动态采样的技术，动态的收集表

和索引上的一些数据信息。

好了，这里改说下优化器依赖哪些统计信息，优化器统计范围：

表统计：

行数，块数，行平均长度；

all_tables

：

NUM_ROWS

，

BLOCKS

，

AVG_ROW_LEN

；

列统计：

列中唯一值的数量（

NDV

），

NULL

值的数量，数据分布；

--DBA_TAB_COLUMNS

：

NUM_DISTINCT

，

NUM_NULLS

，

HISTOGRAM

；

索引统计：

叶块数量，等级，聚簇因子；

--DBA_INDEXES

：

LEAF_BLOCKS

，

CLUSTERING_FACTOR

，

BLEVEL

；

系统统计：

--I/O

性能与使用率；

--CPU

性能与使用率；

存储在

aux_stats$

中，需要使用

dbms_stats

收集，

I/O

统计在

X$KCFIO

中；

上一篇推文讲述了直方图，《【精品篇】

_Oracle

的直方图是个啥？》点击文章链接

简述直方图作用：

“当在表上收集统计时，

DBMS_STATS

收集表中列的数据分布的信息，数据分布最基本的信息是最大值和最

小值，但是如果数据分布是倾斜的，这种级别的统计对于优化器来说不够的，对于倾斜的数据分布，直方

图通常用来作为列统计的一部分。”

接下来讲一讲统计信息收集那点事

统计信息可分为自动收集和手动收集。

自动收集统计信息的情况：

自动统计收集在夜间进行，对所有更改活动中等的对象自动统计应该足够；使用如下命令查看；

SELECT WINDOW_NAME,

REPEAT_INTERVAL,

ENABLED,

ACTIVE,

duration

FROM dba_scheduler_windows;

WINDOW_NAME REPEAT_INTERVAL ENABLED ACTIVE duraon

MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 TRUE FALSE +00

04:00:00.000000

TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 TRUE FALSE +00

04:00:00.000000

WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 TRUE FALSE +00

04:00:00.000000

THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 TRUE FALSE +00

04:00:00.000000

FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 TRUE FALSE +00

04:00:00.000000

SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0TRUE FALSE +00

20:00:00.000000

SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 TRUE FALSE +00

20:00:00.000000

WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0

FALSE FALSE +00 08:00:00.000000

WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 FALSE FALSE +02

00:00:00.000000

可以看出每周一至周五

22:00

开始，历时

小时进行统计信息的自动收集；

每周六和周日早上

：

开始，历时

小时收集统计信息；

of 7

5墨值下载

oracle 日常运维

关注

评论