My Oracle Support Banner

* How to Gather Optimizer Statistics on 11g (Doc ID 749227.1)

Last updated on DECEMBER 14, 2020

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 Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

Goal

This Document outline the recommended method to gather a standard set of optimizer statistics for use by the Cost Based Optimizer under Oracle 11g. For other versions see:

<Document 1226841.1> How To: Gather Statistics for the Cost Based Optimizer

 

NOTE: There is a presentation entitled "Best Practices for Managing Optimizer Statistics" available in:

<Document 1380043.1> Selected Performance Related Seminars from Oracle OpenWorld

Which also addresses and provides specific tips on this subject.

Solution

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
Goal
Solution
 Basic Statistics Recommendations
 Quick Recreate Recommendation
 Important Notes Regarding the Gathering of Optimizer Statistics
 Gathering Object statistics
 Use a large enough sample size
 Gather statistics on all objects
 Collect Column Statistics/Histograms for Skewed Data Distributions
 Gather Global Statistics for Partitioned Objects
 Gather System Statistics
 Gather Dictionary and 'Fixed' Object Statistics
 Upgrading to 11g from an earlier version
 Default Settings
 Sample Statistic Gathering Commands
 Gathering statistics an individual table
 Gathering statistics for all objects in a schema
 Gathering statistics for all objects in the database:
References

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