關於統計資料收集的總結

關於統計資料收集的總結[@more@]

在Oracle9i資料庫以來，收集統計資訊有兩種統計資料：

n 資料庫統計

作為資料庫管理員，需要收集資料庫統計資訊，這些資料可以量化資料的分佈和表、索引和簇儲存的特徵。統計資料是基於成本最佳化器的基礎，如何收集統計資料是實現成本最佳化器的根本。可以使用Analyze和Dbms_stats進行資料庫統計的收集。

n 系統統計

只能使用Dbms_stats進行資料庫統計的收集。

這些資訊被存放在資料字典中。

2007-3-19新添

統計資訊收集的一些建議：

使用dbms_stats包來收集而不是使用analyze命令收集。

在Oracle 10g統計資訊的收集是自動的。

什麼時間收集統計資訊哪？有以下幾種情況需要收集統計資訊：

大的資料的改變的情況下，例如loads,purges,bulk updates。

新的high/low鍵值的產生的時候。

新新增表後

更新CPU，IO子系統的時候（主要是系統統計的收集）

RBO到CBO遷移的時候。

新的資料庫新增的時候。

資料字典ALL_TAB_MODIFICATIONS的使用：

這個檢視可以看到某個表的插入，更新刪除資料的大概次數，這樣就可以根據這些情況考慮這個表是否需要更新統計資料了。

end

統計資訊的內容

1.1 表的統計資訊內容

n 行數

n 資料塊數（一般是準確的）

n 空的資料塊數（一般是準確的）

n 平均可利用空閒空間

n 連結或者遷移行數

n 平均行的長度

n 最後analyze時間和事例的大小

檢視的檢視：

User_tables

All_tables

Dba_tables dba_tab_[sub]paratition

1.2 索引的統計資訊內容

n 索引層數

n 頁塊的數目

n Distinct鍵值的數目

n 每一個鍵的頁塊的平均數目

n 每一個鍵的資料塊的平均數目

n 索引entries的數目

n Clustering factor

n 最後analyze時間和事例的大小

檢視的檢視：

User_indexes

All_indexes

1.3 欄位統計資訊的內容

n Distinct值的數目

n 最小的值

n 最大的值

n 最後一次analyze日期和例子的大小

檢視的檢視：

User_tab_col_statistics

All_tab_col_statistics

1.4 系統統計資訊的內容

n I/O效能和使用

n CPU效能和使用

1.5 收集直方圖統計資訊

2. 收集統計資訊通常有三種方式進行收集：

2.1 Analyze

2.2 Dbms_stats

2.3 Dbms_utility

通常我們使用前兩種方式進行統計資料的收集，oracle推薦使用dbms_stats進行統計資料的收集。

3. 統計資訊收集的間隔時間

在新增和重建索引得時候需要分析索引。可以使用下面的語句來實現

Create index ... compute statistics; Alter index ... rebuild compute statistics;

統計資料收集的頻率和應用程式和環境相關，資料庫中資料的變化和數量變化的大小相關。不是有一個固定的間隔時間能夠適合所有的資料庫。要根據特定資料庫的使用來確定分析資料的時間，一個目的就是要能夠確保字典中的統計資料和表中的實際行數和分佈相一致，至少是較為符合。例如，如果有一個資料庫有大量的DML操作，而另外一個資料庫只是多用於查詢，報表，後一個資料庫就沒有必要經常分析。

4．需要多少樣本

當然對於收集統計資料的時候，樣本的數目越打越好，這樣就能夠保證正確的可信度，可信度的大小極大依賴樣本的大小。對於估計運算來說，經常使用20％的樣本尺寸，這樣的範圍對於一部分是足夠保障最佳化器作出正確的執行計劃。當然如果統計所有的資料，則統計資料的可信度為100％，對於分析一個樣本資料大於49％的時候，Oracle會分析導致統計這個表的所有資料。

1、如果資料是規律分佈的，只需要5％的樣本

2、如果唯一值大於10％的總行數的時候，需要更多的樣本大小。

3、在使用分析直方圖統計資料的時候，樣本的大小必須至少是bucket數目的100倍。

