當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

你知道吗？其实 Oracle 直方图自动统计算法存在这些缺陷！（附验证步骤）

發布時間：2024/8/23 编程问答 30 豆豆

生活随笔收集整理的這篇文章主要介紹了你知道吗？其实 Oracle 直方图自动统计算法存在这些缺陷！（附验证步骤）小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

作者 | 吳海存

責編 | Carol

出品 | CSDN 云計算（ID：CSDNcloud）

封圖|?CSDN下載于視覺中國

在某些場景下，表中某一列的數據分布會比較崎嶇，使得CBO(cost base optimizer)在評估執行計劃的時候可能會出現誤差，從而選擇了不優的執行計劃，影響了sql的執行性能。

為了改善這一情況，Oracle使用直方圖來向CBO描述列的數據分布情況，比如列上唯一值數量和出現頻次等信息，從而幫助CBO選擇較優的執行計劃(主要體現在是否走index 或者table full scan)。

從10g開始，默認情況下，在收集統計信息的時候Oracle有一套機制和算法來自動地評估是否需要為某一列收集直方圖，而該機制和算法中的一些不完善性，有可能會引起性能問題(比如發生謂詞越界的時候)。?

那么Oracle是如何去判斷是否需要對某一列收集直方圖呢？Oracle會不會對數據分布相對均衡的一些列自動創建直方圖呢？和數據類型有無關系呢？面對這一系列的問題，我們將在本文中使用10053 event對CBO進行跟蹤并闡述。

本文非知識講解或使用說明文檔，而是經過詳實的測試和驗證，一步一步地說明Oracle直方圖自動統計算法的缺陷和后期可能造成的潛在性能問題。

【實驗環境】

操作系統: ?centos8.0

數據庫版本：oracle 19.3.0.0 ?

使用較高版本的數據庫版本，是為了驗證Oracle在新的版本中是否有對直方圖自動統計收集的機制和算法有改善。

因為oracle不推薦使用varchar2保存時間字符串，因為CBO在對varchar2列進行評估時，比如列密度，選擇性，唯一值等數據，是將varchar2轉換成raw格式進行評估的，raw存儲的是二進制值，在任何時候不會進行自動的字符集轉換，當使用utl_raw.cast_to_raw轉換時，會直接把字符串中的每個字符的ASCII碼存放到oracle raw類型的字段中。

由于本不相同的純數字的字符串被轉換成raw后值可能是一樣的，所以使用varchar2保存純數字字符串的時候，可能會造成CBO評估不準確。為了驗證直方圖自動收集是否和數據類型相關，本實驗中將會使用varchar2和date類型進行測試。

【10053事件常用信息說明】

表信息??????

Rows: 表記錄數

NBLKS: 高水位以下的block數

Blks: 表的數據塊數

AVG_ROW_LEN: 行的平均長度

TABLE_SCAN_CST: 全表掃描的IO成本

列信息 ?????????

NDV(null_distinct) : 列的不重復值數

NULLs(num_nulls) : 列的空行數

DENS(density) : 列的密度，沒有直方圖的情況下=1/NDV

LO(low_value) : 列的最小值

HI(high_value)? : 列的最大值

BKT(): 列的直方圖桶數，若為1，則沒有直方圖

Selectivity? : 選擇率，用來計算列的基數，基數card=selectivity*rows

Newdensity? : 直方圖密度 [(BktCnt-PopBktCnt)/BktCnt]/(NDV-PopValCnt)

Rounded? : ?輸出行數

索引信息????????

LVLS：索引高度 ?--BLEVEL BTREE

LB: 索引葉塊數 ??--LEAF_BLOCKS

DK: 索引唯一關鍵字數 ??-- DISTINCT_KEYS

LB/K: 每個關鍵字平均占幾個葉塊 ???葉塊/關鍵字

DB/K：每個關鍵字平均占幾個數據塊 ?數據塊/關鍵字

CLUF：索引集群因子 ???--CLUSTERING_FACTOR

實驗步驟

1.測試表中只有單月的varchar2數據類型情況

1.1?創建相應的表和模擬數據，此處我們先使用varchar2類型

CREATE?TABLE?"DAY_TRNFLW"(?"DAY_FLWNO"?VARCHAR2(25)?NOT?NULL?ENABLE,"DAY_TRNTIME"?VARCHAR2(30)?NOT?NULL?ENABLE,"DAY_CSTNO"?VARCHAR2(16),"DAY_STDBSNCOD"?VARCHAR2(30),"DAY_CSTACC"?VARCHAR2(60),"DAY_ACCTYP"?VARCHAR2(3),"DAY_ACCCRY"?VARCHAR2(3),"DAY_TRNAMT"?NUMBER(15,2),"DAY_CHANNEL"?VARCHAR2(20),"DAY_TRNCOUNT"?NUMBER)?;create?table?t1?as?select?*?from?dba_objects;

