后使用快捷导航没有帐号？
查看: 925|回复: 8
【讨论】关于Oracle自动收集统计信息
论坛徽章:15
我们知道从Oracle Database 10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO的统计信息。这个自动任务默认情况下在工作日晚上10：00-6：00和周末全天开启。调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息。该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级，再开始进行统计信息。但是，有很多人都建议关闭自动统计，这个自动化功能已经影响了很多系统的正常运行，晚上10点对于大部分生产系统也并非空闲时段。而自动分析可能导致极为严重的闩锁竞争，进而可能导致数据库Hang或者Crash。
请问大家生产库上有没有开启，如果没有，你定期手动收集的频率、策略分别是什么？
论坛徽章:16
要看你的业务，如果数据时线性增长的就是自动收集，如果是爆炸式增长则自己写脚本收集。
论坛徽章:15
stevendba 发表于
要看你的业务，如果数据时线性增长的就是自动收集，如果是爆炸式增长则自己写脚本收集。
高级会员, 积分 954, 距离下一级还需 46 积分
论坛徽章:3
个人感觉这个任务还是运行的比较好些
中级会员, 积分 381, 距离下一级还需 119 积分
论坛徽章:1
金牌会员, 积分 1133, 距离下一级还需 1867 积分
论坛徽章:7
论坛徽章:15
我最终还是把这个job开启的，但把时间更改到凌晨1点开始，到6点
高级会员, 积分 652, 距离下一级还需 348 积分
论坛徽章:4
我这边是关闭的，凌晨到6点也有大量数据处理。现在改为周末白天来收集。
扫一扫加入本版微信群博客访问： 30621
博文数量： 216
注册时间：
认证徽章：
ITPUB论坛APP
ITPUB论坛APP
APP发帖 享双倍积分
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Oracle
一般情况，oracle11G数据库自动收集统计信息任务的设置如下：
1、Oracle11g 的默认设置；
2、user_tab_modifications 跟踪记录表的修改；
3、当分析对象的数据变化超过10% 时，Oracle 会重新分析；
4、定时任务gather_stats_job 负责定时调度收集统计信息。
5、在Oracle11g 中，默认自动收集统计信息的时间为：
# 周一~周五，22点启动，收集4个小时
# 周六，周日，6点启动，收集20个小时
---查看自动统计信息的时间：
sys@PROD&col WINDOW_NAME for a15
sys@PROD&col REPEAT_INTERVAL for a60
sys@PROD&col DURATION for a30
sys@PROD&set linesize 120
sys@PROD&SELECT t1.window_name, t1.repeat_interval, t1.duration
&&2 &FROM dba_scheduler_windows t1, dba_scheduler_wingroup_members t2
&&3 &WHERE t1.window_name = t2.window_name
&&4 &AND t2.window_group_name IN
&&5 &('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');
WINDOW_NAME &&&&REPEAT_INTERVAL &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&DURATION
--------------- ------------------------------------------------------------ ------------------------------
MONDAY_WINDOW &&freq=byday=MON;byhour=22;byminute=0; bysecond=0 &&&&&&&+000 04:00:00
TUESDAY_WINDOW &freq=byday=TUE;byhour=22;byminute=0; bysecond=0 &&&&&&&+000 04:00:00
WEDNESDAY_WINDO freq=byday=WED;byhour=22;byminute=0; bysecond=0 &&&&&&&+000 04:00:00
THURSDAY_WINDOW freq=byday=THU;byhour=22;byminute=0; bysecond=0 &&&&&&&+000 04:00:00
FRIDAY_WINDOW &&freq=byday=FRI;byhour=22;byminute=0; bysecond=0 &&&&&&&+000 04:00:00
SATURDAY_WINDOW freq=byday=SAT;byhour=6;byminute=0; bysecond=0 &&&&&&&&+000 20:00:00
SUNDAY_WINDOW &&freq=byday=SUN;byhour=6;byminute=0; bysecond=0 &&&&&&&&+000 20:00:00
7 rows selected.
#freq=daily：每天收集；
#;byday=SUN：一周之内的星期，例如这里是星期日；
#byhour=22 ：每天的时间点时，这里是22时；
#byminute=0：每天的的时间点分，这里是0分，则第1分；
#bysecond=0：每天的时间点秒，这里是0秒，则第1秒；
#+000 20:00:00 ：表示收集信息的时间区间长，这里表示20小时。
---修改自动收集统计信息计划任务时间：
--首先停止原来计划;
sys@PROD&BEGIN
&&2 &DBMS_SCHEDULER.DISABLE(
&&3 &name =& '"SYS"."THURSDAY_WINDOW"');
PL/SQL procedure successfully completed.
--修改计划任务的执行时间：
sys@PROD&BEGIN
&&2 &DBMS_SCHEDULER.SET_ATTRIBUTE(
&&3 &name =& '"SYS"."THURSDAY_WINDOW"',
&&4 &attribute =& 'REPEAT_INTERVAL',
&&5 &value =&
&&6 &'freq=byday=THU;byhour=22;byminute=0;bysecond=0');
PL/SQL procedure successfully completed.
--启用新的计划任务的执行时间：&
sys@PROD&BEGIN
&&2 &DBMS_SCHEDULER.ENABLE(
&&3 &name =& '"SYS"."THURSDAY_WINDOW"');
PL/SQL procedure successfully completed.
--理解好的以上的查看与修改过程，往后可以按照需求，可以自己按照需求去改动
自动收集统计信息计划任务的执行时间。
阅读(565) | 评论(0) | 转发(0) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。　　在Oracle的11g版本中提供了统计数据自动收集的功能。在部署安装11g Oracle软件过程中，其中有一个步骤便是提示是否启动这个功能(默认是启用这个功能)。
1、查看自动收集统计信息的任务及状态：
SQL& select client_name,status from dba_autotask_
CLIENT_NAME
---------------------------------------------------------------- --------
auto optimizer stats collection
auto space advisor
sql tuning advisor
2、禁止自动收集统计信息的任务
SQL& exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name =& 'auto optimizer stats collection',operation =& NULL,window_name =& NULL);
PL/SQL procedure successfully completed.
SQL& select client_name,status from dba_autotask_
CLIENT_NAME
---------------------------------------------------------------- --------
auto optimizer stats collection
auto space advisor
sql tuning advisor
3、启用自动收集统计信息的任务
SQL& exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name =& 'auto optimizer stats collection',operation =& NULL,window_name =& NULL);
PL/SQL procedure successfully completed.
SQL& select client_name,status from dba_autotask_
CLIENT_NAME
---------------------------------------------------------------- --------
auto optimizer stats collection
auto space advisor
sql tuning advisor
4、获得当前自动收集统计信息的执行时间：
WINDOW_NAME format a20
SQL& col REPEAT_INTERVAL format a70
SQL& col DURATION format a20
SQL& set line 180
SQL& select t1.window_name,t1.repeat_interval,t1.duration
from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');
WINDOW_NAME
REPEAT_INTERVAL
-------------------- ---------------------------------------------------------------------- --------------------
WEDNESDAY_WINDOW
freq=byday=WED;byhour=22;byminute=0; bysecond=0
+000 04:00:00
SATURDAY_WINDOW
freq=byday=SAT;byhour=6;byminute=0; bysecond=0
+000 20:00:00
THURSDAY_WINDOW
freq=byday=THU;byhour=22;byminute=0; bysecond=0
+000 04:00:00
TUESDAY_WINDOW
freq=byday=TUE;byhour=22;byminute=0; bysecond=0
+000 04:00:00
SUNDAY_WINDOW
freq=byday=SUN;byhour=6;byminute=0; bysecond=0
+000 20:00:00
MONDAY_WINDOW
freq=byday=MON;byhour=22;byminute=0; bysecond=0
+000 04:00:00
FRIDAY_WINDOW
freq=byday=FRI;byhour=22;byminute=0; bysecond=0
+000 04:00:00
7 rows selected.
其中：WINDOW_NAME：任务名 & & &&REPEAT_INTERVAL：任务重复间隔时间 & & &DURATION：持续时间
5.修改统计信息执行的时间：
1.停止任务：
SQL& BEGIN
DBMS_SCHEDULER.DISABLE(
name =& '"SYS"."THURSDAY_WINDOW"',
force =& TRUE);
--停止任务是true
2.修改任务的持续时间，单位是分钟：
SQL& BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name =& '"SYS"."THURSDAY_WINDOW"',
attribute =& 'DURATION',
value =& numtodsinterval(60,'minute'));
3.开始执行时间，BYHOUR=2，表示2点开始执行：
SQL& BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name =& '"SYS"."THURSDAY_WINDOW"',
attribute =& 'REPEAT_INTERVAL',
value =& 'freq=byday=THU;byhour=10;byminute=40;bysecond=0');
4.开启任务：
SQL& BEGIN
DBMS_SCHEDULER.ENABLE(
name =& '"SYS"."THURSDAY_WINDOW"');
5.查看修改后的情况：
SQL& select t1.window_name,t1.repeat_interval,t1.duration
from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');
WINDOW_NAME
REPEAT_INTERVAL
-------------------- ---------------------------------------------------------------------- --------------------
WEDNESDAY_WINDOW
freq=byday=WED;byhour=22;byminute=0; bysecond=0
+000 04:00:00
SATURDAY_WINDOW
freq=byday=SAT;byhour=6;byminute=0; bysecond=0
+000 20:00:00
THURSDAY_WINDOW
freq=byday=THU;byhour=10;byminute=40;bysecond=0
+000 01:00:00
TUESDAY_WINDOW
freq=byday=TUE;byhour=22;byminute=0; bysecond=0
+000 04:00:00
SUNDAY_WINDOW
freq=byday=SUN;byhour=6;byminute=0; bysecond=0
+000 20:00:00
MONDAY_WINDOW
freq=byday=MON;byhour=22;byminute=0; bysecond=0
+000 04:00:00
FRIDAY_WINDOW
freq=byday=FRI;byhour=22;byminute=0; bysecond=0
+000 04:00:00
6.查看统计信息执行的历史记录
--维护窗口组
select * from dba_scheduler_window_
--维护窗口组对应窗口
select * from dba_scheduler_wingroup_members
--维护窗口历史信息
select* from dba_scheduler_windows
--查询自动收集任务正在执行的job
select * from DBA_AUTOTASK_CLIENT_JOB;
--查询自动收集任务历史执行状态
select * from DBA_AUTOTASK_JOB_HISTORY;
select * from DBA_AUTOTASK_CLIENT_HISTORY;
--整理自网络
阅读(...) 评论()如何修改oracle自动统计信息收集所使用的参数
时间： 02:18:24
&&&& 阅读：260
&&&& 评论：
&&&& 收藏：
标签：&&&&&&&&&&&&&&&&&&&&&&&&&&&
SQL& select dbms_stats.get_param(‘cascade‘)
DBMS_STATS.GET_PARAM(‘CASCADE‘)
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_CASCADE
SQL& exec dbms_stats.set_param(‘cascade‘,‘true‘);
PL/SQL procedure successfully completed.
SQL& select dbms_stats.get_param(‘cascade‘)
DBMS_STATS.GET_PARAM(‘CASCADE‘)
--------------------------------------------------------------------------------
SQL& select dbms_stats.get_param(‘degree‘)
DBMS_STATS.GET_PARAM(‘DEGREE‘)
--------------------------------------------------------------------------------
SQL& exec dbms_stats.set_param(‘degree‘,‘4‘);
PL/SQL procedure successfully completed.
SQL& select dbms_stats.get_param(‘degree‘)
DBMS_STATS.GET_PARAM(‘DEGREE‘)
--------------------------------------------------------------------------------
SQL& select dbms_stats.get_param(‘estimate_percent‘)
DBMS_STATS.GET_PARAM(‘ESTIMATE_PERCENT‘)
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE
SQL& exec dbms_stats.set_param(‘estimate_percent‘,‘null‘);
PL/SQL procedure successfully completed.
SQL& select dbms_stats.get_param(‘estimate_percent‘)
DBMS_STATS.GET_PARAM(‘ESTIMATE_PERCENT‘)
--------------------------------------------------------------------------------
SQL& select dbms_stats.get_param(‘method_opt‘)
DBMS_STATS.GET_PARAM(‘METHOD_OPT‘)
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
SQL& exec dbms_stats.set_param(‘method_opt‘,‘for all columns size 1‘);
PL/SQL procedure successfully completed.
SQL& select dbms_stats.get_param(‘method_opt‘)
DBMS_STATS.GET_PARAM(‘METHOD_OPT‘)
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE 1
SQL& select dbms_stats.get_param(‘no_invalidate‘)
DBMS_STATS.GET_PARAM(‘NO_INVALIDATE‘)
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE
SQL& exec dbms_stats.set_param(‘no_invalidate‘,‘false‘);
PL/SQL procedure successfully completed.
SQL& select dbms_stats.get_param(‘no_invalidate‘)
DBMS_STATS.GET_PARAM(‘NO_INVALIDATE‘)
--------------------------------------------------------------------------------
SQL& select dbms_stats.get_param(‘autostats_target‘)
DBMS_STATS.GET_PARAM(‘AUTOSTATS_TARGET‘)
--------------------------------------------------------------------------------
SQL& exec dbms_stats.set_param(‘autostats_target‘,‘all‘);
PL/SQL procedure successfully completed.
SQL& select dbms_stats.get_param(‘autostats_target‘)
DBMS_STATS.GET_PARAM(‘AUTOSTATS_TARGET‘)
--------------------------------------------------------------------------------
SQL& select dbms_stats.get_prefs(‘no_invalidate‘)
DBMS_STATS.GET_PREFS(‘NO_INVALIDATE‘)
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE
SQL& exec dbms_stats.set_global_prefs(pname=&‘no_invalidate‘,pvalue=&‘false‘);
PL/SQL procedure successfully completed.
SQL& select dbms_stats.get_prefs(‘no_invalidate‘)
DBMS_STATS.GET_PREFS(‘NO_INVALIDATE‘)
--------------------------------------------------------------------------------
SQL& select table_name,num_rows,blocks,last_analyzed from dba_tables where
owner=‘SCOTT‘;
TABLE_NAME
BLOCKS LAST_ANALYZE
------------------------------ ---------- ---------- ------------
5 02-DEC-15
80 03-MAY-16
5 02-DEC-15
0 02-DEC-15
0 03-MAY-16
SQL& select
table_name,index_name,blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed from
dba_indexes where owner=‘SCOTT‘ and table_name=‘EMP‘;
TABLE_NAME
INDEX_NAME
BLEVEL LEAF_BLOCKS
DISTINCT_KEYS
NUM_ROWS LAST_ANALYZE
------------------------------ ------------------------------ ---------- -----------
------------- ---------- ------------
30014 03-MAY-16
EMP_EMPNO_DEPTNO
30014 03-MAY-16
SQL& exec dbms_stats.gather_table_stats
(ownname=&‘scott‘,tabname=&‘emp‘,estimate_percent=&100,method_opt=&‘for all columns size
repeat‘);
PL/SQL procedure successfully completed.
SQL& select table_name,num_rows,blocks,last_analyzed from dba_tables where
owner=‘SCOTT‘;
TABLE_NAME
BLOCKS LAST_ANALYZE
------------------------------ ---------- ---------- ------------
5 02-DEC-15
80 19-MAY-16
5 02-DEC-15
0 02-DEC-15
0 03-MAY-16
SQL& select
table_name,index_name,blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed from
dba_indexes where owner=‘SCOTT‘ and table_name=‘EMP‘;
TABLE_NAME
INDEX_NAME
BLEVEL LEAF_BLOCKS
DISTINCT_KEYS
NUM_ROWS LAST_ANALYZE
------------------------------ ------------------------------ ---------- -----------
------------- ---------- ------------
30014 03-MAY-16
EMP_EMPNO_DEPTNO
30014 03-MAY-16
SQL& exec dbms_stats.gather_table_stats
(ownname=&‘scott‘,tabname=&‘emp‘,estimate_percent=&100,method_opt=&‘for all columns size
repeat‘,cascade=&true);
PL/SQL procedure successfully completed.
select table_name,num_rows,blocks,last_analyzed from dba_tables where
owner=‘SCOTT‘;
TABLE_NAME
BLOCKS LAST_ANALYZE
------------------------------ ---------- ---------- ------------
5 02-DEC-15
80 19-MAY-16
5 02-DEC-15
0 02-DEC-15
0 03-MAY-16
SQL& select
table_name,index_name,blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed from
dba_indexes where owner=‘SCOTT‘ and table_name=‘EMP‘;
TABLE_NAME
INDEX_NAME
BLEVEL LEAF_BLOCKS
DISTINCT_KEYS
NUM_ROWS LAST_ANALYZE
------------------------------ ------------------------------ ---------- -----------
------------- ---------- ------------
30014 19-MAY-16
EMP_EMPNO_DEPTNO
30014 19-MAY-16
exec dbms_stats.set_schema_prefs(ownname=&‘scott‘,pname=&‘stale_percent‘,pvalue=&‘5‘);
select dbms_stats.get_prefs(‘CASCADE‘,‘SCOTT‘,‘EMP‘)
DBMS_STATS.GET_PREFS(‘CASCADE‘,‘SCOTT‘,‘EMP‘)
--------------------------------------------------------------------------------
SQL& select dbms_stats.get_prefs(‘STALE_PERCENT‘)
DBMS_STATS.GET_PREFS(‘STALE_PERCENT‘)
--------------------------------------------------------------------------------
标签：&&&&&&&&&&&&&&&&&&&&&&&&&&&原文：http://blog.itpub.net//viewspace-2103400/
教程昨日排行
&&国之画&&&& &&&&&&
&& &&&&&&&&&&&&&&
鲁ICP备号-4
打开技术之扣，分享程序人生！