下面就收集資訊的種類來分別對這幾種方式進行討論：

5．統計資料的收集

5.1 使用analyze收集統計資訊

使用analyze命令的功能為：

n 收集或者刪除表，索引，簇的統計資訊。

n 確認索引或者分割槽索引、表或者分割槽表、索引組織表、簇或者物件的結構。

n 確定表或者簇的行連結和行遷移

收集表的統計資訊

全樣本收集 SQL> analyze table t compute statistics; Table analyzed SQL> select NUM_ROWS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS,NUM_FREELIST_BLOCKS from user_tables where table_name = 'T'; NUM_ROWS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS ---------- ---------- ---------- ----------- ------------------------- ------------------- 19 7837 0 6 0 0 SQL> 預設樣本，在這裡沒有指定樣本的數目，Oracle會使用1064行作為樣本數目 SQL> analyze table t estimate statistics; Table analyzed

刪除統計資訊資料

SQL> analyze table t delete statistics; Table analyzed SQL> select NUM_ROWS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS,NUM_FREELIST_BLOCKS from user_tables where table_name = 'T'; NUM_ROWS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS ---------- ---------- ---------- ----------- ------------------------- ------------------- SQL>

收集直方圖統計資料

SQL> SELECT NUM_DISTINCT, NUM_BUCKETS, SAMPLE_SIZE 2 FROM USER_TAB_COLUMNS 3 WHERE TABLE_NAME = 'T' AND COLUMN_NAME = 'A'; NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE ------------ ----------- ----------- SQL> SQL> ANALYZE TABLE T 2 COMPUTE STATISTICS FOR COLUMNS a SIZE 10; Table analyzed SQL> SQL> SELECT NUM_DISTINCT, NUM_BUCKETS, SAMPLE_SIZE 2 FROM USER_TAB_COLUMNS 3 WHERE TABLE_NAME = 'T' AND COLUMN_NAME = 'A'; NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE ------------ ----------- ----------- 19 10 19

5.1.2 收集索引得統計資訊

5.1.3 收集欄位的統計資訊

5.1.4 收集模式的統計資訊

5.1.5 收集系統的統計資訊

5.1.6 收集直方圖的統計資訊

5.2 使用dbms_stats收集統計資訊

包dbms_stats是Oracle推薦使用收集統計資料，

收集表的統計資訊

SQL> exec dbms_stats.gather_table_stats('TEST','T'); PL/SQL procedure successfully completed SQL> select NUM_ROWS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from user_tables where table_name = 'T'; NUM_ROWS AVG_SPACE CHAIN_CNT AVG_ROW_LEN ---------- ---------- ---------- ----------- 19 0 0 3

刪除表的統計資料

SQL> exec dbms_stats.delete_table_stats('TEST','T'); PL/SQL procedure successfully completed SQL> select NUM_ROWS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from user_tables where table_name = 'T'; NUM_ROWS AVG_SPACE CHAIN_CNT AVG_ROW_LEN ---------- ---------- ---------- ----------- SQL>

收集索引統計資訊

兩種收集方式的區別

我一直在收集關於這兩個收集方式的區別，在看到資料上只是說，Oracle推薦使用包DBMS_STATS收集統計資料。但是analyze命令有包DBMS_STATS不能替代的功能就是這個命令可以確認索引或者分割槽索引、表或者分割槽表、索引組織表、簇或者物件的結構。

確定表或者簇的行連結和行遷移。這是包DBMS_STATS沒有的功能。但是這些資訊不和成本最佳化器的解析相關。

Oracle之所以推薦使用包DBMS_STATS收集統計資料是因為這個包能夠並行收集統計資料，收集分割槽表全域性的統計和能夠最佳化統計資料的收集。

6. 統計資料的匯出匯入

1、使用dbms_stats.create_stat_table過程建立一個使用者定義的統計表

