好兄弟给个网站,广州装修公司排行榜,中铁建设集团有限公司梅洪亮,苏州网站制作工作室作者 | 吴海存责编 | Carol出品 | CSDN 云计算#xff08;ID#xff1a;CSDNcloud#xff09;封图| CSDN下载于视觉中国在某些场景下#xff0c;表中某一列的数据分布会比较崎岖#xff0c;使得CBO(cost base optimizer)在评估执行计划的时候可能会出现误差#xff0c;从… 作者 | 吴海存责编 | Carol出品 | CSDN 云计算IDCSDNcloud封图| 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/NDVLO(low_value) : 列的最小值HI(high_value) : 列的最大值BKT(): 列的直方图桶数若为1则没有直方图Selectivity : 选择率用来计算列的基数基数cardselectivity*rowsNewdensity : 直方图密度 [(BktCnt-PopBktCnt)/BktCnt]/(NDV-PopValCnt)Rounded : 输出行数索引信息 LVLS索引高度 --BLEVEL BTREELB: 索引叶块数 --LEAF_BLOCKSDK: 索引唯一关键字数 -- DISTINCT_KEYSLB/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 valueinsert 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 rownum5265655;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_invalidatefalse);
说明当使用如上命令收集统计信息时直方图默认是for all columns auto的方式即由oracle根据相应的算法和机制自动判断是否对列收集直方图。col COLUMN_NAME format a30col HISTOGRAM format a10select column_name,histogram,low_value,high_valuefrom dba_tab_columnswhere table_nameDAY_TRNFLWand column_nameDAY_TRNTIME ;COLUMN_NAME HISTOGRAM------------------------------ ----------LOW_VALUE--------------------------------------------------------------------------------HIGH_VALUE--------------------------------------------------------------------------------DAY_TRNTIME NONE32303139303930313030303031313230313930393033323335393537可以看到此时并没有收集直方图1.5 下面使用10053事件跟踪CBO确认无直方图时是否会判断谓词越界获取会话IDSQL select sid from v$mystat where rownum1;select spid,pid from v$process a ,v$session b where a.addrb.paddr AND b.sidsid;SID----------3515SQL SQL Enter value for sid: 3515old 1: select spid,pid from v$process a ,v$session b where a.addrb.paddr AND b.sidsidnew 1: select spid,pid from v$process a ,v$session b where a.addrb.paddr AND b.sid3515SPID PID------------------------ ----------28366 157
1.6新开启一个会话使用oradebug对session 3515进行traceSQL ORADEBUG SETORAPID 157;Oracle pid: 157, Unix process pid: 28366, image: oraclehqxtsl-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.SQLPL/SQL procedure successfully completed.SQLPL/SQL procedure successfully completed.SQL 2 3sumAmt trnCount---------- ----------
1.7结束10053跟踪并找出相应的trace文件SQL ORADEBUG TRACEFILE_NAME;Statement processed.SQLoradebug event 10053 trace name context off/oracle/app/oracle/diag/rdbms/dbcon/dbcon1/trace/dbcon1_ora_28366.trc1.8经过分析10053事件的跟踪文件发现在没有直方图的情况下就算传递超出列值范围的绑定变量值CBO在此情况下不会判断谓词越界的现象分析如下绑定变量信息----- Bind Info (kkscoacd) -----Bind#0oacdty01 mxl128(90) mxlc00 mal00 scl00 pre00oacflg03 fl21000000 frm01 csi873 siz384 off0kxsbbbfp7fa6f5003ce8 bln128 avl05 flg05value12345Bind#1oacdty01 mxl128(90) mxlc00 mal00 scl00 pre00oacflg03 fl21000000 frm01 csi873 siz0 off128kxsbbbfp7fa6f5003d68 bln128 avl08 flg01value20190721Bind#2oacdty01 mxl128(90) mxlc00 mal00 scl00 pre00oacflg03 fl21000000 frm01 csi873 siz0 off256kxsbbbfp7fa6f5003de8 bln128 avl08 flg01value20190722执行计划筛选分析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_invalidatefalse);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_nameDAY_TRNFLWand column_nameDAY_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_TRNTIMEto_char(to_date(DAY_TRNTIME,yyyymmddhh24miss)-3,yyyymmddhh24miss) where rownum10000;commit;
2.2收集统计信息SQL exec dbms_stats.gather_table_stats(user,DAY_TRNFLW,no_invalidatefalse);
使用如下sql检查发现当表中有8月和9月的数据时收集统计信息的时候oracle自动收集了直方图SQL col COLUMN_NAME format a30col HISTOGRAM format a10select column_name,histogram,low_value,high_valuefrom dba_tab_columnswhere table_nameDAY_TRNFLWand column_nameDAY_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_idsql_id;Enter value for sql_id: azf5wm5qhptmyold 1: select sql_id,address,hash_value,PLAN_HASH_VALUE from v$sql where sql_idsql_idnew 1: select sql_id,address,hash_value,PLAN_HASH_VALUE from v$sql where sql_idazf5wm5qhptmySQL_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_idazf5wm5qhptmy;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通过比较开销选择了索引CSQL的执行计划发生了相应变化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 rownum10;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再次进行绑定变量窥测的时候有可能会选择效率不高的执行计划造成后续的性能问题(如上面同时存在89月份数据的测试案例)。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 rownum5265655;commit;4.2模拟表中同时存在8月和9月的数据update DAY_TRNFLW set DAY_TRNTIMEDAY_TRNTIME-3 where rownum10000;Commit;4.3确认col_usage$是否有谓词使用记录SQL select object_id from dba_objects where ownerSAM and object_nameDAY_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 SQLPL/SQL procedure successfully completed.SQLPL/SQL procedure successfully completed.SQLPL/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 5134.5收集统计信息SQL exec dbms_stats.gather_table_stats(user,DAY_TRNFLW,no_invalidatefalse);4.6清理原有执行计划SQL alter system flush shared_pool;System altered.4.7进行date类型的10053跟踪开启会话跟踪SQL select sid from v$mystat where rownum1;select spid,pid from v$process a ,v$session b where a.addrb.paddr AND b.sidsid;SID----------2664SQL SQL Enter value for sid: 2664old 1: select spid,pid from v$process a ,v$session b where a.addrb.paddr AND b.sidsidnew 1: select spid,pid from v$process a ,v$session b where a.addrb.paddr AND b.sid2664SPID PID------------------------ ----------18094 150SQL ORADEBUG SETORAPID 150;Oracle pid: 150, Unix process pid: 18094, image: oraclehqxtsl-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.SQLPL/SQL procedure successfully completed.SQLPL/SQL procedure successfully completed.SQL SQL 2 3sumAmt trnCount---------- ----------关闭10053跟踪SQL ORADEBUG TRACEFILE_NAME;Statement processed.SQLoradebug event 10053 trace name context off;/oracle/app/oracle/diag/rdbms/dbcon/dbcon1/trace/dbcon1_ora_18094.trc4.8进行date类型的10053分析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: 1866880 Nulls: 0 Density: 0.000001Estimated selectivity: 5.3565e-07 , col: #3kkecdn: Single Table Predicate:DAY_TRNFLW.DAY_CSTNO:B1Estimated selectivity: 5.3565e-07 , col: #3kkecdn: Single Table Predicate:DAY_TRNFLW.DAY_TRNTIME:B1Column (#2):NewDensity:0.000004, OldDensity:0.000004 BktCnt:5495.000000, PopBktCnt:0.000000, PopValCnt:0, NDV:272032Column (#2): DAY_TRNTIME(DATE)AvgLen: 8 NDV: 272032 Nulls: 0 Density: 0.000004 Min: 2458725.000243 Max: 2458730.999988Histogram: 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:B1Using prorated density: 9.4955e-08 of col #2 as selectivity of out-of-range/non-existent value pred --此处同样发生了谓词越界Table: DAY_TRNFLW Alias: DAY_TRNFLWCard: 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_C1Cost: 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 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详析这些年互联网的发展及未来方向真香朕在看了
