Oracle 12c數據庫優化器統計信息收集的最佳實踐（二）

原文鏈接 http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf

譯者  沃趣科技 楊禹航

何時收集統計信息

為了選擇最佳執行計劃，優化器必須可以獲得有代表性的統計信息。有代表性的統計數據不必是最新的，而是一組能夠幫助優化器確定執行計劃中每個操作所能返回的行數。

自動統計信息收集任務

Oracle會在預定義維護窗口期間 (工作日10pm 到2am 和周末6am 到2am )收集數據庫中所有缺失統計信息或者統計信息過期對象的統計信息，您可以在Oracle企業管理器或使用DBMS_SCHEDULER和DBMS_AUTO_TASK_ADMIN軟件包來更改維護窗口。

圖3: 更改自動收集統計信息作業運行時間的維護窗口

如果你已經有一個行之有效的統計收集程序，或者您想要禁用自動收集統計信息任務，您可以使用如下命令:

begin

dbms_auto_task_admin.disable(

client_name=>'auto optimizer stats collection',

operation=>null,

window_name=>null);

end;

/

手工統計收集

如果您打算手動維護優化器的統計信息，則需要確定何時去收集統計信息。基于過期的信息您可以決定什么時候統計信息應該被收集，您可以根據統計信息的失效性確定何時收集統計信息，就像自動作業一樣，或者基于您的環境中新數據加載的時間。如果基本的數據沒有明顯變化，則不建議重新收集統計信息，因為這將不必要地浪費系統資源。

如果數據僅在預先定義的 ETL 或 ELT 作業期間加載到您的環境中, 則可以將統計信息收集操作安排為此過程的一部分。

在線收集統計信息

在Oracle Database 12 c中，在線收集統計信息"piggybacks"作為直接路徑數據加載操作的一部分進行收集, 例如, 像使用CTAS的方式創建表，以及IAS方式插入數據。收集統計數據作為數據加載操作的一部分，這意味著不需要額外的全表掃描，就可以在加載數據后立即提供統計信息。

圖4:在線收集統計信息為新創建的sales2表提供表和列的統計信息收集

在線收集統計信息并不會收集直方圖和索引統計信息，因為這些種類的統計信息需要額外的數據掃描，這可能在數據加載時對性能產生較大影響。

如果要收集直方圖或者索引的統計信息，而不重新收集列的基本統計信息， 請使用 DBMS_STATS.GATHER_TABLE_STATS 中新的選擇”OPTIONS”參數設置成GATHER AUTO。

請注意，出于性能原因，GATHER AUTO在生成直方圖時，使用行中的樣本數據而不是表中的所有數據。

 圖5 : 設置選項為GATHER AUTO來創建 SALES2 表上的直方圖, 而不涉及基本統計信息

注意列“HISTOGRAM_ONLY”指示在不重新收集列基本統計信息的情況下收集了直方圖。有兩種方法確認在線收集統計信息是否發生: 一種方式是檢查執行計劃，查看執行計劃中是否出現”OPTIMIZER STATISTICS GATHERING”，另外一種方式是查看USER_TAB_COL_STATISTICS表中notes字段的狀態是否為stats_on_load。

                         圖6:在線統計信息收集操作的執行計劃

在線統計信息收集為了減少對直接路徑加載時性能上的影響，只有在被加載的對象為空時在線統計信息收集才會被觸發。要確保在加載現有表的新分區時進行在線收集統計信息，請使用擴展語法明確指定分區。在這種情況下, 將創建分區級別統計信息, 但不會更新全局級別 (表級別) 統計信息。如果在分區表上啟用增量統計信息，則會在數據加載操作中創建”synopsis”。

可以使用HINT: NO_GATHER_OPTIMIZER_STATISTICS來禁用單條SQL語句去使用在線收集統計信息。

增量統計和分區交換數據加載

對分區表的統計信息收集包括表級別(global)和(sub)分區級別的統計信息。如果分區表的”incremental”首選項設置為true，則DBMS_STATS.GATHER_*_STATS參數中GRANULARITY包含global，ESTIMATE_PERCENT設置成AUTO_SAMPLE_SIZE，Oracle將只掃描那些添加或修改的分區，而不是整個表，從而準確的得出所有全局級別的統計信息。增量全局統計信息通過存儲表中每個分區的“synopsis”來工作，“synopsis”是用于該分區和分區中的列的統計元數據，從而消除了掃描整個表的需要。將分區級別統計信息和每個分區的“synopses”聚合，將準確地生成全局統計信息，從而無需掃描整個表。

