成為MySQL DBA後，再看ORACLE資料庫（十四、統計資訊與執行計劃）

海布里_MySQL發表於2024-08-14
MySqlOracle資料庫

一、前言

一條SQL到達資料庫核心之後，會解析為一條邏輯執行計劃，CBO最佳化器對邏輯計劃進行改寫和轉換，生成多個物理執行計劃。為SQL構造出搜尋空間，根據資料的統計資訊、基數估計、運算元代價模型為搜尋空間中的執行計劃估算出執行所需要的代價（CPU、記憶體、網路、I/O 等資源消耗），最終選出代價最小的執行計劃作為SQL的具體執行方式。因此，為了保證SQL的最佳執行效能，資料庫需要找到一個最優的執行計劃，生成執行計劃的最佳化器必須要知道最新的統計資訊，例如條數，block數量，某個欄位的選擇率等。

二、硬解析與軟解析

一條SQL只有經過解析後才能生成執行計劃，在ORACLE中，解析分為硬解析和軟解析。硬解析（Hard Parse）是指Oracle在執行目標SQL時，在share pool的庫快取（Library Cache）中找不到可以重用的解析樹和執行計劃，而不得不從頭開始解析目標SQL並生成相應的父遊標（Parent Cursor）和子游標（Child Cursor）的過程。軟解析（Soft Parse)是指Oracle在執行目標SQL時，在Library Cache中找到了匹配的父遊標（Parent Cursor）和子游標（Child Cursor），並將儲存在子游標中的解析樹和執行計劃直接拿過來重用而無須從頭開始解析的過程。建立解析樹、生成執行計劃對於SQL的執行來說是開銷昂貴的動作，所以，應當極力避免硬解析，儘量使用軟解析。這就是在很多專案中，倡導開發設計人員對功能相同的程式碼要儘量保持程式碼的一致性，以及要在程式中多使用繫結變數的原因。

三、統計資訊

透過前面硬解析和軟解析的介紹可以知道ORACLE資料庫中SQL的執行計劃是可以快取在share pool裡面，執行計劃是根據資料庫物件的統計資訊生成的，最佳化器為了產生最佳的執行計劃，依賴於物件統計資訊。統計資訊主要是描述資料庫中表，索引的大小，規模，資料分佈狀況等資訊。統計資訊包括表統計資訊、索引統計資訊、列統計資訊、系統統計資訊。借用Jieke Xu老師的這張圖很清楚的描述了統計資訊是什麼：

那在ORACLE中統計資訊又存在哪呢？或者說我們如何去查詢ORACLE的統計資訊呢？統計資訊都是存放在資料字典中，透過dba_tables、dba_tab_statistics、dba_tab_columns、dba_tab_col_statistics、dba_indexes、dba_ind_statistics等資料字典檢視可以查詢ORACLE的表、列和索引的統計資訊。而系統統計資訊存放在系統表sys.aux_stats$中。

查詢某個表的統計資訊：
SELECT D.NUM_ROWS, --表中的記錄數
      D.BLOCKS, --轟中資料所佔的資料塊數
      D.EMPTY_BLOCKS, --表中的空塊數
      D.AVG_SPACE, --資料塊中平均的，使用空間
      D.CHAIN_CNT, --表中行連線和行遷移的數量
      D.AVG_ROW_LEN, --每條記錄的平均長度
      D.STALE_STATS, --統計資訊是否過期
      D.LAST_ANALYZED --最近一次蒐集統計資訊的時間
 FROM DBA_TAB_STATISTICS D  --DBA_TAB_STATISTICS DBA_TABLES
WHERE D.TABLE_NAME = 'ATBWSL';

查詢表上列的統計資訊：
SELECT D.COLUMN_NAME,
      D.NUM_DISTINCT, --唯一值的個數
      D.LOW_VALUE, --列上的最小值
      D.HIGH_VALUE, --列上的最大值
      D.DENSITY, --若不存在柱狀圖的話,則表示選擇率因子（密度）=1/(NDV)
      D.NUM_NULLS, --空值的個數
      D.NUM_BUCKETS, --直方圖的BUCKETS個數
      D.HISTOGRAM, --直方圖的型別
      D.LAST_ANALYZED --最近一次蒐集統計資訊的時間
 FROM DBA_TAB_COLUMNS D  --DBA_TAB_COL_STATISTICS
WHERE TABLE_NAME = 'ATBWSL';

四、自動收集統計資訊

