1987.Oracle谓词越界

张鹏 2024-01-24

130

1987.Oracle谓词越界
什么是谓词越界？
目标列指定的where 查询条件的值不在统计信息收集的最大值与最小值之前。这就是谓词越界如果出现了这种现状，CBO就无法判断出针对该列的查询条件的选择率，只能用一个估算的值，作为查询条件的可选择率，如果这个估算的值与实际情况严重不符的话，就可能是CBO选错执行计划。

屡次发生的Oracle谓词越界
近期在客户现场屡次遇到由于统计信息过旧导致执行计划选错引发的数据库性能问题，今天做个总结：
谓词越界常见发生在where谓词是时间字段的，总的来说统计信息记录的是一个过旧的时间，而SQL传入的时间是一个最新的时间范围（往往是<time time1<c<time2），由于统计信息不全，按照CBO计算出来的结果集就很小，在多表关联的情况下，CBO就会选择认为的最优的关联方式，而实际执行时发现不是那么回事，有大量结果集需要扫描，就会爆发SQL性能问题。
谓词越界就是select的谓词的条件不在统计信息low_value 和 high_value 之间，在实际选择结果集要大于CBO记录的结果集数量，即实际的selectivity偏大，这种情况下CBO评估出来的selectivity会出现严重的偏差，导致CBO选错执行计划。
测试验证
下面做一组测试，从执行计划cost看谓词越界的发生过程，先插入部分数据
DECLARE
i INT;
BEGIN
i := 78179;
WHILE(i < 100000)
LOOP
i := i + 1;
INSERT INTO test_obj(object_id) VALUES(i);
COMMIT;
END LOOP;
END;
/
查看此时的num_rows：
TEST@PROD1> select count() from test_obj;
COUNT()

TEST@PROD1> select max(object_ID),dump(max(object_id),16) from test_obj;

MAX(OBJECT_ID) DUMP(MAX(OBJECT_ID),16)

    100000 Typ=2 Len=2: c3,b

TEST@PROD1> select min(object_ID),dump(min(object_id),16) from test_obj;

MIN(OBJECT_ID ) DUMP(MIN(OBJECT_ID),16)

  2                          Typ=2 Len=2: c1,3        --C103

不收集统计信息，此时统计列统计信息过旧，HIGH_VALUE依然是原来的值78179
TEST@PROD1> select low_value ,high_value,num_distinct,num_nulls from DBA_TAB_COL_STATISTICS where table_name=‘TEST_OBJ’ and owner=‘TEST’;

Distinct Number
LOW_VALUE HIGH_VALUE Values Nulls

C103 C3085250 72,462（原值） 0

查询结果返回2081行结果集。
TEST@PROD1> select count() from test_obj where object_id between 78200 and 81000;
COUNT()

计算公式为：
selectivity=((VAL2 - VAL1) / (HIGH_VALUE - LOW_VALUE)+2 / NUM_DISTINCT) * null_adjust
null_adjust=(NUM_ROES - NUM_NULLS) / NUM_ROES
计算结果为：
TEST@PROD1> select round(((81000-78200)/(100000-2)+2/94283)(94283-0)/9428394283) from dual;

ROUND(((81000-78200)/(100000-2)+2/94283)(94283-0)/9428394283)

查看结果集发现dictionary值为1，这明显是一个错误的执行计划，由于统计信息过旧，已经低于谓词条件区间（谓词过界）导致CBO低估了查询成本。
TEST@PROD1> select count(*) from test_obj where object_id between 78200 and 81000;

Execution Plan

Plan hash value: 2217143630

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 5 | 289 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| TEST_OBJ | 1 | 5 | 289 (1)| 00:00:04 |

Predicate Information (identified by operation id):

2 - filter(“OBJECT_ID”>=78200 AND “OBJECT_ID”<=81000)

Statistics

      1  recursive calls
      0  db block gets
   1117  consistent gets
      0  physical reads
      0  redo size
    423  bytes sent via SQL*Net to client
    419  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

重新收集统计信息再次查看执行计划。
TEST@PROD1> exec dbms_stats.gather_table_stats(‘test’,‘test_obj’);
TEST@PROD1> select low_value ,high_value,num_distinct,num_nulls from DBA_TAB_COL_STATISTICS where table_name=‘TEST_OBJ’ and owner=‘TEST’;

                                          Distinct     Number

LOW_VALUE HIGH_VALUE Values Nulls

C103 C30B 94,283 0
此时统计信息HIGH_VALUE已经和最初计算的值相等，Typ=2 Len=2: c3,b。再次查看执行计划，此时CBO已经能够产生了正确的执行计划了。
执行计划为：
TEST@PROD1> select count(*) from test_obj where object_id between 78200 and 81000;

