最近在测试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。