BOBO手记系列之Oracle 统计信息收集.docx

BOBO手记系列之Oracle 统计信息收集.docx

《BOBO手记系列之Oracle 统计信息收集.docx》由会员分享，可在线阅读，更多相关《BOBO手记系列之Oracle 统计信息收集.docx（23页珍藏版）》请在冰豆网上搜索。

BOBO手记系列之Oracle统计信息收集

BOBO手记之统计信息收集

前言

我总结这篇关于统计信息收集的文章的目的在于想阐述一个重要性，一个对于Oracle基于CBO的优化思想的重要性，所谓，兵马未动，粮草先行！

又所谓，知己知彼，百战百胜！

如果DBA希望CBO能够准确的给出最优的执行计划，那你就要给它提供一个准确的信息。

统计信息收集到底重不重要呢？

很多DBA对这个问题可能觉得这是数据库自己的事，我需要过多的干预吗？

又或者，我的数据库中TOPSql的执行计划一直很好，我都已经把统计信息收集lock了，没所谓的。

这样做到底对不对呢？

我们说，对于业务处理模型简单的系统，这样做没有什么错，什么谓词越界、Bindpeeking、各种join（nestedloop,hash,merge）、table-drive的选择等对于这样的系统没什么影响。

但是，我们知道CBO是基于代价的优化方式，它会在不同的执行路径中选择成本值最小的路径来作为SQL的执行计划，而所谓成本的计算就是根据SQL所涉及到的表、索引、列等相关对象的统计信息，通过CBO内部的计算公式而得。

因此，统计信息收集策略的好与坏就直接决定了CBO的选择是否正确。

文章里面很多SQL为了文档美观，采用了截图的方式，童鞋们要是需要这些语句就自己照着敲吧，有点缺德，别骂我啊。

。

。

什么是统计信息？

就是存储在数据字典里的一组数据，它从多个角度描述了数据库对象的详细信息（num_rows,num_distinct,histogram……..）。

CBO（Cost-BasedOptimization）会根据这些信息计算出各种可能的执行成本，并从中选取cost最小的path来作为executionplan。

数据库中都有哪些统计信息呢？

内部对象（x$..）的统计信息，x$表只是Oracle的自定义内存结构，没有物理块；

系统（system）的统计信息，可以获取CPU/IO特性，CBO更好的估计实际成本；

数据字典（user$,file$..）的统计信息，和普通表收集的信息没啥子区别；

表的统计信息，包括num_rows,avg_row_len,blocks…..；

索引的统计信息，包括Clustering_factor（这个指标老重要了！

）,Blevel,Leaf_blocks….;

列的统计信息，包括num_distinct,low/high_value,num_nulls,histogram…

1、Oracle自动统计信息收集的维护

1.1统计信息收集的JOB

很多从事Oracle数据库工作时间较久的DBA都知道，8i，9i的时候要收集统计信息可以通过analyze语句和dbms_stats包来实现，通常我们会将常用表采用dbms_stats包的方式放到shell脚本中实现批量定时收集，10g以后，Oracle提供了自动收集统计信息的schedulejob（GATHER_STATS_JOB），11g（autooptimizerstatscollection）

提示：

1、统计信息收集作业首先为没有统计信息和进行了大量实质性修改（>10%）的对象收集统计信息，这样可以保证在Job的窗口范围内首先刷新最陈旧的统计信息。

童鞋们不要误认为这些Job每天都在重复劳动。

怎么判断某张表的DML变化比例呢？

可通过dba_tab_modifications视图中的inserts,updates,deletes,flags与TAB$中目标表的总记录数相比较来判断。

dba_tab_modifications视图的基表是”mon_mods_all$”,在每次对目标表进行统计信息收集后，”mon_mods_all$”中对应的记录将被删除。

大家要是感兴趣，可以自己做实验噻！

2、10g和11g的job名称虽然不同，但核心调用的程序包是一样的（gather_stats_prog）

1.1.1统计信息收集JOB的名称和状态查看

1、11g的schedulejob

2、10g的scheedulejob

