作者：Maria Colgan 曾担任Oracle优化器的产品经理 发表时间： April 9, 2012
原文链接：https://blogs.oracle.com/optimizer/post/lies-damned-lies-and-statistics-part-2
去年，OOW大会（译者注：Oracle Open World, 甲骨文全球业务及技术大会,每年一届）的管理优化器统计信息专场引起了人们的巨大兴趣。似乎统计信息及其管理依然困扰着人们。为了帮助人们驱散围绕在统计信息管理周围的迷雾，我们在优化器统计信息上编写了两本白皮书。
第一本发表于去年的11月份，通过示例详细描述了优化器统计信息的不同概念。今天，我们发表第二本，它聚焦于统计信息收集的最佳实践，包括具体案例的测试，围绕在直方图和类似全局临时表这类易变表的统计信息管理方面的担忧。
下面是本文的引言和开头的快速浏览。你可以在这里找到全文。先睹为快！
（译者注：因原链接失效，译者将这两本白皮书的下载链接附后，同时，也在计划完成这两本白皮书的翻译，完成后，会更新这里的链接，另外提供翻译后的白皮书链接。）
使用Oracle Database 12c Release 2收集优化器统计信息的最佳实践
理解Oracle Database 12c Release 2的优化器统计信息
引言
Oracle优化器检测SQL语句所有可能的执行计划，并选取成本最低的那个，这里的成本表示对于特定执行计划评估的资源使用量。为了使优化器精准地确定执行计划的成本，它必须拥有SQL语句所访问的所有对象（表和索引）的信息，以及运行SQL语句的系统的信息。
这些必须的信息通常被称之为优化器统计信息。理解和管理优化器统计信息是优化SQL运行的关键。知道何时和如何定期地收集统计信息，是维护可接受的性能是至关重要的。本白皮书是优化器统计信息的两本白皮书系列中的第二本。本系列的第一本是理解优化器统计信息，其聚焦于统计信息的概念，并将被做为补充的信息源被多次引用。而本白皮书将详细讨论，在Oracle数据库最常用的场景下，何时以及如何收集统计信息。涉及的主题有：
- 如何收集统计信息
- 何时收集统计信息
- 改善统计信息收集的效率
- 何时不应收集统计信息
- 其它类型统计信息的收集
如何收集统计信息
在ORACLE中收集统计信息的首选方面是使用其提供的自动统计信息收集任务。
自动统计信息收集任务
该任务会在预先定义的维护窗口期间，收集所有缺少统计信息或统计信息过旧的数据库对象的统计信息。Oracle内部会对需要统计信息的对象按优先级排序，以便那些最需要更新统计信息的对象，被优先处理。
自动统计信息收集任务使用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC存储过程，其使用与其它DBMS_STATS.GATHER_STATS过程同样的缺省参数。这些缺省参数满足绝大多数的情况。然而，偶尔也会需要改变统计信息收集参数的缺省值，这可以通过DBMS_STATS.SET_PREF过程来实现。参数值应该在尽可能小的影响范围内修改，最好是基于每个对象。
原文链接：https://blogs.oracle.com/optimizer/post/lies-damned-lies-and-statistics-part-2
原文内容：
Lies, damned lies, and statistics Part 2
Maria Colgan | April 9, 2012 | 2 minute read
Distinguished Product Manager
There was huge interest in our OOW session last year on Managing Optimizer Statistics. It seems statistics and the maintenance of them continues to baffle people. In order to help dispel the mysteries surround statistics management we have created a two part white paper series on Optimizer statistics.
Part one of this series was released in November last years and describes in detail, with worked examples, the different concepts of Optimizer statistics. Today we have published part two of the series, which focuses on the best practices for gathering statistics, and examines specific use cases including, the fears that surround histograms and statistics management of volatile tables like Global Temporary Tables.
Here is a quick look at the Introduction and the start of the paper. You can find the full paper here. Happy Reading!
Introduction
The Oracle Optimizer examines all of the possible plans for a SQL statement and picks the one with the lowest cost, where cost represents the estimated resource usage for a given plan. In order for the Optimizer to accurately determine the cost for an execution plan it must have information about all of the objects (table and indexes) accessed in the SQL statement as well as information about the system on which the SQL statement will be run.
This necessary information is commonly referred to as Optimizer statistics. Understanding and managing Optimizer statistics is key to optimal SQL execution. Knowing when and how to gather statistics in a timely manner is critical to maintaining acceptable performance. This whitepaper is the second of a two part series on Optimizer statistics. The first part of this series, Understanding Optimizer Statistics, focuses on the concepts of statistics and will be referenced several times in this paper as a source of additional information. This paper will discuss in detail, when and how to gather statistics for the most common scenarios seen in an Oracle Database. The topics are
· How to gather statistics
· When to gather statistics
· Improving the efficiency of gathering statistics
· When not to gather statistics
· Gathering other types of statistics
How to gather statistics
The preferred method for gathering statistics in Oracle is to use the supplied automatic statistics-gathering job.
Automatic statistics gathering job
The job collects statistics for all database objects, which are missing statistics or have stale statistics by running an Oracle AutoTask task during a predefined maintenance window. Oracle internally prioritizes the database objects that require statistics, so that those objects, which most need updated statistics, are processed first.
The automatic statistics-gathering job uses the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure, which uses the same default parameter values as the other DBMS_STATS.GATHER_STATS procedures. The defaults are sufficient in most cases. However, it is occasionally necessary to change the default value of one of the statistics gathering parameters, which can be accomplished by using the DBMS_STATS.SET_PREF procedures. Parameter values should be changed at the smallest scope possible, ideally on a per-object bases.
You can find the full paper here. Happy Reading!
