oracle 设置自动采样,oracle的优化(3)——自动收集统计信息Auto Maintenance Tasks和动态采样optimizer_dynamic_sampling...

在oracle中10g开始，由于CBO被广泛推广，而oracle的CBO又是基于对象的统计信息的，所以自动收集对象统计信息Automatic Optimizer Statistics Collection这一特性被引入。那我们就来研究一下这一特性。

在oracle10g的时候自动收集对象统计信息的是GATHER_STATS_JOB任务，如果用dbca建库的话，那默认就会跑这个job，在每个工作日的晚上也周日和周末的全天被执行。在dba_scheduler_jobs这个视图中可以查看到这个job。

在oracle11g中，这点做了一个修正。我们引入了一个新的特性，叫做Auto Maintenance Tasks，也被叫做Autotasks(后文都用这个简称了)。在11g中dba_scheduler_jobs视图里已经看不到这个job了，采用window的方式来完成自动的job(和10g不同，现在已经分成Monday一直到Sunday，以前只分weekday和weekend)，只有在这个job真正跑的时候才可以在dba_scheduler_jobs视图里看到这个job的真正名字。并且自动收集统计信息已经成为Autotasks的一部分了(一共有3个部分，另外两个是自动段管理Automatic Segment Advisor，自动sql语句优化Automatic SQL Tuning Advisor)，所有有关于Autotasks的管理可以通过DBMS_AUTO_TASK_ADMIN这个包来完成，

BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE(

client_name =>'auto optimizer stats collection',

operation =>NULL,

window_name =>NULL);

END;

/

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

client_name =>'auto optimizer stats collection',

operation =>NULL,

window_name =>NULL);

END;

/

相比以前独立的job，现在都组合在一起管理了，逻辑上更加便于理解。而优点二在下面的一个参数说明中一起给出。

需要注意的是如果在安装数据库软件的时候同时选择了建库，那是不会启动自动收集统计信息这一特性的，只有使用dbca建库才会有这个选项。在dbca建库的时候也会默认选上这个选项，如图：

然后第二个话题是关于参数optimizer_dynamic_sampling。o小白研究这个参数的起因是由于自己在做关于CBO的实验的时候发现oracle总是很智能，无论如何调整表的数据，oracle总是按照比较优的方式去走执行计划，而o小白从来没有收集过统计信息，在经过询问查询之后知道有这么一个参数。并且我们可以看到，在oracle10g的时候，有人推荐我们把自动收集统计信息的job给停用掉，由于很多生产库是24×7的模式，系统资源相当重要，而如果定时定期跑这个job的话(特别是双休日是全天的)，那肯定会消耗系统资源。在oracle11g中改成了Autotasks以后，这也是Autotasks的第二个优点，这个脚本占用的系统资源数码被限制，尽量少影响重要的业务的运作。即使如此，我们也有可能禁掉这个tasks来保证数据库的运行，这时optimizer_dynamic_sampling参数就有了作用。

正如这个参数的字面意思所说，这个参数是控制在执行sql语句时oracle自动对对象进行动态采样的等级，简单来说就是控制oracle在执行sql语句时是否进行对对象的信息进行采样。

这个参数共分为10个等级：

等级

采样条件

采样大小(Blocks)

0

不采样

-

1

查询中：

a)至少有一个未分析过的非分区表。

b)未分析过的表没有索引。

c)未分析的表的数据块数比采样的数据块数更多。

32

2

查询中至少有一张表没有统计数据。

64

3

查询中：

a)满足等级2。

b)查询语句中使用的where谓词部分有一个或多个表达式，例如WHERE SUBSTR(CUSTLASTNAME,1,3).

64

4

查询中：

a)满足等级3.

b)查询语句中使用复杂的where谓词(对同一张表使用or或者and来连接多个谓词)

64

56

78

9

查询中满足等级4

128256

512 1024 4096

10

无论如何都进行采样。

所有数据块

从10g开始，oracle默认把optimizer_dynamic_sampling的等级设为2，那就会出现如果对象没有统计信息，oracle就会在每次查询的时候进行采样，所以oracle看起来会相当的智能。