【DB筆試面試644】在Oracle中，如何並發地收集統計信息？

• 2019 年 10 月 10 日
• 筆記

題目部分

在Oracle中，如何並發地收集統計信息？

♣

答案部分

對於大表的統計信息收集可以通過DEGREE參數使得掃描大表的時候進行並行掃描，從而加快掃描速度，縮短了收集統計信息的時間。但是，即使加了DEGREE參數，在收集統計信息的時候，還是進行一個表一個表的掃描，並沒有並發的同時掃描多個表。在Oracle 11.2.0.2之後，有了一個參數，可以並發掃描表，這就是CONCURRENT參數。可以通過以下SQL語句查詢數據庫是否啟用了CONCURRENT收集統計信息，默認為FALSE，表示沒有開啟並發收集統計信息：

SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;  

開啟方式為:

EXEC  DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');  

開啟CONCURRENT之後，收集統計信息就會以並發的形式進行，會並發出多個JOB進程。在並發收集統計信息時，數據庫生成的JOB數會根據具體情況來分配。在大多數情況下，DBMS_STATS程序會給每個對象分配一個JOB；但如果對象（表或者分區）的大小太小，為了節省資源，Oracle會合併多個表和分區在一個JOB中執行。為了防止同時處理多個分區表的分區時發生死鎖，所以，對於分區表的處理機制是每次只能處理一個分區表，其它的分區表需要等待，待前一個分區表處理完後再處理下一個。在Oracle 11.2.0.2到11.2.0.4的版本上，CONCURRENT可取的值為TRUE（開啟並發）和FALSE（關閉並發）。在Oracle 12c的版本上，可以設置以下的值：

MANUAL：只有當手動收集時，並發有效  AUTOMATIC：只有當自動收集時，並發有效  ALL：當手動/自動收集，並發都有效  OFF：並發無效  

可以通過以下的視圖，對並發統計信息收集進行監視：

DBA_OPTSTAT_OPERATION_TASKS：當前和歷史的統計信息收集的執行任務  DBA_OPTSTAT_OPERATIONS：當前和歷史的統計信息收集的執行操作  DBA_SCHEDULER_JOBS：SCHEDULER JOBS信息  

監控並發收集統計信息JOB的SQL代碼如下：

SELECT JOB_NAME, STATE, COMMENTS  FROM DBA_SCHEDULER_JOBS  WHERE JOB_CLASS LIKE 'CONC%';  SELECT STATE,COUNT(*)  FROM DBA_SCHEDULER_JOBS  WHERE JOB_CLASS LIKE 'CONC%'  GROUP BY STATE;  

對於並發收集統計信息需要注意如下幾點：

（1）用CONCURRENT收集統計信息，需要收集統計信息的用戶具有CREATE JOB、MANAGE SCHEDULER和MANAGE ANY QUEUE權限。即使是該用戶具有了DBA角色，也還是需要顯式授權上述權限。否則執行JOB的時候，可能會報錯：「ORA-27486 insufficient privileges」、「ORA-20000: Statistics collection failed for 32235 objects in the database」。

（2）因為CONCURRENT不能控制並發度的大小，所以，如果數據庫的初始化參數JOB_QUEUE_PROCESSES設置的太高（在Oracle 11.2.0.3之後，這個值的默認值是1000，所以就可能並發出1000個JOB），那麼對數據庫的性能影響較大。所以開啟CONCURRENT的另外一個建議就是使用Resource Manager來控制資源的使用。

（3）下表列出了並發和並行在收集統計信息方面的一些區別：

& 說明：

有關並發地收集統計信息的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2134312/

本文選自《Oracle程序員面試筆試寶典》，作者：小麥苗