开发人员给我报告了一个奇怪的问题。根据对这个问题的跟踪,找到了表结构设计上的一个错误,同时也发现了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(*)
----------
0
当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(*)
----------
4
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(*)
----------
0
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(*)
----------
0
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(*)
----------
0
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(*)
----------
0
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(*)
----------
0
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,
CHECK (plat_id <> '') ENABLE,
CHECK (plat_id <> '') 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(*)
----------
10
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;
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;
ID
----------
1
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调优篇(建议复制链接至电脑端)