The Cost Based Optimizer (CBO) uses statistics to determine the execution plan for a particular query. Differences in statistics can cause the CBO to generate different execution plans, which in turn can lead to differences in performance of the same query.
How to Gather Optimizer Statistics
Document 1226841.1 How To: Gather Statistics for the Cost Based Optimizer
Document 1445302.1 How to Gather Optimizer Statistics on 12c
Document 749227.1 How to Gather Optimizer Statistics on 11g
Document 605439.1 How to Gather Optimizer Statistics on 10g
Document 388474.1 How to Gather Optimizer Statistics on 9iTroubleshooting Issues Following Statistics Gathering
Document 1613147.1 What to do when Statistics Gathering does not Produce the Plans that you Expect
Document 452011.1 Restoring Table StatisticsBest Practices
Document 44961.1 Statistics Gathering: Frequency and Strategy Guidelines
Document 465787.1 How to: Manage CBO Statistics During an Upgrade to 10g or 11g
There is a Webcast recording entitled "CBO Statistics on 11g: How to reduce poorly performing Execution Plans" which you can find here:Document 1456176.1 Oracle Database Advisor Webcast Schedule and Archive recordingsAutomated Statistics Collection
Document 1233203.1 FAQ: Automatic Statistics Collection Job (10g and 11g)
Document 377152.1 Best Practices for automatic statistics collection
Document 731935.1 How To Manually Execute the Optimizer Statistics Auto Task in Oracle 11g
Document 743507.1 How to Benefit from Automatic Maintenance Tasks Following the Removal of the GATHER_STATS_JOB in 11g?
Document 368475.1 How To Extend Maintenance Windows For GATHER_STATS_JOB for More Than 8 Hours?
Document 377143.1 How to check what automatic statistics collection is scheduled on 10g
Automatic Detection of Un-Gathered/Stale Objects
Document 228186.1 Differences between GATHER STALE and GATHER AUTO
Document 1073120.1 DBMS_STATS 'gather auto' and 'gather stale' and column histograms
Document 252597.1 Relation between Table Monitoring and STATISTICS_LEVEL parameter in 10g
Document 295249.1 Automated Object Statistics in 10g and Above
Document 390737.1 Modifying the 10% Staleness Threshold for DBMS_STATS Automatic Statistics GatheringBacking up and Restoring Statistics
Document 464939.1 Statistics Best Practices: How to Backup and Restore Statistics
Document 452011.1 Restoring Table StatisticsTransferring Statistics to a different database
Document 333742.1 How to transfer statistics from one database to anotherDictionary and Fixed Table Statistics
It is important to gather accurate statistics on the data dictionary and fixed tables. For information about how to do this, see the following:Document 457926.1 How to gather statistics on SYS objects and fixed_objects?
Document 798257.1 GATHER_FIXED_OBJECTS_STATS ConsiderationsSystem Statistics
System statistics allow you to record information about the actual performance of the system you are running on. This can be used by the optimizer to adjust its calculations to reflect the individual nuances of your system. However, it is not mandatory to gather system statistics. This is dependent upon the environment. Since the performance of IO etc may vary with load, if you have variable load on the system at different times then you may want to gather multiple sets of system statistics to reflect those workloads accurately. For more information see:Document 149560.1 How to Collect and Display System Statistics (CPU and IO) for CBO use
Document 470316.1 Using Actual System Statistics (Collected CPU and IO information)
Document 153761.1 System Statistics: Scaling the System to Improve CBO optimizer
Document 427939.1 System Statistics: How to gather system stats for a particular batch of workStatistics Frequently Asked Questions
The following documents outline many of the frequently asked questions associated with gathering statistics:Document 1501712.1 FAQ: Statistics Gathering Frequently Asked Questions
Document 1233203.1 FAQ: Automatic Statistics Collection Job (10g and 11g)Checking for the presence of Statistics
Missing statistics is a common cause of performance issues. the following can help identify where potential issues may exist:Document 1366133.1 SQL Tuning Health-Check Script
Document 957993.1 Script to show objects that are missing statisticsPending Statistics
From 11g Release 2, it is possible to defer the implementation of statistics until later date instead of immediately applying them. This provides a number of advantages, not least the ability for users to check the impact of statistics before they are actually changed. For more details see:Document 1456776.1 How to Use Pending Statistics
There is a webcast on this topic entitled "How to prevent impact of statistics gathering on application queries in 11G" that can be found in the 2012 archives of the Oracle Database section of the following document:Document 740966.1 Advisor Webcasts: Current Schedule and Archived recordingsMiscellaneous Topics
Document 336267.1 Parameter : OPTIMIZER_DYNAMIC_SAMPLING
Document 406475.1 What are the Default Parameters when Gathering Table Statistics on 9i and 10g?
Document 725845.1 How to Change Default Parameters for Gathering Statistics
Document 1445372.1 Histograms: An Overview (10g and Above)
Document 72539.1 Interpreting Histogram Information
Document 390249.1 How To Quickly Add/Remove Column Statistics (Histograms) For A Column
Document 823934.1 Statistics Not collected on Table After Executing GATHER_STATS_JOB
Document 236935.1 Global statistics - An Explanation
Document 785110.1 Bad Plan Still Persistent even after Gathering New Fresh Statistics
Document 814337.1 Why The Running Query Does Not Use The New Statistics?
Document 557661.1 Rolling Cursor Invalidations with DBMS_STATS in Oracle10
REFERENCES
NOTE:1073120.1 - DBMS_STATS 'gather auto' and 'gather stale' and column histogramsNOTE:814337.1 - Why The Running Query Does Not Use The New Statistics?
NOTE:1456776.1 - How to Use Pending Statistics
NOTE:465787.1 - How to: Manage CBO Statistics During an Upgrade from 10g or 9i into 11g/12c
NOTE:388474.1 - * How to Gather Optimizer Statistics on 9i
NOTE:377143.1 - How to Check what Automatic Statistics Collections are Scheduled on Oracle 10g
NOTE:228186.1 - Differences between GATHER STALE and GATHER AUTO
NOTE:377152.1 - Best Practices for Automatic Statistics Collection
NOTE:44961.1 - Statistics Gathering: Frequency and Strategy Guidelines
NOTE:457926.1 - How to Gather Statistics on Objects Owned by the 'SYS' User and 'Fixed' Objects
NOTE:464939.1 - Statistics Best Practices: How to Backup and Restore Statistics
NOTE:236935.1 - Global statistics - An Explanation
NOTE:1233203.1 - FAQ: Automatic Statistics Collection
NOTE:1366133.1 - SQL Tuning Health-Check Script (SQLHC)
NOTE:957993.1 - Script to Show Objects That are Missing Statistics
NOTE:390737.1 - Modifying the 10% Staleness Threshold for DBMS_STATS Automatic Statistics Gathering
NOTE:740966.1 - Advisor Webcasts: Current Schedule and Archived Recordings
NOTE:427939.1 - System Statistics: How to gather system stats for a particular batch of work
NOTE:743507.1 - How to Benefit from Automatic Maintenance Tasks Following the Removal of the GATHER_STATS_JOB in 11g and Onward?
NOTE:749227.1 - * How to Gather Optimizer Statistics on 11g
NOTE:557661.1 - Rolling Cursor Invalidations with DBMS_STATS.AUTO_INVALIDATE
NOTE:153761.1 - System Statistics: Scaling the System to Improve CBO optimizer
NOTE:731935.1 - How To Manually Execute the Optimizer Statistics Auto Task in Oracle11g or Later
NOTE:452011.1 - How to Restore Oracle Optimizer Statistics
NOTE:470316.1 - Using Actual System Statistics (Collected CPU and IO information)
NOTE:406475.1 - What are the Default Parameters when Gathering Table Statistics on 9i and 10g?
NOTE:390249.1 - How To Quickly Add/Remove Column Statistics (Histograms) For A Column
NOTE:149560.1 - How to Collect and Display System Statistics (CPU and IO) for CBO use
NOTE:295249.1 - Automated Object Statistics in 10g and Above
NOTE:336267.1 - Optimizer Dynamic Statistics (OPTIMIZER_DYNAMIC_SAMPLING)
NOTE:605439.1 - * How to Gather Optimizer Statistics on 10g
NOTE:72539.1 - Interpreting Histogram Information
NOTE:725845.1 - How to Change Default Parameters for Gathering Statistics in 10g
NOTE:368475.1 - How To Extend Maintenance Windows so that GATHER_STATS_JOB may run for More Than 8 Hours?
NOTE:1445372.1 - Histograms: An Overview (10g and Above)
NOTE:785110.1 - Bad Plan Still Persistent even after Gathering New Fresh Statistics
NOTE:1226841.1 - How To: Gather Statistics for the Cost Based Optimizer
NOTE:798257.1 - Fixed Objects Statistics (GATHER_FIXED_OBJECTS_STATS) Considerations
NOTE:823934.1 - Statistics Not collected on Table After Executing GATHER_STATS_JOB
Comments
Post a Comment