My Oracle Support Banner

FAQ: Automatic Statistics Collection (Doc ID 1233203.1)

Last updated on APRIL 26, 2021

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

Purpose

To provide a one stop shop for automated statistics collection (operation)  provided in 10g and 11g.

For general questions on statistics gathering see the Statistics FAQ:
  
<Document 1501712.1> FAQ: Statistics Gathering Frequently Asked Questions
 

Questions and Answers

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Questions and Answers
 What kind of statistics do the Automated tasks collect
 How do I revert to a previous set of statistics?
 Does the automatic statistic collection jobs populate CHAIN_CNT?
 11g+ Automatic Maintenance Tasks
 What is the name of the default stats gathering job on 11g?
 What are the default windows for the automated maintenance task to run?
 How do you change the default windows for the automated maintenance task to run?
 Whats new/changed in 11g with respect to automatic statistics collection?
 How to enable auto stats collection?
 How to disable the auto stats collection?
 What is the affect of setting STATISTICS_LEVEL= BASIC on automatic statistics collection?
 How can I check the status of the 'auto optimizer stats collection'?
 How can I check whether or not the database has the 'auto optimizer stats collection' job enabled to run during the next maintenance window?
 How can I see the history of the automatic stats job for each day?
 How to manually execute the Optimizer Statistics Auto Task?
 How do to check values of parameter( estimate percent, type of histograms etc) used by the job?
 How to set preference for the next maintenance job run to pick?
 How to I change the "STALE PERCENT", for example?
 Does the auto stats gathering job gather statistics on all schemas in the instance?
 What is the AUTOSTATS_TARGET of SET_GLOBAL_PREFS?
 How Are Statistics Gathered on Partitioned Tables during the Auto Gather Stats Job?

 May I Perform Partition Maintenance while the Auto Gather Stats Job is Running?
 How does auto optimizer stats collection prioritize which tables are analyzed first?
 How do you disable automatic statistics gathering for a specific schema?
 Is there any reason to gather full schema stats regularly in addition to the automatic stats gathering job?
 Can extended statistics be gathered automatically by the automatic stats gathering job?
 10g Collection: Via GATHER_STATS_JOB
 What is the name of the default stats gathering job on 10g?
 What are the default windows for the GATHER_STATS_JOB ?
 How do you disable the GATHER_STATS_JOB?
 How do you enable the GATHER_STATS_JOB?
 How do you Determine That the GATHER_STATS_JOB Completed
 How to Change the NEXT_RUN_DATE on the GATHER_STATS_JOB ?
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.