1.2模擬數據，此時只模擬單月數據，數據不跨月，因為若數據跨月的話，oracle在轉換成raw類型的時候，會產生兩個internal value

insert?into?DAY_TRNFLW?(DAY_FLWNO,DAY_TRNTIME,DAY_CSTNO)select?rownum?,?to_char(to_date('20190901','yyyymmdd')+round(dbms_random.value(0?,86400*3-1))/86400,'yyyymmddhh24miss')?,round(dbms_random.value(0,2000000))from?t1?where?rownum<=5265655;commit;??

1.3創建相應的索引

CREATE?INDEX?"INDEX_DAY_TRNFLW_A"?ON?"DAY_TRNFLW"?("DAY_CSTNO",?"DAY_CHANNEL",?"DAY_TRNTIME");CREATE?INDEX?"INDEX_DAY_TRNFLW_C"?ON?"DAY_TRNFLW"?("DAY_TRNTIME");

1.4收集統計信息并確認此時沒有自動收集直方圖

exec?dbms_stats.gather_table_stats(user,'DAY_TRNFLW',no_invalidate=>false);

說明：當使用如上命令收集統計信息時，直方圖默認是for all columns auto的方式，即由oracle根據相應的算法和機制自動判斷是否對列收集直方圖。

col?COLUMN_NAME?format?a30col?HISTOGRAM?format?a10select?column_name,histogram,low_value,high_valuefrom?dba_tab_columnswhere?table_name='DAY_TRNFLW'and?column_name='DAY_TRNTIME'?;COLUMN_NAME????????????????????HISTOGRAM------------------------------?----------LOW_VALUE--------------------------------------------------------------------------------HIGH_VALUE--------------------------------------------------------------------------------DAY_TRNTIME????????????????????NONE32303139303930313030303031313230313930393033323335393537

可以看到，此時并沒有收集直方圖

1.5 下面使用10053事件跟蹤CBO，確認無直方圖時是否會判斷謂詞越界

獲取會話ID

SQL>?select?sid?from?v$mystat?where?rownum=1;select?spid,pid?from?v$process?a?,v$session?b?where?a.addr=b.paddr?AND??b.sid=&sid;SID----------3515SQL>?SQL>?Enter?value?for?sid:?3515old???1:?select?spid,pid?from?v$process?a?,v$session?b?where?a.addr=b.paddr?AND??b.sid=&sidnew???1:?select?spid,pid?from?v$process?a?,v$session?b?where?a.addr=b.paddr?AND??b.sid=3515SPID????????????????????????????PID------------------------?----------28366???????????????????????????157

1.6新開啟一個會話，使用oradebug對session 3515進行trace

SQL>?ORADEBUG?SETORAPID?157;Oracle?pid:?157,?Unix?process?pid:?28366,?image:?oracle@hqxtsl-oracle-a01?(TNS?V1-V3)SQL>?oradebug?event?10053?trace?name?context?forever,level?2;

在session 3515中執行sql觸發硬解析（第一次執行）

SQL>?var?p0?varchar2(30);var?p1?varchar2(30);var?p2?varchar2(30);SQL>?SQL>?SQL>?exec?:p0:='12345';exec?:p1:='20190721';???exec?:p2:='20190722';???--由于表中都是9月份數據，所以此處變量p2超出了列DAY_TRNTIME的取值范圍select?SUM(nvl(DAY_TRNAMT,1))?as?"sumAmt"?,?SUM(nvl(DAY_TRNCOUNT,1))?as?"trnCount"??from?DAY_TRNFLWwhere?DAY_CSTNO?=?:p0and??DAY_TRNTIME?between?:p1?and?:p2;PL/SQL?procedure?successfully?completed.SQL>PL/SQL?procedure?successfully?completed.SQL>PL/SQL?procedure?successfully?completed.SQL>???2????3sumAmt???trnCount----------?----------

1.7結束10053跟蹤，并找出相應的trace文件

SQL>?ORADEBUG?TRACEFILE_NAME;Statement?processed.SQL>oradebug?event?10053?trace?name?context?off/oracle/app/oracle/diag/rdbms/dbcon/dbcon1/trace/dbcon1_ora_28366.trc

