統計信息 Jonathan Lewis DBA 性能和可用性
2024-07-21 02:37:59
供稿：網友
了解向一個 Oracle 數據庫新版本移植時，優化程序使用系統統計將給移植帶來很大的不同。
Oracle 數據庫引擎最重要的組件是什么？我認為是優化程序。數據庫所做的一切都涉及到 SQL，必須轉換每一段 SQL，使其能夠有效運行。 而無論您對數據進行什么操作，都要涉及到優化程序。
基于成本的優化程序 (CBO) 是一段精細復雜的代碼，因此當它第一次出現在 Oracle7 中時，存在一些問題就不足為怪了。不過，在經過為數不多的幾個補丁版本之后，它用來實施純粹的基于成本的系統就變得完全可行了。我記得在 7.1 中安裝了幾個這樣的系統，在 7.2 中至少安裝了一個重大的系統。
盡管如此，仍有驚人數量的站點很長時間不愿使用 CBO — 原因是許多開發人員和 DBA 仍以“基于規則”的方式進行思考，因此他們所作的事情使 CBO 很難進行工作。從根本上講，他們沒有為優化程序提供真實的信息，然后卻迷惑為什么它會作出糟糕的決策。
我猜想我最早成功的原因是我碰巧注重到了一些現在已理解透徹的事情：當參數 db_file_multiblock_read_count 的值較大時，CBO “喜歡”執行表掃描，而當這個值較小時，CBO “更喜歡”使用索引的事實。正如我們現在所了解的，其它一些參數表現出類似的特性 — 也就是說，RBO 喜歡它們的值大，而 CBO 需要它們的值小。當然，有一個小細節：在最后的比較中，CBO 將在 from 子句中沿著表的列表向下處理，而 RBO 將沿著該列表向上處理。
CBO 與 RBO 很不相同 — 但只有一些非凡要害的差異您必須了解，因為假如您不了解它們而仍用與對待 RBO 相同的方式來對待 CBO，那么您將給自己帶來很多不必要的問題。
歷史總在重演
CBO 總在發展。一些 Oracle 版本引進了次要增強，一些版本引進了主要增強，一些版本改變了范例。
除非您發現了主要增強和范例轉變，您才能結束多年來一直打擊 CBO，試圖解決舊缺點而不是發揮新優勢 — 就像許多人在從 RBO 向 CBO 轉變時所做的那樣。
那么在 CBO 中是否有任何新變化能夠對我們的思維方式產生和從 RBO 向 CBO 轉變一樣大的影響呢？答案是肯定的。
在 Oracle9i 數據庫中，Oracle 增加了系統統計（我認為它是一種新的范例）和自動工作區大小調整（我認為它是一個主要增強）。在 Oracle 數據庫 10g 中，我們獲得了各種調整顧問工具，這些顧問工具通過幫助開發人員更快速地創建更多有效的 SQL、索引、物化視圖或 PL/SQL 來優化開發人員的時間。我們還獲得了查詢概要文件，這是一個主要增強，它將統計信息存儲在數據庫中，以幫助優化程序作出更好的決策。這種信息在您必須處理無法接觸的第三方 SQL 時非凡有用。
在這兩個版本中，我們獲得了動態采樣 (dynamic_sampling)，這是在 9i 中引進的一種非常有用的特性，對數據倉庫和決策支持系統非凡有好處。但假如您沒有留意 9i 中的動態采樣特性，那么當您在 10g 上運行 OLTP 系統時就可能變為麻煩事，因為動態采樣是高效地默認啟用的，它可能成為不必要的開銷。
所有這些變化中最重要的是引進了系統統計。實際上，我甚至可以說從 Oracle8 向 Oracle9i 移植的要害步驟之一是啟用系統統計，并充分了解它們對系統的影響。同樣，系統統計真的對充分利用 10g 中的優化程序相當要害，熟悉系統統計的最佳時間是您決定從 8i 中進行移植的時候。
這個特性如此重要，因此我將在本文剩下的部分中專門對其進行說明，而將動態采樣和概要文件留待改天再進行討論。
系統統計
在 Oracle9i 之前，CBO 的計算基于滿足一個查詢所需的 I/O 請求的數量，它使用各種常量來為表掃描處理數字，并引入了一些規則來解決諸如小型索引高速緩存之類的事情。（請參閱我的 DBAzine.com 文章“為什么 Oracle 不使用我的索引？”[獲取對該主題的介紹。）
最早在優化程序中建立的一些假設有點幼稚，但隨著時間的推移，這些假設經過了提煉，算法得到了改進，新的特性也得到了實施。然而，估計 I/O 請求的副作用成為一個持久的限制。
在 9i 中，Oracle 引進了 cpu_costing，這是答應將一次操作的 CPU 成本包含在總體估計的一部分中的一種機制。在 9i 中，只有當您收集了系統統計數據時，才啟用這個特性；在 10g 中，它的啟用是默認的。
那么 cpu_costing 做些什么，系統統計具體是什么？讓我們從系統統計開始，并使用幾個對 dbms_stats 程序包的調用來進行演示。（這個例子使用 9.2.0.4，要進行工作，您的帳號將需要擁有 gather_system_statistics 權限。）
execute dbms_stats.gather_system_stats('Start');
— 當數據庫處于典型工作負載下時可能延遲一段時間
execute dbms_stats.gather_system_stats('Stop');
要看看您做了什么，您可以查詢一個名稱為 aux_stats$ 的表（由 SYS 模式所有）。
在收集了系統統計數據之后，這個表將包含一些由新的優化器算法用來計算成本的要害數字。（不過，您必須刷新 shared_pool，以使現有的執行計劃無效。）以下查詢將向您顯示當前的設置：
select pname, pval1
from sys.aux_stats$
where sname = 'SYSSTATS_MAIN';
確切的結果列表取決于版本（代碼仍在發展，Oracle 的一些版本比其它版本收集更多的統計數據），但您將可能看到類似以下的內容：
PNAME PVAL1
------------------------------ ----------
CPUSPEED 564
MAXTHR 13899776
MBRC 6
MREADTIM 10.496
SLAVETHR 182272
SREADTIM 1.468
10g 還引進了一些額外的行，這些行的值在數據庫啟動時設置：
CPUSPEEDNW 904.86697
IOSEEKTIM 10
IOTFRSPEED 4096
為方便起見，我這里提到的是 SQL；實際上，批準查看這些信息的方法是 dbms_stats 程序包中的 get_system_stats 過程。假如您想“調整”這些值（沒有正確地收集它們），那么還有一個 set_system_stats 過程。
表掃描
當提供系統統計時，在優化程序成本計算上有兩個顯著的變化。首先您將注重到 sys.aux_stats$ 保存了以下參數的值：
sreadtim：一次單塊讀請求的平均時間（以毫秒為單位）
mreadtim：一次多塊讀請求的平均時間（以毫秒為單位）
MBRC： 一次多塊讀操作中的平均塊數。
利用這些信息，Oracle 可以估計執行一次表掃描（或索引快速全掃描）要花多長時間。計算很輕易：它就是執行掃描所需的多塊讀操作的數量乘以執行一次多塊讀操作的平均時間。忽略因自動段空間治理造成的小的變化，我們只注重高水線標記，并從那里開始工作：
完成時間＝ mreadtim * HWM / MBRC。
Oracle 根據等價的單塊讀操作數重新聲明了“完成時間”，而不是將“完成時間”報告為查詢的成本。為此，只需將完成時間除以一次單塊讀操作的平均時間。
成本＝完成時間/ sreadtim
或者，將這兩個公式放在一起并重新排列項目：
表掃描的成本＝ (HWM / MBRC) * (mreadtim / sreadtim)
從這個例子中您可以看到，查詢的成本是查詢的完成時間，但用單塊讀操作為單位進行表示而不是使用正常的時間單位。
當您開始使用系統統計時，優化程序在表掃描和索引訪問路徑之間作選擇時自動開始變得更“明智”，因為用于表掃描的多塊讀操作的成本將包含一個正確和適當的時間組成部分。
過去，表掃描的成本很簡單：
表掃描的成本＝ HWM /（修改后的 db_file_multiblock_read_count）。
您為參數 db_file_multiblock_read_count 選擇的值可能不實際，而這個公式幾乎沒有考慮這一事實，也沒有考慮與單塊讀操作相比，一個極大的 db_file_multiblock_read_count 將花費的額外時間。
這個缺點是 Oracle 在 8.1.6 中創建 optimizer_index_cost_adj 參數的主要原因，這個參數使您能夠引進實質上類似于您在系統統計中收集的 mreadtim 的一個因子。（您可能已經發現了新的成本公式中的 mreadtim/sreadtim 部分和估計一個切合實際的 optimizer_index_cost_adj 的常見方法之間的相似性。）但使用 optimizer_index_cost_adj 參數存在一些可能產生問題的意外副作用，而在您開始使用系統統計時開始起作用的一些機制變得更強健得多。
順便提一下，甚至在使用系統統計時，使用 optimizer_index_cost_adj 作為表高速緩存效果（非凡是，多少百分比的單塊表讀操作可能轉變成實際的讀請求）的一個線索仍然很有意義。不過，在 10g 中會提供一些信息，使得甚至這種線索在不遠的將來也將變得不必要。
CPU 成本
系統統計不僅能夠修正單塊讀操作和多塊讀操作之間的 I/O 和時間權衡。它們還考慮到了對成本計算的兩個進一步的增強（或修正）：首先，Oracle 甚至能夠更好地根據索引訪問路徑平衡表掃描；其次，Oracle 能夠智能地重新安排猜測順序。
注重統計如何包含表面的 CPU 速度（名義上以 MHz 為單位）。假如這與您的系統的實際 CPU 速度一點也不相同，請不用大驚小怪 — 這個數字可能只是一個基準線操作的內部校準，Oracle 使用這種基準操作來