在ORACLE 11g中會預設開啟統計資訊的自動收集功能，每天會透過定時任務收集普通物件和資料字典的統計資訊。透過dba_autotask_client檢視可以檢視自動收集統計資訊的任務及狀態，其中"auto optimizer stats collection"便是要尋找的自動收集統計資訊的任務名稱，它的狀態目前是啟用狀態。

透過dba_scheduler_windows檢視可以檢視統計資訊的自動收集情況。可以看到Oracle 11g的預設的維護視窗配置覆蓋了以下時間段：每個工作日的晚上10點到第二天凌晨2點，持續4小時；每個週六上午6點到週日凌晨2點，週日上午6點到週一凌晨2點，都是持續20個小時。實際應用中可以根據業務情況把自動收集統計資訊的任務調整到業務低峰期，比如透過以下語句將週一的自動收集統計資訊的任務時間調整到1點開始，持續5個小時：

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=1;byminute=0;bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'duration', value => '0 05:00:00');
end;

那麼自動收集統計資訊任務執行時，所有的物件都會被收集嗎？答案是否定的，在自動收集統計資訊時，優先收集缺失統計資訊的物件，然後收集陳舊統計資訊的物件。可以從DBA_TAB_STATISTICS和DBA_IND_STATISTICS這兩個檢視中查詢缺失或陳舊統計資訊的物件。LAST_ANALYZED IS NULL表示統計資訊缺失，STALE_STATS='YES'表示統計資訊陳舊。當表或分割槽的資料變化量超過10%或者執行過truncate操作，該物件的統計資訊則變為陳舊。在ORACLE 11g中，這個10%可以根據表的大小在表級別修改。

禁用自動收集統計資訊的任務可以使用DBMS_AUTO_TASK_ADMIN包完成：
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
啟用自動收集統計資訊的任務：
exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

五、手動收集統計資訊

手動收集統計資訊常用於對錶做了大批次的DML，或者資料庫剛遷移完、還沒到自動統計資訊蒐集的時候。在ORACLE中一般透過呼叫dbms_stats包手動收集統計資訊。

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TB_NAME',CASCADE=>TRUE,DEGREE=>8);  #收集使用者下面某個表的統計資訊，cascace表示是否收集列和索引的統計資訊，DEGREE設定收集統計資訊的並行性
EXEC DBMS_STATS.GATHER_DATABASE_STATS();  #收集當前資料庫下所有使用者的統計資訊
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(USER);  #收集使用者下所有物件的統計資訊
EXEC DBMS_STATS.GATHER_INDEX_STATS(USER,'INDEX_NAME');  #單獨收集某個索引的統計資訊

而系統統計資訊收集則是透過呼叫DBMS_STATS.GATHER_SYSTEM_STATS包，一般有以下兩種方法：

EXEC DBMS_STATS.GATHER_SYSTEM_STATS('start');
系統正常執行負載一段時間
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('stop');
或
EXEC DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE=>INTERVAL,INTERVAL=>10)  #INTERVAL為間隔時長，單位為分鐘

六、固定執行計劃

透過前面的總結可以知道，最佳化器會根據最佳化器統計資訊來生成“它認為最好的”執行計劃，但是並不能夠保證每一次都會選擇最優的哪個執行計劃。如果遇到了sql語句在某段時間內執行效能變差，並且發現它的執行計劃發生了變化，那麼可以考慮固定那個“執行效能好的”時候的執行計劃。在ORALE中推薦使用SQL Plan Management（SQL計劃管理，簡稱SPM）把執行計劃繫結到某個sql_id。

當啟用SPM之後每一個SQL都會存在對應的SQL plan baseline，可以從DBA_SQL_PLAN_BASELINES檢視中檢視目標SQL的所有SQL plan baseline。只有DBA_SQL_PLAN_BASELINES中列ENABLED和ACCEPTED兩個列都是“YES”，SQL plan baseline對應的執行計劃才會被Oracle啟用。透過執行DBMS_SPM.ALTER_SQL_PLAN_BASELINE可以固定SQL的執行計劃，當DBA_SQL_PLAN_BASELINES中FIX列已經變成了YES，則說明該計劃已經被固定。

引數OPTIMIZER_CAPTURE_SQL_PALN_BASELINES用於控制是否啟用自動捕獲SQL plan baseline，預設值是FALSE，不開啟自動捕獲SQL plan baseline。引數OPTIMIZER_USE_SQL_PALN_BASELINES用於控制是否使用SQL plan baseline，預設為TRUE，預設Oracle在生成執行計劃就會生成SPM，使用以後的SQL plan baseline。

相關文章