CBO使用CHECK的一个Bug

云和恩墨

于 2015-05-14 09:07:10 发布

阅读量119

点赞数

文章标签： mysql 数据库 java oracle sql

原文链接：https://mp.weixin.qq.com/s?__biz=MjM5MzExMTU2OQ==&mid=205686926&idx=1&sn=20666ad5f734112127152d19376fb46f&chksm=2f2a23f0185daae65f209abb7d59ba6213ff018e482e3418ba9ba0a9c9ed385b8176405307b6

版权

开发人员给我报告了一个奇怪的问题。根据对这个问题的跟踪，找到了表结构设计上的一个错误，同时也发现了Oracle的一个Bug。

开发人员提供给我的是一个非常简单的SQL语句：

SQL> SELECT COUNT(*) FROM CAT_AREA_QUALITY_DEFINE
2 WHEREPLAT_ID = 'FR20T0000020000000000001';
COUNT(*)
----------
4

但是在它添加一个OR条件后，错误就出现了：

SQL> SELECT COUNT(*) FROM CAT_AREA_QUALITY_DEFINE

2 WHERE PLAT_ID = 'FR20T0000020000000000001'

3 OR PLAT_ID = 'FR20T0000020000000000032';

COUNT(*)

----------

当PLAT_ID等于第一个值时还有记录，增加一个OR条件后居然会没有记录？我的第一反应是SQL写错了，仔细检查了一下SQL语句，没有发现什么异常之处。尝试将OR的写法改写成IN值列表判断，问题依旧。

看来是表和索引出现了不同步的情况，从而导致了这个问题。如果表和索引中的信息不同步，就有可能导致这个问题的产生，可能不加OR条件时，Oracle选择了索引扫描，而加上了OR条件后，Oracle选择全表扫描。于是重建了PLAT_ID索引，结果发现问题仍然存在。

看来碰到的这个问题还真有点奇怪，于是查看了两个语句的执行计划：

SQL> SET AUTOT ON EXP

SQL> SELECT COUNT(*) FROM CAT_AREA_QUALITY_DEFINE

2 WHERE PLAT_ID = 'FR20T0000020000000000001';

COUNT(*)

----------

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE(Cost=2 Card=1 Bytes=25)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'CAT_AREA_QUALITY_DEFINE'(Cost=2 Card=4 Bytes=100)

SQL> SELECT COUNT(*) FROM CAT_AREA_QUALITY_DEFINE

2 WHERE PLAT_ID = 'FR20T0000020000000000001'

3 OR PLAT_ID = 'FR20T0000020000000000032';

COUNT(*)

----------

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE(Cost=2 Card=1 Bytes=25)

1 0 SORT (AGGREGATE)

2 1 FILTER

3 2 TABLE ACCESS (FULL) OF 'CAT_AREA_QUALITY_DEFINE'(Cost=2 Card=10 Bytes=250)

发现第二个SQL语句比第一个SQL语句多出了一个FILTER的步骤，感觉这个FILTER的步骤完全是多余的，Oracle为什么会平白无故地多执行一个FILTER的步骤，难道是和统计信息有关？

备份了当前表的统计信息后，将表的统计信息删除并重新收集，结果发现问题仍然存在。

SQL> EXEC DBMS_STATS.DELETE_TABLE_STATS(USER,'CAT_AREA_QUALITY_DEFINE')

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'CAT_AREA_QUALITY_DEFINE', METHOD_OPT => 'FOR ALL COLUMNS SIZE 100')

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM CAT_AREA_QUALITY_DEFINE

2 WHERE PLAT_ID = 'FR20T0000020000000000001'

3 OR PLAT_ID = 'FR20T0000020000000000032';

COUNT(*)

----------

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE(Cost=2 Card=1 Bytes=25)

1 0 SORT (AGGREGATE)

2 1 FILTER

3 2 TABLE ACCESS (FULL) OF 'CAT_AREA_QUALITY_DEFINE' (Cost=2 Card=10 Bytes=250)

尝试添加HINT，指定索引扫描：

SQL> SELECT /*+ INDEX (CAT_AREA_QUALITY_DEFINE) */ COUNT(*) FROM CAT_AREA_QUALITY_DEFINE

2 WHERE PLAT_ID = 'FR20T0000020000000000001'

3 OR PLAT_ID = 'FR20T0000020000000000032';

COUNT(*)

----------

Execution Plan

-------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1 Bytes=25)

1 0 SORT(AGGREGATE)

2 1 FILTER

3 2 INLIST ITERATOR

4 3 INDEX (RANGE SCAN) OF'TU_CAT_AREA_Q_DEF_PLAT' (NON-UNIQUE) (Cost=16 Card=10 Bytes=250)

索引提示生效了，但是这个FILTER的步骤还是去不掉。不过在测试的时候发现，RBO可以得到正确的结果，问题只会在CBO的情况下出现。

SQL> ALTER SESSION SET OPTIMIZER_MODE= FIRST_ROWS;

Session altered.

SQL> SELECT COUNT(*) FROM CAT_AREA_QUALITY_DEFINE

