概述

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方面的內容，感興趣的朋友可以關注下~