當新分區添加到表中時，您只需要收集新分區的統計信息，使用新的分區“synopsis”和現有分區的“synopses”, 將自動、準確地計算表級別的統計信息。

請注意，當啟用增量統計信息時，分區統計信息不會從子分區統計信息進行聚合計算。

如果您正使用分區交換并希望利用增量統計信息，則需要在非分區表上設置DBMS_STATS首選項INCREMENTAL_LEVEL，以確定它將在分區交換過程中使用。設置INCREMENTAL_LEVEL為TABLE，當在其上收集統計信息，Oracle會自動創建“synopsis”，此表級的“synopsis”在分區交換后會變成分區級別的“synopsis”。

但是，如果一天當中在您的環境存在很多插入少量數據的在線事務，您需要確定統計數據何時過期，然后觸發自動統計數據收集任務。

如果您計劃依賴user _ tab _ statistics中的stale_stats列來確定統計信息是否過期，則應注意此信息僅按每日更新。

如果您需要及時地了解在您的表上發生了哪些DML, 您需要查看USER_TAB_MODIFICATIONS 表, 其中列出了在每個表上發生的INSERT、UPDATE和DELETE的數量, 該表是否已被TRUNCATED (TRUNCATE column) 并自行計算是否過期。

再次，您應該注意到此信息是自動從內存中更新而來，如果需要最新的信息，則需要使用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO來手動刷新信息。

防止“超出范圍”條件

無論您使用自動統計信息收集任務還是手動收集統計信息，如果最終用戶在收集統計信息之前開始查詢新插入的數據，即使表中不到10%的行被更改，也可能會由于陳舊的統計信息而獲得次優執行計劃，其中最常見的情況之一發生在當 where 子句謂詞中提供的值超出 [最小、最大] 列統計的值域時，就會發生這種情況。這通常被稱為“范圍外”錯誤。在這種情況下，優化器根據謂詞值之間的距離和最大值(假設值高于最大值)對選擇性進行按比例分配，即，最大值或最小值，從而降低選擇性。

此場景與范圍分區表非常常見。一個新的分區被添加到一個現有的范圍分區表中，行被插入到這個分區中。在此新分區收集統計信息之前用戶開始查詢此新數據。對于分區表，可以使用DBMS_STATS.COPY_TABLE_STATS(從Oracle數據庫10.2.0.4開始)，以防止“超出范圍”條件。此過程將具有代表性的源 [sub] 分區的統計信息復制到新創建的和空的目標 [子] 分區中。

它還會拷貝相關對象的統計信息:列、本地(分區)索引等。并將最高界限值作為分區列的最大值和前一個分區的最高界限值作為該分區列的最小值。

拷貝的統計信息應該僅視為臨時解決方案，直到可以收集分區的準確統計信息。不應該將拷貝的統計信息用作實際收集統計信息的替代方法。

默認情況下，DBMS_STATS.COPY_TABLE_STATS僅用來調整分區統計信息，而不是全局或表級別統計信息。如果希望將分區列的全局級別統計信息作為副本的一部分進行更新, 則需要將 DBMS_STATS.COPY_TABLE_STATS 的參數設置為8。

對于非分區表，可以使用DBMS_STATS.SET_COLUMN_STATS手動設置列的最大值。但是 一般不建議使用這種方法來代替實際收集統計信息。

提高統計信息質量

良好質量的統計信息對生成最佳的SQL執行計劃是至關重要的，但是有時統計信息的質量很差，而這種情況可能會被忽視。例如，“通過繼承得到的”系統可能使用被數據庫管理員所不能理解的腳本，或者可以理解但是卻不愿意更改它們。但是，由于Oracle不斷提高統計數據收集功能，因此可能忽略最佳做法和建議。出于這些原因，Oracle數據庫12cR2包含了一個新的優化統計顧問，來幫助您提高數據庫中的統計信息的質量。該優化統計顧問分析數據字典中的信息，評估統計數據的質量，并發現如何收集統計信息。它將報告質量較差和缺失的統計數字，并提出解決這些問題的建議。