Execution Plan

Plan hash value: 2217143630

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 5 | 314 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| TEST_OBJ | 2642 | 13210 | 314 (1)| 00:00:04 |

Predicate Information (identified by operation id):

2 - filter(“OBJECT_ID”>=78200 AND “OBJECT_ID”<=81000)

Statistics

      0  recursive calls
      0  db block gets
   1117  consistent gets
      0  physical reads
      0  redo size
    423  bytes sent via SQL*Net to client
    419  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

谓词越界主要发生在大表，按照Oracle统计信息收集机制，表的数据变化量达到10%以上才会进行统计信息收集，大表不常收集统计信息就容易爆发谓词越界
预防方式：
可对关键表实行按谓词查询条件分区，即按天或者按月分区可规避此问题发生。

Oracle谓词越界引发的案例
什么是谓词越界？
目标列指定的where 查询条件的值不在统计信息收集的最大值与最小值之前。这就是谓词越界如果出现了这种现状，CBO就无法判断出针对该列的查询条件的选择率，只能用一个估算的值，作为查询条件的可选择率，如果这个估算的值与实际情况严重不符的话，就可能是CBO选错执行计划。
具体案例如下：
开发人员发过一个sql，让给优化下;
SELECT o.refxxxx, count(o.refxxxx)
FROM t_xxxxxxx_open_SSSS o
join (SELECT i.xxxxxxx_account, sum(i.intxxxxx) ljsy1
FROM t_XXXXXXX_inXXXXX_inXXXXXt i
where i.inxxxxxx_date <= ‘2015-11-23’ --1
and i.inxxxxxx_date >= ‘2015-07-01’
group by i.xxxxxxx_account) tt
on o.xxxxxxx_account = tt.xxxxxxx_account
join (SELECT i.xxxxxxx_account, sum(i.intxxxxx) ljsy2
FROM t_XXXXXXX_inXXXXX_inXXXXXt i
where i.inxxxxxx_date <= ‘2015-11-24’ --2
and i.inxxxxxx_date >= ‘2015-07-01’
group by i.xxxxxxx_account) ttt
on o.xxxxxxx_account = ttt.xxxxxxx_account
where to_char(o.create_date, ‘yyyy-MM-dd’) >= ‘2015-09-01’
and tt.ljsy1 < 1
and ttt.ljsy2 >= 1
and o.areacode = ‘350000’
*and o.cxxxcode !=‘510100’ */
and o.from_xxxxx != ‘07’
group by o.refxxxx
union all
SELECT o.refxxxx, count(o.refxxxx)
FROM t_xxxxxxx_open_SSSS o
join (SELECT i.xxxxxxx_account, sum(i.intxxxxx) ljsy1
FROM t_XXXXXXX_inXXXXX_inXXXXXt i
where i.inxxxxxx_date <= ‘2015-11-23’ --1
and i.inxxxxxx_date >= ‘2015-07-01’
group by i.xxxxxxx_account) tt
on o.xxxxxxx_account = tt.xxxxxxx_account
join (SELECT i.xxxxxxx_account, sum(i.intxxxxx) ljsy2
FROM t_XXXXXXX_inXXXXX_inXXXXXt i
where i.inxxxxxx_date <= ‘2015-11-24’ --2
and i.inxxxxxx_date >= ‘2015-07-01’
group by i.xxxxxxx_account) ttt
on o.xxxxxxx_account = ttt.xxxxxxx_account
where to_char(o.create_date, ‘yyyy-MM-dd’) >= ‘2015-09-01’
and tt.ljsy1 < 1
and ttt.ljsy2 >= 1
and o.areacode != ‘350000’
/*and o.cxxxcode !=‘510100’ */
group by o.refxxxx;
经过与其沟通 sql最终更改如下：
SELECT o.refxxxx, count(o.refxxxx)
FROM t_xxxxxxx_open_SSSS o
where o.create_date >= to_date(‘2015-10-01’, ‘yyyy-mm-dd’)
and ((o.areacode = ‘350000’ and o.from_xxxxx != ‘07’) or
o.areacode != ‘350000’)
and exists (SELECT 1
FROM t_XXXXXXX_inXXXXX_inXXXXXt i
where i.inxxxxxx_date = ‘2015-12-07’ --2
and i.intxxxxx > 0
and i.xxxxxxx_account = o.xxxxxxx_account)
and not exists (SELECT i.xxxxxxx_account
FROM t_XXXXXXX_inXXXXX_inXXXXXt i
where i.inxxxxxx_date <= ‘2015-12-06’ --1
and i.inxxxxxx_date >= ‘2015-11-01’
and i.intxxxxx >=1
and i.xxxxxxx_account = o.xxxxxxx_account)
group by o.refxxxx;
更改后的sql 基本由原来的2-3个小时可以缩短到150s左右可以出结果.
没过几天开发又反应这个优化后的sql 又跑不出来了，
现在看到的执行计划如下：