1.1.2统计信息收集JOB的维护

Oracle10g和11g在统计信息的Job维护方面还是有区别的，下面详细介绍了统计信息收集作业的启动和关闭、Job的完成情况查看、Job的历史执行情况等等。

1.1.2.1统计信息收集Job的启动和关闭

11gJob的启动和关闭

关闭：

BEGIN

dbms_auto_task_admin.disable（

client_name=>'autooptimizerstatscollection',

operation=>NULL,

window_name=>NULL）;

END;

/

启动：

BEGIN

dbms_auto_task_admin.enable（

client_name=>'autooptimizerstatscollection',

operation=>NULL,

window_name=>NULL）;

END;

/

例图：

10gJob的启动和关闭（两种方法）

方法一（推荐方法）：

execdbms_scheduler.disable（'SYS.GATHER_STATS_JOB'）;

execdbms_scheduler.enable（'SYS.GATHER_STATS_JOB'）;

例图：

方法二：

altersystemset"_optimizer_autostats_job"=falsescope=spfile;

altersystemset"_optimizer_autostats_job"=truescope=spfile;

1.1.2.2统计信息收集Job的执行细节查看

11g统计信息收集Job运行细节

10g统计信息收集运行细节

细心的大哥大姐们会发现10g和11g的job_name不一样了是不，其实，本质是一样的，11g在dba_scheduler_job_run_details中看到的”ORA$AT_OS_OPT_SY_***”就是自动任务”GATHER_STATS_PROG”所对应的client”autooptimizerstatscollection”生成的Job。

大家看到的status都是SUCCEEDED，意思是都成功完成了作业，其实这个状态还有很多种，在数据仓库或者TB级数据库中我们经常会看到STOPPED状态，意思是在执行窗口期没有完成，信息收集被强制停止了。

如果我们发现这种情况，就要针对性的制定统计信息收集策略了！

1.1.2.3查看某张表的统计信息历史收集情况

WRI$_OPTSTAT_TAB_HISTORY表可以帮助我们查看某张表的统计信息收集的历史情况

1.2统计信息收集的Window

我们知道了ScheduleJob，那它们都是什么时间开始又持续多久呢？

Oracle其实是靠Window来控制Job的运行周期和持续时间的。

下面我们就详细的说明一下如何查看和维护执行窗口

1.2.1统计信息收集window的查看

从下图可以看到11g和10g的Maintenancewindow，我们来总结一下哈：

10g只有两个window（Weeknight和Weekend），周一至周五每晚22点开始，持续8个小时至次日6点结束，周六和周日0点开始，持续48小时至周一0点结束；

11g增加到7个window（每天一个），周一至周五每晚22点开始，持续4个小时至次日2点结束，周六和周日从早6点开始，持续20个小时至次日2点结束。

如果大家有兴趣可以看$ORACLE_HOME/rdbms/admin/catmwin.sql这个脚本，10g和11g都是调用这个脚本来搭建统计信息收集作业的。

11g的window

10g的window

提示:

11g的Maintenancewindow增加到了7个，这极大的提高了统计信息收集的灵活性，我们可以对每天的窗口设置开始和持续时间，这对于不同的业务系统，能够有效的避开资源的争用。

可以看出Oracle还是一直在朝着人性化前进地！

1.2.2Window对系统资源的利用

大家可能还不知道，10g和11g在收集统计信息的时候对系统资源的利用上还有很大的区别，从下图可以看出，10g在执行窗口作业时是没有资源限制的，也就是说在收集统计信息时可以无限的消耗资源。

这也就是我们经常会看到有时候夜间的批量突然耗时增加的原因，批量和统计信息收集冲突后，由于资源没有被限制导致了资源的争用和等待。

11g在这方面加以了改善，有了一个”DEFAULT_MAINTENACE_PLAN”对资源消耗加以了控制。

11g的window资源限制

10g的window资源限制

1.2.2Window的维护

1.2.2.1禁用和启用某个Window

10g和11g启用或者禁用一个Window的方法一致:

