作者 | 吳海存
出品 | 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類型
<code>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;/<code>
1.2模擬數據，此時只模擬單月數據，數據不跨月，因為若數據跨月的話，oracle在轉換成raw類型的時候，會產生兩個internal value
<code>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; /<code>
1.3創建相應的索引
<code>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");/<code>1.4收集統計信息並確認此時沒有自動收集直方圖
<code>exec dbms_stats.gather_table_stats(user,'DAY_TRNFLW',no_invalidate=>false);/<code>
說明：當使用如上命令收集統計信息時，直方圖默認是for all columns auto的方式，即由oracle根據相應的算法和機制自動判斷是否對列收集直方圖。
<code>col COLUMN_NAME format a30
col HISTOGRAM format a10
select column_name,histogram,low_value,high_value
from dba_tab_columns
where table_name='DAY_TRNFLW'
and column_name='DAY_TRNTIME' ;
COLUMN_NAME HISTOGRAM
------------------------------ ----------
LOW_VALUE
--------------------------------------------------------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------
DAY_TRNTIME NONE
3230313930393031303030303131
3230313930393033323335393537/<code>
可以看到，此時並沒有收集直方圖
1.5 下面使用10053事件跟蹤CBO，確認無直方圖時是否會判斷謂詞越界
獲取會話ID
<code>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
----------
3515
SQL> SQL> Enter value for sid: 3515
old 1: select spid,pid from v$process a ,v$session b where a.addr=b.paddr AND b.sid=&sid
new 1: select spid,pid from v$process a ,v$session b where a.addr=b.paddr AND b.sid=3515
SPID PID
------------------------ ----------
28366 157/<code>
1.6新開啟一個會話，使用oradebug對session 3515進行trace
<code>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;/<code>
在session 3515中執行sql觸發硬解析（第一次執行）
<code>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_TRNFLW
where DAY_CSTNO = :p0
and 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 3
sumAmt trnCount
---------- ----------/<code>
1.7結束10053跟蹤，並找出相應的trace文件
<code>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/<code>
1.8經過分析10053事件的跟蹤文件，發現在沒有直方圖的情況下，就算傳遞超出列值範圍的綁定變量值，CBO在此情況下不會判斷謂詞越界的現象，分析如下：
綁定變量信息
<code>----- Bind Info (kkscoacd) -----
Bind#0
oacdty=01 mxl=128(90) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=873 siz=384 off=0
kxsbbbfp=7fa6f5003ce8 bln=128 avl=05 flg=05
value="12345"
Bind#1
oacdty=01 mxl=128(90) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=128
kxsbbbfp=7fa6f5003d68 bln=128 avl=08 flg=01
value="20190721"
Bind#2
oacdty=01 mxl=128(90) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=256
kxsbbbfp=7fa6f5003de8 bln=128 avl=08 flg=01
value="20190722"/<code>
執行計劃篩選分析
<code>=====================================
Access path analysis for DAY_TRNFLW
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for DAY_TRNFLW[DAY_TRNFLW]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
Column (#3): DAY_CSTNO(VARCHAR2)
AvgLen: 8 NDV: 70976 Nulls: 0 Density: 0.000014
Estimated selectivity: 1.4089e-05 , col: #3
kkecdn: Single Table Predicate:"DAY_TRNFLW"."DAY_CSTNO"=:B1
Estimated selectivity: 1.4089e-05 , col: #3
kkecdn: Single Table Predicate:"DAY_TRNFLW"."DAY_TRNTIME">=:B1
Column (#2): DAY_TRNTIME(VARCHAR2) --此處數據類型是varchar2
AvgLen: 15 NDV: 63912 Nulls: 0 Density: 0.000016
Estimated selectivity: 1.000000 , col: #2 --說明：該處的選擇率為1是正確的，因為表中所有的行都滿足大於綁定變量的值’20190721’，基數即為所有的行
kkecdn: Single Table Predicate:"DAY_TRNFLW"."DAY_TRNTIME"<=:B1
Using density: 1.5647e-05 of col #2 as selectivity of unpopular value pred --因為從謂詞中得不到合適的選擇率，此處直接使用上面的密度，這裡可以看到，雖然傳遞的綁定變量值’20190722’都小於列值(9月份數據)，CBO並沒有判斷髮生了謂詞越界
Table: DAY_TRNFLW Alias: DAY_TRNFLW
Card: Original: 73269.000000 Rounded: 1 Computed: 0.000016 Non Adjusted: 0.000016 --使用該密度的情況下，輸出值認為是1
Scan IO Cost (Disk) = 102.000000
Scan CPU Cost (Disk) = 16556182.800000
.
.
.
Best:: AccessPath: IndexRange
Index: INDEX_DAY_TRNFLW_A --CBO選擇了正確的索引A
Cost: 3.000594 Degree: 1 Resp: 3.000594 Card: 0.000016 Bytes: 0.000000/<code>
確認在執行sql查詢後,表sys.col_usage$裡已有列DAY_TRNTIME的謂詞使用信息,因為若該表中沒有列DAY_TRNTIME的謂詞使用記錄，則收集統計信息時候不會主動收集直方圖
<code>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 8
114538 3 3 0 0 0 0 0 20-FEB-20 513/<code>
1.9進行統計信息蒐集
<code>SQL> exec dbms_stats.gather_table_stats(user,'DAY_TRNFLW',no_invalidate=>false);
PL/SQL procedure successfully completed./<code>
1.10確認直方圖信息
<code>SQL> col COLUMN_NAME format a30
col HISTOGRAM format a10
select column_name,histogram,low_value,high_value
from dba_tab_columns
where table_name='DAY_TRNFLW'
and column_name='DAY_TRNTIME' ;SQL> SQL> 2 3 4
COLUMN_NAME HISTOGRAM
------------------------------ ----------
LOW_VALUE
--------------------------------------------------------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------
DAY_TRNTIME NONE
3230313930393031303030303032
3230313930393033323335393532/<code>
說明：可以發現即使col_usage$裡有列DAY_TRNTIME的謂詞使用記錄，但是由於表中只有9月份的記錄，轉換成RAW格式後只有一個internal value, CBO認為該列上的值分均均衡，沒有收集直方圖信息。
測試表中含有多月(8月和9月)的數據進行
2.1將表中數據更新為8月份，模擬多月數據
<code>update DAY_TRNFLW set DAY_TRNTIME=to_char(to_date(DAY_TRNTIME,'yyyymmddhh24miss')-3,'yyyymmddhh24miss') where rownum<=10000;
commit;/<code>
2.2收集統計信息
<code>SQL> exec dbms_stats.gather_table_stats(user,'DAY_TRNFLW',no_invalidate=>false);/<code>
使用如下sql檢查，發現當表中有8月和9月的數據時，收集統計信息的時候oracle自動收集了直方圖
<code>SQL> col COLUMN_NAME format a30
col HISTOGRAM format a10
select column_name,histogram,low_value,high_value
from dba_tab_columns
where table_name='DAY_TRNFLW'
and column_name='DAY_TRNTIME' ;SQL> SQL> 2 3 4
COLUMN_NAME HISTOGRAM
------------------------------ ----------
LOW_VALUE
--------------------------------------------------------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------
DAY_TRNTIME HYBRID
3230313930383239303030303032
3230313930393033323335393532/<code>
此時，將已有的sql執行計劃從share pool中清理掉，重新觸發硬解析並使用10053進行跟蹤，發現CBO在有直方圖的情況下判斷了是否會發生謂詞越界。
2.3從share pool中清理現有sql執行計劃，以便重新硬解析該sql(10053事件只能跟蹤硬解析，不能跟蹤軟解析和軟軟解析)
<code>SQL> select sql_id,address,hash_value,PLAN_HASH_VALUE from v$sql where sql_id='&sql_id';
Enter value for sql_id: azf5wm5qhptmy
old 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 2119561882
SQL> BEGIN
DBMS_SHARED_POOL.PURGE('&address,&hash_value', 'C');
END;
/ 2 3 4
Enter value for address: 00000000FF897F90
Enter value for hash_value: 1829430910
old 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/<code>
2.4執行sql並使用上面同樣的oradebug方法進行trace,並對跟蹤文件進行分析
執行計劃分析
<code>Access path analysis for DAY_TRNFLW
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for DAY_TRNFLW[DAY_TRNFLW]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
Column (#3): DAY_CSTNO(VARCHAR2)
AvgLen: 8 NDV: 70976 Nulls: 0 Density: 0.000014
Estimated selectivity: 1.4089e-05 , col: #3
kkecdn: Single Table Predicate:"DAY_TRNFLW"."DAY_CSTNO"=:B1
Estimated selectivity: 1.4089e-05 , col: #3
kkecdn: Single Table Predicate:"DAY_TRNFLW"."DAY_TRNTIME">=:B1
Column (#2):
NewDensity:0.000015, OldDensity:0.000015 BktCnt:5371.000000, PopBktCnt:0.000000, PopValCnt:0, NDV:65096
Column (#2): DAY_TRNTIME(VARCHAR2)
AvgLen: 15 NDV: 65096 Nulls: 0 Density: 0.000015
Histogram: Hybrid #Bkts: 254 UncompBkts: 5371 EndPtVals: 254 ActualVal: yes
Estimated selectivity: 1.000000 , col: #2 --此處CBO評估出來選擇率還是1，因為所有行都滿足大於’20190721’的條件，但是在收集直方圖統計信息的時候，桶數為254，因此收集統計信息的時候是可以探測到該列上是存在大於或等於254個唯一值的，且有5371個被壓縮的buckets
kkecdn: Single Table Predicate:"DAY_TRNFLW"."DAY_TRNTIME"<=:B1
Using 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_A
resc_io: 4.000000 resc_cpu: 29216 --CBO評估出走索引A的開銷為4
ix_sel: 1.4089e-05 ix_sel_with_filters: 9.6148e-11
Cost: 4.000785 Resp: 4.000785 Degree: 1
****** Costing Index INDEX_DAY_TRNFLW_C
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
Estimated selectivity: 1.000000 , col: #2
Using prorated density: 6.8242e-06 of col #2 as selectivity of out-of-range/non-existent value pred
Access Path: index (RangeScan)
Index: INDEX_DAY_TRNFLW_C
resc_io: 3.000000 resc_cpu: 21919 --CBO通過索引C可以直接通過謂詞越界過濾掉所有的行，評估出走索引C的開銷為3，低於索引A
ix_sel: 6.8242e-06 ix_sel_with_filters: 6.8242e-06
Cost: 3.000588 Resp: 3.000588 Degree: 1
Used INDEX_DAY_TRNFLW_C
Cost = 3.000393, sel = 1.5362e-05
Not used INDEX_DAY_TRNFLW_A
Cost = 4.000590, sel = 1.4089e-05
.
.
.
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: INDEX_DAY_TRNFLW_C --最終，CBO通過比較開銷，選擇了索引C，SQL的執行計劃發生了相應變化
Cost: 3.000588 Degree: 1 Resp: 3.000588 Card: 0.000016 Bytes: 0.000000/<code>
至此，我們可以得出如下結論：
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中得到一定的證實：
<code>SQL> desc dba_tab_columns
Name 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)/<code>
說明：可以看到列的最值是被轉換成raw類型放在數據庫中的，該最值在有直方圖的時候會被用來判斷是否謂詞越界。
RAW轉換模擬測試
我們可以使用utl_raw函數模擬了一下CBO對列進行評估，可以發現純數字字符串被轉換成raw的時候的確變成了一樣的值
<code>SQL> select utl_raw.cast_to_raw('DAY_TRNTIME') from DAY_TRNFLW where rownum<=10;
UTL_RAW.CAST_TO_RAW('DAY_TRNTIME')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4441595F54524E54494D45
4441595F54524E54494D45
4441595F54524E54494D45
4441595F54524E54494D45
4441595F54524E54494D45
4441595F54524E54494D45
4441595F54524E54494D45
4441595F54524E54494D45
4441595F54524E54494D45
4441595F54524E54494D45
SQL> select utl_raw.cast_to_raw('DAY_TRNTIME') raw_for_cbo, count(1) from DAY_TRNFLW group by 1;
RAW_FOR_CBO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COUNT(1)
----------
4441595F54524E54494D45
73269/<code>說明：該函數只是為了模擬測試使用，CBO具體使用什麼轉換函數，筆者查了相應的資料，並且也對統計信息會話進行了10046 trace和分析，目前沒有得到準確的結果。
但是從此時該列上的數據實際分佈情況來判斷，數據分佈並不傾斜：
<code>select DAY_TRNTIME, count(1) num_rows from DAY_TRNFLW group by DAY_TRNTIME order by 2;
.
.
.
20190901200645 4
20190901200958 4
20190901213042 4
20190901224944 4
20190902005954 4
20190902092241 4
20190902102315 4
20190902113652 4
20190902135131 4
20190902151930 4
20190902172156 4
DAY_TRNTIME NUM_ROWS
------------------------------ ----------
20190902173951 4
20190902182755 4
20190902201506 4
20190903001538 4
20190903033351 4
20190903040014 4
20190903180612 4
20190902212139 5
65887 rows selected./<code>
說明：該列上重複值最多的列值僅僅為5行，分佈相對均衡，所以是沒有必要統計直方圖的，但是Oracle根據相應的算法和機制，為該列統計了直方圖，CBO再次進行綁定變量窺測的時候，有可能會選擇效率不高的執行計劃，造成後續的性能問題(如上面同時存在8，9月份數據的測試案例)。
Date數據類型測試
如上所述，Oracle不建議使用varchar2保存純數字的字符串，因為轉換成RAW後可能會造成CBO評估不準確。下面，我們將該列改成date類型進一步測試
4.1創建date型數據表
<code>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 11
Table 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;/<code>
4.2模擬表中同時存在8月和9月的數據
<code>update DAY_TRNFLW set DAY_TRNTIME=DAY_TRNTIME-3 where rownum<=10000;
Commit;/<code>
4.3確認col_usage$是否有謂詞使用記錄
<code>SQL> select object_id from dba_objects where owner='SAM' and object_name='DAY_TRNFLW';
OBJECT_ID
----------
114546
SQL> select * from sys.col_usage$ where obj#=114546;
no rows selected./<code>
4.4執行一下sql,以便oracle可以記錄該謂詞使用記錄
<code>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 where
DAY_CSTNO = :p0 and
DAY_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 3
sumAmt 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 0
1 0 0 20-FEB-20 8
114546 3 1 0 0
0 0 0 20-FEB-20 513/<code>
4.5收集統計信息
<code>SQL> exec dbms_stats.gather_table_stats(user,'DAY_TRNFLW',no_invalidate=>false);/<code>
4.6清理原有執行計劃
<code>SQL> alter system flush shared_pool;
System altered./<code>
4.7進行date類型的10053跟蹤
開啟會話跟蹤
<code>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
----------
2664
SQL> SQL> Enter value for sid: 2664
old 1: select spid,pid from v$process a ,v$session b where a.addr=b.paddr AND b.sid=&sid
new 1: select spid,pid from v$process a ,v$session b where a.addr=b.paddr AND b.sid=2664
SPID PID
------------------------ ----------
18094 150
SQL> 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./<code>
執行sql，觸發硬解析
<code>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 where
DAY_CSTNO = :p0 and
DAY_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 3
sumAmt trnCount
---------- ----------/<code>
關閉10053跟蹤
<code>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/<code>
4.8進行date類型的10053分析
<code>Access path analysis for DAY_TRNFLW
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for DAY_TRNFLW[DAY_TRNFLW]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
Column (#3): DAY_CSTNO(VARCHAR2)
AvgLen: 8 NDV: 1866880 Nulls: 0 Density: 0.000001
Estimated selectivity: 5.3565e-07 , col: #3
kkecdn: Single Table Predicate:"DAY_TRNFLW"."DAY_CSTNO"=:B1
Estimated selectivity: 5.3565e-07 , col: #3
kkecdn: Single Table Predicate:"DAY_TRNFLW"."DAY_TRNTIME">=:B1
Column (#2):
NewDensity:0.000004, OldDensity:0.000004 BktCnt:5495.000000, PopBktCnt:0.000000, PopValCnt:0, NDV:272032
Column (#2): DAY_TRNTIME(DATE)
AvgLen: 8 NDV: 272032 Nulls: 0 Density: 0.000004 Min: 2458725.000243 Max: 2458730.999988
Histogram: Hybrid #Bkts: 254 UncompBkts: 5495 EndPtVals: 254 ActualVal: no --換成date後，此處的選擇率同樣為1，並且同樣收集了直方圖
Estimated selectivity: 1.000000 , col: #2
kkecdn: Single Table Predicate:"DAY_TRNFLW"."DAY_TRNTIME"<=:B1
Using prorated density: 9.4955e-08 of col #2 as selectivity of out-of-range/non-existent value pred --此處同樣發生了謂詞越界
Table: DAY_TRNFLW Alias: DAY_TRNFLW
Card: Original: 5265655.000000 Rounded: 1 Computed: 0.000010 Non Adjusted: 0.000010
.
.
.
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange -可以發現，換成date後該問題一樣重現，說明和該列的數據類型無關係，而是和oracle收集直方圖的機制和算法有關係
Index: INDEX_DAY_TRNFLW_C1
Cost: 4.000779 Degree: 1 Resp: 4.000779 Card: 0.000010 Bytes: 0.000000/<code>
說明：將列修改為date類型後，當表中存在8月和9月的數據時，oracle同樣對列DAY_TRNFLW收集了直方圖並檢查了謂詞越界，說明和該列的數據類型無關係，而是由oracle收集直方圖的機制和算法決定的。
4.9檢查數據分佈情況
<code>select DAY_TRNTIME, count(1) num_rows from DAY_TRNFLW group by DAY_TRNTIME order by 2;
.
.
.
03-SEP-19 40
03-SEP-19 40
03-SEP-19 40
03-SEP-19 40
02-SEP-19 40
DAY_TRNTI NUM_ROWS
--------- ----------
01-SEP-19 40
01-SEP-19 40
03-SEP-19 41
02-SEP-19 41
03-SEP-19 41
03-SEP-19 41
01-SEP-19 42
03-SEP-19 42
01-SEP-19 42
01-SEP-19 42
02-SEP-19 43
269016 rows selected./<code>
說明：在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，目前供職於中國農業銀行，擔任資深數據庫專家。
閱讀更多 啟迪雲Tuscloud 的文章
