关于oracle统计信息，你应该知道的一切

oracle统计信息的重要性不言而喻，下面就开始全面的了解一下。英文的就不翻译了，还是原汁原味的好。

A、How to Gather Optimizer Statistics

1. Document 1226841.1 How To: Gather Statistics for the Cost Based Optimizer
2. Document 1445302.1 How to Gather Optimizer Statistics on 12c
3. Document 749227.1 How to Gather Optimizer Statistics on 11g
4. Document 605439.1 How to Gather Optimizer Statistics on 10g
5. Document 388474.1 How to Gather Optimizer Statistics on 9i

B、Troubleshooting Issues Following Statistics Gathering

1. Document 1613147.1 What to do when Statistics Gathering does not Produce the Plans that you Expect
2. Document 452011.1 Restoring Table Statistics

C、Best Practices

1. Document 44961.1 Statistics Gathering: Frequency and Strategy Guidelines
2. 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:

1. Document 1456176.1 Oracle Database Advisor Webcast Schedule and Archive recordings

D、Automated Statistics Collection

1. Document 1233203.1 FAQ: Automatic Statistics Collection Job (10g and 11g)
2. Document 377152.1 Best Practices for automatic statistics collection
3. Document 731935.1 How To Manually Execute the Optimizer Statistics Auto Task in Oracle 11g
4. Document 743507.1 How to Benefit from Automatic Maintenance Tasks Following the Removal of the
5. GATHER_STATS_JOB in 11g?
6. Document 368475.1 How To Extend Maintenance Windows For GATHER_STATS_JOB for More Than 8
7. Hours?
8. Document 377143.1 How to check what automatic statistics collection is scheduled on 10g

E、Automatic Detection of Un-Gathered/Stale Objects

1. Document 228186.1 Differences between GATHER STALE and GATHER AUTO
2. Document 1073120.1 DBMS_STATS 'gather auto' and 'gather stale' and column histograms
3. Document 252597.1 Relation between Table Monitoring and STATISTICS_LEVEL parameter in 10g
4. Document 295249.1 Automated Object Statistics in 10g and Above
5. Document 390737.1 Modifying the 10% Staleness Threshold for DBMS_STATS Automatic Statistics
6. Gathering

F、Backing up and Restoring Statistics

1. Document 464939.1 Statistics Best Practices: How to Backup and Restore Statistics
2. Document 452011.1 Restoring Table Statistics

G、Transferring Statistics to a different database

1. Document 333742.1 How to transfer statistics from one database to another

H、Dictionary 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:

1. Document 457926.1 How to gather statistics on SYS objects and fixed_objects?
2. Document 798257.1 GATHER_FIXED_OBJECTS_STATS Considerations

I、System 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:

1. Document 149560.1 How to Collect and Display System Statistics (CPU and IO) for CBO use
2. Document 470316.1 Using Actual System Statistics (Collected CPU and IO information)
3. Document 153761.1 System Statistics: Scaling the System to Improve CBO optimizer
4. Document 427939.1 System Statistics: How to gather system stats for a particular batch of work

J、Statistics Frequently Asked Questions

1. Document 1501712.1 FAQ: Statistics Gathering Frequently Asked Questions
2. Document 1233203.1 FAQ: Automatic Statistics Collection Job (10g and 11g)

K、Checking for the presence of Statistics

1. Document 1366133.1 SQL Tuning Health-Check Script
2. Document 957993.1 Script to show objects that are missing statistics

L、Pending 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:

1. 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:

1. Document 740966.1 Advisor Webcasts: Current Schedule and Archived recordings

M、Miscellaneous Topics

1. Document 336267.1 Parameter : OPTIMIZER_DYNAMIC_SAMPLING
2. Document 406475.1 What are the Default Parameters when Gathering Table Statistics on 9i and 10g?
3. Document 725845.1 How to Change Default Parameters for Gathering Statistics
4. Document 1445372.1 Histograms: An Overview (10g and Above)
5. Document 72539.1 Interpreting Histogram Information
6. Document 390249.1 How To Quickly Add/Remove Column Statistics (Histograms) For A Column
7. Document 823934.1 Statistics Not collected on Table After Executing GATHER_STATS_JOB
8. Document 236935.1 Global statistics - An Explanation
9. Document 785110.1 Bad Plan Still Persistent even after Gathering New Fresh Statistics
10. Document 814337.1 Why The Running Query Does Not Use The New Statistics?
11. Document 557661.1 Rolling Cursor Invalidations with DBMS_STATS in Oracle10

相信读完这些文章后，肯定会有所收获。