SQL> exec dbms_stats.create_stat_table(ownname => 'TEST',stattab => 'mystats'); PL/SQL procedure successfully completed SQL> desc mystats; Name Type Nullable Default Comments ------- -------------- -------- ------- -------- STATID VARCHAR2(30) Y TYPE CHAR(1) Y VERSION NUMBER Y FLAGS NUMBER Y C1 VARCHAR2(30) Y C2 VARCHAR2(30) Y C3 VARCHAR2(30) Y C4 VARCHAR2(30) Y C5 VARCHAR2(30) Y N1 NUMBER Y N2 NUMBER Y N3 NUMBER Y N4 NUMBER Y N5 NUMBER Y N6 NUMBER Y N7 NUMBER Y N8 NUMBER Y N9 NUMBER Y N10 NUMBER Y N11 NUMBER Y N12 NUMBER Y D1 DATE Y R1 RAW(32) Y R2 RAW(32) Y CH1 VARCHAR2(1000) Y SQL>

2、使用dbms_stats.export_table_stats過程將統計資料從資料字典複製到使用者定義的統計表中。

SQL> select NUM_ROWS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from user_tables where table_name = 'T'; NUM_ROWS AVG_SPACE CHAIN_CNT AVG_ROW_LEN ---------- ---------- ---------- ----------- 19 0 0 3 SQL> select count(*) from mystats; COUNT(*) ---------- 0 SQL> exec dbms_stats.export_table_stats(ownname => 'test',tabname => 'T',stattab => 'mystats'); PL/SQL procedure successfully completed SQL> select count(*) from mystats; COUNT(*) ---------- 2

3使用export和import工具將統計資訊匯出，匯入到相應的需要的資料庫中的統計表。

4 使用過程將匯入的統計資訊匯入到資料字典表中，在這裡步驟3、4做一個模擬。

SQL> exec dbms_stats.delete_table_stats(ownname => 'TEST',tabname => 'T'); PL/SQL procedure successfully completed SQL> select NUM_ROWS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from user_tables where table_name = 'T'; NUM_ROWS AVG_SPACE CHAIN_CNT AVG_ROW_LEN ---------- ---------- ---------- ----------- SQL> exec dbms_stats.import_table_stats(ownname => 'test',tabname => 't',stattab => 'mystats'); PL/SQL procedure successfully completed SQL> select NUM_ROWS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from user_tables where table_name = 'T'; NUM_ROWS AVG_SPACE CHAIN_CNT AVG_ROW_LEN ---------- ---------- ---------- ----------- 19 0 0 3 SQL>

7 系統統計

這是在Oracle9i上新增的一項功能。在

系統統計是由包dbms_stats來收集，這些資料被存放在aux_stats$表中。

n CPU速度（MHz）

n 單一塊讀的時間（毫秒）

n 一毫秒讀多少塊

n Typical achieved multiblock read

系統統計收集的過程

手動收集系統統計

n 開始收集execute dbms_stats.gather_system_stats('Start');

n 執行批次負荷

n 停止收集execute dbms_stats.gather_system_stats('Stop');

附：

瞭解系統統計 作者：Jonathan Lewis 這個地址上有一片介紹系統統計的文章，可以參考一下。以下部分內容是節選自這篇文章。

這是可以透過查詢檢視aux_stats$ 的表（由 SYS 模式所有）。在收集了系統統計資料之後，這個表將包含一些由新的最佳化器演算法用來計算成本的關鍵數字。

select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

這時候會有一下的結果出現：

PNAME PVAL1 ------------------------------ ---------- CPUSPEED 564 MAXTHR 13899776 MBRC 6 MREADTIM 10.496 SLAVETHR 182272 SREADTIM 1.468 10g 還引進了一些額外的行，這些行的值在資料庫啟動時設定： CPUSPEEDNW 904.86697 IOSEEKTIM 10 IOTFRSPEED 4096

dbms_stats 程式包中的 get_system_stats 過程是收集系統資訊。如果您想“調整”這些值（沒有正確地收集它們），那麼還有一個 set_system_stats 過程。

系統統計的使用內幕

表掃描

當提供系統統計時，在最佳化程式成本計算上有兩個顯著的變化。首先您將注意到 sys.aux_stats$ 儲存了以下引數的值：

n sreadtim：一次單塊讀請求的平均時間（以毫秒為單位）

n mreadtim：一次多塊讀請求的平均時間（以毫秒為單位）

