Oracle 数据库自己会例行做一些定时任务,比如会自动进行统计信息收集等作业任务。如果统计信息收集的时间正好赶上业务的高峰期,那就有可能由此引发一系列性能故障。
那么,我们该如何查看这些数据库自动去做的任务执行计划和执行情况呢?
首先,通过查询 dba_scheduler_windows,可以看到有关窗口的定义详情。
SQL>
col window_name for a30
col REPEAT_INTERVAL for a60
set lines 180
select window_name,repeat_interval,duration,enabled from dba_scheduler_windows;
如果数据库是 10g 版本,结果如下:
- SQL> select window_name,repeat_interval,duration,enabled from dba_scheduler_windows;
- WINDOW_NAME REPEAT_INTERVAL DURATION ENABL
- ------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------- -----
- WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; b +000 08:00:00 TRUE
- ysecond=0
- WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00 TRUE
如果数据库是 11g 和 12c 版本,则是类似这样的结果:
- SQL> select window_name,repeat_interval,duration,enabled from dba_scheduler_windows;
- WINDOW_NAME REPEAT_INTERVAL DURATION ENABL
- ------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------- -----
- WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00 FALSE
- WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; b +000 08:00:00 FALSE
- ysecond=0
- SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE
- SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE
- FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
- THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
- WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
- TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
- MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
可以看到,在 11g 之后,oracle 将之前只区分工作日和休息日的分类,细化到一周中的每一天。而且默认值的收集时间也比 10g 有大幅度的减少。
修改窗口启动时间和 duration:
- --修改窗口启动时间
- EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW','repeat_interval','freq=daily;byday=MON;byhour=22;byminute=0;bysecond=0');
- EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW','repeat_interval','freq=daily;byday=TUE;byhour=22;byminute=0;bysecond=0');
- EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=22;byminute=0;bysecond=0');
- EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW','repeat_interval','freq=daily;byday=THU;byhour=22;byminute=0;bysecond=0');
- EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW','repeat_interval','freq=daily;byday=FRI;byhour=22;byminute=0;bysecond=0');
- EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW','repeat_interval','freq=daily;byday=SAT;byhour=6;byminute=0;bysecond=0');
- EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW','repeat_interval','freq=daily;byday=SUN;byhour=6;byminute=0;bysecond=0');
- --修改窗口duration
- exec dbms_scheduler.set_attribute('MONDAY_WINDOW','duration',numtodsinterval(240,'minute'));
- exec dbms_scheduler.set_attribute('TUESDAY_WINDOW','duration',numtodsinterval(240,'minute'));
- exec dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','duration',numtodsinterval(240,'minute'));
- exec dbms_scheduler.set_attribute('THURSDAY_WINDOW','duration',numtodsinterval(240,'minute'));
- exec dbms_scheduler.set_attribute('FRIDAY_WINDOW','duration',numtodsinterval(240,'minute'));
- exec dbms_scheduler.set_attribute('SATURDAY_WINDOW','duration',numtodsinterval(1200,'minute'));
- exec dbms_scheduler.set_attribute('SUNDAY_WINDOW','duration',numtodsinterval(1200,'minute'));
以上实际都是 Oracle 默认的值(周一到周五每晚 10 点开始收集统计信息,duration 是 4h;周六周日早上 6 点开始收集统计信息,duration 是 20h),可以根据实际业务需求进行调整。
很多周六日业务量也很大的企业,一定要注意调整这个默认值。
10g 版本没有这些自动维护任务,以下都是以 11g 以上版本为例,主要介绍如何关闭/启用自动任务(默认是关闭的。)。
查询: select client_name,status from dba_autotask_client;
- SQL> select client_name,status from dba_autotask_client;
- CLIENT_NAME STATUS
- ---------------------------------------------------------------- --------
- auto optimizer stats collection ENABLED
- auto space advisor ENABLED
- sql tuning advisor ENABLED
关闭自动维护任务:
- --关闭sql tuning advisor,避免消耗过多的资源
- BEGIN
- DBMS_AUTO_TASK_ADMIN.disable(
- client_name => 'sql tuning advisor',
- operation => NULL,
- window_name => NULL);
- END;
- /
- --关闭auto space advisor,避免消耗过多的IO,还有避免出现这个任务引起的library cache lock
- BEGIN
- DBMS_AUTO_TASK_ADMIN.disable(
- client_name => 'auto space advisor',
- operation => NULL,
- window_name => NULL);
- END;
- /
- --光闭自动统计信息收集,(慎用,除非有其他手工收集统计信息的完整方案,否则不建议关闭)
- BEGIN
- DBMS_AUTO_TASK_ADMIN.disable(
- client_name => 'auto optimizer stats collection',
- operation => NULL,
- window_name => NULL);
- END;
- /
启动自动维护任务:
- --启动sql tuning advisor
- BEGIN
- DBMS_AUTO_TASK_ADMIN.enable(
- client_name => 'sql tuning advisor',
- operation => NULL,
- window_name => NULL);
- END;
- /
- --启动auto space advisor
- BEGIN
- DBMS_AUTO_TASK_ADMIN.enable(
- client_name => 'auto space advisor',
- operation => NULL,
- window_name => NULL);
- END;
- /
- --启动自动统计信息收集
- BEGIN
- DBMS_AUTO_TASK_ADMIN.enable(
- client_name => 'auto optimizer stats collection',
- operation => NULL,
- window_name => NULL);
- END;
- /
主要查询这个视图:dba_scheduler_job_run_details
SQL>
col job_name for a30
col ACTUAL_START_DATE for a40
col RUN_DURATION for a30
set lines 180 pages 100
--10g
select owner, job_name, status, ACTUAL_START_DATE, RUN_DURATION from dba_scheduler_job_run_details where job_name = 'GATHER_STATS_JOB' order by 4;
10g 版本查询结果类似如下:
- OWNER JOB_NAME STATUS ACTUAL_START_DATE RUN_DURATION
- ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- ------------------------------
- SYS GATHER_STATS_JOB SUCCEEDED 31-OCT-17 10.00.02.512503 AM +08:00 +000 00:00:41
- SYS GATHER_STATS_JOB SUCCEEDED 01-NOV-17 10.00.03.102893 AM +08:00 +000 00:00:53
- SYS GATHER_STATS_JOB SUCCEEDED 02-NOV-17 10.00.02.822735 AM +08:00 +000 00:02:19
- SYS GATHER_STATS_JOB SUCCEEDED 03-NOV-17 10.00.03.635398 AM +08:00 +000 00:00:54
- SYS GATHER_STATS_JOB SUCCEEDED 04-NOV-17 10.00.04.819712 AM +08:00 +000 00:01:25
- SYS GATHER_STATS_JOB SUCCEEDED 04-NOV-17 06.00.03.443851 PM +08:00 +000 00:00:16
- SYS GATHER_STATS_JOB STOPPED 09-NOV-17 11.00.03.008706 AM +08:00 +005 01:30:12
- SYS GATHER_STATS_JOB SUCCEEDED 15-NOV-17 11.00.01.423370 AM +08:00 +000 00:01:39
- SYS GATHER_STATS_JOB SUCCEEDED 16-NOV-17 11.00.03.842124 AM +08:00 +000 00:00:43
- SYS GATHER_STATS_JOB SUCCEEDED 17-NOV-17 11.00.01.535534 AM +08:00 +000 00:00:43
- SYS GATHER_STATS_JOB SUCCEEDED 21-NOV-17 11.00.02.590796 AM +08:00 +000 00:01:04
- SYS GATHER_STATS_JOB SUCCEEDED 24-NOV-17 11.00.02.291902 AM +08:00 +000 00:00:44
- SYS GATHER_STATS_JOB SUCCEEDED 25-NOV-17 11.00.02.660842 AM +08:00 +000 00:01:04
- SYS GATHER_STATS_JOB SUCCEEDED 25-NOV-17 07.00.02.587985 PM +08:00 +000 00:00:40
- SYS GATHER_STATS_JOB SUCCEEDED 28-NOV-17 11.00.01.916662 AM +08:00 +000 00:01:08
- SYS GATHER_STATS_JOB SUCCEEDED 29-NOV-17 11.00.03.060642 AM +08:00 +000 00:00:38
- SYS GATHER_STATS_JOB SUCCEEDED 30-NOV-17 11.00.00.737144 AM +08:00 +000 00:01:45
--11g
select owner, job_name, status, ACTUAL_START_DATE, RUN_DURATION from dba_scheduler_job_run_details where job_name like 'ORA$AT_OS_OPT_S%' order by 4;
11g 版本查询结果类似如下:
- OWNER JOB_NAME STATUS ACTUAL_START_DATE RUN_DURATION
- ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- ------------------------------
- SYS ORA$AT_OS_OPT_SY_3926 SUCCEEDED 22-NOV-17 10.00.02.384206 PM EST5EDT +000 00:01:41
- SYS ORA$AT_OS_OPT_SY_3946 SUCCEEDED 23-NOV-17 10.00.02.078143 PM EST5EDT +000 00:01:54
- SYS ORA$AT_OS_OPT_SY_3966 SUCCEEDED 24-NOV-17 10.00.02.684644 PM EST5EDT +000 00:02:03
- SYS ORA$AT_OS_OPT_SY_3986 SUCCEEDED 25-NOV-17 06.00.02.592675 AM EST5EDT +000 00:01:54
- SYS ORA$AT_OS_OPT_SY_4006 SUCCEEDED 25-NOV-17 10.02.37.976591 AM EST5EDT +000 00:00:39
- SYS ORA$AT_OS_OPT_SY_4026 SUCCEEDED 25-NOV-17 02.02.55.191309 PM EST5EDT +000 00:00:36
- SYS ORA$AT_OS_OPT_SY_4046 SUCCEEDED 25-NOV-17 06.03.08.913991 PM EST5EDT +000 00:00:36
- SYS ORA$AT_OS_OPT_SY_4066 SUCCEEDED 25-NOV-17 10.03.22.624319 PM EST5EDT +000 00:01:12
- SYS ORA$AT_OS_OPT_SY_4086 SUCCEEDED 26-NOV-17 06.00.08.274082 AM EST5EDT +000 00:01:14
- SYS ORA$AT_OS_OPT_SY_4106 SUCCEEDED 26-NOV-17 10.04.06.172856 AM EST5EDT +000 00:00:46
- SYS ORA$AT_OS_OPT_SY_4126 SUCCEEDED 26-NOV-17 02.04.23.754379 PM EST5EDT +000 00:01:04
- SYS ORA$AT_OS_OPT_SY_4146 SUCCEEDED 26-NOV-17 06.04.40.989803 PM EST5EDT +000 00:00:54
- SYS ORA$AT_OS_OPT_SY_4166 SUCCEEDED 26-NOV-17 10.04.55.633700 PM EST5EDT +000 00:00:56
- SYS ORA$AT_OS_OPT_SY_4186 SUCCEEDED 27-NOV-17 10.00.08.203510 PM EST5EDT +000 00:02:25
- SYS ORA$AT_OS_OPT_SY_4206 SUCCEEDED 28-NOV-17 10.00.01.365122 PM EST5EDT +000 00:02:16
- SYS ORA$AT_OS_OPT_SY_4226 SUCCEEDED 29-NOV-17 11.05.57.084541 PM EST5EDT +000 00:01:58
可以看到,以统计信息收集的 JOB_NAME 为例,11g 版本之后不再是固定的一个名字,而是以 ORA$AT_OS_OPT_SY 开头的命名。
来源: http://www.linuxidc.com/Linux/2017-12/149977.htm