execdbms_scheduler.disable（name=>'"SYS"."WEEKNIGHT_WINDOW"',force=>TRUE）;

execdbms_scheduler.enable（name=>'"SYS"."WEEKNIGHT_WINDOW"'）;

图例如下：

提示：

禁用一个Window，相当于禁掉了依赖于这个window的所有schedulejob（AutomaticSegmentAdvisor,AutomaticSQLTuningAdvisor），除非你想要这么作，否则，请修改该窗口，禁掉相对应的job即可。

10g中我还没有发现可以单独禁用某个窗口中的某个schedulejob的方法，如果没有，就又说明11g的进步啊。

下面我们来介绍11g修改的方法

禁用和启用某个window中的某个job:

禁用掉周一的统计信息收集作业

BEGIN

dbms_auto_task_admin.disable/enable（

client_name=>'autooptimizerstatscollection',

operation=>NULL,

window_name=>'MONDAY_WINDOW'）;

END;

/

可通过如下语句查看是否生效

Selectwindow_name,optimizer_stats,segment_advisor,sql_tune_advisorfromdba_autotask_window_clients;

图例如下：

1.2.2.1修改Window的Attribute

下面我们做个示例（仅针对11g，10g不支持），修改周一的窗口的开始时间为21点，并且将持续时间调整为5个小时。

执行方法如下：

BEGIN

dbms_scheduler.disable（

name=>'SATURDAY_WINDOW'）;

dbms_scheduler.set_attribute（

name=>'MONDAY_WINDOW',

attribute=>'repeat_interval',

value=>'freq=daily;byday=SAT;byhour=21;byminute=0;bysecond=0'）;

dbms_scheduler.set_attribute（

name=>'MONDAY_WINDOW',

attribute=>'DURATION',

value=>numtodsinterval（5,'hour'））;

dbms_scheduler.enable（

name=>'SATURDAY_WINDOW'）;

END;

/

图例如下：

从上面可以看出，在11g中我们可以很灵活的调整自动作业的窗口，尽量错开我们的业务高峰。

提示：

在修改window属性时，必须要先disable再enable

1.3统计信息收集默认参数的修改

在采用自动统计信息收集的数据库中，为了设计一个完善的、适合自己业务系统的统计信息收集策略，我们就要学会如何修改统计信息收集时的一些首选参数，当然，如果你用的是脚本定时收集，那就可以忽略这个章节了。

我们为什么要修改这些参数呢，很简单，因为我不喜欢它这么工作呗！

嘿嘿，逗你玩儿，其实是有些参数可能不太适合某些业务系统，改改更健康对不。

举个例子，比如我们希望给某张数据匀称度低的表设置一个较高的收集比例，我们就可以在表级别上修改”ESTIMATE_PERCENT”这个参数为30%，为啥这样做呢？

数据不匀称就多收集点呗，不然不准确吧…..反之，就默认10%就ok啦！

“DBMS_STATS.SET_*_PREFS”过程是用来修改和控制11g统计信息收集的默认参数的，它可以用来修改不同层级统计信息收集的参数默认值。

”DBMS_STATS.SET_PARAM”这个过程是10g用来修改统计信息全局默认参数的，Oracle11g在这方面也有了很大的改进，可以分别设置global,database,schema,table层面的参数，而10g只能修改全局参数。

下面我们分别介绍一下11g和10g的统计信息收集都有哪些参数，又如何来修改。

1.3.1Oracle11g的DBMS_STATS.SET_*_PREFS

1.3.1.1DBMS_STATS.SET_*_PREFS层级分类

过程名称

描述

SET_TABLE_PREFS

为某张表指定dbms_stats_gather_*_stats过程所使用的默认参数

SET_SCHEMA_PREFS

为某个schema下的所有对象

修改dbms_stats_gather_*_stats过程所使用的默认参数

SET_DATABASE_PREFS

为整个数据库（包括所有用户、sys、system）

修改dbms_stats_gather_*_stats过程所使用的默认参数

SET_GLOBAL_PREFS

设置全局统计信息收集首选参数，

