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

查看原文

其他

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

Original 吴海存 CSDN云计算 2020-10-29

作者 | 吴海存

责编 | 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开发者万人大会」在线直播门票!

推荐阅读：

真香，朕在看了！

喜欢此内容的人还喜欢

”FAN某”的离婚财产分割判决书（全文）

”FAN某”的离婚财产分割判决书（全文）

公益慈善｜“翼行天下一生守护”慈善项目捐赠仪式圆满举行！

哈里斯女粉搞4B运动、毒杀丈夫，回旋镖能否让美国“血流成河”

比国产光刻机更重要的IPO要来了！