如何在11g上收集 Optimizer 收集表的统计信息息
点击联系发帖人
时间：2016-02-22 03:53
Oracle 11g 统计信息收集脚本-feelpurple-ITPUB博客
Oracle 11g 统计信息收集脚本
2460阅读 0评论
在 Oracle 11g，系统自带的 JOB 里面有收集统计信息的任务，但并不一定每天每天都会收集。
在 DBMS_STATS 包中有一个&STALE_PERCENT 参数，这个参数的意义是：当表中的数据量修改超过总数据量的一定比例数，会再次触发统计信息收集，默认值为10%。
这样对于大表来说就有一个问题，收集的时间不会很及时，可能会隔好几天才会收集一次，这样会导致表的统计信息不准确，对数据库的 SQL 解析产生影响。
大表通常都是分区表，下面的收集方案主要是针对分区表，也涵盖了普通表。
对于非分区表，使用默认的统计信息采集方法；对于分区表，使用增量采集方法，只对数据有变动的分区做收集。
& --采集非分区表的统计信息
& FOR i IN (select s.TABLE_NAME, s.num_rows
& & & & & & & from dba_tables s
& & & & & & &where s.OWNER = 'ACCT'
& & & & & & & &and not exists
& & & & & & &(select distinct TABLE_NAME
& & & & & & & & & & & from dba_tab_partitions p
& & & & & & & & & & &where table_owner = 'ACCT'
& & & & & & & & & & & &and p.table_name = s.TABLE_NAME)) LOOP
& & DBMS_STATS.GATHER_TABLE_STATS('ACCT',
& & & & & & & & & & & & & & & & & i.TABLE_NAME,
& & & & & & & & & & & & & & & & & DEGREE & & & => 5,
& & & & & & & & & & & & & & & & & CASCADE & & &=> TRUE);
& END LOOP;
& --采集分区表的统计信息 &
& FOR j IN (select distinct TABLE_NAME
& & & & & & & from dba_tab_partitions p
& & & & & & &where table_owner = 'ACCT') LOOP
& & DBMS_STATS.GATHER_TABLE_STATS('ACCT',
& & & & & & & & & & & & & & & & & tabname & & & & &=> j.table_name,
& & & & & & & & & & & & & & & & & estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
& & & & & & & & & & & & & & & & & GRANULARITY & & &=> 'AUTO',
& & & & & & & & & & & & & & & & & DEGREE & & & & & => 5,
& & & & & & & & & & & & & & & & & CASCADE & & & & &=> TRUE);
& END LOOP;
--对于分区表，设置 INCREMENTAL 参数只对数据有变动的分区做收集
在对分区表收集统计信息的时候，如果想让 Oracle 只扫描发生数据改变的分区，避免全表扫描，需要满足以下条件：
(1) 指定分区表的 INCREMENTAL 参数设置为 TRUE
(2) 指定分区表的 PUBLISH 设置为 TRUE
(3) ESTIMATE_PERCENT 参数设为 AUTO_SAMPLE_SIZE，GRANULARITY 参数设为 AUTO
--统计信息收集设置选项
索引的统计信息是否和表一起收集。
收集的并行度。
ESTIMATE_PERCENT 决定收集的比例，范围是[0.]。可以通过DBMS_STATS.AUTO_SAMPLE_SIZE来让oracle自动决定收集的比例。这是默认值。
METHOD_OPT 控制列和柱状图的收集方法。
GRANULARITY 收集的粒度。
当统计信息收集JOB执行完毕后，是否更新新的统计信息。
INCREMENTAL 决定是否维持分区表的全局统计信息,而不做全表扫描。当开启这个参数的时候，Oracle仅需扫描分区表中有数据变化的分区来更新分区表的统计信息，而无需扫描整个分区表。
STALE_PERCENT 当表的数据量修改超过总数据量的一定比例数，再次触发统计信息收集，默认值为10%。
--查询相关参数
select dbms_stats.get_prefs('PUBLISH')
select dbms_stats.get_prefs('method_opt')
select dbms_stats.get_prefs('GRANULARITY')
select dbms_stats.get_prefs('INCREMENTAL')
--查看分区表INCREMENTAL、PUBLISH的值
select dbms_stats.get_prefs('INCREMENTAL','ACCT','ELMP_TRANS_CUSTOMACCOUNTFLOW')
select dbms_stats.get_prefs('PUBLISH','ACCT','ELMP_TRANS_CUSTOMACCOUNTFLOW')
--exec DBMS_STATS.SET_PARAM('INCREMENTAL','TRUE');
--只收集数据变动的分区
& FOR j IN (select distinct TABLE_NAME
& & & & & & & from dba_tab_partitions p
& & & & & & &where table_owner = 'ACCT') LOOP
& & DBMS_STATS.SET_TABLE_PREFS('ACCT', j.table_name, 'INCREMENTAL', 'TRUE');
& END LOOP;
只修改 STALE_PERCENT 参数，不创建 JOB。
STALE_PERCENT 当表的数据量修改超过总数据量的一定比例数，再次触发统计信息收集，默认值为10%。
select dbms_stats.get_prefs('STALE_PERCENT')
DBMS_STATS.SET_GLOBAL_PREFS('STALE_PERCENT','5');
对于非分区表，使用默认的统计信息采集方法；对于分区表，由于现有分区每天会产生一个新的分区，在保证所有分区的统计信息都收集的基础上，每天只对新生成的分区做收集。
& --采集非分区表的统计信息
& FOR i IN (select s.TABLE_NAME, s.num_rows
& & & & & & & from dba_tables s
& & & & & & &where s.OWNER = 'ACCT'
& & & & & & & &and not exists
& & & & & & &(select distinct TABLE_NAME
& & & & & & & & & & & from dba_tab_partitions p
& & & & & & & & & & &where table_owner = 'ACCT'
& & & & & & & & & & & &and p.table_name = s.TABLE_NAME)) LOOP
& & DBMS_STATS.GATHER_TABLE_STATS('ACCT',
& & & & & & & & & & & & & & & & & i.TABLE_NAME,
& & & & & & & & & & & & & & & & & DEGREE & & & => 5,
& & & & & & & & & & & & & & & & & CASCADE & & &=> TRUE);
& END LOOP;
& --采集分区表的统计信息 &
& FOR j IN (select distinct table_name,
& & & & & & & & & & & & & & last_value(partition_name) over(partition by table_name order by partition_position ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) partition_name
& & & & & & & from dba_tab_partitions p
& & & & & & &where table_owner = 'ACCT') LOOP
& & DBMS_STATS.GATHER_TABLE_STATS('ACCT',
& & & & & & & & & & & & & & & & & tabname & & & & &=> j.table_name,
& & & & & & & & & & & & & & & & & partname & & & & => j.partition_name,
& & & & & & & & & & & & & & & & & estimate_percent => 0.000001,
& & & & & & & & & & & & & & & & & DEGREE & & & & & => 5,
& & & & & & & & & & & & & & & & & CASCADE & & & & &=> TRUE);
& END LOOP;
北京皓辰网域网络信息技术有限公司. 版权所有更多最新文章相关作者文章搜狗：感谢您阅读自动统计信息收集（Automatic Optimizer Statistics Collection） 本文版权归原作者所有，本文由网友投递产生，如有侵权请联系 ，会第一时间为您处理删除。oracle 11g自动收集统计信息任务
oracle 11g的自动收集统计信息为oracle自动去启用auto optimizer stats collection的client name，该任务有7个window name的窗口来调度，分别为MONDAY_WINDOW、TUESDAY_WINDOW、WEDNESDAY_WINDOW、THURSDAY_WINDOW、FRIDAY_WINDOW、SATURDAY_WINDOW和SUNDAY_WINDOW，并且在资源管理上面限制了自动收集统计信息任务对系统资源使用，用户可以根据各自系统的业务场景来配置是否开启自动收集统计信息，也可以调整窗口调度的开始时间、持续时间和资源组限制等。
oracle 11g的自动统计分析任务执行时间默认是：周一到周五晚上10点开始到2点结束、周六和周天是早点6点开始，持续20个小时结束
查看自动收集任务是否开启
SQL& select client_name,status,consumer_group,window_group from dba_autotask_
CLIENT_NAME & & & & & & & & & & & & & & &STATUS & CONSUMER_GROUP & & & & & & & & WINDOW_GROUP
---------------------------------------- -------- ------------------------------ ----------------------------------------------------------------
auto optimizer stats collection & & & & &ENABLED &ORA$AUTOTASK_STATS_GROUP & & & ORA$AT_WGRP_OS
auto space advisor & & & & & & & & & & & DISABLED ORA$AUTOTASK_SPACE_GROUP & & & ORA$AT_WGRP_SA
sql tuning advisor & & & & & & & & & & & DISABLED ORA$AUTOTASK_SQL_GROUP & & & & ORA$AT_WGRP_SQ
需要注意的时dba_autotask_task里面的status字段并不表示该client_name是否开启，查询client_name是否开启一定要去dba_autotask_client中的status字段查询
SQL& select task_name,client_name,status,CURRENT_JOB_NAME from dba_autotask_
TASK_NAME & & & & & & & & & & & & & & & & & & & & & & & & & & & &CLIENT_NAME & & & & & & & STATUS & & CURRENT_JOB_NAME
---------------------------------------------------------------- ------------------------- ---------- -----------------------------------------------------------------
gather_stats_prog & & & & & & & & & & & & & & & & & & & & & & & &auto optimizer stats coll ENABLED
& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &ection
SQL& col program_action for a50
SQL& select program_name,program_type,program_action,enabled from dba_scheduler_programs where program_name='GATHER_STATS_PROG';
PROGRAM_NAME & & & & & & & & & PROGRAM_TYPE & & PROGRAM_ACTION & & & & & & & & & & & & & & & & & & ENABL
------------------------------ ---------------- -------------------------------------------------- -----
GATHER_STATS_PROG & & & & & & &STORED_PROCEDURE dbms_stats.gather_database_stats_job_proc & & & & &TRUE
11g的自动统计分析任务依然是调用的dbms_stats.gather_database_stats_job_proc这个内部的存储过程，这个和10g没有区别。
dba_autotask_client、dba_autotask_task和dba_scheduler_programsd的之间的关联关系可以通过上面三个查询中得知，需要注意的时在不开启自动任务的环境中dba_autotask_task视图中是没有数据的。
启用自动收集任务：
& & & & DBMS_AUTO_TASK_ADMIN.enable(
& & & & & & & & & & & &client_name =& 'auto optimizer stats collection',
& & & & & & & & & & & &operation =& NULL,
& & & & & & & & & & & &window_name =& NULL);
禁用自动收集任务
& & & & DBMS_AUTO_TASK_ADMIN.disable(
& & & & & & & & & & & &client_name =& 'auto optimizer stats collection',
& & & & & & & & & & & &operation =& NULL,
& & & & & & & & & & & &window_name =& NULL);
在某客户的备份恢复测试库中开启11g的自动收集统计信息，由于这套库的达到了T级别的数据量，系统内的两个业务用户tbcs和common在默认的4个小时收集任务内只收集了一部分表
SQL& select count(*) total_table, trunc(last_analyzed)
& 2 & & & &from dba_tables
& 3 & & & where owner in ('TBCS', 'COMMON')
& 4 & & & group by trunc(last_analyzed)
& 5 & & & order by trunc(last_analyzed) desc
& 6 & & &;
TOTAL_TABLE TRUNC(LAS
----------- ---------
& & & & & 7 15-JAN-15
& & & &4583 09-JAN-15
& & & &4186 08-JAN-15
& & & & & 1 20-MAY-14
& & & & & 1 20-JUN-10
& & & & & 1 19-JUN-10
6 rows selected.
SQL& select count(*) total_table, trunc(last_analyzed)
& 2 & & & &from dba_tab_partitions
& 3 & & & where table_owner in ('TBCS', 'COMMON')
& 4 & & & group by trunc(last_analyzed)
& 5 & & & order by trunc(last_analyzed) desc
& 6 & & &;
TOTAL_TABLE TRUNC(LAS
----------- ---------
& & & & &66
& & & 15574 09-JAN-15
& & & 61779 08-JAN-15
& & & & & 2 22-DEC-14
& & & & &30 18-DEC-14
& & & & &30 13-DEC-14
& & & & & 1 25-NOV-14
& & & & & 1 28-OCT-14
& & & & & 1 26-SEP-14
& & & & & 1 31-AUG-14
& & & & &10 27-AUG-14
& & & & & 1 31-JUL-14
& & & & &11 25-JUL-14
& & & & & 1 18-JUL-14
& & & & & 2 11-JUL-14
& & & & &18 25-JUN-14
& & & & & 5 20-MAY-14
& & & & &10 20-FEB-14
18 rows selected.
如何来查看这个调度任务是否执行完毕了，在oracle 11g后自动统计分析的任务每次运行时都会生成一个ORA$AT_OS_OPT_XXX的作业，我们可以通过查看dba_scheduler_job_run_details视图来查看执行状态。
查看历史系统统计信息任务对应的的job_name和job_status等信息
SQL& col client_name for a20
SQL& col client_name for a25
SQL& col job_duration for a15
SQL& col job_start_time for a25;
SQL& col job_status for a10
SQL& col job_name for a20
SQL& col window_name for a20
SQL& col window_start_time for a25
SQL& col window_duration for a20
SQL& col window_end_time for a20
SQL& SELECT * FROM dba_autotask_client_history WHERE client_name like '%stats%';
CLIENT_NAME & & & & & & & WINDOW_NAME & & & & &WINDOW_START_TIME & & & & WINDOW_DURATION & & &JOBS_CREATED JOBS_STARTED JOBS_COMPLETED WINDOW_END_TIME
------------------------- -------------------- ------------------------- -------------------- ------------ ------------ -------------- --------------------
auto optimizer stats coll WEDNESDAY_WINDOW & & 14-JAN-15 10.00.00.:00:00. & & & & & &1 & & & & & &0 & & & & & & &0 15-JAN-15 02.00.00.0
ection & & & & & & & & & & & & & & & & & & & & &PM +08:00 & & & & & & & &006886 & & & & & & & & & & & & & & & & & & & & & & & & & & & &26720 AM +08:00
其中dba_autotask_client_history视图也可以查看当前正在执行的任务，其中的window_duration和WINDOW_END_TIME会一直处于变化中，直到达到指定的时间
SQL& &SELECT * FROM dba_autotask_client_history WHERE client_name like '%stats%';
CLIENT_NAME & & & & & & & WINDOW_NAME & & & & &WINDOW_START_TIME & & & & WINDOW_DURATION & & &JOBS_CREATED JOBS_STARTED JOBS_COMPLETED WINDOW_END_TIME
------------------------- -------------------- ------------------------- -------------------- ------------ ------------ -------------- --------------------
auto optimizer stats coll TUESDAY_WINDOW & & & 05-5月 -15 10.07.44.:49:56. & & & & & &1 & & & & & &0 & & & & & & &0 05-5月 -15 10.57.40.9
ection & & & & & & & & & & & & & & & & & & & & &上午 +08:00 & & & & & & &708429 & & & & & & & & & & & & & & & & & & & & & & & & & & & &51084 上午 +08:00
SQL& SELECT * FROM dba_autotask_client_history WHERE client_name like '%stats%';
CLIENT_NAME & & & & & & & WINDOW_NAME & & & & &WINDOW_START_TIME & & & & WINDOW_DURATION & & &JOBS_CREATED JOBS_STARTED JOBS_COMPLETED WINDOW_END_TIME
------------------------- -------------------- ------------------------- -------------------- ------------ ------------ -------------- --------------------
auto optimizer stats coll TUESDAY_WINDOW & & & 05-5月 -15 10.07.44.:50:05. & & & & & &1 & & & & & &0 & & & & & & &0 05-5月 -15 10.57.50.1
ection & & & & & & & & & & & & & & & & & & & & &上午 +08:00 & & & & & & &910705 & & & & & & & & & & & & & & & & & & & & & & & & & & & &53360 上午 +08:00
上面的查询中window_duration和WINDOW_END_TIME会一直处于变化中，表示任务正在执行。
SQL& select client_name,window_name,window_start_time,window_duration,job_name,job_status,job_start_time,job_duration,job_error from dba_autotask_job_history where client_name='auto optimizer stats collection'
CLIENT_NAME & & & & & & & WINDOW_NAME & & & & &WINDOW_START_TIME & & & & WINDOW_DURATION & & &JOB_NAME & & & & & & & & &JOB_STATUS
------------------------- -------------------- ------------------------- -------------------- ------------------------- ------------------------------
JOB_START_TIME & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &JOB_DURATION & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & JOB_ERROR
--------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------
auto optimizer stats coll WEDNESDAY_WINDOW & & 14-JAN-15 10.00.00.:00:00. ORA$AT_OS_OPT_SY_1 & & & &STOPPED & & & & & & & & & & &
ection & & & & & & & & & & & & & & & & & & & & &PM +08:00 & & & & & & & &006886
14-JAN-15 10.00.00.557309 PM +08:00 & & & & & & & & & & & & & & & & & & & & +000 04:00:00 & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &0
比如这里的历史job_name是ORA$AT_OS_OPT_SY_1，job_status是stopped
SQL& col job_name for a25
SQL& col status for a10
SQL& col error# for 9
SQL& col ACTUAL_START_DATE for a20
SQL& col RUN_DURATION for a10
SQL& col cpu_used for a10
SQL& select job_name,status,error#,actual_start_date,run_duration,cpu_used from dba_scheduler_job_run_details where job_name like 'ORA$AT_OS_OPT%';
JOB_NAME & & & & & & & & &STATUS & & ERROR# ACTUAL_START_DATE & &RUN_DURATI CPU_USED
------------------------- ---------- ------ -------------------- ---------- ----------
ORA$AT_OS_OPT_SY_1 & & & &STOPPED & & & & 0 14-JAN-15 10.00.00.5 +000 04:00 +000 01:41
& & & & & & & & & & & & & & & & & & & & & & 57309 PM +08:00 & & &:00 & & & &:49.34
得知从这个status字段为stopped得知，恢复测试库该收集统计信息的任务在4个小时之内并没有运行完毕，任务没有执行完毕就停止了。
job_name和job_status在这dba_scheduler_job_run_details和dba_autotask_job_history两个视图中的数据是完全吻合的。
查看自动收集任务是否正在执行，只有对应的task正在执行时，dba_autktask_client_job视图中才有数据：
SQL& select client_name,job_name,job_scheduler_status,task_name from dba_autotask_client_job where client_name='auto optimizer stats collection';
CLIENT_NAME & & & & & & & JOB_NAME & & & & & & JOB_SCHEDULER_S TASK_NAME
------------------------- -------------------- --------------- ----------------------------------------------------------------
auto optimizer stats coll ORA$AT_OS_OPT_SY_21 &RUNNING & & & & gather_stats_prog
查看autotask对应的window_name的开启状态。
SQL& select * from dba_autotask_window_
WINDOW_NAME & & & & &WINDOW_NEXT_TIME & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
-------------------- --------------------------------------------------------------------------- ----- -------- -------- -------- -------- --------
MONDAY_WINDOW & & & &19-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE ENABLED &ENABLED &DISABLED DISABLED DISABLED
TUESDAY_WINDOW & & & 20-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE ENABLED &ENABLED &DISABLED DISABLED DISABLED
WEDNESDAY_WINDOW & & 21-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE ENABLED &ENABLED &DISABLED DISABLED DISABLED
THURSDAY_WINDOW & & &15-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE ENABLED &ENABLED &DISABLED DISABLED DISABLED
FRIDAY_WINDOW & & & &16-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE ENABLED &ENABLED &DISABLED DISABLED DISABLED
SATURDAY_WINDOW & & &17-JAN-15 06.00.00.000000 AM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE ENABLED &ENABLED &DISABLED DISABLED DISABLED
SUNDAY_WINDOW & & & &18-JAN-15 06.00.00.000000 AM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE ENABLED &ENABLED &DISABLED DISABLED DISABLED
7 rows selected.
除了可以直接针对该自动任务是否开启来干预，还可以针对该任务所对应的各个window_name来干预，这个是可以通过DBMS_AUTO_TASK_ADMIN.enable和DBMS_AUTO_TASK_ADMIN.disable和DBMS_SCHEDULER.SET_ATTRIBUTE进行来调整。
比如修改window_name SATURDAY_WINDOW的start_time步骤:
1）禁掉可维护窗口
dbms_scheduler.disable(
name=&'SATURDAY_WINDOW',
force=&true);
2） 修改window_name THURSDAY_WINDOW的属性：
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW','repeat_interval','freq=byday=SAT;byhour=1;byminute=0;bysecond=0');
execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','duration',numtodsinterval(420,'minute'));
3）启用可维护窗口
dbms_scheduler.enable(
name=&'SATURDAY_WINDOW');
修改窗口的策略后可以查看是否修改成功
SQL& select window_name,repeat_interval,duration,enabled from dba_scheduler_
WINDOW_NAME & & & & & & & & & &REPEAT_INTERVAL & & & & & & & & & & & & & & & & & & & & & & & & & DURATION & & & & & & & & &ENABL
------------------------------ ----------------------------------------------------------------- ------------------------- -----
WEEKNIGHT_WINDOW & & & & & & & freq=byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; byseco +000 08:00:00 & & & & & & FALSE
& & & & & & & & & & & & & & & &nd=0
WEEKEND_WINDOW & & & & & & & & freq=byday=SAT;byhour=0;byminute=0;bysecond=0 & & & & & & & +002 00:00:00 & & & & & & FALSE
MONDAY_WINDOW & & & & & & & & &freq=byday=MON;byhour=9;byminute=49;bysecond=0 & & & & & & &+000 07:00:00 & & & & & & TRUE
TUESDAY_WINDOW & & & & & & & & freq=byday=TUE;byhour=22;byminute=0; bysecond=0 & & & & & & +000 04:00:00 & & & & & & TRUE
WEDNESDAY_WINDOW & & & & & & & freq=byday=WED;byhour=22;byminute=0; bysecond=0 & & & & & & +000 04:00:00 & & & & & & TRUE
THURSDAY_WINDOW & & & & & & & &freq=byday=THU;byhour=22;byminute=0;bysecond=0 & & & & & & &+000 04:00:00 & & & & & & TRUE
FRIDAY_WINDOW & & & & & & & & &freq=byday=FRI;byhour=22;byminute=0;bysecond=0 & & & & & & +000 04:00:00 & & & & & & TRUE
SATURDAY_WINDOW & & & & & & & &freq=byday=SAT;byhour=1;byminute=0;bysecond=0 & & & & & & & +000 07:00:00 & & & & & & TRUE
SUNDAY_WINDOW & & & & & & & & &freq=byday=SUN;byhour=6;byminute=0; bysecond=0 & & & & & & &+000 20:00:00 & & & & & & TRUE
9 rows selected.
上面要注意一定要先禁掉维护窗口，然后才能修改相关的属性。
11g修改收集统计信息的部分参数
exec dbms_stats.set_global_prefs(pname=&'degree',pvalue=&'2');
exec dbms_stats.set_global_prefs(pname=&'method_opt',pvalue=&'for all columns size auto');
在oracle 10g对应的存储过程为dbms_stats.set_param,11g下也保留了这个存储过程，也可以用dbms_stats.set_param来设置
11g的自动统计除了对于window_name做了修改，把10g原来的只有两个window_name修改为七个window_name，还对资源做了限制，而在10g环境下，oracle不会对自动收集统计信息的任务进行资源干预。
可以根据dba_autotask_client中的WINDOW_GROUP联合关联dba_scheduler_window_groups中的window_group_name
SQL& select * from dba_scheduler_window_groups where window_group_name='ORA$AT_WGRP_OS';
WINDOW_GROUP_NAME & & & & & & &ENABL NUMBER_OF_WINDOWS NEXT_START_DATE & & & & & & & & & & & & & & & & & & & & & & & & & & & & & COMMENTS
------------------------------ ----- ----------------- ------------------------------------------------------------------------- -----------------------------------
ORA$AT_WGRP_OS & & & & & & & & TRUE & & & & & & & & &7 18-JAN-15 06.00.00.000000 AM +08:00 & & & & & & & & & & & & & & & & & & auto optimizer stats collection
查看这个window_group_name下的window group members
SQL& select * from dba_scheduler_wingroup_members where window_group_name='ORA$AT_WGRP_OS';
WINDOW_GROUP_NAME & & & & & & &WINDOW_NAME
------------------------------ -------------------------
ORA$AT_WGRP_OS & & & & & & & & MONDAY_WINDOW
ORA$AT_WGRP_OS & & & & & & & & TUESDAY_WINDOW
ORA$AT_WGRP_OS & & & & & & & & WEDNESDAY_WINDOW
ORA$AT_WGRP_OS & & & & & & & & THURSDAY_WINDOW
ORA$AT_WGRP_OS & & & & & & & & FRIDAY_WINDOW
ORA$AT_WGRP_OS & & & & & & & & SATURDAY_WINDOW
ORA$AT_WGRP_OS & & & & & & & & SUNDAY_WINDOW
7 rows selected.
SQL& select a.window_name,a.next_start_date,a.active from dba_scheduler_windows a
& & &inner join dba_scheduler_wingroup_members b on a.window_name = b.window_name
& & &where b.window_group_name='ORA$AT_WGRP_OS';
WINDOW_NAME & & & & &NEXT_START_DATE & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & ACTIV
-------------------- --------------------------------------------------------------------------- -----
WEEKNIGHT_WINDOW & & 19-MAY-14 10.00.00.700000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE
WEEKEND_WINDOW & & & 24-MAY-14 12.00.00.000000 AM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE
MONDAY_WINDOW & & & &19-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE
TUESDAY_WINDOW & & & 20-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE
WEDNESDAY_WINDOW & & 21-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE
THURSDAY_WINDOW & & &15-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE
FRIDAY_WINDOW & & & &16-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE
SATURDAY_WINDOW & & &17-JAN-15 01.00.00.000000 AM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE
SUNDAY_WINDOW & & & &18-JAN-15 06.00.00.000000 AM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE
9 rows selected.
总结一下统计信息自动收集任务运行的步骤：
首先是dba_autotask_task--&dba_autotask_client建立自动执行任务
再根据时间窗口及资源组建立自动执行作业dba_autotask_client--&dba_scheduler_window_groups--&dba_scheduler_windows （查看自动统计信息任务对应的window_groups、window_name信息）
& & & & & & & & & & & & & & & & & & & & & & & & & --&dba_autotask_job_history--&dba_scheduler_job_run_details(查看自动统计信息任务生成job的历史运行状态）
& & & & & & & & & & & & & & & & & & & & & & & & & --&dba_scheduler_programs （查看自动统计信息任务对应的program_name,program_action）
About xiaoyu
xiaoyu，享受数据库带给xiaoyu的乐趣！
欢迎邮件联系讨论
This entry was posted in . Bookmark the .
2017年六月
12131415161718
19202122232425
2627282930}
Oracle 11g 统计信息收集脚本
2460阅读 0评论
在 Oracle 11g，系统自带的 JOB 里面有收集统计信息的任务，但并不一定每天每天都会收集。
在 DBMS_STATS 包中有一个&STALE_PERCENT 参数，这个参数的意义是：当表中的数据量修改超过总数据量的一定比例数，会再次触发统计信息收集，默认值为10%。
这样对于大表来说就有一个问题，收集的时间不会很及时，可能会隔好几天才会收集一次，这样会导致表的统计信息不准确，对数据库的 SQL 解析产生影响。
大表通常都是分区表，下面的收集方案主要是针对分区表，也涵盖了普通表。
对于非分区表，使用默认的统计信息采集方法；对于分区表，使用增量采集方法，只对数据有变动的分区做收集。
& --采集非分区表的统计信息
& FOR i IN (select s.TABLE_NAME, s.num_rows
& & & & & & & from dba_tables s
& & & & & & &where s.OWNER = 'ACCT'
& & & & & & & &and not exists
& & & & & & &(select distinct TABLE_NAME
& & & & & & & & & & & from dba_tab_partitions p
& & & & & & & & & & &where table_owner = 'ACCT'
& & & & & & & & & & & &and p.table_name = s.TABLE_NAME)) LOOP
& & DBMS_STATS.GATHER_TABLE_STATS('ACCT',
& & & & & & & & & & & & & & & & & i.TABLE_NAME,
& & & & & & & & & & & & & & & & & DEGREE & & & => 5,
& & & & & & & & & & & & & & & & & CASCADE & & &=> TRUE);
& END LOOP;
& --采集分区表的统计信息 &
& FOR j IN (select distinct TABLE_NAME
& & & & & & & from dba_tab_partitions p
& & & & & & &where table_owner = 'ACCT') LOOP
& & DBMS_STATS.GATHER_TABLE_STATS('ACCT',
& & & & & & & & & & & & & & & & & tabname & & & & &=> j.table_name,
& & & & & & & & & & & & & & & & & estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
& & & & & & & & & & & & & & & & & GRANULARITY & & &=> 'AUTO',
& & & & & & & & & & & & & & & & & DEGREE & & & & & => 5,
& & & & & & & & & & & & & & & & & CASCADE & & & & &=> TRUE);
& END LOOP;
--对于分区表，设置 INCREMENTAL 参数只对数据有变动的分区做收集
在对分区表收集统计信息的时候，如果想让 Oracle 只扫描发生数据改变的分区，避免全表扫描，需要满足以下条件：
(1) 指定分区表的 INCREMENTAL 参数设置为 TRUE
(2) 指定分区表的 PUBLISH 设置为 TRUE
(3) ESTIMATE_PERCENT 参数设为 AUTO_SAMPLE_SIZE，GRANULARITY 参数设为 AUTO
--统计信息收集设置选项
索引的统计信息是否和表一起收集。
收集的并行度。
ESTIMATE_PERCENT 决定收集的比例，范围是[0.]。可以通过DBMS_STATS.AUTO_SAMPLE_SIZE来让oracle自动决定收集的比例。这是默认值。
METHOD_OPT 控制列和柱状图的收集方法。
GRANULARITY 收集的粒度。
当统计信息收集JOB执行完毕后，是否更新新的统计信息。
INCREMENTAL 决定是否维持分区表的全局统计信息,而不做全表扫描。当开启这个参数的时候，Oracle仅需扫描分区表中有数据变化的分区来更新分区表的统计信息，而无需扫描整个分区表。
STALE_PERCENT 当表的数据量修改超过总数据量的一定比例数，再次触发统计信息收集，默认值为10%。
--查询相关参数
select dbms_stats.get_prefs('PUBLISH')
select dbms_stats.get_prefs('method_opt')
select dbms_stats.get_prefs('GRANULARITY')
select dbms_stats.get_prefs('INCREMENTAL')
--查看分区表INCREMENTAL、PUBLISH的值
select dbms_stats.get_prefs('INCREMENTAL','ACCT','ELMP_TRANS_CUSTOMACCOUNTFLOW')
select dbms_stats.get_prefs('PUBLISH','ACCT','ELMP_TRANS_CUSTOMACCOUNTFLOW')
--exec DBMS_STATS.SET_PARAM('INCREMENTAL','TRUE');
--只收集数据变动的分区
& FOR j IN (select distinct TABLE_NAME
& & & & & & & from dba_tab_partitions p
& & & & & & &where table_owner = 'ACCT') LOOP
& & DBMS_STATS.SET_TABLE_PREFS('ACCT', j.table_name, 'INCREMENTAL', 'TRUE');
& END LOOP;
只修改 STALE_PERCENT 参数，不创建 JOB。
STALE_PERCENT 当表的数据量修改超过总数据量的一定比例数，再次触发统计信息收集，默认值为10%。
select dbms_stats.get_prefs('STALE_PERCENT')
DBMS_STATS.SET_GLOBAL_PREFS('STALE_PERCENT','5');
对于非分区表，使用默认的统计信息采集方法；对于分区表，由于现有分区每天会产生一个新的分区，在保证所有分区的统计信息都收集的基础上，每天只对新生成的分区做收集。
& --采集非分区表的统计信息
& FOR i IN (select s.TABLE_NAME, s.num_rows
& & & & & & & from dba_tables s
& & & & & & &where s.OWNER = 'ACCT'
& & & & & & & &and not exists
& & & & & & &(select distinct TABLE_NAME
& & & & & & & & & & & from dba_tab_partitions p
& & & & & & & & & & &where table_owner = 'ACCT'
& & & & & & & & & & & &and p.table_name = s.TABLE_NAME)) LOOP
& & DBMS_STATS.GATHER_TABLE_STATS('ACCT',
& & & & & & & & & & & & & & & & & i.TABLE_NAME,
& & & & & & & & & & & & & & & & & DEGREE & & & => 5,
& & & & & & & & & & & & & & & & & CASCADE & & &=> TRUE);
& END LOOP;
& --采集分区表的统计信息 &
& FOR j IN (select distinct table_name,
& & & & & & & & & & & & & & last_value(partition_name) over(partition by table_name order by partition_position ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) partition_name
& & & & & & & from dba_tab_partitions p
& & & & & & &where table_owner = 'ACCT') LOOP
& & DBMS_STATS.GATHER_TABLE_STATS('ACCT',
& & & & & & & & & & & & & & & & & tabname & & & & &=> j.table_name,
& & & & & & & & & & & & & & & & & partname & & & & => j.partition_name,
& & & & & & & & & & & & & & & & & estimate_percent => 0.000001,
& & & & & & & & & & & & & & & & & DEGREE & & & & & => 5,
& & & & & & & & & & & & & & & & & CASCADE & & & & &=> TRUE);
& END LOOP;
北京皓辰网域网络信息技术有限公司. 版权所有更多最新文章相关作者文章搜狗：感谢您阅读自动统计信息收集（Automatic Optimizer Statistics Collection） 本文版权归原作者所有，本文由网友投递产生，如有侵权请联系 ，会第一时间为您处理删除。oracle 11g自动收集统计信息任务
oracle 11g的自动收集统计信息为oracle自动去启用auto optimizer stats collection的client name，该任务有7个window name的窗口来调度，分别为MONDAY_WINDOW、TUESDAY_WINDOW、WEDNESDAY_WINDOW、THURSDAY_WINDOW、FRIDAY_WINDOW、SATURDAY_WINDOW和SUNDAY_WINDOW，并且在资源管理上面限制了自动收集统计信息任务对系统资源使用，用户可以根据各自系统的业务场景来配置是否开启自动收集统计信息，也可以调整窗口调度的开始时间、持续时间和资源组限制等。
oracle 11g的自动统计分析任务执行时间默认是：周一到周五晚上10点开始到2点结束、周六和周天是早点6点开始，持续20个小时结束
查看自动收集任务是否开启
SQL& select client_name,status,consumer_group,window_group from dba_autotask_
CLIENT_NAME & & & & & & & & & & & & & & &STATUS & CONSUMER_GROUP & & & & & & & & WINDOW_GROUP
---------------------------------------- -------- ------------------------------ ----------------------------------------------------------------
auto optimizer stats collection & & & & &ENABLED &ORA$AUTOTASK_STATS_GROUP & & & ORA$AT_WGRP_OS
auto space advisor & & & & & & & & & & & DISABLED ORA$AUTOTASK_SPACE_GROUP & & & ORA$AT_WGRP_SA
sql tuning advisor & & & & & & & & & & & DISABLED ORA$AUTOTASK_SQL_GROUP & & & & ORA$AT_WGRP_SQ
需要注意的时dba_autotask_task里面的status字段并不表示该client_name是否开启，查询client_name是否开启一定要去dba_autotask_client中的status字段查询
SQL& select task_name,client_name,status,CURRENT_JOB_NAME from dba_autotask_
TASK_NAME & & & & & & & & & & & & & & & & & & & & & & & & & & & &CLIENT_NAME & & & & & & & STATUS & & CURRENT_JOB_NAME
---------------------------------------------------------------- ------------------------- ---------- -----------------------------------------------------------------
gather_stats_prog & & & & & & & & & & & & & & & & & & & & & & & &auto optimizer stats coll ENABLED
& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &ection
SQL& col program_action for a50
SQL& select program_name,program_type,program_action,enabled from dba_scheduler_programs where program_name='GATHER_STATS_PROG';
PROGRAM_NAME & & & & & & & & & PROGRAM_TYPE & & PROGRAM_ACTION & & & & & & & & & & & & & & & & & & ENABL
------------------------------ ---------------- -------------------------------------------------- -----
GATHER_STATS_PROG & & & & & & &STORED_PROCEDURE dbms_stats.gather_database_stats_job_proc & & & & &TRUE
11g的自动统计分析任务依然是调用的dbms_stats.gather_database_stats_job_proc这个内部的存储过程，这个和10g没有区别。
dba_autotask_client、dba_autotask_task和dba_scheduler_programsd的之间的关联关系可以通过上面三个查询中得知，需要注意的时在不开启自动任务的环境中dba_autotask_task视图中是没有数据的。
启用自动收集任务：
& & & & DBMS_AUTO_TASK_ADMIN.enable(
& & & & & & & & & & & &client_name =& 'auto optimizer stats collection',
& & & & & & & & & & & &operation =& NULL,
& & & & & & & & & & & &window_name =& NULL);
禁用自动收集任务
& & & & DBMS_AUTO_TASK_ADMIN.disable(
& & & & & & & & & & & &client_name =& 'auto optimizer stats collection',
& & & & & & & & & & & &operation =& NULL,
& & & & & & & & & & & &window_name =& NULL);
在某客户的备份恢复测试库中开启11g的自动收集统计信息，由于这套库的达到了T级别的数据量，系统内的两个业务用户tbcs和common在默认的4个小时收集任务内只收集了一部分表
SQL& select count(*) total_table, trunc(last_analyzed)
& 2 & & & &from dba_tables
& 3 & & & where owner in ('TBCS', 'COMMON')
& 4 & & & group by trunc(last_analyzed)
& 5 & & & order by trunc(last_analyzed) desc
& 6 & & &;
TOTAL_TABLE TRUNC(LAS
----------- ---------
& & & & & 7 15-JAN-15
& & & &4583 09-JAN-15
& & & &4186 08-JAN-15
& & & & & 1 20-MAY-14
& & & & & 1 20-JUN-10
& & & & & 1 19-JUN-10
6 rows selected.
SQL& select count(*) total_table, trunc(last_analyzed)
& 2 & & & &from dba_tab_partitions
& 3 & & & where table_owner in ('TBCS', 'COMMON')
& 4 & & & group by trunc(last_analyzed)
& 5 & & & order by trunc(last_analyzed) desc
& 6 & & &;
TOTAL_TABLE TRUNC(LAS
----------- ---------
& & & & &66
& & & 15574 09-JAN-15
& & & 61779 08-JAN-15
& & & & & 2 22-DEC-14
& & & & &30 18-DEC-14
& & & & &30 13-DEC-14
& & & & & 1 25-NOV-14
& & & & & 1 28-OCT-14
& & & & & 1 26-SEP-14
& & & & & 1 31-AUG-14
& & & & &10 27-AUG-14
& & & & & 1 31-JUL-14
& & & & &11 25-JUL-14
& & & & & 1 18-JUL-14
& & & & & 2 11-JUL-14
& & & & &18 25-JUN-14
& & & & & 5 20-MAY-14
& & & & &10 20-FEB-14
18 rows selected.
如何来查看这个调度任务是否执行完毕了，在oracle 11g后自动统计分析的任务每次运行时都会生成一个ORA$AT_OS_OPT_XXX的作业，我们可以通过查看dba_scheduler_job_run_details视图来查看执行状态。
查看历史系统统计信息任务对应的的job_name和job_status等信息
SQL& col client_name for a20
SQL& col client_name for a25
SQL& col job_duration for a15
SQL& col job_start_time for a25;
SQL& col job_status for a10
SQL& col job_name for a20
SQL& col window_name for a20
SQL& col window_start_time for a25
SQL& col window_duration for a20
SQL& col window_end_time for a20
SQL& SELECT * FROM dba_autotask_client_history WHERE client_name like '%stats%';
CLIENT_NAME & & & & & & & WINDOW_NAME & & & & &WINDOW_START_TIME & & & & WINDOW_DURATION & & &JOBS_CREATED JOBS_STARTED JOBS_COMPLETED WINDOW_END_TIME
------------------------- -------------------- ------------------------- -------------------- ------------ ------------ -------------- --------------------
auto optimizer stats coll WEDNESDAY_WINDOW & & 14-JAN-15 10.00.00.:00:00. & & & & & &1 & & & & & &0 & & & & & & &0 15-JAN-15 02.00.00.0
ection & & & & & & & & & & & & & & & & & & & & &PM +08:00 & & & & & & & &006886 & & & & & & & & & & & & & & & & & & & & & & & & & & & &26720 AM +08:00
其中dba_autotask_client_history视图也可以查看当前正在执行的任务，其中的window_duration和WINDOW_END_TIME会一直处于变化中，直到达到指定的时间
SQL& &SELECT * FROM dba_autotask_client_history WHERE client_name like '%stats%';
CLIENT_NAME & & & & & & & WINDOW_NAME & & & & &WINDOW_START_TIME & & & & WINDOW_DURATION & & &JOBS_CREATED JOBS_STARTED JOBS_COMPLETED WINDOW_END_TIME
------------------------- -------------------- ------------------------- -------------------- ------------ ------------ -------------- --------------------
auto optimizer stats coll TUESDAY_WINDOW & & & 05-5月 -15 10.07.44.:49:56. & & & & & &1 & & & & & &0 & & & & & & &0 05-5月 -15 10.57.40.9
ection & & & & & & & & & & & & & & & & & & & & &上午 +08:00 & & & & & & &708429 & & & & & & & & & & & & & & & & & & & & & & & & & & & &51084 上午 +08:00
SQL& SELECT * FROM dba_autotask_client_history WHERE client_name like '%stats%';
CLIENT_NAME & & & & & & & WINDOW_NAME & & & & &WINDOW_START_TIME & & & & WINDOW_DURATION & & &JOBS_CREATED JOBS_STARTED JOBS_COMPLETED WINDOW_END_TIME
------------------------- -------------------- ------------------------- -------------------- ------------ ------------ -------------- --------------------
auto optimizer stats coll TUESDAY_WINDOW & & & 05-5月 -15 10.07.44.:50:05. & & & & & &1 & & & & & &0 & & & & & & &0 05-5月 -15 10.57.50.1
ection & & & & & & & & & & & & & & & & & & & & &上午 +08:00 & & & & & & &910705 & & & & & & & & & & & & & & & & & & & & & & & & & & & &53360 上午 +08:00
上面的查询中window_duration和WINDOW_END_TIME会一直处于变化中，表示任务正在执行。
SQL& select client_name,window_name,window_start_time,window_duration,job_name,job_status,job_start_time,job_duration,job_error from dba_autotask_job_history where client_name='auto optimizer stats collection'
CLIENT_NAME & & & & & & & WINDOW_NAME & & & & &WINDOW_START_TIME & & & & WINDOW_DURATION & & &JOB_NAME & & & & & & & & &JOB_STATUS
------------------------- -------------------- ------------------------- -------------------- ------------------------- ------------------------------
JOB_START_TIME & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &JOB_DURATION & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & JOB_ERROR
--------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------
auto optimizer stats coll WEDNESDAY_WINDOW & & 14-JAN-15 10.00.00.:00:00. ORA$AT_OS_OPT_SY_1 & & & &STOPPED & & & & & & & & & & &
ection & & & & & & & & & & & & & & & & & & & & &PM +08:00 & & & & & & & &006886
14-JAN-15 10.00.00.557309 PM +08:00 & & & & & & & & & & & & & & & & & & & & +000 04:00:00 & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &0
比如这里的历史job_name是ORA$AT_OS_OPT_SY_1，job_status是stopped
SQL& col job_name for a25
SQL& col status for a10
SQL& col error# for 9
SQL& col ACTUAL_START_DATE for a20
SQL& col RUN_DURATION for a10
SQL& col cpu_used for a10
SQL& select job_name,status,error#,actual_start_date,run_duration,cpu_used from dba_scheduler_job_run_details where job_name like 'ORA$AT_OS_OPT%';
JOB_NAME & & & & & & & & &STATUS & & ERROR# ACTUAL_START_DATE & &RUN_DURATI CPU_USED
------------------------- ---------- ------ -------------------- ---------- ----------
ORA$AT_OS_OPT_SY_1 & & & &STOPPED & & & & 0 14-JAN-15 10.00.00.5 +000 04:00 +000 01:41
& & & & & & & & & & & & & & & & & & & & & & 57309 PM +08:00 & & &:00 & & & &:49.34
得知从这个status字段为stopped得知，恢复测试库该收集统计信息的任务在4个小时之内并没有运行完毕，任务没有执行完毕就停止了。
job_name和job_status在这dba_scheduler_job_run_details和dba_autotask_job_history两个视图中的数据是完全吻合的。
查看自动收集任务是否正在执行，只有对应的task正在执行时，dba_autktask_client_job视图中才有数据：
SQL& select client_name,job_name,job_scheduler_status,task_name from dba_autotask_client_job where client_name='auto optimizer stats collection';
CLIENT_NAME & & & & & & & JOB_NAME & & & & & & JOB_SCHEDULER_S TASK_NAME
------------------------- -------------------- --------------- ----------------------------------------------------------------
auto optimizer stats coll ORA$AT_OS_OPT_SY_21 &RUNNING & & & & gather_stats_prog
查看autotask对应的window_name的开启状态。
SQL& select * from dba_autotask_window_
WINDOW_NAME & & & & &WINDOW_NEXT_TIME & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
-------------------- --------------------------------------------------------------------------- ----- -------- -------- -------- -------- --------
MONDAY_WINDOW & & & &19-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE ENABLED &ENABLED &DISABLED DISABLED DISABLED
TUESDAY_WINDOW & & & 20-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE ENABLED &ENABLED &DISABLED DISABLED DISABLED
WEDNESDAY_WINDOW & & 21-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE ENABLED &ENABLED &DISABLED DISABLED DISABLED
THURSDAY_WINDOW & & &15-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE ENABLED &ENABLED &DISABLED DISABLED DISABLED
FRIDAY_WINDOW & & & &16-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE ENABLED &ENABLED &DISABLED DISABLED DISABLED
SATURDAY_WINDOW & & &17-JAN-15 06.00.00.000000 AM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE ENABLED &ENABLED &DISABLED DISABLED DISABLED
SUNDAY_WINDOW & & & &18-JAN-15 06.00.00.000000 AM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE ENABLED &ENABLED &DISABLED DISABLED DISABLED
7 rows selected.
除了可以直接针对该自动任务是否开启来干预，还可以针对该任务所对应的各个window_name来干预，这个是可以通过DBMS_AUTO_TASK_ADMIN.enable和DBMS_AUTO_TASK_ADMIN.disable和DBMS_SCHEDULER.SET_ATTRIBUTE进行来调整。
比如修改window_name SATURDAY_WINDOW的start_time步骤:
1）禁掉可维护窗口
dbms_scheduler.disable(
name=&'SATURDAY_WINDOW',
force=&true);
2） 修改window_name THURSDAY_WINDOW的属性：
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW','repeat_interval','freq=byday=SAT;byhour=1;byminute=0;bysecond=0');
execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','duration',numtodsinterval(420,'minute'));
3）启用可维护窗口
dbms_scheduler.enable(
name=&'SATURDAY_WINDOW');
修改窗口的策略后可以查看是否修改成功
SQL& select window_name,repeat_interval,duration,enabled from dba_scheduler_
WINDOW_NAME & & & & & & & & & &REPEAT_INTERVAL & & & & & & & & & & & & & & & & & & & & & & & & & DURATION & & & & & & & & &ENABL
------------------------------ ----------------------------------------------------------------- ------------------------- -----
WEEKNIGHT_WINDOW & & & & & & & freq=byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; byseco +000 08:00:00 & & & & & & FALSE
& & & & & & & & & & & & & & & &nd=0
WEEKEND_WINDOW & & & & & & & & freq=byday=SAT;byhour=0;byminute=0;bysecond=0 & & & & & & & +002 00:00:00 & & & & & & FALSE
MONDAY_WINDOW & & & & & & & & &freq=byday=MON;byhour=9;byminute=49;bysecond=0 & & & & & & &+000 07:00:00 & & & & & & TRUE
TUESDAY_WINDOW & & & & & & & & freq=byday=TUE;byhour=22;byminute=0; bysecond=0 & & & & & & +000 04:00:00 & & & & & & TRUE
WEDNESDAY_WINDOW & & & & & & & freq=byday=WED;byhour=22;byminute=0; bysecond=0 & & & & & & +000 04:00:00 & & & & & & TRUE
THURSDAY_WINDOW & & & & & & & &freq=byday=THU;byhour=22;byminute=0;bysecond=0 & & & & & & &+000 04:00:00 & & & & & & TRUE
FRIDAY_WINDOW & & & & & & & & &freq=byday=FRI;byhour=22;byminute=0;bysecond=0 & & & & & & +000 04:00:00 & & & & & & TRUE
SATURDAY_WINDOW & & & & & & & &freq=byday=SAT;byhour=1;byminute=0;bysecond=0 & & & & & & & +000 07:00:00 & & & & & & TRUE
SUNDAY_WINDOW & & & & & & & & &freq=byday=SUN;byhour=6;byminute=0; bysecond=0 & & & & & & &+000 20:00:00 & & & & & & TRUE
9 rows selected.
上面要注意一定要先禁掉维护窗口，然后才能修改相关的属性。
11g修改收集统计信息的部分参数
exec dbms_stats.set_global_prefs(pname=&'degree',pvalue=&'2');
exec dbms_stats.set_global_prefs(pname=&'method_opt',pvalue=&'for all columns size auto');
在oracle 10g对应的存储过程为dbms_stats.set_param,11g下也保留了这个存储过程，也可以用dbms_stats.set_param来设置
11g的自动统计除了对于window_name做了修改，把10g原来的只有两个window_name修改为七个window_name，还对资源做了限制，而在10g环境下，oracle不会对自动收集统计信息的任务进行资源干预。
可以根据dba_autotask_client中的WINDOW_GROUP联合关联dba_scheduler_window_groups中的window_group_name
SQL& select * from dba_scheduler_window_groups where window_group_name='ORA$AT_WGRP_OS';
WINDOW_GROUP_NAME & & & & & & &ENABL NUMBER_OF_WINDOWS NEXT_START_DATE & & & & & & & & & & & & & & & & & & & & & & & & & & & & & COMMENTS
------------------------------ ----- ----------------- ------------------------------------------------------------------------- -----------------------------------
ORA$AT_WGRP_OS & & & & & & & & TRUE & & & & & & & & &7 18-JAN-15 06.00.00.000000 AM +08:00 & & & & & & & & & & & & & & & & & & auto optimizer stats collection
查看这个window_group_name下的window group members
SQL& select * from dba_scheduler_wingroup_members where window_group_name='ORA$AT_WGRP_OS';
WINDOW_GROUP_NAME & & & & & & &WINDOW_NAME
------------------------------ -------------------------
ORA$AT_WGRP_OS & & & & & & & & MONDAY_WINDOW
ORA$AT_WGRP_OS & & & & & & & & TUESDAY_WINDOW
ORA$AT_WGRP_OS & & & & & & & & WEDNESDAY_WINDOW
ORA$AT_WGRP_OS & & & & & & & & THURSDAY_WINDOW
ORA$AT_WGRP_OS & & & & & & & & FRIDAY_WINDOW
ORA$AT_WGRP_OS & & & & & & & & SATURDAY_WINDOW
ORA$AT_WGRP_OS & & & & & & & & SUNDAY_WINDOW
7 rows selected.
SQL& select a.window_name,a.next_start_date,a.active from dba_scheduler_windows a
& & &inner join dba_scheduler_wingroup_members b on a.window_name = b.window_name
& & &where b.window_group_name='ORA$AT_WGRP_OS';
WINDOW_NAME & & & & &NEXT_START_DATE & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & ACTIV
-------------------- --------------------------------------------------------------------------- -----
WEEKNIGHT_WINDOW & & 19-MAY-14 10.00.00.700000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE
WEEKEND_WINDOW & & & 24-MAY-14 12.00.00.000000 AM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE
MONDAY_WINDOW & & & &19-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE
TUESDAY_WINDOW & & & 20-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE
WEDNESDAY_WINDOW & & 21-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE
THURSDAY_WINDOW & & &15-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE
FRIDAY_WINDOW & & & &16-JAN-15 10.00.00.000000 PM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE
SATURDAY_WINDOW & & &17-JAN-15 01.00.00.000000 AM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE
SUNDAY_WINDOW & & & &18-JAN-15 06.00.00.000000 AM +08:00 & & & & & & & & & & & & & & & & & & & & FALSE
9 rows selected.
总结一下统计信息自动收集任务运行的步骤：
首先是dba_autotask_task--&dba_autotask_client建立自动执行任务
再根据时间窗口及资源组建立自动执行作业dba_autotask_client--&dba_scheduler_window_groups--&dba_scheduler_windows （查看自动统计信息任务对应的window_groups、window_name信息）
& & & & & & & & & & & & & & & & & & & & & & & & & --&dba_autotask_job_history--&dba_scheduler_job_run_details(查看自动统计信息任务生成job的历史运行状态）
& & & & & & & & & & & & & & & & & & & & & & & & & --&dba_scheduler_programs （查看自动统计信息任务对应的program_name,program_action）
About xiaoyu
xiaoyu，享受数据库带给xiaoyu的乐趣！
欢迎邮件联系讨论
This entry was posted in . Bookmark the .
2017年六月
12131415161718
19202122232425
2627282930}
我要回帖
更多关于 表统计信息收集 的文章
更多推荐
- ·什么是凯恩斯主义的核心内容是什么,基本观点
- ·红军25000里长征长征一开始有多少人最后剩下多少人去
- ·红军长征为何要去陕北到达陕北后蒋为什么不打了
- ·红军二万五2万5千里长征走了几年后到陕北还有多少兵力
- ·红军2二万五千里长征路线,平均每天要行多少公里?
- ·逐平县2015年2015边阳农业总产值值多少
- ·张古董的张娅姝一生守候视频,金融词
- ·现货原油怎么开户开户流程是怎样的?
- ·保健内裤工厂厂家直销广告语录音
- ·兰考县2017年新农合报销标准都需要什么
- ·【上瘾网络剧未删减1 18】全集，含未删剪版！谁要！？
- ·微信oauth2.0网页授权 授权的工作原理是怎样的？足够安全吗
- ·自动消防水炮找食人鱼软件千炮
- ·爱神巧克力夏紫瞳h文图片手绘
- ·一键红外转发器器免费下载
- ·谁帮我制作一张假火车票报销哪里买截图。万分感谢
- ·请问一下这个app是《百度知道》吗？怎么打开电脑不知道干什么来像约泡软件？
- ·怎么把照片弄成这种asdw和上下左右有白框的形式，in好像只能弄成左右的白框
- ·谁个有下水道的美人鱼资源的资源的.书分享
- ·谁帮我把这图下面的字打出来 谢谢你帮助我
- ·如何在11g上收集 Optimizer 收集表的统计信息息
- ·Golang 官方网站被全封闭阳台怎样晒被子了怎么办
- ·求个lovelivelol国服rank排名号，g+端的，只求高rank练手，活动可拿一卡，私信
- ·有什么软件和新商盟官方下载安装软件相似
- ·三星smg93507200应用程序建文件夹
- ·多久以后能出现三维空间是哪三维游戏
- ·博乐人仔抽抽乐是单面表情，还是双面表情
- ·在3d绘图软件做的maya法线贴图教程导入maya里效果不好，怎么办？
- ·那你能查到什么呀帮我查一下吗拜托了谢谢
- ·一键写入天赋老写在最后一页
- ·求文笔好的宠文女主文，要不小白的，甜宠文、各种宠的
- ·有没有系统之绝色尤物txt全文阅读完整版
- ·谁有口袋妖怪漆黑的魅影闪光金手指5.0带金手指的 没有广告 用金手指不会奔溃的
- ·我想把手机上福的图片片不想展现的东西涂鸦一下，怎么处理的，有知道吗？
- ·不做生意和做买卖区别，只是求证真伪？
