在昨天晚上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
但是不会当时生效,需要过一段时间,所以需要提前禁用。