n 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 中會提供一些資訊，使得甚至這種線索在不遠的將來也將變得不必要。

注：

在這裡有兩個比較複雜的引數：db_file_multiblock_read_count和引數optimizer_index_cost_adj的使用問題，在我們的實際應用中慧出現這樣的情況，在你設定db_file_multiblock_read_count引數是根據作業系統的實際IO吞吐能力來確定的，但是如果設定這個引數比較高的話，最佳化器就會認為全表掃描來代替索引掃描，所以後來（8.1.6）引入了一個引數optimizer_index_cost_adj，使用這個引數來調整使用索引的花費，預設是100，設定的範圍是1－10000，100的意思就是索引和全表掃描的花費是一致的，通常是設定一個小於100的值，這樣就告訴Oracle使用索引來替代全表掃描。但是我們在設定這個引數的時候是沒有一個能夠定量的參考，系統統計就給我們提供了這個參考。最佳化器利用系統統計來確定是否使用索引或者全表掃描。

CPU 成本

系統統計不僅能夠修正單塊讀操作和多塊讀操作之間的 I/O 和時間權衡。它們還考慮到了對成本計算的兩個進一步的增強（或修正）：首先，Oracle 甚至能夠更好地根據索引訪問路徑平衡表掃描；其次，Oracle 能夠智慧地重新安排預測順序。

注意統計如何包含表面的 CPU 速度（名義上以 MHz 為單位）。如果這與您的系統的實際 CPU 速度一點也不相同，請不用大驚小怪 — 這個數字可能只是一個基準線操作的內部校準，Oracle 使用這種基準操作來計算其它操作的相對 CPU 成本。在以 2.8GHz 的速度執行的計算機上，我一般得到的表面 CPU 速度是幾百 MHz。（記住您看到的是單個 CPU 的名義速度，而不是一個多 CPU 系統中的所有 CPU 的總和。）

那麼為什麼它幫助最佳化程式瞭解您的 CPU 的（表面）速度？考慮一個例子，其中您能夠選擇：

選項 1：使用一個簡單的日期列上的一個索引來在表中查詢 20 個分散的行。

選項 2：使用表掃描來檢查表中的每一行，檢視錶中每一行，看看日期列是否落在正確的範圍之內。

Oracle 可能純粹根據單塊和多塊讀取的數量和速度決定表掃描將更快。但如果表掃描需要在 10,000 行上執行類似以下的一個測試，那麼這將花費多少 CPU 時間：

date_col between to_date('01-Jan-2004') and to_date('02-Jan-2004);

CPU 操作也花費時間，並且如果必須在表掃描上執行的測試的數量和特性需要許多 CPU 時間，那麼 Oracle 將把這個成本轉換成公式中的一個因子，並且可能將一個查詢從 CPU 密集型的表掃描轉變成索引範圍掃描。您可以從 Oracle9i 資料庫效能調整指南和參考中的公式 (A96533 p. 9-22) 中看到這些：

Cost = (

#SRds * sreadtim +

#MRds * mreadtim +

#CPUCycles / cpuspeed

) / sreadtim

這個公式中的 #CPUCycles 值可以在 Explain Plan 程式使用的更新版本 plan_table 的 cpu_cost 列中看到。（下面是最佳化程式增強的另一個小但卻重要的細節：始終檢視 Explain Plan 程式如何發展。）

實際上，如果您在成本公式中使用該列，那麼該公式需要引進一個謊言因素 (fudge factor) — cpuspeed 以 MHz 為單位記錄，其它的計時參量以毫秒為單位給出，因此該公式的 CPU 部分看起來就像用一個因子 1000 進行了調整一樣：

#CPUCycles / (cpuspeed * 1000)

預測順序

除高水平的選擇之外，對 CPU 操作的瞭解和預測的複雜性可以允許 Oracle 去做一些您在手工調整實踐中可能從來不會去考慮的事情。對這一原則的最佳演示來自一個（稍微經過設計的）工作示例。為了可重複測試的目的，以下程式碼在

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/222350/viewspace-908014/，如需轉載，請註明出處，否則將追究法律責任。