【DB筆試面試628】Oracle的統計資訊包括哪幾種類型？
- 2019 年 10 月 10 日
- 筆記
題目部分
Oracle的統計資訊包括哪幾種類型？
♣
答案部分
Oracle資料庫里的統計資訊是一組存儲在數據字典里，且從多個維度描述了資料庫里對象的詳細資訊的一組數據。當Oracle資料庫工作在CBO（Cost Based Optimization，基於代價的優化器）模式下時，優化器會根據數據字典中記錄的對象的統計資訊來評估SQL語句的不同執行計劃的成本，從而找到最優或者是相對最優的執行計劃。所以，可以說，SQL語句的執行計劃由統計資訊來決定，若沒有統計資訊則會採取動態取樣的方式來生成執行計劃。統計資訊決定著SQL的執行計劃的正確性，屬於SQL執行的指導思想。若統計資訊不準確，則會導致表的訪問方式（例如應該使用索引，但是選擇了全表掃描）、表與表的連接方式出現問題（例如應該使用HJ，但是使用了NL連接），從而導致CBO選擇錯誤的執行計劃。
統計資訊主要包括6種類型，其中表、列和索引的統計資訊也可以統稱為普通對象的統計資訊，如下所示：
查詢表統計資訊的SQL如下所示：
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 = 'CUSTOMERS';
查詢表上列的統計資訊的SQL如下所示：
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 --直方圖的類型 FROM DBA_TAB_COLUMNS D --DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = 'CUSTOMERS';
關於上表中需要注意的幾點：
（一）索引統計資訊
BLEVEL存儲的就是目標索引的層級，它表示的是從根節點到葉子塊的深度，BLEVEL被CBO用於計算訪問索引葉子塊的成本。BLEVEL的值越大，則從根節點到葉子塊所需要訪問的數據塊的數量就會越多，耗費的I/O就會越多，訪問索引的成本就會越大。BLEVEL的值從0開始算起，當BLEVEL的值為0時，表示該B樹索引只有一層，且根節點和葉子塊就是同一個塊。在Oracle資料庫里，如果要降低目標B樹索引的層級，那麼只能通過REBUILD該索引的方式來實現。
（二）列的統計資訊
列的統計資訊用於描述Oracle資料庫里列的詳細資訊，包含了列的DISTINCT值的數量、列的NULL值的數量、列的最小值、列的最大值等一些典型維度。這些列統計資訊實際上是存儲在數據字典基表SYS.HIST_HEAD$中，可以通過數據字典DBA_TAB_COL_STATISTICS、DBA_PART_COL_STATISTICS和DBA_SUBPART_COL_STATISTICS來分別查看錶、分區表的分區和分區表的子分區的列統計資訊。在這些數據字典中的欄位NUM_DISTINCT存儲的就是目標列的DISTINCT值的數量。CBO用NUM_DISTINCT的值來評估用目標列做等值查詢的可選擇率（Selectivity）。CBO會用NUM_NULLS的值來調整對有NULL值的目標列做等值查詢的可選擇率。
數據字典中的欄位DENSITY和NUM_BUCKETS分別存儲的是目標列的密度和所用桶的數量，這兩個維度僅和直方圖有關。在沒有直方圖統計資訊時，DENSITY的值就等於I/NUM_DISTINCT；在有頻率直方圖的時，DENSITY的值就等於1/(2*(NUM_ROWS-NUM_NULLS))。示例如下：
CREATE TABLE T_MD_20170606_LHR AS SELECT ROWNUM ID,ROWNUM SAL FROM DUAL CONNECT BY LEVEL<=10000; UPDATE T_MD_20170606_LHR SET SAL=5000 WHERE SAL BETWEEN 6 AND 9995; --9990 UPDATE T_MD_20170606_LHR SET SAL='' WHERE SAL BETWEEN 2 AND 3; --2
在無直方圖的情況下：
LHR@orclasm > EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_MD_20170606_LHR',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1'); PL/SQL procedure successfully completed. LHR@orclasm > SET LINESIZE 120 LHR@orclasm > SELECT D.COLUMN_NAME,D.NUM_DISTINCT,D.NUM_NULLS,D.NUM_BUCKETS,D.HISTOGRAM,D.DENSITY FROM DBA_TAB_COLUMNS D WHERE D.TABLE_NAME = 'T_MD_20170606_LHR'; COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_BUCKETS HISTOGRAM DENSITY ------------------------------ ------------ ---------- ----------- --------------- ---------- SAL 9 2 1 NONE .111111111 ID 10000 0 1 NONE .0001 LHR@orclasm > SELECT 1/9,1/10000 FROM DUAL; 1/9 1/10000 ---------- ---------- .111111111 .0001
在有直方圖的情況下：
LHR@orclasm > EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_MD_20170606_LHR',CASCADE=>TRUE,METHOD_OPT=>'FOR COLUMNS SAL SIZE 9'); PL/SQL procedure successfully completed. LHR@orclasm > SELECT D.COLUMN_NAME,D.NUM_DISTINCT,D.NUM_NULLS,D.NUM_BUCKETS,D.HISTOGRAM,D.DENSITY FROM DBA_TAB_COLUMNS D WHERE D.TABLE_NAME = 'T_MD_20170606_LHR'; COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_BUCKETS HISTOGRAM DENSITY ------------------------------ ------------ ---------- ----------- --------------- ---------- SAL 9 2 9 FREQUENCY .00005001 ID 10000 0 1 NONE .0001 LHR@orclasm > SELECT 1/(2*(10000-2)) FROM DUAL; 1/(2*(10000-2)) --------------- .00005001
數據字典中的欄位LOW_VALUE和HIGH_VALUE分別存儲的就是目標列的最小值和最大值，CBO用LOW_VALUE和HIGH_VALUE來評估對目標列做範圍查詢時的可選擇率。不過這兩個欄位的返回值是RAW類型的，需要轉換後才能識別。可以使用UTL_RAW.CAST_TO_NUMBER、UTL_RAW.CAST_TO_VARCHAR2等函數來轉換，也可以使用存儲過程DBMS_STATS.CONVERT_RAW_VALUE來轉換，下面給出示例：
CREATE OR REPLACE FUNCTION FUN_DISPLAY_RAW_LHR(P_RAWVAL RAW,P_TYPE VARCHAR2) RETURN VARCHAR2 IS V_NUMBER NUMBER; V_VARCHAR2 VARCHAR2(32); V_DATE DATE; V_NVARCHAR2 NVARCHAR2(32); V_ROWID ROWID; V_CHAR CHAR(32); BEGIN IF (P_TYPE = 'NUMBER' OR P_TYPE = 'FLOAT') THEN DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_NUMBER); RETURN TO_CHAR(V_NUMBER); ELSIF (P_TYPE = 'VARCHAR2') THEN DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_VARCHAR2); RETURN TO_CHAR(V_VARCHAR2); ELSIF (P_TYPE = 'DATE' OR P_TYPE LIKE 'TIMESTAMP%') THEN DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_DATE); RETURN TO_CHAR(V_DATE); ELSIF (P_TYPE = 'NVARCHAR2') THEN DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_NVARCHAR2); RETURN TO_CHAR(V_NVARCHAR2); ELSIF (P_TYPE = 'ROWID') THEN DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_ROWID); RETURN TO_CHAR(V_ROWID); ELSIF (P_TYPE = 'CHAR') THEN DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_CHAR); RETURN TO_CHAR(V_CHAR); ELSIF (P_TYPE = 'RAW') THEN RETURN TO_CHAR(P_RAWVAL); ELSE RETURN 'UNKNOWN DATATYPE!'; END IF; EXCEPTION WHEN OTHERS THEN RETURN 'ERRORS!'; END FUN_DISPLAY_RAW_LHR;
使用該函數查詢：
CREATE TABLE T_AA_20170606_LHR AS SELECT * FROM DBA_OBJECTS; EXEC DBMS_STATS.gather_table_stats(USER,'T_AA_20170606_LHR'); SELECT D.COLUMN_NAME, D.LOW_VALUE, D.HIGH_VALUE, D.DENSITY, D.NUM_DISTINCT, D.NUM_NULLS, D.NUM_BUCKETS, D.HISTOGRAM, D.DATA_TYPE, FUN_DISPLAY_RAW_LHR(D.LOW_VALUE, D.DATA_TYPE) LOW_VALUE1, FUN_DISPLAY_RAW_LHR(D.HIGH_VALUE, D.DATA_TYPE) HIGH_VALUE1--, --UTL_RAW.CAST_TO_NUMBER(D.LOW_VALUE) LOW_VALUE2, --UTL_RAW.CAST_TO_NUMBER(D.HIGH_VALUE) HIGH_VALUE2, FROM USER_TAB_COLS D WHERE D.TABLE_NAME = 'T_AA_20170606_LHR';
& 說明：
有關轉換的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140335/
（三）系統統計資訊
系統統計資訊主要包括目標資料庫伺服器CPU的主頻、單塊讀的平均耗費時間、多塊讀的平均耗費時間和單次多塊讀所能讀取的數據塊的平均值等。收集系統統計資訊的方法主要是使用系統存儲過程：
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('start'); 系統正常負載運行一段時間 EXEC DBMS_STATS.GATHER_SYSTEM_STATS('stop');
或：
EXEC DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE => 'INTERVAL',INTERVAL =>1);--INTERVAL為間隔時長，單位為分鐘
系統統計資訊主要存儲在SYS.AUX_STATS$表中，也可以使用DBMS_STATS.GET_SYSTEM_STATS獲取系統統計資訊的內容，修改系統統計資訊可以使用DBMS_STATS.SET_SYSTEM_STATS，刪除系統統計資訊可以使用DBMS_STATS.DELETE_SYSTEM_STATS。
在未引入系統統計資訊之前，CBO所計算的成本值全部是基於I/O來計算的；在Oracle引入了系統統計資訊之後，實際上就額外地引入了CPU成本計算模型（CPU Cost model），從此以後，CBO所計算的成本值就包括I/O Cost和CPU Cost這兩個部分。CBO在計算成本的時候就會分別對它們各自計算，並將算出來的I/O Cost和CPU Cost值的總和作為目標SQL新的成本值。
從Oracle 9i開始，Oracle通過一個隱含參數「_OPTIMIZER_COST_MODEL」來控制是否開啟CPU Cost model。該參數的默認值為CHOOSE，意思是如果SYS.AUX_STATS$表裡有相關記錄，那麼表示開啟CPU Cost model，否則就還是沿用以前的成本計算模型（即計算的成本全部是I/O Cost）。
SYS@orclasm > set pagesize 9999 SYS@orclasm > set line 9999 SYS@orclasm > col NAME format a40 SYS@orclasm > col KSPPDESC format a50 SYS@orclasm > col KSPPSTVL format a20 SYS@orclasm > SELECT a.INDX, 2 a.KSPPINM NAME, 3 a.KSPPDESC, 4 b.KSPPSTVL 5 FROM x$ksppi a, 6 x$ksppcv b 7 WHERE a.INDX = b.INDX 8 and lower(a.KSPPINM) like lower('%¶meter%'); Enter value for parameter: _OPTIMIZER_COST_MODEL old 8: and lower(a.KSPPINM) like lower('%¶meter%') new 8: and lower(a.KSPPINM) like lower('%_OPTIMIZER_COST_MODEL%') INDX NAME KSPPDESC KSPPSTVL ---------- ---------------------------------------- -------------------------------------------------- -------------------- 1917 _optimizer_cost_model optimizer cost model CHOOSE SYS@orclasm > SET LINESIZE 9999 SYS@orclasm > COL PVAL1 FOR 999999999 SYS@orclasm > COL PVAL2 FOR A30 SYS@orclasm > COL SNAME FOR A15 SYS@orclasm > SELECT * FROM SYS.AUX_STATS$; SNAME PNAME PVAL1 PVAL2 --------------- ------------------------------ ---------- ------------------------------ SYSSTATS_INFO STATUS COMPLETED SYSSTATS_INFO DSTART 06-02-2017 13:54 SYSSTATS_INFO DSTOP 06-02-2017 13:55 SYSSTATS_INFO FLAGS 0 SYSSTATS_MAIN CPUSPEEDNW 1752 SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_MAIN SREADTIM 4 SYSSTATS_MAIN MREADTIM SYSSTATS_MAIN CPUSPEED 2099 SYSSTATS_MAIN MBRC SYSSTATS_MAIN MAXTHR SYSSTATS_MAIN SLAVETHR
結果含義如下所示：
l CPUSPEEDNW：非工作量統計模式下CPU主頻，即每秒可以完成的機器指命數據，直接來自硬體。
l IOSEEKTIM：I/O定址時間（毫秒），默認值為10，直接來自硬體。
l IOTFRSPEED：I/O傳輸速率（位元組/毫秒），默認為4096。
l SREADTIM：讀取單個數據塊的平均時間，單位是毫秒（ms）。
l MREADTIM：讀取多個數據塊的平均時間，單位是毫秒（ms）。
l CPUSPEED：工作量統計模式下CPU主頻，根據當前工作量評估出一個合理值。
l MBRC：Oracle收集完統計資訊後評估出的一次多塊讀可以讀幾個數據塊（DB_FILE_MULTIBLOCK_READ_COUNT）。
l MAXTHR：最大I/O吞吐量（位元組/秒）。
l SLAVETHR：單個並行進程的最大吞吐量（位元組/秒）。
l SYSSTATS_INFO：系統統計資訊的狀態和收集時間。
l SYSSTATS_MAIN：系統統計資訊的結果集。
l SYSSTATS_TEMP：只有當收集系統統計資訊時才可用。
以下是10053事件的trace關於系統統計數據的部分內容：
----------------------------- SYSTEM STATISTICS INFORMATION ----------------------------- Using NOWORKLOAD Stats CPUSPEEDNW: 3097 millions instructions/sec (default is 100) IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 16 milliseconds (default is 10) MBRC: -1 blocks (default is 16)
（四）內部對象統計資訊
數據字典基表SYS.TAB_STATS$中會存儲X$表的表對象統計資訊。默認情況下（包括默認的自動統計資訊收集作業在內），Oracle不會對X$系列表收集內部對象統計資訊，所以默認情況下SYS.TAB_STATS$中沒有任何記錄。
需要注意的是，X$表雖然只是記憶體結構，不佔用資料庫的物理存儲空間，但X$系列表的內部對象統計資訊實際上已經被Oracle存儲在了數據字典里，這些統計資訊是佔用了實際的物理存儲空間的，這意味著X$表的統計資訊已經被持久化了，並不會隨著資料庫的起停而消失。所以，實際上並不需要隨著資料庫的起停而對X$表反覆收集內部對象統計資訊，除非系統的負載發生了很大的變化，之前收集的內部對象統計資訊已經不再具備代表性。
即使相關的X$表沒有內部對象統計資訊，Oracle也不會在訪問這些X$表時使用動態取樣。在明確診斷出系統已有的性能問題是因為X$表的內部對象統計資訊不準引起的，這個時候就應該收集X$表的內部對象統計資訊，其它情形就不要收集了。因為X$表實際上就是記憶體結構，所以在RAC環境下收集內部對象統計資訊時需要在每個節點都進行收集統計資訊。
本文選自《Oracle程式設計師面試筆試寶典》，作者：小麥苗
