修改Oracle10g、11g系统自动统计Job的运行时间属性
更新时间:2024-04-19 03:57:01 阅读量: 综合文库 文档下载
修改Oracle10g、11g系统自动统计Job的运行时间属性
Oracle 10g和11g,都有自动收集数据库统计信息的任务(10G和11G的JOB名不同),但是任务的执行时间可能与我们期望的不同,比如:周末两个全天都要运行,会影响系统性能,下面的方式可修改: 一.修改10g自动统计的时间属性 --GATHER_STATS_JOB
--查看SCHEDULER_JOBS
select job_name from dba_scheduler_running_jobs;
SELECT job_name , SCHEDULE_NAME FROM DBA_SCHEDULER_JOBS; select * from dba_scheduler_wingroup_members where window_group_name='MAINTENANCE_WINDOW_GROUP'; /* WINDOW_GROUP_NAME WINDOW_NAME
1 MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW 2 MAINTENANCE_WINDOW_GROUP WEEKEND_WINDOW */
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='MAINTENANCE_WINDOW_GROUP'; /*
WINDOW_NAME REPEAT_INTERVAL DURATION
1 WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 +000 08:00:00
2 WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00 */
--下面修改配置(使用sys用户登录oracle执行) sqlplus sys/xxx@xxxdb as sysdba set linesize 120
--修改WEEKEND_WINDOW的配置 (改成和WEEKNIGHT_WINDOW相同,即周一~周五,每日的22:00向后8小时,至次日凌晨6点) begin
dbms_scheduler.set_attribute('WEEKEND_WINDOW','REPEAT_INTERVAL','freq=daily;byday=SAT,SUN;byhour=22;byminute=0;bysecond=0');
dbms_scheduler.set_attribute('WEEKEND_WINDOW','DURATION','+000 08:00:00');
end; /
--查看修改结果:
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='MAINTENANCE_WINDOW_GROUP'; /*
WINDOW_NAME REPEAT_INTERVAL DURATION
1 WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 +000 08:00:00
2 WEEKEND_WINDOW freq=daily;byday=SAT,SUN;byhour=22;byminute=0;bysecond=0 +000 08:00:00 */
--若要还原成以前默认设置,可执行如下 --周末两天都是全天: begin
dbms_scheduler.set_attribute('WEEKEND_WINDOW','REPEAT_INTERVAL','freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0');
dbms_scheduler.set_attribute('WEEKEND_WINDOW','DURATION','+002 00:00:00'); end; /
二.修改11g自动统计时间属性 --查看SCHEDULER_JOBS
select job_name from dba_scheduler_running_jobs; SELECT * FROM DBA_SCHEDULER_JOBS;
select * from dba_scheduler_wingroup_members where window_group_name='MAINTENANCE_WINDOW_GROUP'; /*WINDOW_GROUP_NAME WINDOW_NAME
MAINTENANCE_WINDOW_GROUP MONDAY_WINDOW
MAINTENANCE_WINDOW_GROUP TUESDAY_WINDOW MAINTENANCE_WINDOW_GROUP WEDNESDAY_WINDOW MAINTENANCE_WINDOW_GROUP THURSDAY_WINDOW MAINTENANCE_WINDOW_GROUP FRIDAY_WINDOW MAINTENANCE_WINDOW_GROUP SATURDAY_WINDOW MAINTENANCE_WINDOW_GROUP SUNDAY_WINDOW */
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 DURATION
1 MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
2 TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
3 WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
4 THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
5 FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
6 SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
7 SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 */
--下面修改配置(使用sys用户登录oracle执行) sqlplus sys/xxx@xxxdb as sysdba
--修改SATURDAY_WINDOW、SUNDAY_WINDOW的配置 (改成和平常相同,即每日都是22:00向后4小时,至次日凌晨2点)
--下面的方式也可修改周六、周日的时间Window begin
sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'repeat_interval', value =>
'Freq=daily;ByDay=SAT;ByHour=22;ByMinute=0;BySecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'duration', value => '0 04:00:00'); end; /
begin
sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval', value =>
'Freq=daily;ByDay=SUN;ByHour=22;ByMinute=0;BySecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');
end; /
--查看修改结果:
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='MAINTENANCE_WINDOW_GROUP';
/* WINDOW_NAME REPEAT_INTERVAL DURATION
1 MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
2 TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
3 WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
4 THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
5 FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
6 SATURDAY_WINDOW Freq=daily;ByDay=SAT;ByHour=22;ByMinute=0;BySecond=0 +000 04:00:00
7 SUNDAY_WINDOW Freq=daily;ByDay=SUN;ByHour=22;ByMinute=0;BySecond=0 +000 04:00:00 */
三.相关视图
1.SCHEDULER_JOBS
select job_name from dba_scheduler_running_jobs; SELECT * FROM DBA_SCHEDULER_JOBS;
2.有关自动数据库维护任务的信息的视图: select * from DBA_AUTOTASK_CLIENT_JOB; select * from DBA_AUTOTASK_CLIENT;
select * from DBA_AUTOTASK_JOB_HISTORY; select * from DBA_AUTOTASK_WINDOW_CLIENTS; select * from DBA_AUTOTASK_CLIENT_HISTORY;
正在阅读:
修改Oracle10g、11g系统自动统计Job的运行时间属性04-19
变质岩基本特征和分类06-03
大学生职业生涯规划书简短(最新3篇)04-02
重庆市事业单位公开招聘人员实施办法10-23
康宇达医疗器械公司安全管理制度01-20
西安某SHOPPINGMALL项目策划05-10
医疗器械质量管理、专业技术及售后服务培训试题及答案07-17
《中国海洋大学教师岗位设置管理与聘任实施细则》(海大人字〔2007〕122号)01-18
梧桐意象解读05-28
材力试卷212-09
- 小学生造句大全
- 增压泵投资项目可行性研究报告(模板)
- 高中语文人教版粤教版必修1-5全部文言文知识点归纳
- 两学一做专题民主生活会组织生活会批评与自我批评环节个人发言提
- 管理处环境保洁工作操作标准作业指导书
- 2012六一儿童节活动议程 - 图文
- 移树申请报告
- 《贵州省市政工程计价定额》2016定额说明及计算规则
- 计算机长期没有向WSUS报告状态
- 汉语拼音教学策略研究
- 发展西部领先的航空货运枢纽
- 司法所上半年工作总结4篇
- 如何提高银行服务水平
- 发电厂各级人员岗位职责
- 丰田汽车的外部环境分析
- 2017—2018年最新冀教版四年级数学下册《混合运算》教案精品优质
- 中建八局样板策划 - 图文
- 戚安邦《项目管理学》电子书
- 2015年高级项目经理笔记
- 弯桥的设计要点
- Oracle10g
- 属性
- 运行
- 修改
- 统计
- 自动
- 时间
- 系统
- 11g
- Job