254
技術社區[雲棲]
藏在表分區統計信息背後的小秘密
曾令軍
雲和恩墨技術專家，8年數據庫運維經驗。思維敏捷，擅長於數據庫開發、解決棘手的數據庫故障和性能問題，在數據庫故障診斷、運維監控、性能優化方麵積累了豐富的經驗。
本文由一個表分區統計信息沒有按預期更新的問題，逐步深入設疑、探因、求實，解開關於表分區統計信息收集的秘密。曲徑通幽處，禪房花木深。讓我們打開數據庫知識的那扇窗，去看到花木濃茂幽靜自然的美好。
案例背景
客戶的業務係統中，做了AB表設計。A代表日間業務表，隻存放一天的交易數據；B代表曆史數據表，每天一個分區。每天晚上A表的所有數據會轉入B表的最大分區中，然後B表的最大分區分裂成當天分區和新的最大分區。
在做完上述數據轉換及分區拆分之後，雖然此時P_20170101這個分區有大量的數據，雖然自動收集統計信息的任務每天都會運行，但這個分區的統計信息始終為0。
【問題】：在數據庫自動收集統計信息任務運行後，分區P_20170101的統計信息錯誤，導致執行計劃選擇錯誤，與曆史數據有關聯的查詢運行特別緩慢。
場景模擬
創建曆史數據表，按交易日期做範圍分區：
插入10000行數據後提交，模擬業務表數據轉入曆史表：
檢查分區的統計信息：
此時雖然插入了數據，但沒有手動或自動收集過，因此統計信息仍然為空。
且讓它保持為空，並在這個前提下，繼續往下做。
接下來拆分分區，然後再次檢查分區的統計信息：
拆分完成之後，原分區PEVER和新分區P20170821此時的num_rows均為空。
調用自動收集統計信息任務的過程，然後檢查分區的統計信息：
小貼士：dbms_stats.gather_database_stats_job_proc過程就是自動收集統計信息任務執行的程序，此處手工調用，模擬客戶數據庫每天自動收集任務的運行。該過程相當於使用gather auto選項調用了dbms_stats.gather_database_stats過程
注意此時統計的P20170821記錄數為10000行，統計信息完全正確！並沒有模擬出與客戶問題相符的現象。
繼續插入20000行數據，但日期換成20170822，模擬第二天的交易：
拆分分區，然後檢查分區的統計信息：
注意這裏的差異，新分區P20170822的num_rows並不是空，而是0。
調用自動收集統計信息任務的過程，然後檢查分區的統計信息：
問題模擬出來了。新分區P20170822實際上有20000行數據，但自動收集的任務運行後，該分區的行數仍然是0。
模擬過程中引出來的問題：
1） 為什麼拆分分區的初始化統計信息開始是空，而後麵又變成 0？
2） 為什麼分區的統計信息為空時，自動收集任務運行後，統計信息更新了？
3） 為什麼分區的統計信息為0時，這個分區有大量數據，而統計信息始終不更新？
4） PEVER分區一開始是空，先插入了20000行，然後數據又分裂出去，重新變回一個空分區，為什麼它的統計信息又更新了？
後三個問題，都指向了同一個問題：自動收集任務運行時，哪些對象被收集？
撥開迷霧
問：為什麼拆分分區的初始化統計信息開始是空，而後麵又變成0？
答：分區分裂時，新分區的統計信息繼承了原分區的統計信息值
第一次分裂的時候，由於PEVER分區的統計信息為空，因此分裂出來的P20170821和新的PEVER分區初始的統計信息也為空；第二次分裂的時候，由於此時PEVER分區的統計信息被收集過，變成了 0行，那麼分裂出來的P20170821和新的PEVER分區的初始統計信息就是0行。那假如PEVER分區是10000行，新分區也會是10000行，依此類推。
問：自動收集任務運行時，哪些對象被收集？
答：存在缺失和陳舊的統計信息的表、索引、分區
自動收集任務運行時,優先收集缺失統計信息的對象，然後再收集陳舊統計信息的對象。缺失或陳舊統計信息的對象，可以從dba_tab_statistics和dba_ind_statistics這兩個視圖中查詢。
問：如何判斷對象的統計信息為陳舊？
答：表或分區的數據變化量超過10% 
控製統計信息是否陳舊的數據變化量的比例默認為10%，可通過dbms_stats.get_prefs這個函數查詢，這個值也可通過DBMS_STATS.SET_GLOBAL_PREFS修改，但一般不建議這樣做。
問題分析到這裏，之前提出來的四個問題的答案就已經全部解開了。
為什麼拆分分區的初始化統計信息開始是空，而後麵又變成 0 ？
----分裂出來的分區繼承原分區的統計信息
為什麼分區的統計信息為空時，自動收集任務運行後，統計信息更新了？
----缺失統計信息的對象，會被收集
為什麼分區的統計信息為0時，這個分區有大量數據，而統計信息始終不更新？
----不滿足缺失或陳舊的條件，不被收集
PEVER分區一開始是空，先插入了20000行，然後數據又分裂出去，重新變回一個空分區，為什麼它的統計信息又更新了？
----陳舊統計信息的對象，會被收集
那麼這個案例如何優化呢？有兩種選擇都可以解決問題:
a) 修改程序邏輯順序：先插入數據再SPLIT分區 修改為 先SPLIT分區再插入數據
b) 手動補充收集一次：原業務邏輯不變，但操作完之後，對split出來的分區單獨收集
知識擴展
莎士比亞說：大海有崖岸,熱烈的愛卻沒有邊界。雖然問題已經解決，仔細思考，還有更多細致的問題在等著我們去探索：
1、如何查詢對象的數據變化量？
2、對象的數據變化是如何刷新的？
3、分區表統計信息的更新機製？如果整個分區表的數據更新，會不會掃描那些沒有發生數據變化的分區呢？
如何查詢對象的數據變化量？
oracle提供了一個名為USER_TAB_MODIFICATIONS的視圖，可以查詢到分區以及分區表的DML操作次數，例如：
select * from dba_tab_modifications wheretable_name='T_HISTDATA_PART';
這個視圖還能查詢到對象被truncate的次數。但是存在一個問題：數據修改之後，並不能馬上在視圖中查詢到，需要手工刷新：
begin
dbms_stats.flush_database_monitoring_info();
end;
數據修改不能立即刷新的原因，就是下麵要討論的問題。
對象的數據變化是如何刷新的？
USER_TAB_MODIFICATIONS的刷新機製
在10G之後，USER_TAB_MODIFICATIONS視圖的數據並不能立即更新，而是每天隻更新一次，因此需要通過這個視圖準確查詢到數據變化時，需要先手工刷新。
分區表統計信息的更新機製？
當分區的數據變化達到10%，自動收集統計信息任務運行時，會更新該分區的統計信息。
當分區表中所有分區中數據變化量的總和達到分區表總數據量的10%，會更新該分區表的統計信息。
分區表的統計信息收集更新時，以前必須要掃描該表所有的分區或整個表的數據，在10.2.0.5版本之後，可以設置分區表按增量變化統計，隻收集有數據變化的分區。
要設置分區表按增量變化統計，可以設置表統計信息的incremental屬性。
【學以致用】：對於一些數據量特別大的分區表，可以考慮設置INCREMENTAL=TRUE屬性，能夠顯著提升分區表統計信息收集的速度。
不聞不若聞之，聞之不若見之，見之不若知之，知之不若行之。通過一個問題的深入剖析，逐層推進，我們看見了、理解了、實踐了，最終也收獲了。以上這些知識要點，在運維實戰中，具備非常有價值的指導意義。
原文發布時間為：2017-09-19
作者：曾令軍
本文來自雲棲社區合作夥伴“數據和雲”，了解相關信息可以關注“數據和雲”微信公眾號
最後更新：2017-09-20 14:03:07