为没有在dbms_stats_gather_*_stats中显示指定参数和设置表级首选参数的对象指定全局默认参数

1.3.1.2SET_*_PREFS可接收的参数

名称

描述

pname

指定首选参数名称，如'ESTIMATE_PERCENT'

pvalue

为首选参数指定一个值，如果指定的值时‘NULL’，则参数值会被设置为Oracle的默认值

add_sys

可选参数，如果设置为TRUE，则会把所有oracle拥有的表也加入到统计信息收集过程中来

1.3.1.3统计信息收集的各种参数（Pname细分）

参数名称

描述

CASCADE

指定数据库是否应该在收集表统计信息的同时收集索引统计信息，默认值是CASCADE=TRUE

DEGREE

指定数据库在收集统计信息时的并行度，Oracle推荐使用DBMS_STATS.AUTO_DEGREE，oracle会根据初始化参数自动选择并行度，如果对象很小，oracle会顺序收集统计信息，反之，Oracle会基于CPU个数的默认并行度，默认值是NULL，意味着只有在表级使用degree子句设置了并行度，数据库才会并行地收集统计信息

ESTIMATE_PERCENT

指定数据库在估算统计信息时，必须使用的数据行数百分比。

对于大表的统计信息收集是一个非常耗费资源的过程，为这个参数指定0-100的值。

如果表中的数据匀称度很高，就可以将采样比设置的小些，反之，则应该采用更高的采样比。

默认值=DBMS_STATS.AUTO_SAMPLE_SIZE，一般情况下AUTO和100所收集的统计信息数据是基本一致的，都可以比较准确的收集统计信息，可采用下面的方法采用AUTO来收集

METHOD_OPT

该参数可指定两方面内容:

数据库将要收集统计信息的列，以及数据库将会在其上创建柱状图（HISTGRAM）的列，还可以指定柱状图的柱子数量,该参数可指定下面选项中的一个【FORALL[INDEXED|HIDDEN]COLUMNS[size_clause]】,默认值是AUTO

SQL>execdbms_stats.gather_table_stats（'BOBO','BOBOSTATOLD',method_opt=>'forcolumnssize254statid'）;----收集直方图在startid列

SQL>execdbms_stats.gather_table_stats（'BOBO','BOBOSTATOLD',method_opt=>'forallcloumnssize1'）;----不收集直方图信息

size_clause的另一种选择就是声明下面三个值的一种

REPEAT----指定数据库仅为那些已经收集了柱状图信息的列收集新的信息

AUTO----让数据库基于每一列的数据分布（是均匀的还是偏态的）以及数据列的实际使用率统计信息来决定应该收集那些列的柱状图信息

SKEWONLY----让数据库根据每一列的数据分布来决定应该收集那些列的柱状图信息

SQL>execdbms_stats.gather_table_stats（'BOBO','BOBOSTATOLD',method_opt=>'forallcolumnssizeskewonly'）;

NO_INVALIDATE

包括三个不同的值

TRUE----表示对数据库收集统计信息的表，不会使其从属游标失效

FALSE----表示数据库会立刻使从属游标失效（重新解析）

DBMS_STATS.AUTO_INVALIDATE----Oracle自己决定是否使游标失效，这也是NO_INVALIDATE的默认参数

GRANULARITY

这个参数确定了数据库如何处理分区表的统计信息收集

ALL----收集分区、子分区和全局的统计信息，非常耗时，但准确

GLOBAL----只为表收集全局统计信息

PARTITION----只收集分区级统计信息，会上升到表级，但不够准确

GLOBALANDPARTITION----收集全局和分区级统计信息，但不包括子分区

SUBPARTITION----只收集子分区统计信息

AUTO----GRANULARITY的默认值，根据分区类型来确定收集统计信息的粒度

PUBLISH

默认情况下，数据库会在收集完毕后立即发布，如果设置为FALSE，会使数据库将新收集的信息保留为待定

INCREMENTAL

表示数据库是否需要全表扫描来维护一张分区表的统计信息，默认值是FALSE

STALE_PERCENT

