BOBO手记系列之Oracle 统计信息收集文档格式.docx
- 文档编号:20527331
- 上传时间:2023-01-23
- 格式:DOCX
- 页数:23
- 大小:429.59KB
BOBO手记系列之Oracle 统计信息收集文档格式.docx
《BOBO手记系列之Oracle 统计信息收集文档格式.docx》由会员分享,可在线阅读,更多相关《BOBO手记系列之Oracle 统计信息收集文档格式.docx(23页珍藏版)》请在冰豆网上搜索。
索引的统计信息,包括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;
/
启动:
dbms_auto_task_admin.enable(
例图:
10gJob的启动和关闭(两种方法)
方法一(推荐方法):
execdbms_scheduler.disable('
SYS.GATHER_STATS_JOB'
);
execdbms_scheduler.enable('
方法二:
altersystemset"
_optimizer_autostats_job"
=falsescope=spfile;
=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=>
图例如下:
禁用一个Window,相当于禁掉了依赖于这个window的所有schedulejob(AutomaticSegmentAdvisor,AutomaticSQLTuningAdvisor),除非你想要这么作,否则,请修改该窗口,禁掉相对应的job即可。
10g中我还没有发现可以单独禁用某个窗口中的某个schedulejob的方法,如果没有,就又说明11g的进步啊。
下面我们来介绍11g修改的方法
禁用和启用某个window中的某个job:
禁用掉周一的统计信息收集作业
dbms_auto_task_admin.disable/enable(
client_name=>
operation=>
NULL,
window_name=>
MONDAY_WINDOW'
可通过如下语句查看是否生效
Selectwindow_name,optimizer_stats,segment_advisor,sql_tune_advisorfromdba_autotask_window_clients;
1.2.2.1修改Window的Attribute
下面我们做个示例(仅针对11g,10g不支持),修改周一的窗口的开始时间为21点,并且将持续时间调整为5个小时。
执行方法如下:
dbms_scheduler.disable(
name=>
SATURDAY_WINDOW'
dbms_scheduler.set_attribute(
attribute=>
repeat_interval'
value=>
freq=daily;
byday=SAT;
byhour=21;
byminute=0;
bysecond=0'
DURATION'
numtodsinterval(5,'
hour'
));
dbms_scheduler.enable(
从上面可以看出,在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)
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列
forallcloumnssize1'
----不收集直方图信息
size_clause的另一种选择就是声明下面三个值的一种
REPEAT----指定数据库仅为那些已经收集了柱状图信息的列收集新的信息
AUTO----让数据库基于每一列的数据分布(是均匀的还是偏态的)以及数据列的实际使用率统计信息来决定应该收集那些列的柱状图信息
SKEWONLY----让数据库根据每一列的数据分布来决定应该收集那些列的柱状图信息
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;
也可以精确到某个用户某张表的参数值
改一下它
executedbms_stats.set_table_prefs(‘SCOTT’,’EMP’,'
20'
11g的统计信息收集参数按层级进行了细分,表级参数设置(set_table_perfs)为最终细粒度设置,可参考此原则进行层级设置。
1.3.2Oracle10g的DBMS_STATS.SET_PARAM
1.3.2.1SET_PARAM可接收的参数
1.3.2.2统计信息收集的各种参数(Pname细分)
指定数据库是否应该在收集表统计信息的同时收集索引统计信息,默认值是CASCADE=DBMS_STATS.AUTO_CASCADE
指定数据库在收集统计信息时的并行度,默认值是NULL,意味着只有在表级使用degree子句设置了并行度,数据库才会并行地收集统计信息
默认值=DBMS_STATS.AUTO_SAMPLE_SIZE,oracle10g建议给出明确的百分比,不推荐默认值
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- BOBO手记系列之Oracle 统计信息收集 BOBO 手记 系列 Oracle 统计 信息 收集