2 WHERE PLAT_ID = 'FR20T0000020000000000001'

3 OR PLAT_ID = 'FR20T0000020000000000032';

COUNT(*)

----------

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=FIRST_ROWS(Cost=2 Card=1 Bytes=25)

1 0 SORT (AGGREGATE)

2 1 FILTER

3 2 TABLE ACCESS (FULL) OF 'CAT_AREA_QUALITY_DEFINE'(Cost=2 Card=10 Bytes=250)

SQL> ALTER SESSION SET OPTIMIZER_MODE= ALL_ROWS;

Session altered.

SQL> SELECT COUNT(*) FROM CAT_AREA_QUALITY_DEFINE

2 WHERE PLAT_ID = 'FR20T0000020000000000001'

3 OR PLAT_ID = 'FR20T0000020000000000032'

4 ;

COUNT(*)

----------

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS(Cost=2 Card=1 Bytes=25)

1 0 SORT (AGGREGATE)

2 1 FILTER

3 2 TABLE ACCESS (FULL) OF 'CAT_AREA_QUALITY_DEFINE'(Cost=2 Card=10 Bytes=250)

SQL> ALTER SESSION SET OPTIMIZER_MODE= CHOOSE;

Session altered.

可以看到，无论是FIRST_ROWS，还是ALL_ROWS优化模式，都会出现同样的问题，但是使用RULE优化模式就是正常的，这说明问题和CBO有关。

重新收集表的统计信息，但仍然没有解决问题，这说明问题与统计信息无关。莫非是表中的数据出现了错误而导致了这个问题。尝试利用CREATETABLE AS SELECT的方式建立测试表，对测试表建立索引并分析，发现测试表的访问是正常的。

尝试设置EVENT 10046和10053，并没有在得到的trace中发现任何有价值的信息。

将这个表用exp、imp导入到其他数据库中，错误可以重现。

能使用的方法基本都用过了，看来似乎只有重建表这一个方法了。不过重建之前不妨整理一下思路：这个问题与CBO优化器有关，但是和统计信息又没有什么关系。表中的数据本身没有问题，索引也没有不同步。如果把这个表导入到其他数据库中还能重现问题。问题的答案应该已经浮出水面了，肯定是与表本身的结构有关。

既然如此，不妨通过DBMS_METADATA包来检查一下表的结构：

SQL> SET LONG 10000

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','CAT_AREA_QUALITY_DEFINE') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','CAT_AREA_QUALITY_DEFINE')

-------------------------------------------------------------------------

CREATE TABLE "BIDW1"."CAT_AREA_QUALITY_DEFINE"

