在自动收集统计信息之前，有几个问题需要考虑：
1、那些表适合使用计算模式收集统计信息？那些表适合使用估算模式收集统计信息？估算比率是多少？
2、那些列需要收集直方图信息，bucket是多少？
3、在什么时间收集统计信息，收集多长时间，如何让收集统计信息的作业不会对业务造成影响。
4、如何避免收集统计信息的时间太长，
5、如何高效的收集全库统计信息，统计信息过期的判断标准是什么，或者说重复收集统计信息的策略是什么？
如上这些问题其实很那回答，在oracle10g之后，使用自动统计信息收集作业能够自动判断以上问题。自动收集统计信息的前提是statistics_level为typical或者all。另外，oracle自动收集统计信息作业会自动收集数据字典统计信息，但是不会收集内部对象（X$）对象信息。
在oracle 10g，自动收集统计信息作业为'GATHER_STATS_JOB',查看库预定义作业：
select job_name,program_name,schedule_name from dba_scheduler_jobs where job_name='GATHER_STATS_JOB';
查看调用的窗口：
select * from dba_scheduler_wingroup_members where window_group_name='MAINTENACE_WINDOW_GROUP'
查看窗口调用时间：
select window_name,repeat_interval,duration from dba_scheduler_windows where windows_name in (select WINDOWS_NAME from dba_scheduler_wingroup_members where window_group_name=(select schedule_name from dba_scheduler_jobs where job_name='GATHER_STATS_JOB')) AND ENABLED='TRUE';
查看窗口执行历史：
select actual_start_date,job_name,status from (select * from dba_scheduler_job_run_details where job_name='GATHER_STATS_JOB' ORDER BY LOG_DATE DESC ) WHERE ROWNUM<4;
在oracle 11g，自动收集统计信息作业为'GATHER_STATS_PROG',查看库预定义作业：
select client_name,task_name,operation_name,status from dba_autotask_task;
select program_action from dba_scheduler_programs where program_name='GATHER_STATS_PROG';
查看窗口：
select windows_name,autotask_sttus from dba_autotask_window_clients;
查看窗口调用时间：
select window_name,repeat_interval,duration from dba_scheduler_windows where enabled='TRUE';
查看窗口默认资源限制：
SELECT WINDOW_NAME,RESOURCE_PLAN FROM DBA_SCHEDULER_WINDOWS ;
历史执行信息：
select client_name,windows_name,jon_name,jon_start_time from (select * from dba_autotask_job_history where clinet_name='auto optimizer stats collection' order by window_start_time desc ) where rownum<4;
Oracle 11g中的自动统计信息作业是通过GATHER_STATS_PROG来实现的，每次运行时会先生成ORA$AT_OS_OPT_XX的作业，然后在执行这个作业来自动收集统计信息。
数据库通过获取sys.mon_mods_all$信息监控每个对象操作信息，包括inserts,updates，deletes和truncate操作。
SQL> select obj#,inserts,updates,deletes,flags from sys.mon_mods_all$ where obj#=87688;
OBJ# INSERTS UPDATES DELETES FLAGS
---------- ---------- ---------- ---------- ----------
87688 1 10000 499 0
其中FLAGS是标记是否执行truncate操作。
策略
1、当系统导入了大量数据之后，如果要马上进行相关业务处理，建议在开始业务处理之前及时对相关表手工收集统计信息。因为这些被导入了大量数据的表已经来不及等到当天的自动统计信息收集作业自动收集统计信息了。
2、某些新上线或新迁移的系统，其中的某些表在上线/迁移之前初始数据量为0（相关统计信息也为0），接在导入商量数据后开始正式上线业务处理，建议在进行相关业务之前对这些表手工收集统计信息。
自定义统计信息自动收集任务：
1、禁止掉自动统计信息收集作业：
在10g中，exec dbms_scheduler.disable('GATHER_STATS_JOB');
而在11g中，exec dbms_auto_task_admin.disable(
client_name=>'auto optimier stats collection',
operation=>null,
window_name=>null);
2、禁止掉周一到周五的自动收集作业：
在10g中，周一至周五是weeknight_window,所以我们直接禁止掉就可以了。
begin
dbms_scheduler.disable(
name=>'"SYS"."WEEKNIGHT_WINDOW"',
force=>true);
end;
/
select windows_name,repeat_interval,duration,enabled from dba_scheduler_windows where window_name in ('WEEKNIGHT_WINDOW','WEEKEND_WINDOW');
3、在11g中，禁止掉周一自动收集作业，将周二的其实执行时间改为晚上11点，周三起始执行时间改为晚上9点，周三执行时间调整为5个小时。
select window_name,repeat_interval,duration,enabled from dba_scheduler_windows;
WINDOW_NAME REPEAT_INTERVAL DURATION ENABLED
-------------------- ------------------------------ -------------------- ----------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22 +000 04:00:00 TRUE
;byminute=0; bysecond=0
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22 +000 04:00:00 TRUE
;byminute=0; bysecond=0
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22 +000 04:00:00 TRUE
;byminute=0; bysecond=0
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22 +000 04:00:00 TRUE
;byminute=0; bysecond=0
WINDOW_NAME REPEAT_INTERVAL DURATION ENABLED
-------------------- ------------------------------ -------------------- ----------
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22 +000 04:00:00 TRUE
;byminute=0; bysecond=0
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6; +000 20:00:00 TRUE
byminute=0; bysecond=0
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6; +000 20:00:00 TRUE
byminute=0; bysecond=0
WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,T +000 08:00:00 FALSE
WINDOW_NAME REPEAT_INTERVAL DURATION ENABLED
-------------------- ------------------------------ -------------------- ----------
HU,FRI;byhour=22;byminute=0; b
ysecond=0
WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0; +002 00:00:00 FALSE
byminute=0;bysecond=0
禁止周一自动收集任务需要先disable掉在enabled掉，最后在修改
禁止掉周一自动收集任务：
begin
dbms_scheduler.disable(
name=>'"SYS"."MONDAY_WINDOW"',
force=>true);
end;
/
修改周二自动收集任务：
begin
dbms_scheduler.disable(
name=>'"SYS"."TUESDAY_WINDOW"',
force=>true);
end;
/
begin
dbms_scheduler.set_attribute(
name=>'"SYS"."TUESDAY_WINDOW"',
attribute=>'repeat_interval',
value=>'freq=daily;byday=TUE;byhour=23;byminute=0;bysecond=0');
end;
/
begin
dbms_scheduler.enable(
name=>'"SYS"."TUESDAY_WINDOW"');
end;
/
修改周三自动收集任务：
begin
dbms_scheduler.disable(
name=>'"SYS"."WEDNESDAY_WINDOW"',
force=>true);
end;
/
修改周三自动收集作业夜晚9点开始
begin
dbms_scheduler.set_attribute(
name=>'"SYS"."WEDNESDAY_WINDOW"',
attribute=>'repeat_interval',
value=>'freq=daily;byday=WED;byhour=21;byminute=0;bysecond=0');
end;
/
修改周三自动收集作业时长为5小时
begin
dbms_scheduler.set_attribute(
name=>'"SYS"."WEDNESDAY_WINDOW"',
attribute=>'duration',
value=>numtodsinterval(300,'minute'));
end;
/
begin
dbms_scheduler.enable(
name=>'"SYS"."WEDNESDAY_WINDOW"');
end;
/
如果只想禁止掉自动收集统计信息作业，而保留automatic segment adviser和automatic sql tuning adviser作业的话，则需要：
SQL> select window_name,optimizer_stats,segment_advisor,sql_tune_advisor from dba_autotask_window_clients;
WINDOW_NAME OPTIMIZER_STATS SEGMENT_ADVISOR SQL_TUNE_ADVISOR
-------------------- ---------------- ---------------- ----------------
TUESDAY_WINDOW ENABLED ENABLED ENABLED
WEDNESDAY_WINDOW ENABLED ENABLED ENABLED
THURSDAY_WINDOW ENABLED ENABLED ENABLED
FRIDAY_WINDOW ENABLED ENABLED ENABLED
SATURDAY_WINDOW ENABLED ENABLED ENABLED
SUNDAY_WINDOW ENABLED ENABLED ENABLED
begin
dbms_auto_task_admin.disable(
client_name=>'auto optimier stats collection',
operation=>null,
window_name=>'MONDAY_WINDOW ');
end;
/
查看
SQL> select window_name,optimizer_stats,segment_advisor,sql_tune_advisor from dba_autotask_window_clients;
WINDOW_NAME OPTIMIZER_STATS SEGMENT_ADVISOR SQL_TUNE_ADVISOR
-------------------- ---------------- ---------------- ----------------
MONDAY_WINDOW DISABLED ENABLED ENABLED
TUESDAY_WINDOW ENABLED ENABLED ENABLED
WEDNESDAY_WINDOW ENABLED ENABLED ENABLED
THURSDAY_WINDOW ENABLED ENABLED ENABLED
FRIDAY_WINDOW ENABLED ENABLED ENABLED
SATURDAY_WINDOW ENABLED ENABLED ENABLED
SUNDAY_WINDOW ENABLED ENABLED ENABLED
系统会默认采集直方图信息，为了保持系统稳定性我们建议对已存在的直方图信息才收集，其余的手动收集。
查看系统默直方图收集范围，修改为对已经存在的直方图收集信息：
SQL> select dbms_stats.get_param('method_opt') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
修改成对已存在的直方图收集
SQL> exec dbms_stats.set_param('method_opt','for all columns size repeat');
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_param('method_opt') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE REPEAT
231
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
