④内部公开请勿外传--1问题背景，客户计划把版本升级到7.5，测试时发现进度卡在30%，等了很久不见前进，--语句执行了1.2小时后，被杀掉，--2从EM的SQL监视功能观察，发现有个进程在执行某个查询时，执行了很久，状态如下界面，在第25分钟时，我把此查询进程中断了，--3执行代码如下：SELECTsends.FSENDIDSENDID,sends.FSRCBIILLFORMIDSENDFORMID,rec.FRECIDRECID,productdime.FPRODUCTIDPMATERIALID_ID,1/6④内部公开请勿外传productdime.FPRODUCTNOPBILLNO,productdime.FBILLSEQPBILLSEQ,sends.FOUTINSTOCKIDSEQENTRYID,stockdmie.FMATERIALIDCMATERIALID_ID,sends.FBILLNOCBILLNO,sends.FBILLSEQCBILLSEQ,sends.FSTEPSTEPFROMT_CB_COSTRESULTREC_138003RECINNERJOINT_CB_PROORDERDIMEPRODUCTDIMEONproductdime.FProductDimeId=rec.FProductDimeIdINNERJOINt_bd_MaterialMATERIALONproductdime.FPRODUCTID=material.FMATERIALIDINNERJOINT_CB_COSTRESULTSEND_138003SENDSONrec.FSENDID=sends.FSENDIDINNERJOINT_HS_InivStockDimensionSTOCKDMIEONstockdmie.FENTRYID=sends.FDIMEENTRYIDINNERJOINt_bd_MaterialSENDMATERIALONsendmaterial.FMATERIALID=stockdmie.FMATERIALIDINNERJOINTMP8C5C1E64177211EBB020005056B"TEMP"ONmaterial.FMASTERID="TEMP".FMASTERIDINNERJOINTMP8C5C1E64177211EBB020005056BSENDTEMPONsendmaterial.FMASTERID=sendtemp.FMASTERIDWHERE((((sends.FOUTACCTGID=:FAcctgIdAND"TEMP".FLevel=:FLevel)ANDsendtemp.FLevel=:FLevel1)ANDNOTEXISTS(SELECT1FROMt_bd_MaterialMATERIALSUBWHERE((materialsub.FMATERIALID=material.FMATERIALIDANDmaterialsub.FMASTERID=sendmaterial.FMASTERID)ANDsends.FSTEP=6)))ANDNOTEXISTS(SELECT1FROM(SELECT/*+CARDINALITY(B4473)*/FIDFROM(SELECTCOLUMN_VALUEASFIDFROMTABLE(CAST(:FID_udtASudt_inttable)))b)U0WHEREsends.FSENDID=U0.FID))/--4经观察分析语句的执行计划，发现问题如下：红框部分，可以看到，走索引时，优化器认为只有1行（预估数）记录符合要求，但实际有1619行记录（实际行数），2/6④内部公开请勿外传--4该临时表的索引情况如下：FLEVEL为索引的第1个字段,FSEQ为第二个字段。--5字段FLEVEL的唯一性，及各值的情况如下：可以看到，FLEVLE字段，有6个唯一值，除了FLEVEL=1有379385行记录外，其他值的记录数，从4条记录，到1619条；从执行计划的实际值看，此时，FLEVEL=7(1619行记录)，但为何优化器里却显示，预估值=1？？？下面是从共享池中捕获该语句的执行计划，该语句和EM里的一样，3/6④内部公开请勿外传可以看到，ID=13，和1D=16这两步，对应的是WHERE段中的过滤条件：AND"TEMP".FLevel=:FLevelANDsendtemp.FLevel=:FLevel1所得，由于两者的预估值都是1，优化器因此对这两条件作了笛卡尔乘积，认为结果集行数应该为：1*1=1（ID=11步）（但实际结果却是：1619*1619=2621161(2621K)（EM的执行计划）），导致后续以此结果集作驱动源，选择了错误执行顺序和关联方法，致使后续结果集非常大，也就是，优化器认为FLEVEL=7的记录数为1，是语句低效（卡顿）的根源。--7我查看数据字典时，看到该表的FLEVEL字段的唯一性个数，是1，但显然，这个值，肯定不是绑定变量里的值，因为EM的执行计划显示，该绑定变量值有1619条，从上面语句查到FLEVEL的各个值的数量看，此时该绑定变量值为7，但优化器采集在采集该表时，为何却是1，也就是为何没采集到其他5个数值？--8之后我手工采集，发现此时该字段的唯一性个数变成6，为何之前却是1???，百思不得其解，4/6④内部公开请勿外传--9可以看出，若优化器不知道还存在其他数据，这SQL低效的问题没法修正：当时还在苦苦思索中，没什么点子，当晚客户任由系统做计算，经过一晚上的等待，第二天一看，该语句耗时2.1小时。--10想不通就不想了，把这块先放一遍；为改变执行计划，建议同事删掉(FLEVEL,FSEQ)这两字段的索引：因为很显然，没有此索引，优化器将对临时表作全表扫描，此时很可能对该临时表及关系表作哈希关联，这样就不会有笛卡尔乘积的问题，但同事不同意，说后面会用上此索引；后又建议调换两索引字段的顺序，没响应；最后又想到把这临时表改为列表分区表，事先对FLEVEL定义20个分区。心想着，做了列表分区后，即便统计信息不对，至少物理上也已经分组了各类的数据，会话进程定位需要的读取数据也方便，，，同事觉得可行，按照我的思路，把临时表改成列表分区表（此时还不知道问题的根源），让客户去测试。测试发现，由于列表分区排除了其他不符合条件的大部分的数据（FLEVEL=1的情况），并且能够快速读取到符合条件的数据（读取指定分区的数据），最终执行效率还算好，1.5分钟完成操作，比之前的2.1小时快多了。5/6④内部公开请勿外传到这时，再去问同事，这些1619条数据是怎么产生的？才知道：一开始是插完数据，采集完表的统计信息后开始业务操作，，，之后随着业务的深入，程序后续又对这表的数据，做了UPDATE操作，把部分原先FLEVEL=1的值，改成=2,3,4,5,6,7,但此时，修改完数据后，却又不采集统计信息：此时茅塞顿开：我在EM里看到的执行计划使用的统计信息，是UPDATE前的；而我手工采集到的，是UPDATE完之后的，，，真伤脑子，后续提醒同事，UPDATE完之后，记得再采集。6/6
