本文转载请联系数据云公众号。最近多次遇到客户现场统计信息过时导致执行计划选择错误导致的数据库性能问题。今天我来做一个总结。谓词越界通常发生在where谓词是时间字段的时候。一般来说，统计信息记录的是一个过时的时间，而SQL传入的时间是最新的时间范围（往往是selectcount(*)fromtest_obj;COUNT(*)------------94283TEST@PROD1>selectmax(object_ID),dump(max(object_id),16)fromtest_obj;MAX(OBJECT_ID)DUMP(MAX(OBJECT_ID),16)----------------------------------------------------100000Typ=2Len=2:c3,bTEST@PROD1>selectmin(object_ID),dump(min(object_id),16)fromtest_obj;MIN(OBJECT_ID)DUMP(MIN(OBJECT_ID),16)---------------------------------------------------------------------2Typ=2Len=2:c1,3--C103不收集统计信息。此时统计列的统计信息太旧了，HIGH_VALUE还是原来的值78179。------------------------------------------------------------------------------C103C308525072,462（原值）0查询结果返回2081行的结果集。TEST@PROD1>selectcount(*)fromtest_objwhereobject_idbetween78200and81000;COUNT(*)----------2801计算公式为：selectivity=((VAL2-VAL1)/(HIGH_VALUE-LOW_VALUE)+2/NUM_DISTINCT)*null_adjustnull_adjust=(NUM_ROES-NUM_NULLS)/NUM_ROES计算结果为：TEST@PROD1>selectround(((81000-78200)/(100000-2)+2/94283)*(94283-0)/94283*94283)fromdual；ROUND(((81000-78200)/(100000-2)+2/94283)*(94283-0)/94283*94283)------------------------------------------------------------------2642看看结果集，发现字典值为1，显然是错误的执行计划。因为统计信息太旧，已经低于predicate条件范围（predicateoutofbounds），导致CBO低估了查询成本。TEST@PROD1>selectcount(*)fromtest_objwhereobject_idbetween78200and81000;执行计划------------------------------------------------------Planhashvalue:2217143630---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|5|289(1)|00:00:04||1|SORTAGGREGATE||1|5||||*2|TABLEACCESSFULL|TEST_OBJ|1|5|289(1)|00:00:04|--------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):--------------------------------------------------2-filter("OBJECT_ID">=78200AND"OBJECT_ID"<=81000)统计------------------------------------------------------1recursivecalls0dbblockgets1117consistentgets0physicalreads0redosize423bytessentviaSQL*Nettoclient419bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed重新收集统计信息，再次查看执行计划TEST@PROD1>selectlow_value,high_value,num_distinct,num_nullsfromDBA_TAB_COL_STATISTICSwheretable_name='TEST@PROD1>andowner='TEST';DistinctNumberLOW_VALUEHIGH_VALUEValuesNulls----------------------------------------------------------C103C30B94,2830此时统计信息HIGH_VALUE等于最初计算的值，Typ=2Len=2:c3,b。再次查看执行计划，此时CBO已经能够生成正确的执行计划了。执行计划为：TEST@PROD1>selectcount(*)fromtest_objwhereobject_idbetween78200and81000;ExecutionPlan----------------------------------------------------------Planhashvalue:2217143630---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-----------------------------------------------------------------------------|0|SELECTSTATEMENT||1|5|314(1)|00:00:04||1|SORTAGGREGATE||1|5||||*2|TABLEACCESSFULL|TEST_OBJ|2642|13210|314(1)|00:00:04|--------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):------------------------------------------------2-filter("OBJECT_ID">=78200AND"OBJECT_ID"<=81000)统计------------------------------------------------------0recursivecalls0dbblockgets1117consistentgets0physicalreads0redosize423bytessentviaSQL*Nettoclient419bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed谓词越界主要发生在大表中。根据Oracle统计信息采集机制，只有当表数据变化超过10%时才会采集统计信息。大表不经常收集统计信息。易爆谓词越界预防方法可以根据谓词查询条件对关键表进行分区，即按天或按月分区可以避免这个问题。
