My Oracle Support Banner

FAQ: Statistics Gathering Frequently Asked Questions (Doc ID 1501712.1)

Last updated on JUNE 22, 2021

Applies to:

Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database - Personal Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

Purpose

This document covers many of the frequently asked questions about gathering statistics.

Ask Questions, Get Help, And Share Your Experiences With This Article

Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?

Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.
Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Tuning.

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
 Ask Questions, Get Help, And Share Your Experiences With This Article
Questions and Answers
 Automatic Statistics Collection
 General Gathering Guidelines
 Is there a central point document for Statistics Collection information
 What is the best way to gather stats in 11g?
 Are there any Whitepapers dealing with statistics?
 What can I do if  data is extremely changeable?
 Are there any times when statistics are gathered as a side effect of a SQL statement?
 How do I determine which database objects are missing statistics? 
 Is there a way to decide what frequency to gather statistics by analyzing changes in data for a particular table
 How can you tell whether statistics gathering should be run more or less frequently?
 Can you tell whether statistics are "too old"?
 What does "Data changing a lot" mean? Is it a higher Number of insert and updates or The distinct values/null/not null values in individual columns are changing frequently ?
 What could be regarded as frequent gathering?
 How can I test new statistics without disrupting other sessions' performance or replacing the existing statistics?
 Gathering Sample Size
 What is the best sample size to use?
 What is the default auto_sample_size?
 What would be good sampling size, 5 or 10 percent?
 What is a good starting value for sample size on 10g?
 Would you count 10% as a small sample size or less such as 2%?
 Does the recommendation to not use AUTO_SAMPLE_SIZE on 10g also apply on a 11g database with the optimizer_features_enable initialisation parameter set to 10g?
 How can I tell the difference in accuracy between auto_sample or 10% or 60%?
 Parallel Statistics Gathering
 Concurrent Statistics Gathering
 Partitions and Incremental Statistics
 What do you recommend regarding statistics for new partitions?
 Do You recommend gathering statistics only on partition level and let Oracle use aggregated stats for global one ?
 Can you gather statistics just for a range of partitions?
 What are the best practices for running incremental stats on partitioned data? What's the recommended frequency ? Monthly or weekly?
 Are Incremental stats gathered automatically after a change in the data?
 Do we need to include cascade=true with incremental Statistics?
 How do incremental stats work on partitions without data at the month end, but then data is added during 1st week . Do we need to run incremental stats again?
 How does Oracle handle NDVs in partition stats vs global stats? For example, if your query accesses only a couple of partitions from hundreds and the NDV in those 2 partitions is radically different to the global NDV, does Oracle change access paths to reflect this?
 Where can I find all the different types of optimizer statistics for a given, (sub-)partitioned table?
 Column Histograms
 Does 11g collect histograms when gathering the default statistics?
 Does statistics collection read each column in turn to gather column statistics?
 If I collect column statistics on some 'new' columns using a specific METHOD_OPT clause, will my existing column statistics be deleted?
 Are there any tools that I can use to help decide whether a column has skewed data or conforms to a normal distribution?
 Do we need histograms for non-indexed columns ?
 Is there a size recommendation for gathering histograms?
 How can I identify whether a column has a histogram?
 If my Number of Distinct Values (NDV) is higher than 254, does that mean a histogram is poor?
 If histograms can be beneficial and harmful at the same time, what are scenarios where using histogram will be a good thing or a bad thing?
 Are Indexed Column statistics a better options than column stats on all columns?
 System Statistics
 What are system statistics?
 When should I gather system statistics?
 What is the best way to gather system stats ?
 What gives best load information for system statistics? Should we run for single business day, multiple days, full work week?
 How often should we gather system stats?
 After an upgrade, how important is it to get system stats immediately on new platform?
 Has 11gR2 introduced any automated mechanism to gather system statistics?
 Dictionary Statistics
 What is the difference between System Statistics and Dictionary Statistics
 Do You recommend gathering sys schema statistics via Auto_task in 10gr2 ?
 How often should you gather dictionary statistics?
 Should I use DBMS_STATS.GATHER_SCHEMA_STATS('SYS') to gather dictionary statistics?
 Whats the difference between GATHER_DICTIONARY_STATS and GATHER_SCHEMA_STATS('SYS')
 How often do fixed table stats need to be gathered?
 Upgrades
 We are migrating from an earlier version to 11g Release 2.  Are there any recommendations for how to handle statistics in that case?
 Miscellaneous
 Can I copy statistics between different databases?
 Can I compare statistics from different sources?
 Can I copy statistics between different RAC Instances with different numbers of nodes?
 How can I see what statistics have been gathered?
 Is 11gR2 statistics gathering stats faster than 10gR2?
 Is there any other information available about statistics?
 If table statistics are locked, if the environment is changed, should we export stats and import again to be sure we have the original statistics in place?
 When we refresh our TEST environment do we have to import Production stats for optimal performance?
 What is default value for "no_invalidate" option for DBMS_STATS.GATHER_SCHEMA_STATS?
 Is there any way to gather statistics for specific portion of the table?
 We have found that gathering statistics on sys.syn$ has helped when all_synonmys or user_synonyms was slow
 How can you tell which columns are candidates for grouping?
 I see difference between exadata statistics and normal database? Any plans to include the storage level stats/plans also for exadata?
 Application Specific Recommendations
 What is the difference between DBMS_STATS and FND_STATS.
 We are using Peoplesoft and one of the recommendations said to disable histograms by using columns size 1. Does that sound reasonable?
 Is Incremental mode available with specific a applications? For example EBS using FND_STATS?
 Why is it recommended to remove statistics on empty tables for solutions like Siebel?
 Discuss Statistics Gathering!
References

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