Oracle 10G - ATO

[ericfjchen]

In Oracle 10g, the Automatic statistics collection maintains statistics up to date?
DBA doesn't have to gather schema statistics any more?

[Indira Aramandla]

Hi ericfjchen,

Prior to Oracle 10g , one had to enable DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement.

In Oracle 10g, the MONITORING and NOMONITORING keywords have been deprecated. If you do specify these keywords, they are ignored.

In 10g, table-monitoring feature is controlled by the STATISTICS_LEVEL parameter.

When STATISTICS_LEVEL is set to BASIC, monitoring is disabled on the table. When STATISTICS_LEVEL is set to TYPICAL, then monitoring is enabled.

By default STATISTICS_LEVEL is set to Typical, that is monitoring of tables is enabled. It is strongly recommended to set STATISTICS_LEVEL to Typical in 10g.

By setting this parameter to BASIC, you will be disabling most of the manageability features in 10g.

ie ASH (Active Session History)
AWR (Automatic Workload Repository)
ASSM (Automatic Shared Memory Management)
ADDM (Automatic Database Diagnostic Monitor)

Are all turned off!

Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the table since the last time statistics were gathered. Information about how many rows are affected is maintained in the SGA, until periodically (about every 15 minutes) SMON flush the data into the data dictionary.

This data dictionary information is made visible through the tables
DBA_TAB_MODIFICATIONS,ALL_TAB_MODIFICATIONS, or USER_TAB_MODIFICATIONS
views.

Oracle uses these views to identify tables with stale statistics. Whenever there is 10% change in data in a table, Oracle considers its statistics to be stale.

Since in 10g CBO is used, having up to date statistics is so important to generate good execution plans. Automatic statistics collection job using DBMS_STATS package depends on the monitoring data to determine when to collect statistics on objects with stale objects.

Indira A

[Yogeeraj_1]

hi,

with Oracle 10g, your statistics no longer depend on chance!

With Oracle 10g, the Rules-Based Optimizer (RBO) is finally desupported (not deprecated).

Because the CBO depends on accurate (or reasonably accurate) statistics to produce optimal execution paths, DBAs need to ensure that statistics are gathered regularly, creating yet another enforcement checklist. Prior to 10g, this process could be futile for various reasons. This difficulty gave rise to the theory that the CBO has a "mind of its own"â which implies behavior such as changing execution paths at will!

With 10g we no longer have to worry about these! Statistics can now be collected automatically. In Oracle9i, you could check if the data in a table had changed significantly by turning on the table monitoring option (ALTER TABLE ... MONITORING) and then checking the view DBA_TAB_MODIFICATIONS for those tables.

In 10g, the MONITORING statement is gone. Instead, statistics are collected automatically if the initialization parameter STATISTIC_LEVEL is set to TYPICAL or ALL. (The default value is TYPICAL, so automatic statistics gathering is enabled out of the box.) Oracle Database 10g has a predefined Scheduler job named GATHER_STATS_JOB, which is activated with the appropriate value of the STATISTIC_LEVEL parameter.

hope this helps too!

regards
Yogee

[Yogeeraj_1]

hi again,

You should also remember that the collection of statistics is fairly resource-intensive, so you may want to ensure it doesn't affect regular operation of the database.

In 10g, you can do so automatically: a special resource consumer group named AUTO_TASK_CONSUMER_GROUP is available predefined for automatically executed tasks such as gathering of statistics. This consumer group makes sure that the priority of these stats collection jobs is below that of the default consumer group, and hence that the risk of automatic tasks taking over the machine is reduced or eliminated.

What if you want to set the parameter STATISTIC_LEVEL to TYPICAL but don't want to make the statistics collection automatic? Simple. Just disable the Scheduler job by issuing the following:

BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;

And why would you want to do that?
Because:
a. Although most of the table's rows changed the distribution may not have changed, which is common in data warehouses. In this case you don't want to collect statistics again, but just want to reuse the old statistics.

b. You are using partition exchange to refresh a materialized view (MV) and don't want to collect statistics on the MV, as the statistics on the exchanged table will be imported as well. However, you could also exclude certain tables from the automatic stats collection job, eliminating the need to disable the entire job.

regards
Yogeeraj