( "QUALITY_CODE"CHAR(24) NOT NULL ENABLE,

"PLAT_ID" CHAR(24) NOT NULL ENABLE,

"QUALITY_NAME" VARCHAR2(100),

"CREATE_USER" CHAR(24),

"CREATE_DATE" DATE,

"CREATE_PLAT" CHAR(24),

"CREATE_ORG" CHAR(24),

"LAST_UPDATE_USER" CHAR(24),

"LAST_UPDATE_DATE" DATE,

"LAST_UPDATE_PLAT" CHAR(24),

"LAST_UPDATE_ORG" CHAR(24),

"SYNCHRONIZED_DATE" DATE,

"CLEAN_DATE" DATE,

"DESCRIPTION" VARCHAR2(50),

"QUALITY_DESC" VARCHAR2(150),

"QUALITY_DEFINE" VARCHAR2(150),

CONSTRAINT "LOG_GROUP_CAT_AREA_QUA_DEF_PK",

CONSTRAINT "PK_CAT_AREA_QUALITY_DEFINE"PRIMARY KEY ("QUALITY_CODE")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "NDINDEX" ENABLE,

CHECK (plat_id <> '') ENABLE,

CHECK (plat_id <> '') ENABLE

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "NDMAIN"

很明显，不知道是谁在表上添加了一堆CHECK约束，而且添加约束的人显然对NULL不了解，居然写出了<>‘’这种语法，何况PLAT_ID上已经存在NOTNULL的约束了。因此这些CHECK完全没有意义，而且由于写法有误，会造成CHECK约束条件的结果恒为NULL。

看来问题就是这个约束造成的，删掉CHECK约束后，问题得以解决：

SQL> SET AUTOT OFF

SQL> COL SEARCH_CONDITION FORMAT A60

SQL> SELECT CONSTRAINT_NAME, SEARCH_CONDITIONFROM USER_CONSTRAINTS WHERE TABLE_NAME = 'CAT_AREA_QUALITY_DEFINE';

CONSTRAINT_NAME SEARCH_CONDITION
------------------------------ -----------------------------------------
SYS_C0011866 "QUALITY_CODE" IS NOT NULL
SYS_C0011867 "PLAT_ID" IS NOT NULL
PK_CAT_AREA_QUALITY_DEFINE
SYS_C0011870 plat_id <> ''
SYS_C0011871 plat_id <> ''
SYS_C0011872 plat_id <> ''
SYS_C0011873 plat_id <> ''
SYS_C0011874 plat_id <> ''
SYS_C0011875 plat_id <> ''
9 rows selected.

SQL> SELECT 'ALTER TABLE CAT_AREA_QUALITY_DEFINE DROP CONSTRAINT ' || CONSTRAINT_NAME|| ';'

2 FROM USER_CONSTRAINTS WHERE TABLE_NAME= 'CAT_AREA_QUALITY_DEFINE'

3 AND CONSTRAINT_NAME LIKE 'SYS_C001187_';

'ALTERTABLECAT_AREA_QUALITY_DEFINEDROPCONSTRAINT'||CONSTRAINT_NAME||';'

---------------------------------------------------------------------------

ALTER TABLE CAT_AREA_QUALITY_DEFINE DROPCONSTRAINT SYS_C0011870;

ALTER TABLE CAT_AREA_QUALITY_DEFINE DROPCONSTRAINT SYS_C0011871;

ALTER TABLE CAT_AREA_QUALITY_DEFINE DROPCONSTRAINT SYS_C0011872;

ALTER TABLE CAT_AREA_QUALITY_DEFINE DROPCONSTRAINT SYS_C0011873;

ALTER TABLE CAT_AREA_QUALITY_DEFINE DROPCONSTRAINT SYS_C0011874;

ALTER TABLE CAT_AREA_QUALITY_DEFINE DROPCONSTRAINT SYS_C0011875;

6 rows selected.

SQL> ALTER TABLE CAT_AREA_QUALITY_DEFINEDROP CONSTRAINT SYS_C0011870;

Table altered.

SQL> ALTER TABLE CAT_AREA_QUALITY_DEFINEDROP CONSTRAINT SYS_C0011871;

Table altered.

SQL> ALTER TABLE CAT_AREA_QUALITY_DEFINEDROP CONSTRAINT SYS_C0011872;

Table altered.

SQL> ALTER TABLE CAT_AREA_QUALITY_DEFINEDROP CONSTRAINT SYS_C0011873;

Table altered.

SQL> ALTER TABLE CAT_AREA_QUALITY_DEFINEDROP CONSTRAINT SYS_C0011874;

Table altered.

SQL> ALTER TABLE CAT_AREA_QUALITY_DEFINEDROP CONSTRAINT SYS_C0011875;

Table altered.

SQL> SET AUTOT ON EXP

SQL> SELECT COUNT(*) FROM CAT_AREA_QUALITY_DEFINE

2 WHERE PLAT_ID = 'FR20T0000020000000000001'

3 OR PLAT_ID = 'FR20T0000020000000000032'

4 ;

COUNT(*)

----------

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE(Cost=2 Card=1 Bytes=25)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'CAT_AREA_QUALITY_DEFINE' (Cost=2 Card=10 Bytes=250)

问题显然就是CHECK约束在捣鬼。记得Oracle 9i的CBO在查询条件违反CHECK约束条件时，会自动添加FILTER步骤，避免真正地去执行SQL语句。当前碰到的应该就是这个问题。下面用个小例子验证一下。

SQL> CREATE TABLE T (ID NUMBER CHECK(ID < 5));

Table created.

SQL> SET AUTOT OFF

SQL> INSERT INTO T VALUES (1);

1 row created.

SQL> INSERT INTO T VALUES (6);

INSERT INTO T VALUES (6)

ERROR at line 1:

ORA-02290: check constraint (BIDW1.SYS_C0011876)violated

SQL> INSERT INTO T VALUES (NULL);

1 row created.

SQL> COMMIT;

Commit complete.

CHECK约束阻止CHECK条件为FALSE的记录进入到表中，但如果CHECK条件为NULL时，数据是可以插入到表中的。下面再看看CHECK对查询的影响：

SQL> SET AUTOT ON EXP

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T')

PL/SQL procedure successfully completed.

SQL> SELECT * FROM T WHERE ID = 1;

----------

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE(Cost=2 Card=1 Bytes=2)

1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=2)

SQL> SELECT * FROM T WHERE ID <5;

----------

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1Bytes=2)

1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=2)

SQL> SELECT * FROM T WHERE ID >5;

no rows selected

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1Bytes=2)

1 0 FILTER

2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=2)

果然，当CBO发现查询的条件不满足表中的约束条件，且当前的约束状态为ENABLEVALIDATE时，Oracle的CBO会认为：查询不会返回结果。所以直接在执行计划外面嵌套了一层FILTER，这时Oracle甚至不会去真正地扫描表，而是直接返回了结果。这个特性应该是9i的CBO新增功能，可惜的是当碰到约束条件恒为NULL的情况时，还存在一些问题。

第一个SQL没有问题，加上OR语句后问题出现，很可能就是OR语句的出现诱发了Bug的产生。

文章来源：《Oracle DBA手记1》第15章执行计划与统计信息案例作者：杨廷琨

配图来源：http://www.yinliseo.com/wp-content/uploads/2012/06/2007930104256.jpg

回复“手记1”可下载本书前三篇内容：DBA工作手记/诊断案例篇/SQL调优篇(建议复制链接至电脑端）