通过上面我们可以看到问题出在第7步；基数才67
实际呢？：
SELECT count(*) FROM t_XXXXXXX_inXXXXX_inXXXXXt i
where i.inxxxxxx_date = ‘2015-12-07’ ; ----5155165
5155165与67 相差的可不是一点点。。
数据量那么大走 NESTED LOOPS 明显不合适了，于是想到了不走索引的情况：
SELECT + no_index(o)/o.refxxxx, count(o.refxxxx)
2 FROM t_xxxxxxx_open_SSSS o
3 where o.create_date >= to_date(‘2015-10-01’, ‘yyyy-mm-dd’)
4 and ((o.areacode = ‘350000’ and o.from_xxxxx != ‘07’) or
5 o.areacode != ‘350000’)
6 and exists (SELECT + no_index(i)/1
7 FROM t_XXXXXXX_inXXXXX_inXXXXXt i
8 where i.inxxxxxx_date = ‘2015-12-07’ --2
9 and i.intxxxxx > 0
10 and i.xxxxxxx_account = o.xxxxxxx_account)
11 and not exists (SELECT + no_index(i)/i.xxxxxxx_account
12 FROM t_XXXXXXX_inXXXXX_inXXXXXt i
13 where i.inxxxxxx_date <= ‘2015-12-06’ --1
14 and i.inxxxxxx_date >= ‘2015-11-01’
15 and i.intxxxxx >=1
16 and i.xxxxxxx_account = o.xxxxxxx_account)
17 group by o.refxxxx;
执行计划：

结果确实很快，150s左右就OK了。
然后我们分析产生这个问题的原因：
于是我们看统计信息：（今天20151208）

我们把统计信息里面的LOW_VALUE ， HIGH_VALUE 转换为我们可以看懂的字符：
select display_raw(‘323031332D31322D3133’,‘VARCHAR2’) low_val,display_raw(‘323031352D31312D3234’,‘VARCHAR2’) HIGHVALUE FROM DUAL;
2013-12-13 2015-11-24
而我们的查询条件为：i.inxxxxxx_date = ‘2015-12-07’ 很明显我们要查的条件不在上面那个范围内.这就是谓词越界的现象. 如果出现了这种现状，CBO就无法判断出针对该列的查询条件的选择率，只能用一个估算的值，作为查询条件的可选择率，如果这个估算的值与实际情况严重不符的话，就可能是CBO选错执行计划，就出现了，我们这个情况。
最后我们收集统计信息，
object_name SEGMENT_TYPE TABLESPACE_NAME SEGMENT_SIZE_G

BTUPAYPROD.T_XXXXXXING_INxxxxxx_intxxxxx TABLE PARTITION TBS_XXXXXXE_DATA 180.564453
该表非常大，对相关的业务了解之后，决定没有必要对全表进行收集，只收集最后几个分区就可以了
begin
DBMS_STATS.GATHER_TABLE_STATS (‘BTUPAYPROD’,‘T_XXXXXXING_INxxxxxx_intxxxxx’,partname=>‘P30’, degree=>8);
end;
最后不加hints 我们看到的执行计划和加hints一样的效果：这就达到了我们的目的了。

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

1987.Oracle谓词越界

查询结果返回2081行结果集。 TEST@PROD1> select count() from test_obj where object_id between 78200 and 81000; COUNT()

ROUND(((81000-78200)/(100000-2)+2/94283)(94283-0)/9428394283)

Execution Plan

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 5 | 289 (1)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | TABLE ACCESS FULL| TEST_OBJ | 1 | 5 | 289 (1)| 00:00:04 |

Predicate Information (identified by operation id):

Statistics

Execution Plan

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 5 | 314 (1)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | TABLE ACCESS FULL| TEST_OBJ | 2642 | 13210 | 314 (1)| 00:00:04 |

Predicate Information (identified by operation id):

Statistics

评论

查询结果返回2081行结果集。
TEST@PROD1> select count() from test_obj where object_id between 78200 and 81000;
COUNT()

| 0 | SELECT STATEMENT | | 1 | 5 | 289 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| TEST_OBJ | 1 | 5 | 289 (1)| 00:00:04 |

| 0 | SELECT STATEMENT | | 1 | 5 | 314 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| TEST_OBJ | 2642 | 13210 | 314 (1)| 00:00:04 |