作者 | 吴海存
责编 | 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 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
可以看到，此时并没有收集直方图
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
----------
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
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_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
---------- ----------
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#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"
执行计划筛选分析
=====================================
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
确认在执行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 8
114538 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 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
说明：可以发现即使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 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
此时，将已有的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: 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
2.4执行sql并使用上面同样的oradebug方法进行trace,并对跟踪文件进行分析
执行计划分析
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
至此，我们可以得出如下结论：
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_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)
说明：可以看到列的最值是被转换成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')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
说明：该函数只是为了模拟测试使用，CBO具体使用什么转换函数，笔者查了相应的资料，并且也对统计信息会话进行了10046 trace和分析，目前没有得到准确的结果。
但是从此时该列上的数据实际分布情况来判断，数据分布并不倾斜：
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.
说明：该列上重复值最多的列值仅仅为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 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;
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
----------
114546
SQL> 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 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
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
----------
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.
执行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 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
---------- ----------
关闭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分析
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
说明：将列修改为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 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.
说明：在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：详析这些年互联网的发展及未来方向真香，朕在看了！
5124
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
