取消Oracle后台job，做一个干净的测试环境

最近在测试redo log的抓取，发现有不少后台写入动作，干扰了测试。比如经常看到如下语句：

insert into sysman.mgmt_system_performance_log(... ...) values(... ...) 

 

网上也有类似的东西，比如：10g Archive log files fill disk  http://www.orafaq.com/forum/t/44973/2/。建议设置将参数“statistics_level”改成“BASIC”，目前用的是“TYPICAL”。

 

下面是这个参数的官方说明：

STATISTICS_LEVEL

Property	Description
Parameter type	String
Syntax	STATISTICS_LEVEL = { ALL | TYPICAL | BASIC }
Default value	TYPICAL
Modifiable	ALTER SESSION, ALTER SYSTEM

STATISTICS_LEVEL specifies the level of collection for database and operating system statistics. The Oracle Database collects these statistics for a variety of purposes, including making self-management decisions.

The default setting of TYPICAL ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments.

When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.

Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including:

• Automatic Workload Repository (AWR) Snapshots

• Automatic Database Diagnostic Monitor (ADDM)

• All server-generated alerts

• Automatic SGA Memory Management

• Automatic optimizer statistics collection

• Object level statistics

• End to End Application Tracing (V$CLIENT_STATS)

• Database time distribution statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)

• Service level statistics

• Buffer cache advisory

• MTTR advisory

• Shared pool sizing advisory

• Segment level statistics

• PGA Target advisory

• Timed statistics

• Monitoring of statistics

Note: Oracle strongly recommends that you do not disable these important features and functionality.

When the STATISTICS_LEVEL parameter is modified by ALTER SYSTEM, all advisories or statistics are dynamically turned on or off, depending on the new value of STATISTICS_LEVEL. When modified by ALTER SESSION, the following advisories or statistics are turned on or off in the local session only. Their system-wide state is not changed:

• Timed statistics

• Timed OS statistics

• Plan execution statistics

The V$STATISTICS_LEVEL view displays information about the status of the statistics or advisories controlled by the STATISTICS_LEVEL parameter. See "V$STATISTICS_LEVEL".

测试一下先。改了之后要重启。结果还引发了不能启动的问题，耽误了一些时间。

 

过一段时间，再重新观察一下。发现还在不停的写入： insert into sysman.mgmt_system_performance_log

 

看了一下schedule，然后disable，也不行。

exec dbms_scheduler.disable('GATHER_STATS_JOB');
exec dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB');
exec dbms_scheduler.disable('PURGE_LOG');
exec dbms_scheduler.disable('EXFSYS.RLM$SCHDNEGACTION');
exec dbms_scheduler.disable('EXFSYS.RLM$EVTCLEANUP');

SQL> select owner, job_name, enabled, state from DBA_SCHEDULER_JOBS;

OWNER                          JOB_NAME                       ENABL STATE
------------------------------ ------------------------------ ----- ---------------
SYS                            AUTO_SPACE_ADVISOR_JOB         FALSE DISABLED
SYS                            GATHER_STATS_JOB               FALSE DISABLED
SYS                            FGR$AUTOPURGE_JOB              FALSE DISABLED
SYS                            PURGE_LOG                      FALSE DISABLED
EXFSYS                         RLM$SCHDNEGACTION              FALSE DISABLED
EXFSYS                         RLM$EVTCLEANUP                 FALSE DISABLED

6 rows selected.

SQL>

 

只好速度放快点，几个DML发出后，立马切换，保存。总算不多不少，刚刚好。

 

可是事情并没有搞定啊。先放一放，主要是分析log。回头有空，再研究一下后台的job。