Turn off Oracle Automatic Table Analysis

The statistics for Oracle tables are very relevant to his execution plan.

Whether the execution plan is normal, is related to the speed of SQL execution

Let's start by explaining how to check if a database is turned on for automatic statistical analysis

1. View parameters: Statistics_level (see Data in the V$parameter table), the default is typical in 10g, only the Statistics_ The level parameter is set to all or typical to allow Oracle to recognize outdated statistics.

2, automatic Table analysis every night 10 start, that the description should be job timing in processing, automatic statistical analysis of the job is: Gather_stats_job.

Each object has a corresponding property, and the job's corresponding property is recorded in the Dba_scheduler_jobs dictionary.

In Dba_scheduler_jobs, this view is particularly interesting, enabled, which is the field, TRUE: Indicates enabled, FALSE: indicates off

3. Check the daily job execution status

By looking at dba_scheduler_job_run_details this view, you can see whether the JOB of automatic Table analysis has been performed gather_stats_job

How to turn on automatic statistics collection

1. Modifying parameter values

alter system set statistics_level=typical;

2. Start Gather_stats_job This Job

BEGIN

Dbms_scheduler. DISABLE (' gather_stats_job ');

END;

How to turn off statistical information

1. Modifying parameter values

alter system set STATISTICS_LEVEL=BASIC;

2. Close Gather_stats_job This Job

BEGIN

Dbms_scheduler. ENABLED (' gather_stats_job ');

END;

Monitor data changes, increase, delete, change the amount of data

Recorded in the Dba_tab_modifications view. We can query the Dba_tab_modifications view of the time may not be able to query the results, or the results of the query is not accurate, this time need to use dbms_stats. The flush_database_monitoring_info process flushes the in-memory information into the view

Turn off Oracle Automatic Table Analysis