How to disable and enable the oracle 10 Gb automatic statistics collection function

Starting from Oracle Database 10 Gb, Oracle creates a scheduled task named GATHER_STATS_JOB by default after the Database is created, which is used to automatically collect CBO statistics.

This automatic task is enabled by default between-on the workday and all day on the weekend. Call DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC to collect statistics. This process first detects the missing and obsolete objects of statistics. Then, determine the priority and start the statistics.

You can query the running status of the JOB as follows:

Select * from Dba_Scheduler_Jobs where JOB_NAME = 'gather _ STATS_JOB'
In fact, there is also an AUTO_SPACE_ADVISOR_JOB in the Job that runs at 10: 00:

SQL> select JOB_NAME, LAST_START_DATE from dba_scheduler_jobs;
JOB_NAME LAST_START_DATE

--------------------------------------------------------------------

AUTO_SPACE_ADVISOR_JOB 04-DEC-07 10.00.00.692269 PM + 08:00

GATHER_STATS_JOB 04-DEC-07 10.00.00.701152 PM + 08:00

FGR $ AUTOPURGE_JOB

PURGE_LOG 05-DEC-07 03.00.00.169059 AM PRC

However, this automation function has affected the normal operation of many systems. At am, most production systems are not idle.

Automatic analysis may lead to extremely serious competition for latches, which may lead to database Hang or Crash.

We recommend that you disable the automatic statistics collection function:

Exec DBMS_SCHEDULER.DISABLE ('gather _ STATS_JOB ');
Automation is always accompanied by serious risks!

To disable and enable the automatic collection function, you can use either of the following methods:

Method 1:

Exec dbms_scheduler.disable ('sys. GATHER_STATS_JOB ');

Exec dbms_scheduler.enable ('sys. GATHER_STATS_JOB ');

Method 2:

Alter system set "_ optimizer_autostats_job" = false scope = spfile;

Alter system set "_ optimizer_autostats_job" = true scope = spfile;

Pfile can directly modify the initialization parameter file and restart the database.

Ii. By default, AWR runs automatically every small part of the MMON and MMNL processes. To save space, the collected data is automatically cleared seven days later.

You can modify the snapshot frequency and retention time. To view the current settings, you can use the following statement:

Select snap_interval, retention

From dba_hist_wr_control;

SNAP_INTERVAL RETENTION

--------------------------------------
+ 00000 01:00:00. 0 + 00007 00:00:00. 0

These SQL statements show that snapshots are collected every hour and the collected data is retained for seven days. To modify the settings-for example, the snapshot interval is 20 minutes and the retention time is two days-you can issue the following command. The parameter is in minutes.

Begin

Dbms_workload_repository.modify_snapshot_settings (

Interval => 20,

Retention => 2*24*60

); End;

AWR uses several tables to store collected statistics. All Tables are stored in the SYS mode in a specific tablespace named SYSAUX, it is named in the format of WRM $ _ * And WRH $. The previous type stores metadata information (such as checked databases and collected snapshots), and the latter type stores actual collected statistics. (As you may have guessed, H represents "historical", and M represents "metadata )".) Several views with DBA_HIST _ prefix are built on these tables. These views can be used to compile your own performance diagnostic tools. The view name is directly related to the table. For example, the view DBA_HIST_SYSMETRIC_SUMMARY is built on the WRH $ _ SYSMETRIC_SUMMARY table.

Your processing plan is generally regular and generally based on your understanding of various events and your experience in handling them. Now imagine that the same thing is done by an engine that collects metrics and launches possible plans based on predefined logic. Isn't your work easier? This engine, which is now available in Oracle Database 10 Gb, is called the automatic Database diagnosis and monitoring program (ADDM ). To make a decision, ADDM uses data collected by AWR.

After each snapshot collection by AWR, ADDM is called to check the measurement and generate suggestions. Therefore, you actually have an automatic database administrator working 24 hours a day. It actively analyzes data and generates suggestions to free you from the situation, this allows you to focus on more strategic issues.

Snapshots are automatically collected by default, but you can also collect them as needed. All AWR functions are implemented in the package DBMS_WORKLOAD_REPOSITORY. To collect a snapshot, run the following command:

Execute dbms_workload_repository.create_snapshot it immediately collects a SNAPSHOT, which is recorded in the WRM $ _ SNAPSHOT table. The collected measurement is for the TYPICAL level. If you want to collect more detailed statistics, you can set the FLUSH_LEVEL parameter to ALL during the above process. Statistics are automatically deleted, but you can also manually delete them by calling drop_snapshot_range.

-