Oracle的计划窗口调整和自动任务

Oracle数据库自己会例行做一些定时任务,比如会自动进行统计信息收集等作业任务。如果统计信息收集的时间正好赶上业务的高峰期,那就有可能由此引发一系列性能故障。

那么,我们该如何查看这些数据库自动去做的任务执行计划和执行情况呢?

  • 1.计划窗口调整
  • 2.自动任务调整
  • 3.任务执行情况

1.计划窗口调整

首先,通过查询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),可以根据实际业务需求进行调整。

很多周六日业务量也很大的企业,一定要注意调整这个默认值。

2.自动任务调整

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;
/

3.任务执行情况

主要查询这个视图: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开头的命名。


更多Oracle相关信息见

Oracle

专题页面

http://www.linuxidc.com/topicnews.aspx?tid=12


本文永久更新链接地址

http://www.linuxidc.com/Linux/2017-12/149977.htm

Linux公社稿源:Linux公社 (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Oracle的计划窗口调整和自动任务

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录