确定数据表中多大比例的数据行发生变化后，数据库就会认为表的统计信息是陈旧的了，并开始收集新的统计信息，默认值为10%

AUTOSTATS_TARGET

这个参数只有对自动统计信息收集才有效，有下面几个参数可选，默认值是AUTO

ALL----为数据库中所有的对象收集统计信息

ORACLE----为所有Oracle拥有的对象收集统计信息

AUTO----数据库自动确定要收集那些对象的统计信

注意ALL和AUTO（默认）的工作原理是一致的，Oracle推荐将AUTOSTATS_TARGET参数的值设置为oracle，确保数据库（对sys和system）收集新的字典统计信息

Example

首先我们要知道某个参数的默认值：

Selectdbms_stats.get_perfs（‘ESTIMATE_PERCENT’）estimate_percentfromdual;

也可以精确到某个用户某张表的参数值

改一下它

SQL>executedbms_stats.set_table_prefs（‘SCOTT’,’EMP’,'ESTIMATE_PERCENT','20'）;

提示：

11g的统计信息收集参数按层级进行了细分，表级参数设置（set_table_perfs）为最终细粒度设置，可参考此原则进行层级设置。

1.3.2Oracle10g的DBMS_STATS.SET_PARAM

1.3.2.1SET_PARAM可接收的参数

名称

描述

pname

指定首选参数名称，如'ESTIMATE_PERCENT'

pvalue

为首选参数指定一个值，如果指定的值时‘NULL’，则参数值会被设置为Oracle的默认值

1.3.2.2统计信息收集的各种参数（Pname细分）

参数名称

描述

CASCADE

指定数据库是否应该在收集表统计信息的同时收集索引统计信息，默认值是CASCADE=DBMS_STATS.AUTO_CASCADE

DEGREE

指定数据库在收集统计信息时的并行度，默认值是NULL，意味着只有在表级使用degree子句设置了并行度，数据库才会并行地收集统计信息

ESTIMATE_PERCENT

指定数据库在估算统计信息时，必须使用的数据行数百分比。

对于大表的统计信息收集是一个非常耗费资源的过程，为这个参数指定0-100的值。

如果表中的数据匀称度很高，就可以将采样比设置的小些，反之，则应该采用更高的采样比。

默认值=DBMS_STATS.AUTO_SAMPLE_SIZE，oracle10g建议给出明确的百分比，不推荐默认值

METHOD_OPT

该参数可指定两方面内容:

数据库将要收集统计信息的列，以及数据库将会在其上创建柱状图（HISTGRAM）的列，还可以指定柱状图的柱子数量,该参数可指定下面选项中的一个【FORALL[INDEXED|HIDDEN]COLUMNS[size_clause]】,默认值是AUTO

SQL>execdbms_stats.gather_table_stats（'BOBO','BOBOSTATOLD',method_opt=>'forcolumnssize254statid'）;----收集直方图在startid列

SQL>execdbms_stats.gather_table_stats（'BOBO','BOBOSTATOLD',method_opt=>'forallcloumnssize1'）;----不收集直方图信息

size_clause的另一种选择就是声明下面三个值的一种

REPEAT----指定数据库仅为那些已经收集了柱状图信息的列收集新的信息

AUTO----让数据库基于每一列的数据分布（是均匀的还是偏态的）以及数据列的实际使用率统计信息来决定应该收集那些列的柱状图信息

SKEWONLY----让数据库根据每一列的数据分布来决定应该收集那些列的柱状图信息

SQL>execdbms_stats.gather_table_stats（'BOBO','BOBOSTATOLD',method_opt=>'forallcolumnssizeskewonly'）;

NO_INVALIDATE

包括三个不同的值

TRUE----表示对数据库收集统计信息的表，不会使其从属游标失效

FALSE----表示数据库会立刻使从属游标失效（重新解析）

DBMS_STATS.AUTO_INVALIDATE----Oracle自己决定是否使游标失效，这也是NO_INVALIDATE的默认参数

GRANULARITY

这个参数确定了数据库如何处理分区表的统计信息收集