1.8經過分析10053事件的跟蹤文件，發現在沒有直方圖的情況下，就算傳遞超出列值范圍的綁定變量值，CBO在此情況下不會判斷謂詞越界的現象，分析如下：

綁定變量信息

-----?Bind?Info?(kkscoacd)?-----Bind#0oacdty=01?mxl=128(90)?mxlc=00?mal=00?scl=00?pre=00oacflg=03?fl2=1000000?frm=01?csi=873?siz=384?off=0kxsbbbfp=7fa6f5003ce8??bln=128??avl=05??flg=05value="12345"Bind#1oacdty=01?mxl=128(90)?mxlc=00?mal=00?scl=00?pre=00oacflg=03?fl2=1000000?frm=01?csi=873?siz=0?off=128kxsbbbfp=7fa6f5003d68??bln=128??avl=08??flg=01value="20190721"Bind#2oacdty=01?mxl=128(90)?mxlc=00?mal=00?scl=00?pre=00oacflg=03?fl2=1000000?frm=01?csi=873?siz=0?off=256kxsbbbfp=7fa6f5003de8??bln=128??avl=08??flg=01value="20190722"

執行計劃篩選分析

=====================================Access?path?analysis?for?DAY_TRNFLW***************************************SINGLE?TABLE?ACCESS?PATHSingle?Table?Cardinality?Estimation?for?DAY_TRNFLW[DAY_TRNFLW]SPD:?Return?code?in?qosdDSDirSetup:?NOCTX,?estType?=?TABLEColumn?(#3):?DAY_CSTNO(VARCHAR2)AvgLen:?8?NDV:?70976?Nulls:?0?Density:?0.000014Estimated?selectivity:?1.4089e-05?,?col:?#3kkecdn:?Single?Table?Predicate:"DAY_TRNFLW"."DAY_CSTNO"=:B1Estimated?selectivity:?1.4089e-05?,?col:?#3kkecdn:?Single?Table?Predicate:"DAY_TRNFLW"."DAY_TRNTIME">=:B1Column?(#2):?DAY_TRNTIME(VARCHAR2)??--此處數據類型是varchar2AvgLen:?15?NDV:?63912?Nulls:?0?Density:?0.000016Estimated?selectivity:?1.000000?,?col:?#2 ???--說明：該處的選擇率為1是正確的，因為表中所有的行都滿足大于綁定變量的值’20190721’，基數即為所有的行kkecdn:?Single?Table?Predicate:"DAY_TRNFLW"."DAY_TRNTIME"<=:B1Using?density:?1.5647e-05?of?col?#2?as?selectivity?of?unpopular?value?pred???--因為從謂詞中得不到合適的選擇率，此處直接使用上面的密度，這里可以看到，雖然傳遞的綁定變量值’20190722’都小于列值(9月份數據)，CBO并沒有判斷發生了謂詞越界Table:?DAY_TRNFLW??Alias:?DAY_TRNFLWCard:?Original:?73269.000000??Rounded:?1??Computed:?0.000016??Non?Adjusted:?0.000016????????????????--使用該密度的情況下，輸出值認為是1Scan?IO??Cost?(Disk)?=???102.000000Scan?CPU?Cost?(Disk)?=???16556182.800000...Best::?AccessPath:?IndexRangeIndex:?INDEX_DAY_TRNFLW_A??????--CBO選擇了正確的索引ACost:?3.000594??Degree:?1??Resp:?3.000594??Card:?0.000016??Bytes:?0.000000

確認在執行sql查詢后,表sys.col_usage$里已有列DAY_TRNTIME的謂詞使用信息,因為若該表中沒有列DAY_TRNTIME的謂詞使用記錄，則收集統計信息時候不會主動收集直方圖

SQL>?select?*?from?sys.col_usage$?where?obj#=114538;OBJ#????INTCOL#?EQUALITY_PREDS?EQUIJOIN_PREDS?NONEQUIJOIN_PREDS?RANGE_PREDS?LIKE_PREDS?NULL_PREDS?TIMESTAMP??????FLAGS----------?----------?--------------?--------------?-----------------?-----------?----------?----------?---------?----------114538??????????2??????????????0??????????????0?????????????????0???????????3??????????0??????????0?20-FEB-20??????????8114538??????????3??????????????3??????????????0?????????????????0???????????0??????????0??????????0?20-FEB-20????????513

1.9進行統計信息搜集

SQL>?exec?dbms_stats.gather_table_stats(user,'DAY_TRNFLW',no_invalidate=>false);PL/SQL?procedure?successfully?completed.

1.10確認直方圖信息

SQL>?col?COLUMN_NAME?format?a30col?HISTOGRAM?format?a10select?column_name,histogram,low_value,high_valuefrom?dba_tab_columnswhere?table_name='DAY_TRNFLW'and?column_name='DAY_TRNTIME'?;SQL>?SQL>???2????3????4COLUMN_NAME????????????????????HISTOGRAM------------------------------?----------LOW_VALUE--------------------------------------------------------------------------------HIGH_VALUE--------------------------------------------------------------------------------DAY_TRNTIME????????????????????NONE32303139303930313030303030323230313930393033323335393532

說明：可以發現即使col_usage$里有列DAY_TRNTIME的謂詞使用記錄，但是由于表中只有9月份的記錄，轉換成RAW格式后只有一個internal value, CBO認為該列上的值分均均衡，沒有收集直方圖信息。

測試表中含有多月(8月和9月)的數據進行

2.1將表中數據更新為8月份，模擬多月數據

update?DAY_TRNFLW?set?DAY_TRNTIME=to_char(to_date(DAY_TRNTIME,'yyyymmddhh24miss')-3,'yyyymmddhh24miss')?where?rownum<=10000;commit;

2.2收集統計信息

SQL>?exec?dbms_stats.gather_table_stats(user,'DAY_TRNFLW',no_invalidate=>false);

使用如下sql檢查，發現當表中有8月和9月的數據時，收集統計信息的時候oracle自動收集了直方圖

SQL>?col?COLUMN_NAME?format?a30col?HISTOGRAM?format?a10select?column_name,histogram,low_value,high_valuefrom?dba_tab_columnswhere?table_name='DAY_TRNFLW'and?column_name='DAY_TRNTIME'?;SQL>?SQL>???2????3????4COLUMN_NAME????????????????????HISTOGRAM------------------------------?----------LOW_VALUE--------------------------------------------------------------------------------HIGH_VALUE--------------------------------------------------------------------------------DAY_TRNTIME????????????????????HYBRID32303139303832393030303030323230313930393033323335393532

此時，將已有的sql執行計劃從share pool中清理掉，重新觸發硬解析并使用10053進行跟蹤，發現CBO在有直方圖的情況下判斷了是否會發生謂詞越界。

2.3從share pool中清理現有sql執行計劃，以便重新硬解析該sql(10053事件只能跟蹤硬解析，不能跟蹤軟解析和軟軟解析)

SQL>?select?sql_id,address,hash_value,PLAN_HASH_VALUE?from?v$sql?where?sql_id='&sql_id';Enter?value?for?sql_id:?azf5wm5qhptmyold???1:?select?sql_id,address,hash_value,PLAN_HASH_VALUE?from?v$sql?where?sql_id='&sql_id'new???1:?select?sql_id,address,hash_value,PLAN_HASH_VALUE?from?v$sql?where?sql_id='azf5wm5qhptmy'SQL_ID????????ADDRESS??????????HASH_VALUE?PLAN_HASH_VALUE-------------?----------------?----------?---------------azf5wm5qhptmy?00000000FF897F90?1829430910??????2119561882SQL>?BEGINDBMS_SHARED_POOL.PURGE('&address,&hash_value',?'C');END;/??2????3????4Enter?value?for?address:?00000000FF897F90Enter?value?for?hash_value:?1829430910old???2:??DBMS_SHARED_POOL.PURGE('&address,&hash_value',?'C');new???2:??DBMS_SHARED_POOL.PURGE('00000000FF897F90,1829430910',?'C');PL/SQL?procedure?successfully?completed.SQL>?select?sql_id,address,hash_value,PLAN_HASH_VALUE?from?v$sql?where?sql_id='azf5wm5qhptmy';no?rows?selected

2.4執行sql并使用上面同樣的oradebug方法進行trace,并對跟蹤文件進行分析

執行計劃分析

Access?path?analysis?for?DAY_TRNFLW *************************************** SINGLE?TABLE?ACCESS?PATHSingle?Table?Cardinality?Estimation?for?DAY_TRNFLW[DAY_TRNFLW]SPD:?Return?code?in?qosdDSDirSetup:?NOCTX,?estType?=?TABLEColumn?(#3):?DAY_CSTNO(VARCHAR2)AvgLen:?8?NDV:?70976?Nulls:?0?Density:?0.000014Estimated?selectivity:?1.4089e-05?,?col:?#3kkecdn:?Single?Table?Predicate:"DAY_TRNFLW"."DAY_CSTNO"=:B1Estimated?selectivity:?1.4089e-05?,?col:?#3kkecdn:?Single?Table?Predicate:"DAY_TRNFLW"."DAY_TRNTIME">=:B1Column?(#2):NewDensity:0.000015,?OldDensity:0.000015?BktCnt:5371.000000,?PopBktCnt:0.000000,?PopValCnt:0,?NDV:65096Column?(#2):?DAY_TRNTIME(VARCHAR2)AvgLen:?15?NDV:?65096?Nulls:?0?Density:?0.000015Histogram:?Hybrid??#Bkts:?254??UncompBkts:?5371??EndPtVals:?254??ActualVal:?yesEstimated?selectivity:?1.000000?,?col:?#2????--此處CBO評估出來選擇率還是1，因為所有行都滿足大于’20190721’的條件，但是在收集直方圖統計信息的時候，桶數為254，因此收集統計信息的時候是可以探測到該列上是存在大于或等于254個唯一值的，且有5371個被壓縮的bucketskkecdn:?Single?Table?Predicate:"DAY_TRNFLW"."DAY_TRNTIME"<=:B1Using?prorated?density:?6.8242e-06?of?col?#2?as?selectivity?of?out-of-range/non-existent?value?pred??--此處CBO根據直方圖判斷出發生了謂詞越界 . . .Access?Path:?index?(RangeScan)Index:?INDEX_DAY_TRNFLW_Aresc_io:?4.000000??resc_cpu:?29216???--CBO評估出走索引A的開銷為4ix_sel:?1.4089e-05??ix_sel_with_filters:?9.6148e-11Cost:?4.000785??Resp:?4.000785??Degree:?1******?Costing?Index?INDEX_DAY_TRNFLW_CSPD:?Return?code?in?qosdDSDirSetup:?NOCTX,?estType?=?INDEX_SCANSPD:?Return?code?in?qosdDSDirSetup:?NOCTX,?estType?=?INDEX_FILTEREstimated?selectivity:?1.000000?,?col:?#2Using?prorated?density:?6.8242e-06?of?col?#2?as?selectivity?of?out-of-range/non-existent?value?predAccess?Path:?index?(RangeScan)Index:?INDEX_DAY_TRNFLW_Cresc_io:?3.000000??resc_cpu:?21919???--CBO通過索引C可以直接通過謂詞越界過濾掉所有的行，評估出走索引C的開銷為3，低于索引A???ix_sel:?6.8242e-06??ix_sel_with_filters:?6.8242e-06Cost:?3.000588??Resp:?3.000588??Degree:?1Used?INDEX_DAY_TRNFLW_CCost?=?3.000393,?sel?=?1.5362e-05Not?used?INDEX_DAY_TRNFLW_ACost?=?4.000590,?sel?=?1.4089e-05 . . .******?finished?trying?bitmap/domain?indexes?******Best::?AccessPath:?IndexRangeIndex:?INDEX_DAY_TRNFLW_C???????--最終，CBO通過比較開銷，選擇了索引C，SQL的執行計劃發生了相應變化Cost:?3.000588??Degree:?1??Resp:?3.000588??Card:?0.000016??Bytes:?0.000000

至此，我們可以得出如下結論：

1.表里有了時間為8月份和9月份的數據時，在對列值DAY_CSTNO轉換成RAW后，生成了兩個internal values, Oracle收集統計信息的時候，根據相應的算法和機制自動對該列收集了直方圖

2.表里只有9月份的數據時，在對列值DAY_CSTNO轉換成RAW后，只有一個internal values，此時即使col_usage$里有列DAY_TRNTIME的謂詞使用記錄，Oracle收集統計信息時，根據相應的算法和機制會認為數據分布均勻(單值是均勻分布的特殊情況，只有一個internal value)，不會主動對該列收集直方圖

重新解析時，傳遞的綁定變量值為’20190722’，不在列值的數值范圍之內，由于該列存在了直方圖，CBO檢查出了謂詞越界，謂詞越界使得通過索引INDEX_DAY_TRNFLW_C查找7月份的數據效率更高（直接過濾，返回空結果集），若后續并發的會話執行該sql時都共享使用了此執行計劃，則有可能造成性能問題。

但是，此處會引出新的疑問，為什么當表列上只有9月份的數據時沒有統計直方圖，當列上同時存在8月和9月的數據值時才會統計直方圖呢？這兩個internal values是怎么生成的呢？

列"DAY_TRNTIME" 被定義為 VARCHAR2(30)，且該列存儲的是純數字的時間字符串。Oracle CBO在對varchar2類型的列評估時，比如列密度，選擇性，唯一值等數據時，是將varchar2轉換成raw格式進行評估的，raw存儲的是二進制值，在任何時候不會進行自動的字符集轉換，但是，由于本不相同的純數字的字符串被轉換成raw后值可能是一樣的，所以使用varchar2保存純數字字符串的時候，可能會造成CBO評估不準確。這一點，我們可以從數據字典表dba_tab_columns中得到一定的證實：

SQL>?desc?dba_tab_columnsName??????????????????????????????????????Null?????Type-----------------------------------------?--------?----------------------------OWNER?????????????????????????????????????NOT?NULL?VARCHAR2(128)TABLE_NAME????????????????????????????????NOT?NULL?VARCHAR2(128)COLUMN_NAME???????????????????????????????NOT?NULL?VARCHAR2(128)...LOW_VALUE??????????????????????????????????????????RAW(2000)HIGH_VALUE?????????????????????????????????????????RAW(2000)

說明：可以看到列的最值是被轉換成raw類型放在數據庫中的，該最值在有直方圖的時候會被用來判斷是否謂詞越界。

RAW轉換模擬測試

我們可以使用utl_raw函數模擬了一下CBO對列進行評估，可以發現純數字字符串被轉換成raw的時候的確變成了一樣的值

SQL>?select?utl_raw.cast_to_raw('DAY_TRNTIME')?from?DAY_TRNFLW?where?rownum<=10;UTL_RAW.CAST_TO_RAW('DAY_TRNTIME')--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------4441595F54524E54494D454441595F54524E54494D454441595F54524E54494D454441595F54524E54494D454441595F54524E54494D454441595F54524E54494D454441595F54524E54494D454441595F54524E54494D454441595F54524E54494D454441595F54524E54494D45SQL>?select?utl_raw.cast_to_raw('DAY_TRNTIME')?raw_for_cbo,?count(1)?from?DAY_TRNFLW?group?by?1;RAW_FOR_CBO--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------COUNT(1)----------4441595F54524E54494D4573269

說明：該函數只是為了模擬測試使用，CBO具體使用什么轉換函數，筆者查了相應的資料，并且也對統計信息會話進行了10046 trace和分析，目前沒有得到準確的結果。

但是從此時該列上的數據實際分布情況來判斷，數據分布并不傾斜：

select?DAY_TRNTIME,?count(1)?num_rows?from?DAY_TRNFLW?group?by?DAY_TRNTIME?order?by?2;...20190901200645??????????????????????????420190901200958??????????????????????????420190901213042??????????????????????????420190901224944??????????????????????????420190902005954??????????????????????????420190902092241??????????????????????????420190902102315??????????????????????????420190902113652??????????????????????????420190902135131??????????????????????????420190902151930??????????????????????????420190902172156??????????????????????????4DAY_TRNTIME??????????????????????NUM_ROWS------------------------------?----------20190902173951??????????????????????????420190902182755??????????????????????????420190902201506??????????????????????????420190903001538??????????????????????????420190903033351??????????????????????????420190903040014??????????????????????????420190903180612??????????????????????????420190902212139??????????????????????????565887?rows?selected.

說明：該列上重復值最多的列值僅僅為5行，分布相對均衡，所以是沒有必要統計直方圖的，但是Oracle根據相應的算法和機制，為該列統計了直方圖，CBO再次進行綁定變量窺測的時候，有可能會選擇效率不高的執行計劃，造成后續的性能問題(如上面同時存在8，9月份數據的測試案例)。

Date數據類型測試

如上所述，Oracle不建議使用varchar2保存純數字的字符串，因為轉換成RAW后可能會造成CBO評估不準確。下面，我們將該列改成date類型進一步測試

4.1創建date型數據表

SQL>?drop?table?DAY_TRNFLW;Table?dropped.SQL>?CREATE?TABLE?"DAY_TRNFLW"(?"DAY_FLWNO"?VARCHAR2(25)?NOT?NULL?ENABLE,"DAY_TRNTIME"?date?NOT?NULL?ENABLE,2????3??"DAY_CSTNO"?VARCHAR2(16),"DAY_STDBSNCOD"?VARCHAR2(30),4????5??"DAY_CSTACC"?VARCHAR2(60),6????7??"DAY_ACCTYP"?VARCHAR2(3),"DAY_ACCCRY"?VARCHAR2(3),"DAY_TRNAMT"?NUMBER(15,2),"DAY_CHANNEL"?VARCHAR2(20),"DAY_TRNCOUNT"?NUMBER);??8????9???10???11Table?created.insert?into?DAY_TRNFLW?(DAY_FLWNO,DAY_TRNTIME,DAY_CSTNO)select?rownum?,?to_date('20190901','yyyymmdd')+round(dbms_random.value(0,86400*3-1))/86400?,round(dbms_random.value(0,2000000))from?t1,t1?where?rownum<=5265655;commit;

4.2模擬表中同時存在8月和9月的數據

update?DAY_TRNFLW?set?DAY_TRNTIME=DAY_TRNTIME-3?where?rownum<=10000;Commit;

4.3確認col_usage$是否有謂詞使用記錄

SQL>?select?object_id?from?dba_objects?where?owner='SAM'?and?object_name='DAY_TRNFLW';OBJECT_ID----------114546SQL>?select?*?from?sys.col_usage$?where?obj#=114546;no?rows?selected.

4.4執行一下sql,以便oracle可以記錄該謂詞使用記錄

SQL>?var?p0?varchar2(30);var?p1?varchar2(30);var?p2?varchar2(30);exec?:p0:='12345';exec?:p1:=to_date('20190721','yyyymmdd');exec?:p2:=to_date('20190722','yyyymmdd');select?SUM(nvl(DAY_TRNAMT,1))?as?"sumAmt"?,?SUM(nvl(DAY_TRNCOUNT,1))?as?"trSQL>?nCount"??from?DAY_TRNFLW?whereDAY_CSTNO?=?:p0?andDAY_TRNTIME?between?:p1?and?:p2;SQL>?SQL>PL/SQL?procedure?successfully?completed.SQL>PL/SQL?procedure?successfully?completed.SQL>PL/SQL?procedure?successfully?completed.SQL>?SQL>???2????3sumAmt???trnCount----------?----------SQL>?select?*?from?sys.col_usage$?where?obj#=114546;OBJ#????INTCOL#?EQUALITY_PREDS?EQUIJOIN_PREDS?NONEQUIJOIN_PREDS----------?----------?--------------?--------------?-----------------RANGE_PREDS?LIKE_PREDS?NULL_PREDS?TIMESTAMP??????FLAGS-----------?----------?----------?---------?----------114546??????????2??????????????0??????????????0?????????????????01??????????0??????????0?20-FEB-20??????????8114546??????????3??????????????1??????????????0?????????????????00??????????0??????????0?20-FEB-20????????513

4.5收集統計信息

SQL>?exec?dbms_stats.gather_table_stats(user,'DAY_TRNFLW',no_invalidate=>false);

4.6清理原有執行計劃

SQL>?alter?system?flush?shared_pool;System?altered.

4.7進行date類型的10053跟蹤

開啟會話跟蹤

SQL>?select?sid?from?v$mystat?where?rownum=1;select?spid,pid?from?v$process?a?,v$session?b?where?a.addr=b.paddr?AND??b.sid=&sid;SID----------2664SQL>?SQL>?Enter?value?for?sid:?2664old???1:?select?spid,pid?from?v$process?a?,v$session?b?where?a.addr=b.paddr?AND??b.sid=&sidnew???1:?select?spid,pid?from?v$process?a?,v$session?b?where?a.addr=b.paddr?AND??b.sid=2664SPID????????????????????????????PID------------------------?----------18094???????????????????????????150SQL>?ORADEBUG?SETORAPID??150;Oracle?pid:?150,?Unix?process?pid:?18094,?image:?oracle@hqxtsl-oracle-a01?(TNS?V1-V3)SQL>?oradebug?event?10053?trace?name?context?forever,level?2;Statement?processed.

執行sql，觸發硬解析

SQL>?var?p0?varchar2(30);var?p1?varchar2(30);var?p2?varchar2(30);SQL>?SQL>?SQL>?exec?:p0:='12345';exec?:p1:=to_date('20190721','yyyymmdd');exec?:p2:=to_date('20190722','yyyymmdd');select?SUM(nvl(DAY_TRNAMT,1))?as?"sumAmt"?,?SUM(nvl(DAY_TRNCOUNT,1))?as?"trnCount"??from?DAY_TRNFLW?whereDAY_CSTNO?=?:p0?andDAY_TRNTIME?between?:p1?and?:p2;PL/SQL?procedure?successfully?completed.SQL>PL/SQL?procedure?successfully?completed.SQL>PL/SQL?procedure?successfully?completed.SQL>?SQL>???2????3sumAmt???trnCount----------?----------

關閉10053跟蹤

SQL>?ORADEBUG?TRACEFILE_NAME;Statement?processed.SQL>oradebug?event?10053?trace?name?context?off;/oracle/app/oracle/diag/rdbms/dbcon/dbcon1/trace/dbcon1_ora_18094.trc

4.8進行date類型的10053分析

說明：將列修改為date類型后，當表中存在8月和9月的數據時，oracle同樣對列DAY_TRNFLW收集了直方圖并檢查了謂詞越界，說明和該列的數據類型無關系，而是由oracle收集直方圖的機制和算法決定的。

4.9檢查數據分布情況

select?DAY_TRNTIME,?count(1)?num_rows?from?DAY_TRNFLW?group?by?DAY_TRNTIME?order?by?2;...03-SEP-19?????????4003-SEP-19?????????4003-SEP-19?????????4003-SEP-19?????????4002-SEP-19?????????40DAY_TRNTI???NUM_ROWS---------?----------01-SEP-19?????????4001-SEP-19?????????4003-SEP-19?????????4102-SEP-19?????????4103-SEP-19?????????4103-SEP-19?????????4101-SEP-19?????????4203-SEP-19?????????4201-SEP-19?????????4201-SEP-19?????????4202-SEP-19?????????43269016?rows?selected.

說明：在526W的表中，重復值最多的列值也只有43行，這不算是數據分布不均衡，并不需要收集直方圖信息，因為最相對于表數據，該列選擇率還是很高的，但是此時Oracle根據相應的算法和機制，一樣為該列收集了直方圖，可能會造成后續執行計劃的改變。?

Oracle在自動收集直方圖的時，當相應的列被當作謂詞使用并被capture到col_usage$后，Oracle會遵循以下幾個原則來判斷是否要創建直方圖，相應說明如下：

1.The column has value skew and column usage indicates RANGE, LIKE, EQ or EQ_JOIN.(列值分區崎嶇，且相應的列被用作上訴謂詞)

2.The column has range skew and column usage indicates LIKE or RANGE.(列值在相同大小的范圍內，數據分區崎嶇，且相應的列被用作上訴謂詞)

備注：列值崎嶇和范圍崎嶇是數據分布崎嶇的兩種類型。

3.The column has a low number of distinct values (with some repeated values) and column usage indicates RANGE, LIKE, EQ or EQ_JOIN.(列上的唯一值相對于表的總行數而言較少，且相應的列被用作上訴謂詞，Oracle默認收集統計信息的時候，也對改列收集直方圖)

4.When incremental statistics are used, and even though a column might not have value/range skew in a partition, a histogram may be created. The database will use partition-level histograms to derive global histograms. Histograms created for non-skewed data are ignored by optimizer stats.(當對分區表增量收集統計信息時，oracle可能也會為數據分布并不崎嶇的列在分區級別收集統計信息，以此來獲得全表的統計信息，不過CBO會忽略這些分布并不崎嶇的列上的直方圖)

通過實驗和分析，我們可知基于原則3，在將列值進行RAW轉換后，Oracle很有可能會在數據分布相對均衡但是轉換成RAW后的唯一值數和表總行數相差較大的列上創建直方圖，造成執行計劃的變更，從而引起執行計劃波動和性能問題，一直到19.3.0.0版本，該算法和機制也沒有得到進一步的改善。

目前規避該問題最好的解決辦法是，作為DBA和開發人員，需要盡量多地了解表中數據的實際分布情況，在收集統計信息的時候，根據數據的實際分布情況和謂詞使用情況手工地收集直方圖，而不是由Oracle代替我們來進行判斷是否需要收集直方圖。

作者介紹：

吳海存，10g/11g/12c OCM, Oracle Exadata/Golden Gate 專家, 曾于Amazon和Oracle公司擔任全球業務資深DBA，目前供職于中國農業銀行，擔任資深數據庫專家。

同時，歡迎所有開發者掃描下方二維碼填寫《開發者與AI大調研》，只需2分鐘，便可收獲價值299元的「AI開發者萬人大會」在線直播門票!

推薦閱讀：你公司的虛擬機還閑著？基于 Jenkins 和 Kubernetes 的持續集成測試實踐了解一下！北京四環堵車引發的智能交通大構想 400 多行代碼！超詳細中文聊天機器人開發指南 | 原力計劃三大運營商將上線 5G 消息；蘋果谷歌聯手，追蹤 30 億用戶；jQuery 3.5.0 發布 | 極客頭條比特幣當贖金，WannaRen 勒索病毒二度來襲！從 Web 1.0到Web 3.0：詳析這些年互聯網的發展及未來方向真香，朕在看了！

總結

以上是生活随笔為你收集整理的你知道吗？其实 Oracle 直方图自动统计算法存在这些缺陷！（附验证步骤）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： 12 种主流编程语言输出“ Hello
下一篇：红点中国、红杉中国联合领投，WakeDa