FAQ: Statistics Gathering Frequently Asked Questions
(Doc ID 1501712.1)
Last updated on MAY 09, 2024
Applies to:
Oracle Cloud Infrastructure - Database Service - Version N/A and laterOracle 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 Technical Briefs 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? |
| 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 |