其操作的原則是應用最佳實踐規則來發現潛在的問題。這些問題作為一系列結果被報告，反過來又可以產生具體的建議。這些建議可以通過操作自動實現(立即或通過自動生成的腳本由數據庫管理員執行)。

圖7: 優化統計顧問

優化統計顧問任務是在維護窗口中自動運行，但也可以按需運行。可以隨時查看由優化統計顧問生成的html或文本報告，并且可以隨時執行進行操作。圖8舉例說明了一個特定規則的示例，它引出了查找、建議和解決問題的操作：

圖8:規則、查找、建議和操作的示例。

優化統計顧問收集并將信息存儲在數據字典中。它的性能開銷較低，因為它對收集的統計信息 (已經保存在數據字典中) 進行分析，并且不對已經存儲在應用 Schema中對象的統計信息進行二次分析。

圖9:讀取數據字典，通過過濾器執行任務并存儲結果

任務完成后，可以用html或文本格式生成報告，也可以創建操作( SQL)腳本。

圖10:報告advisor任務和生成操作SQL腳本。

查看自動化任務生成的報表非常簡單：

select dbms_stats.report_advisor_task('auto_stats_advisor_task') as report from dual;

或者，具有使用顧問權限的用戶可以手動執行任務，并使用以下三個步驟報告結果:

DECLARE

tname VARCHAR2(32767) := 'demo'; -- task name

BEGIN

tname := dbms_stats.create_advisor_task(tname);

END;

/

DECLARE

tname VARCHAR2(32767) := 'demo'; -- task name

ename VARCHAR2(32767) := NULL; -- execute name

BEGIN

ename := dbms_stats.execute_advisor_task(tname);

END;

/

SELECT dbms_stats.report_advisor_task('demo') AS report

FROM dual;

優化統計顧問生成的操作可以立即實施:

DECLARE

tname VARCHAR2 (32767) := 'demo'; -- task name

impl_result CLOB; -- report of

implementation

BEGIN

impl_result := dbms_stats.implement_advisor_task(tname);

END;

/

此外, Oracle12c Real Application Testing還包括實用的性能保證功能，如 SQL 性能顧問快速檢查。見 Oracle 白皮書，《Database 12c Real Application Testing Overview》有關更多詳細信息 (請參見21頁上的參考資料)。

快速收集統計信息

隨著數據的增長和維護窗口縮減，及時收集統計信息比以往任何時候都更重要。Oracle提供了各種加速統計數據收集的方法，從并行化統計信息收集到生成統計信息而不是收集統計信息。

使用并行法

可以通過幾種方式利用并行性來進行統計收集

? 使用DEGREE參數

? 并發統計收集

? 結合DEGREE和并發收集

使用并行參數

DBMS_STATS中的”DEGREE”參數用于控制收集統計信息時并行執行進程的數量。默認情況下，Oracle使用與數據字典中表的屬性(并行度)指定的相同數量的并行服務器進程。Oracle數據庫中的所有具有此屬性的表都默認設置為1，為了加快統計信息的收集，可以在對大表進行收集統計信息時顯示指定該參數，或者你可以設置degree為auto_degree；Oracle將根據對象的大小自動確定應該用于收集統計信息的適當的并行服務器進程數量。該值可以介于 1 (串行執行)(小對象) 到 DEFAULT_DEGREE (PARALLEL_THREADS_PER_CPU XCPU_COUNT) (較大的對象)。

圖11: 通過DEGREE參數使用并行性

您應該注意，設置分區表的DEGREE意味著多個并行服務器進程將用于收集每個分區的統計信息，但不會在不同分區上同時收集統計信息。統計信息將在每個分區上收集一次。 

并發統計收集

并發統計信息收集功能可以在 Schema (或Database) 中的多個表上和多個 (子) 分區中并發的進行統計信息的收集。通過讓Oracle充分利用多處理器環境，同時去收集多個表和(sub)分區的統計信息可以減少收集統計數據所需的總體時間。

并發統計信息收集是由全局選項CONCURRENT控制，可以設置MANUAL,AUTOMATIC, ALL, OFF，默認為OFF。當CONCURRENT被啟用時，Oracle使用作業調度器和高級隊列組件來創建和管理多個統計信息收集作業并發的執行。

