Oracle分析表和索引

最後更新：2018-12-06 來源：互聯網

上載者：User

創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

analyze table tablename compute statistics;

analyze index indexname compute statistics;

對於使用CBO很有好處，可以使用更可靠的table資訊，從而執行計畫也可以更準確一些，在10g會自動analyze，之前的版本需要手動定期

產生統計資訊,,選擇合理的執行計畫..

Oracle的online document這樣描述analyze的作用：
Use the ANALYZE statement to collect non-optimizer statistics, for example, to:

1、Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.

2、Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).

3、Identify migrated and chained rows of a table or cluster.

對於收集統計資訊這塊，我一般使用dbms_stats這個package，收集統計資訊的做用使CBO方式的最佳化選擇執行計畫更準確。

統計資訊最好定期收集，以業務的不同確定不同的收集周期

dbms_stats包問世以後，Oracle專家可通過一種簡單的方式來為CBO收集統計資料。目前，已經不再推薦你使用老式的分析表和dbms_utility方法來產生CBO統計資料。那些古老的方式甚至有可能危及SQL的效能，因為它們並非總是能夠捕捉到有關表和索引的高品質資訊。CBO使用對象統計，為所有SQL語句選擇最佳的執行計畫。

dbms_stats能良好地估計統計資料（尤其是針對較大的分區表），並能獲得更好的統計結果，最終制定出速度更快的SQL執行計畫。

清單A展示了dbms_stats的一次示範執行情況，其中使用了options子句。

execdbms_stats.gather_schema_stats( -
ownname => 'SCOTT', -
options => 'GATHER AUTO', -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt => 'for all columns size repeat', -
degree => 15 -
)

為了充分認識dbms_stats的好處，你需要仔細體會每一條主要的先行編譯指令（directive）。下面讓我們研究每一條指令，並體會如何用它為基於代價的SQL最佳化器收集最高品質的統計資料。

options參數

使用4個預設的方法之一，這個選項能控制Oracle統計的重新整理方式：

gather——重新分析整個架構（Schema）。
gather empty——只分析目前還沒有統計的表。
gather stale——只重新分析修改量超過10%的表（這些修改包括插入、更新和刪除）。
gather auto——重新分析當前沒有統計的對象，以及統計資料到期（變髒）的對象。注意，使用gather auto類似於組合使用gather stale和gather empty。

注意，無論gather stale還是gather auto，都要求進行監視。如果你執行一個alter table xxx monitoring命令，Oracle會用dba_tab_modifications視圖來跟蹤發生變動的表。這樣一來，你就確切地知道，自從上一次分析統計資料以來，發生了多少次插入、更新和刪除操作。

estimate_percent選項

以下estimate_percent參數是一種比較新的設計，它允許Oracle的dbms_stats在收集統計資料時，自動估計要採樣的一個segment的最佳百分比：
estimate_percent => dbms_stats.auto_sample_size

要驗證自動統計採樣的準確性，你可檢視dba_tables sample_size列。一個有趣的地方是，在使用自動採樣時，Oracle會為一個樣本尺寸選擇5到20的百分比。記住，統計資料品質越好，CBO做出的決定越好。

method_opt選項

dbms_stats的method_opt參數尤其適合在表和索引資料發生變化時重新整理統計資料。method_opt參數也適合用於判斷哪些列需要長條圖（histograms）。

某些情況下，索引內的各個值的分布會影響CBO是使用一個索引還是執行一次全表掃描的決策。例如，假如在where子句中指定的值的數量不對稱，全表掃描就顯得比索引訪問更經濟。

如果你有一個高度傾斜的索引（某些值的行數不對稱），就可建立Oracle長條圖統計。但在現實世界中，出現這種情況的機率相當小。使用CBO時，最常見的錯誤之一就是在CBO統計中不必要地引入長條圖。根據經驗，只有在列值要求必須修改執行計畫時，才應使用長條圖。

為了智能地產生長條圖，Oracle為dbms_stats準備了method_opt參數。在method_opt子句中，還有一些重要的新選項，包括skewonly，repeat和auto：method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto'

skewonly選項會耗費大量處理時間，因為它要檢查每個索引中的每個列的值的分布情況。

假如dbms_stat發現一個索引的各個列分布得不均勻，就會為那個索引建立長條圖，協助基於代價的SQL最佳化器決定是進行索引訪問，還是進行全表掃描訪問。例如，在一個索引中，假定有一個列在50%的行中，如清單B所示，那麼為了檢索這些行，全表掃描的速度會快於索引掃描。--*************************************************************
-- SKEWONLY option—Detailed analysis
--
-- Use this method for a first-time analysis for skewed indexes
-- This runs a long time because all indexes are examined
--*************************************************************
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
degree => 7
);
end;

重新分析統計資料時，使用repeat選項，重新分析任務所消耗的資源就會少一些。使用repeat選項（清單C）時，只會為現有的長條圖重新分析索引，不再搜尋其他長條圖機會。定期重新分析統計資料時，你應該採取這種方式。--**************************************************************
-- REPEAT OPTION - Only reanalyze histograms for indexes
-- that have histograms
--
-- Following the initial analysis, the weekly analysis
-- job will use the “repeat” option. The repeat option
-- tells dbms_stats that no indexes have changed, and
-- it will only reanalyze histograms for
-- indexes that have histograms.
--**************************************************************begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 7
);
end;

使用alter table xxx monitoring;命令來實現Oracle表監視時，需要使用dbms_stats中的auto選項。如清單D所示，auto選項根據資料分布以及應用程式訪問列的方式（例如通過監視而確定的一個列的工作量）來建立長條圖。使用method_opt=>’auto’類似於在dbms_stats的option參數中使用gather
auto。begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7
);
end;

並行收集

Oracle允許以並行方式來收集CBO統計資料，這就顯著提高了收集統計資料的速度。但是，要想並行收集統計資料，你需要一台安裝了多個CPU的SMP伺服器。

更快的執行速度

dbms_stats是提高SQL執行速度的一種出色機制。通過使用dbms_stats來收集最高品質的統計資料，CBO能夠正確判斷執行任何SQL查詢時的最快途徑。dbms_stats還在不斷地改進。目前，它的一些令人激動的新特性（自動樣本大小和自動長條圖產生）已經顯著簡化了Oracle專家的工作。

如何使用dbms_stats分析統計資訊？

Dbms_stats是oracle8i新增的程式包，它使統計資料的產生和處理更加方便，很多人都在推薦使用dbms_stats替代analyze,我倒是不怎麼用過，記錄一下

estimate_percent --估算抽樣百分比
method_opt：for table --只統計表　for all indexed columns --只統計有索引的表列 for all indexes --只分析統計相關索引

--建立統計資訊曆史保留表
sql> exec dbms_stats.create_stat_table(ownname => 'scott',stattab => 'stat_table') ;