知識&案例：並行和並發統計資訊收集

2020 年 3 月 26 日
筆記

概述

隨著應用數據的增多和表量的增加，為了增加統計資訊收集的效率，Oracle推出了並行和並發收集統計資訊的方法。

本文將針對並行和並發收集統計資訊的相關知識內容以及部分案例進行介紹，並重點介紹並發統計資訊收集。

並行收集統計資訊(PARALLEL )

當某個表的Size特別大時，可以通過並行的Slave進程共同工作來加快統計資訊收集的效率。默認情況下，資料庫能夠根據在表或者索引級別設置的並行度（默認：1）進行並行統計資訊收集。但我們也可以通過顯示地設置degree參數來控制並行統計資訊收集的並行度。

DEGREE參數：

DEGREE參數用於控制統計資訊收集的並行度。你可以通過以下的方式進行賦值：

例：

Oracle推薦指定degree參數為DBMS_STATS.AUTO_DEGREE，由Oracle根據對象的大小和並行參數的設置情況來決定統計資訊收集的並行度。例：

※注意： Oracle不能並行收集某些類型的索引，如：cluster indexes, domain indexes, and bitmap join indexes。

並發收集統計資訊(CONCURRENT)

並行的統計資訊收集僅僅意味著：對某個對象進行統計收集時會採用多個並行Slave進行處理，但是對於多個對象(表、索引或分區)來講，處理還是串列的。即：處理完一個對象後再去處理下一個對象。

從11.2.0.2 開始，Oracle為了能夠使多個對象的統計資訊收集也能夠同時進行，推出了並發收集統計資訊(CONCURRENT)模式，使多進程的環境更加有效率。即：同時啟動多個JOB，並發地處理多個對象(表、索引或分區)。

從12.1.0.1 開始，Oracle進一步擴展了並發收集統計資訊，使Oracle自動統計收集任務（automatic statistics gather task）也能從中受益使用並發收集統計資訊。

並發收集統計資訊主要使用了以下的幾種技術：

要啟用並發收集統計資訊，需要設置以下的參數：

並發收集統計資訊時，資料庫生成的JOB數會根據具體情況來分配，大多數情況下，DBMS_STATS 程式會給每個對象分配一個JOB；但如果對象（表或者分區）的大小太小，為了節省資源，Oracle會合併多個表和分區在一個JOB中執行。

※注意：為了防止同時處理多個分區表的分區時發生死鎖，對於分區表是通過Queue的機制進行處理的。即：每次只能處理一個分區表，其他的需要在Queue等待，待前一個分區表處理完後再處理下一個。

以下是Oracle白皮書中的一個並發收集統計資訊的例圖：

通過該例子我們可以看到，在針對並發收集統計資訊時會有不同層級的JOB，對於分區表除了一個協調JOB外還會針對各個分區分配1個JOB。另外，如同前面所講的，多個分區表不能同時處理。圖中的COSTS表和SALES表的處理會被排序，即：COSTS表的JOB結束後SALES表的處理才會開始。

CONCURRENT參數：

CONCURRENT參數用於控制並發收集統計資訊。需要通過DBMS_STATS.SET_GLOBAL_PREFS來進行全局設置。

在11.2.0.2~11.2.0.4的版本上，可以設置的值：

12c的版本上，可以設置以下的值：

並發執行例1（11.2.0.3）：

11.2.0.3環境上的測試：

1.測試數據的準備：

2.設置相關的參數：

3.執行統計資訊收集

4.監視結果

並發執行例2（12.1.0.2 ）：

12.1.0.2 環境上的測試：

1.測試數據的準備：

2.設置相關的參數：

3.執行統計資訊收集

4.監視結果

並發統計資訊收集的監視

可以通過以下的視圖，對並發統計資訊收集進行監視

並行 VS 並發

有時候可能對並行和並發統計資訊收集的概念有些混淆，下面我們通過一張表來總結對比一下並行和並發統計資訊收集：

並發和並行執行統計資訊收集組合

為了提高效率，可以使並發和並行執行統計資訊收集同時有效，這種組合使用對於非常大的表和分區非常有效。要使組合有效，你需要再設置PARALLEL_ADAPTIVE_MULTI_USER參數為False，以防止自適應導致的並行無效。

例：

關於PARALLEL_ADAPTIVE_MULTI_USER參數，可以參考在線文檔：

Database Reference PARALLEL_ADAPTIVE_MULTI_USER

諮詢案例：

在筆者的經驗中，曾遇到過以下的幾個諮詢案例，在這裡對調查方法和結果進行一些分享。

SE版本並發統計資訊收集是否有效？

雖然並發執行統計資訊收集使用的技術中包括Resource Manager，並且Resource Manager是Enterprise Edition版本才能使用的功能，但是Standard Edition內部的一些動作也會用到Resource Manager功能，所以不明確使用Resource Manager的情況下，也能夠進行並發執行統計資訊收集。通過上面的例子在Standard Edition運行，也可以驗證這個結論。

設定相關內容，並發統計資訊收集卻無效?

有用戶諮詢，為什麼我的環境中設定了並發執行統計資訊收集，查看相關的視圖卻發現，統計資訊收集時並沒有並發執行？這個問題，其實由於進行並發執行時，Oracle內部事實上是有一定臨界值設定的。當這些表很小和其他一些環境因素時，Oracle會合併多個表和分區在一個JOB中批量執行，就會產生統計資訊收集時沒有並發執行的表象。

我們可以通過跟蹤dbms_stats可以查看到相關的一些內容。例：

查看相關的跟蹤文件：

我們可以看到，在DBMS_STATS執行過程中在做一些比較，如果預估值沒有滿足臨界值的話，就會進行批量處理。

※注意：這個臨界值（batching threshold）由多方面因素影響，會根據環境不同而不同。

如何限定僅對一部分表進行並發統計資訊收集？

在過去的諮詢案件中，確實有些客戶希望僅對某Schema的一部分表進行並發統計資訊收集。針對這種需求可以通過DBMS_STATS的「obj_filter_list」參數來實現。

例：

關於這點，詳細可以參考 Maria Colgan-Oracle寫的一個Blog： Oracle Optimizer Blog >How do I restrict concurrent statistics gathering to a small set of tables from a single schema?

參考：

FAQ: Gathering Concurrent Statistics Using DBMS_STATS Frequently Asked Questions (Doc ID 1555451.1)

Database SQL Tuning Guide >Gathering Optimizer Statistics Concurrently

Oracle Optimizer Blog >Concurrent Statistics Gathering >How do I restrict concurrent statistics gathering to a small set of tables from a single schema?

★ White Papers Understanding Optimizer Statistics with Oracle Database 12c – Part 1 Best Practices for Gathering Optimizer Statistics with Oracle Database 12c – Part 2