概述

Oracle数据库自己会例行做一些定时任务，比如会自动进行统计信息收集等作业任务。如果统计信息收集的时间正好赶上业务的高峰期，那就有可能由此引发一系列性能故障。今天主要介绍下Oracle数据库的自动任务及常用的三个脚本，仅供参考。

Oracle自动任务

Automated maintenance tasks are tasks that are started automatically at regular intervals to perform maintenance operations on the database. An example is a task that gathers statistics on schema objects for the query optimizer. Automated maintenance tasks run in maintenance windows, which are predefined time intervals that are intended to occur during a period of low system load. You can customize maintenance windows based on the resource usage patterns of your database, or disable certain default windows from running. You can also create your own maintenance windows.

oracle 数据库预先定义了自动任务的三个功能：

1. Automatic Optimizer Statistics Collection

对没有统计信息或者过时统计信息的数据库对象进行收集统计信息，用来提高 sql 执行效率

2. Automatic Segment Advisor

建议回收哪些段空间可以回收

3. Automatic SQL Tuning Advisor

检测高负载的 sql 语句性能，并建议如何进行调优

脚本：autotask_change_window_schedules.sql

实现：Use this script to alter the autotask window schedules.

DECLARE
 TYPE t_window_tab IS TABLE OF VARCHAR2(30)
 INDEX BY BINARY_INTEGER;
 
 l_tab t_window_tab;
 l_repeat_interval VARCHAR2(100);
 l_duration NUMBER;
BEGIN
​
 -- Windows of interest.
 l_tab(1) := 'SYS.MONDAY_WINDOW';
 l_tab(2) := 'SYS.TUESDAY_WINDOW';
 l_tab(3) := 'SYS.WEDNESDAY_WINDOW';
 l_tab(4) := 'SYS.THURSDAY_WINDOW';
 l_tab(5) := 'SYS.FRIDAY_WINDOW';
 --l_tab(6) := 'SYS.SATURDAY_WINDOW';
 --l_tab(7) := 'SYS.SUNDAY_WINDOW';
​
 -- Adjust as required.
 l_repeat_interval := 'freq=weekly; byday=mon; byhour=12; byminute=0; bysecond=0;';
 l_duration := 120; -- minutes
 
 FOR i IN l_tab.FIRST .. l_tab.LAST LOOP
 DBMS_SCHEDULER.disable(name => l_tab(i), force => TRUE);
​
 DBMS_SCHEDULER.set_attribute(
 name => l_tab(i),
 attribute => 'REPEAT_INTERVAL',
 value => l_repeat_interval);
​
 DBMS_SCHEDULER.set_attribute(
 name => l_tab(i),
 attribute => 'DURATION',
 value => numtodsinterval(l_duration, 'minute'));
​
 DBMS_SCHEDULER.enable(name => l_tab(i));
 END LOOP;
END;
/

脚本：autotask_job_history.sql

实现：Displays the job history for the automatic maintenance tasks.

COLUMN client_name FORMAT A40
COLUMN window_name FORMAT A20
COLUMN job_start_time FORMAT A40
COLUMN job_duration FORMAT A20
COLUMN job_status FORMAT A10
​
SELECT client_name,
 window_name,
 job_start_time,
 job_duration,
 job_status,
 job_error
FROM dba_autotask_job_history
ORDER BY job_start_time;

脚本：autotask_schedule.sql

实现：Displays the window schedule the automatic maintenance tasks.

COLUMN window_name FORMAT A20
COLUMN start_time FORMAT A40
COLUMN duration FORMAT A20
SELECT * FROM dba_autotask_schedule ORDER BY start_time;

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~