調用DBMS_STATS.GATHER_TABLE_STATS在分區表上, 當 CONCURRENT 設置為MANUAL時，Oracle會為表中的每個(sub)分區創建單獨的統計信息收集作業。其中有多少個作業并發執行，多少作業在隊列中排隊，是根據可用作業隊列的進程數量 (JOB_QUEUE_PROCESSES 初始化參數、RAC 環境中的每個節點)和可用的系統資源來決定的。隨著正在運行的作業完成，更多的作業將出現并執行，直到所有（子）分區都收集了其統計信息。

如果您使用DBMS_STATS.GATHER_DATABASE_STATS、DBMS_STATS.GATHER_SCHEMA_STATS或者DBMS_STATS.GATHER_DICTIONARY_STATS收集統計信息，那么Oracle將為每個非分區表以及分區表的每個（子）分區創建一個單獨的統計信息收集作業。每個分區表還將有一個協調作業，用于管理其(sub)分區作業。然后，數據庫將運行盡可能多的并發作業，并對其余作業進行排隊，直到作業執行完成。但是，為了防止可能的死鎖情況，不能同時處理多個分區表。因此, 如果已為已分區表運行了某些作業, 則Schema (或數據庫或字典) 中的其他分區表將排入隊列, 直到當前作業完成。對非分區表則沒有這種限制。

圖12顯示了DBMS_STATS.GATHER_SCHEMA_STATS在不同級別創建作業。在Schema:sh上Oracle將為每個非分區表創建一個統計收集作業(圖12中的級別1)；      

? CHANNELS

? COUNTRIES

? TIMES

Oracle將為每個分區表創建一個協調作業: SALES和COSTS，然后分別為SALES和COSTS表中的每個分區創建一個統計數據收集作業(圖12中的級別2)。  

圖12:在sh上的并發統計信息收集時的作業列表

如果指定了DEGREE參數，每個單獨的統計數據收集作業也可以利用并行執行。如果表、分區表或子分區表非常小或為空，則Oracle可以自動將對象與其他小對象合成到一個單獨作業中，以減少作業維護的開銷。

配置并發統計數據收集

默認情況下，統計數據收集的并發設置關閉。它可以按照如下操作打開:

exec dbms_stats.set_global_prefs('concurrent', 'all')

您還需要一些額外的特權和收集統計信息所需的常規權限。用戶必須具有以下Job Scheduler和AQ權限:

? CREATE JOB

? MANAGE SCHEDULER

? MANAGE ANY QUEUE

sysaux表空間應該處于在線狀態，因為作業程序在sysaux表空間中存儲其內部表和視圖。最后，JOB_QUEUE_PROCESSES參數應該設置為充分利用可用于統計收集過程的所有系統資源。如果您不計劃使用并行執行，則應將job _ queue _process設置為2*CPU核心總數(在RAC環境中為每個節點)。請確保在系統級別設置此參數 ( alter system...或在init.ora文件中)而不是在會話級別上( alter session)設置。

如果要將并行執行作為并發統計信息收集的一部分，則應該禁用并行自適應多用戶:

ALTER SYSTEM SET parallel_adaptive_multi_user=false;

資源管理器也必須被激活，例如:

ALTER SYSTEM SET resource_manager_plan = 'DEFAULT_PLAN';

還建議啟用并行語句隊列。這需要激活資源管理器，并創建臨時資源計劃，并且其中的消費者組“others_groups”已啟用。

默認情況下，資源管理器僅在維護窗口期間激活。以下腳本說明了創建臨時資源計劃( pqq _ test)的一種方法，并使資源管理器能夠執行此計劃。

-- connect as a user with dba privileges

begin

dbms_resource_manager.create_pending_area();

dbms_resource_manager.create_plan('pqq_test', 'pqq_test');

dbms_resource_manager.create_plan_directive(

'pqq_test',

'OTHER_GROUPS',

'OTHER_GROUPS directive for pqq',

parallel_target_percentage => 90);

dbms_resource_manager.submit_pending_area();

end;

/

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'pqq_test' SID='*';

如果希望自動統計數據收集任務利用并發性，請將CONCURRENT設置為AUTOMATIC或ALL。在維護窗口中使用的資源管理器計劃中添加了一個新的 AUTOTASK $ 使用者組, 以確保并發統計信息的收集不會使用太多的系統資源。