在昨天晚上10點開始，資料庫的效能開始下降，出現了一些j00開頭的程式。
而且持續了比較長的時間，簡單分析了一下，對應的程式執行的sql語句如下。

####### Process Information from OS level as below ########
oraccbs1 16184 1 23 22:00 ? 00:03:09 ora_j000_PETCUS1
oraccbs1 20829 12754 0 22:14 pts/12 00:00:00 ksh showpid.sh 16184
##############################################

SID SERIAL# USERNAME OSUSER MACHINE PROCESS TERMINAL TYPE LOGIN_TIME
---------- ---------- --------------- --------------- -------------------- --------------- --------------- ---------- -------------------
1139 1071 SYS oraccbs1 xxxxxx 16184 UNKNOWN USER 2014-08-14 22:00:43

.

SQL_ID                         SQL_TEXT
------------------------------ ------------------------------------------------------------
6fwshkjydz9ph                  /* SQL Analyze(1) */ select /*+ full(t)    no_parallel(t) n
                               o_parallel_index(t) dbms_stats cursor_sharing_exact use_weak
                               _name_resl dynamic_sampling(0) no_monitoring no_substrb_pad
                                */to_char(count("PERIOD_KEY")),to_char(substrb(dump(min("PE
                               RIOD_KEY"),16,0,32),1,120)),to_char(substrb(dump(max("PERIOD
                               _KEY"),16,0,32),1,120)),to_char(count("PARTITION_ID")),to_ch
                               ar(substrb(dump(min("PARTITION_ID"),16,0,32),1,120)),to_char
                               (substrb(dump(max("PARTITION_ID"),16,0,32),1,120)),to_char(c
                               ount("DEBIT_ID")),to_char(substrb(dump(min("DEBIT_ID"),16,0,
                               32),1,120)),to_char(substrb(dump(max("DEBIT_ID"),16,0,32),1,
                               120)),to_char(count("SYS_CREATION_DATE")),to_char(substrb(du
                               mp(min("SYS_CREATION_DATE"),16,0,32),1,120)),to_char(substrb
                               (dump(max("SYS_CREATION_DATE"),16,0,32),1,120)),to_char(coun
                               t("SYS_UPDATE_DATE")),to_char(substrb(dump(min("SYS_UPDATE_D
                               ATE"),16,0,32),1,120)),to_char(substrb(dump(max("SYS_UPDATE_
                               DATE"),16,0,32),1,120)),to_char(count("OPERATOR_ID")),to_cha
                               r(substrb(dump(min("OPERATOR_ID"),16,0,3

PREV_SQL_ID SQL_TEXT
------------------------------ ------------------------------------------------------------
fhzj09a7fnrnb SELECT DBTIMEZONE, LENGTH(DBTIMEZONE) FROM SYS.DUAL

看起來是在做一些後臺的維護工作。在11g中引入了幾個schedule job，簡單查詢，結果如下。可以看到在每天都設定了對應的window。
在10點開始做一些工作。以下是禁用以後的狀態。

SQL> execute DBMS_AUTO_TASK_ADMIN.DISABLE;

PL/SQL procedure successfully completed.

SQL> select * from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME                    WINDOW_NEXT_TIME                                                            WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
------------------------------ --------------------------------------------------------------------------- ----- -------- -------- -------- -------- --------
MONDAY_WINDOW                  18-AUG-14 10.00.00.000000 PM ASIA/PHNOM_PENH                                FALSE DISABLED ENABLED ENABLED ENABLED DISABLED
TUESDAY_WINDOW                 19-AUG-14 10.00.00.000000 PM ASIA/PHNOM_PENH                                FALSE DISABLED ENABLED ENABLED ENABLED DISABLED
WEDNESDAY_WINDOW               20-AUG-14 10.00.00.000000 PM ASIA/PHNOM_PENH                                FALSE DISABLED ENABLED ENABLED ENABLED DISABLED
THURSDAY_WINDOW                14-AUG-14 10.00.00.000000 PM ASIA/PHNOM_PENH                                TRUE DISABLED ENABLED ENABLED ENABLED DISABLED
FRIDAY_WINDOW                  15-AUG-14 10.00.00.000000 PM ASIA/PHNOM_PENH                                FALSE DISABLED ENABLED ENABLED ENABLED DISABLED
SATURDAY_WINDOW                16-AUG-14 06.00.00.000000 AM ASIA/PHNOM_PENH                                FALSE DISABLED ENABLED ENABLED ENABLED DISABLED
SUNDAY_WINDOW                  17-AUG-14 06.00.00.000000 AM ASIA/PHNOM_PENH                                FALSE DISABLED ENABLED ENABLED ENABLED DISABLED

7 rows selected.

其實3個主要的後臺job可以選擇關掉。

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

使用如下的pl/sql來禁用。

begin
DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
end;
/

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 => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
end;
/

禁用以後，狀態如下：

SQL> select client_name,status from DBA_AUTOTASK_CLIENT;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  DISABLED
auto space advisor                                               DISABLED
sql tuning advisor                                               DISABLED

但是不會當時生效，需要過一段時間，所以需要提前禁用。

關於oracle後臺啟用的schedule job

相關文章