谓词越界就是sql语句的查询条件超出了数据库统计信息所记录的范围，谓词越界会导致oracle优化器错误的选择sql语句的执行计划，从而导致sql性能问题。但随着oracle优化器的不断完善，对于少量的谓词越界，还是可以容忍的，这也是oracle为了解决统计信息的滞后性所带来的的性能问题而进行的优化吧。但是很多时候，谓词越界问题仍然会对我们造成困扰。
我们先来看看从少到多的谓词越界对sql语句中相关列的选择度是如何影响的：
由于统计信息的滞后性，谓词越界问题几乎是无法避免的，但是从上面的图片可以看出，少量的谓词越界对于oracle在进行列选择度评估的时候影响是不大的，但随着越界的越来越多，才会逐渐影响对列选择度的评估，直到越界的值达到了2倍的列的最大值减去列的最小值，或者两倍列的最小值减去列的最大值，这时候oracle已经无法正常准确的判断列的选择度了，那么这个时候sql语句产生性能问题在大多数情况下似乎也是必然了。
接下来我们先通过实例看看谓词越界问题是如何产生的。
测试数据准备：
create tablespace tbs0905 datafile '/u01/app/oracle/oradata/ces/tbs0905.dbf' size 20M autoextend off; create user test0905 identified by test0905 default tablespace tbs0905; grant dba to test0905; conn test0905/test0905 drop table tb0905; create table tb0905 (id number); create index idx_id on tb0905 (id); begin for i in 1..10000 loop insert into tb0905 values (i); end loop; commit; end; / exec dbms_stats.gather_table_stats(user,'TB0905',cascade=>true); |
查询一下tb0905表id列的最大值，最小值：
select low_value,high_value from dba_tab_col_statistics where table_name='TB0905' and owner='TEST0905' |
我们使用oracle自带的包将raw类型进行转换：
var x number; exec dbms_stats.convert_raw_value('C102',:x); select :x from dual; var y number; exec dbms_stats.convert_raw_value('C302',:y); select :y from dual |
可以看到在收集完统计信息之后，表中的实际的最大值和最小值和统计信息中记录的最大值最小值是一致的。接下来我们发起一条sql观察下该sql语句的执行计划：
explain plan for select * from tb0905 where id between 1 and 100000; select * from table(dbms_xplan.display); |
可以看出，针对这条sql这里oracle选择了最优的执行计划即走index fast full scan。
接下来我们继续往tb0905表中插入记录：
begin for i in 10001..100000 loop insert into tb0905 values (i); end loop; commit; end; / |
我们暂时先不收集统计信息，然后再次发起一条sql，观察该sql的执行计划：
select count(*) from tb0905 where id between 10001 and 100000; select * from table(dbms_xplan.display_cursor(null,null,'advanced')); |
这时候我们发现，oracle评估Rows只有1行，而实际有90000行，并且走了index rang scan，这显示是有问题的，而且正是因为统计信息收集不及时，而导致谓词越界，从而在执行该sql时oracle选择了错误的执行计划，从而可能产生性能问题。
以上我们模拟了最简单的一种造成谓词越界问题的案例，但是由于oracle对谓词越界有一定的容忍度，以及生产环境中可能也很少出现这种比较极端的情况，所以很多情况下少量的谓词越界是不会有大问题的，但是，接下来我们模拟一个案例，这个案例中看似谓词只越界了一点点，但是对sql的执行性能的影响可能是致命的。
测试数据准备：
create table tb0905_2 (id number,create_date number); begin for i in 0..11 loop for j in 1..30 loop insert into tb0905_2 values (i,20190101+i*100+j); end loop; end loop; end; / commit; insert into tb0905_2 select * from tb0905_2; insert into tb0905_2 select * from tb0905_2; insert into tb0905_2 select * from tb0905_2; insert into tb0905_2 select * from tb0905_2; insert into tb0905_2 select * from tb0905_2; insert into tb0905_2 select * from tb0905_2; commit; exec dbms_stats.gather_table_stats(user,'TB0905_2'); |
由上面的插入语句我们可以知道，create_date列（创建日期）的最大值为 20191231，接下来我们发起如下查询，观察其执行计划：
explain plan for select * from tb0905_2 where create_date=20191231; select * from table(dbms_xplan.display); |
可以看到，create_date为20191231的有64条记录，接下来我们将谓词条件改为20200101，也就是日期加一天，那么由前面的内容我们已经知道oracle对于少量的谓词越界是有一定容忍度的，也就是说这里我们想象的出，查询条件由20191231改成20200101时（也就是只越界了一天），oracle对行数的评估应该没有太大的影响，可能为63甚至仍然为64。
explain plan for select * from tb0905_2 where create_date=20200101; select * from table(dbms_xplan.display); |
然而，oracle这里评估的行数只有1行，这与我们之前想象的相差的有点多啊，这是为什么呢？可能细心的同学已经发现了问题的原因，没错，我前面在创建tb0905_2表的时候，create_date字段的类型选择的是number，这样一来如果是日期，那么20191231和20200101的确只差一天，也就是符合少量越界，但如果类型为number，那么20191231和20200101就相差了8870，这么大的越界，足以诱导oracle选择一个错误的执行计划，进而在实际的业务场景中对sql性能的影响可能是致命的。
当然这种情况是可以避免，或者说是可以提前预见的，其实我们在实际工作中遇到的关于越界的问题大多都是日期字段，在排查越界问题的时候，我们有时候就需要额外去关注业务上日期字段的定义类型。
文章最后，我们来看看谓词越界一般会发生在什么样的场景下以及如何解决：
临时表（中间表）
这里指的是业务上的临时表而不是Oracle数据库本身的temporary table。在某些系统中会根据业务条件创建中间表，数据先进入中间表，处理完毕后，存入目标表，并用delete/truncate语句清理中间表的数据，中间表起到一个临时表的作用。我们知道，Oracle自动收集统计信息的默认时间窗口是工作日晚上的22点到凌晨2点，或者周末的早上6点到第二天凌晨2点。在自动收集统计信息窗口内，数据库中间表基本上处于无数据，或者数据量很小的状态，那么产生的统计信息就会和白天实际处理业务数据时有较大偏差，就有可能发生谓词越界的情况。
对于这种情况，一般我们可以在业务上对于这种特殊的中间表进行特殊处理，比如白天加载数据之后，立马进行统计信息的收集。
大表
oracle触发自动收集某个表的统计信息的条件是表中修改的数据量超过该表数据总量的10%，但如果业务上存在一个很大的表，且该表每天的增长量不足以达到总数据量的10%,那么该表可能在短时间内无法触发自动收集统计信息的条件，从而导致谓词越界。
对于这种情况，我们可以修改oracle触发自动收集统计信息的条件，由默认的10%修改为1%或者0.1%，甚至可以通过参数的设置以达到强制某些表每天必须收集统计信息的目的，语句如下：
exec dbms_stats.set_table_prefs('USER','TB','STALE_PERCENT','1'); exec dbms_stats.set_table_prefs('USER','TB','STALE_PERCENT','0.1'); exec dbms_stats.set_table_prefs('USER','TB','STALE_PERCENT','0'); --强制每天都收集统计信息 |
