作者：Maria Colgan 曾擔(dān)任Oracle優(yōu)化器的產(chǎn)品經(jīng)理 發(fā)表時(shí)間： April 9, 2012
原文鏈接：https://blogs.oracle.com/optimizer/post/lies-damned-lies-and-statistics-part-2

去年，OOW大會(huì)（譯者注：Oracle Open World, 甲骨文全球業(yè)務(wù)及技術(shù)大會(huì),每年一屆）的管理優(yōu)化器統(tǒng)計(jì)信息專場(chǎng)引起了人們的巨大興趣。似乎統(tǒng)計(jì)信息及其管理依然困擾著人們。為了幫助人們驅(qū)散圍繞在統(tǒng)計(jì)信息管理周圍的迷霧，我們?cè)趦?yōu)化器統(tǒng)計(jì)信息上編寫了兩本白皮書。

第一本發(fā)表于去年的11月份，通過示例詳細(xì)描述了優(yōu)化器統(tǒng)計(jì)信息的不同概念。今天，我們發(fā)表第二本，它聚焦于統(tǒng)計(jì)信息收集的最佳實(shí)踐，包括具體案例的測(cè)試，圍繞在直方圖和類似全局臨時(shí)表這類易變表的統(tǒng)計(jì)信息管理方面的擔(dān)憂。

下面是本文的引言和開頭的快速瀏覽。你可以在這里找到全文。先睹為快！
（譯者注：因原鏈接失效，譯者將這兩本白皮書的下載鏈接附后，同時(shí)，也在計(jì)劃完成這兩本白皮書的翻譯，完成后，會(huì)更新這里的鏈接，另外提供翻譯后的白皮書鏈接。）
使用Oracle Database 12c Release 2收集優(yōu)化器統(tǒng)計(jì)信息的最佳實(shí)踐
理解Oracle Database 12c Release 2的優(yōu)化器統(tǒng)計(jì)信息

引言

Oracle優(yōu)化器檢測(cè)SQL語句所有可能的執(zhí)行計(jì)劃，并選取成本最低的那個(gè)，這里的成本表示對(duì)于特定執(zhí)行計(jì)劃評(píng)估的資源使用量。為了使優(yōu)化器精準(zhǔn)地確定執(zhí)行計(jì)劃的成本，它必須擁有SQL語句所訪問的所有對(duì)象（表和索引）的信息，以及運(yùn)行SQL語句的系統(tǒng)的信息。

這些必須的信息通常被稱之為優(yōu)化器統(tǒng)計(jì)信息。理解和管理優(yōu)化器統(tǒng)計(jì)信息是優(yōu)化SQL運(yùn)行的關(guān)鍵。知道何時(shí)和如何定期地收集統(tǒng)計(jì)信息，是維護(hù)可接受的性能是至關(guān)重要的。本白皮書是優(yōu)化器統(tǒng)計(jì)信息的兩本白皮書系列中的第二本。本系列的第一本是理解優(yōu)化器統(tǒng)計(jì)信息，其聚焦于統(tǒng)計(jì)信息的概念，并將被做為補(bǔ)充的信息源被多次引用。而本白皮書將詳細(xì)討論，在Oracle數(shù)據(jù)庫最常用的場(chǎng)景下，何時(shí)以及如何收集統(tǒng)計(jì)信息。涉及的主題有：

• 如何收集統(tǒng)計(jì)信息
• 何時(shí)收集統(tǒng)計(jì)信息
• 改善統(tǒng)計(jì)信息收集的效率
• 何時(shí)不應(yīng)收集統(tǒng)計(jì)信息
• 其它類型統(tǒng)計(jì)信息的收集

如何收集統(tǒng)計(jì)信息

在ORACLE中收集統(tǒng)計(jì)信息的首選方面是使用其提供的自動(dòng)統(tǒng)計(jì)信息收集任務(wù)。

自動(dòng)統(tǒng)計(jì)信息收集任務(wù)

該任務(wù)會(huì)在預(yù)先定義的維護(hù)窗口期間，收集所有缺少統(tǒng)計(jì)信息或統(tǒng)計(jì)信息過舊的數(shù)據(jù)庫對(duì)象的統(tǒng)計(jì)信息。Oracle內(nèi)部會(huì)對(duì)需要統(tǒng)計(jì)信息的對(duì)象按優(yōu)先級(jí)排序，以便那些最需要更新統(tǒng)計(jì)信息的對(duì)象，被優(yōu)先處理。
自動(dòng)統(tǒng)計(jì)信息收集任務(wù)使用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC存儲(chǔ)過程，其使用與其它DBMS_STATS.GATHER_STATS過程同樣的缺省參數(shù)。這些缺省參數(shù)滿足絕大多數(shù)的情況。然而，偶爾也會(huì)需要改變統(tǒng)計(jì)信息收集參數(shù)的缺省值，這可以通過DBMS_STATS.SET_PREF過程來實(shí)現(xiàn)。參數(shù)值應(yīng)該在盡可能小的影響范圍內(nèi)修改，最好是基于每個(gè)對(duì)象。

原文鏈接：https://blogs.oracle.com/optimizer/post/lies-damned-lies-and-statistics-part-2
原文內(nèi)容：
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!