[20210420]19c奇怪的过滤条件.txt
--//昨天看新上线的生产系统ODA，发现一条语句奇怪的过滤条件。好在知识储备足够知道这个是由于使用12c以上特性增加新字段导致的
--//情况，顺便说一下我们的生产系统，虽然db time很小仅仅不到10分钟，完全靠机器的性能掩盖许多问题。
--//用一句话来概括就是豆腐渣，再增加一个前缀就是豆腐渣中的豆腐渣。
--//对于遇到的情况，做一个记录与分析。
> @ ver1
> @ prxx
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 19.0.0.0.0
BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
--//经常看到执行计划过滤条件如下：
3 - filter(((DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00021$",0)),NULL,NVL("SP
D",0),'0',NVL("SPD",0),'1',"SPD")=0 OR
DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00021$",0)),NULL,NVL("SPD",0),'0',NVL("S
PD",0),'1',"SPD") IS NULL) AND "YK_YPCD"."JGID"=:JGID))
--//刚开始很感觉很奇怪，什么会出现这样的函数SYS_OP_VECBIT。实际上看到的查询条件是 (SPD = 0 OR SPD IS NULL).
--//看了一下表结构发现SPD字段的编号是21，马上明白为什么出现这样的情况，通过测试环境说明实际的问题。
1.环境：
XXXX> @ prxx
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 18.0.0.0.0
BANNER : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
BANNER_FULL : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
BANNER_LEGACY : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.建立测试表。
create table t (a number);
insert into t select rownum from dual connect by rownum <=10;
commit;
--//分析略。
alter table t add ( b number default 1 not null);
XXXX> select * from t where b=1;
A B
---------- ----------
1 1
..
10 1
10 rows selected.
XXXX> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2gcu8c0ucsuts, child number 0
-------------------------------------
select * from t where b=1
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| T | 10 | 60 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NVL("B",1)=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T"."A"[NUMBER,22], NVL("B",1)[22]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//注意过滤条件filter(NVL("B",1)=1),以及Column Projection Information部分，都可以看到NVL("B",1)。
--//实际上这样增加字段在11g或者12c以上版本，都不会修改表结构，不然就不会出现过滤条件是filter(NVL("B",1)=1的情况。
--//缺省值保存在sys.ecol$数据字段中。
XXXX> select OBJECT_ID,DATA_OBJECT_ID from dba_objects where object_name='T';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
252074 252074
XXXX> select TABOBJ#,COLNUM,BINARYDEFVAL,GUARD_ID from sys.ecol$ a;
TABOBJ# COLNUM BINARYDEFVAL GUARD_ID
---------- ---------- ------------------------------ ----------
252074 2 C102
--//TABOBJ# 就是表段号，COLNUM表示字段顺序2.BINARYDEFVAL 对应数字1的oracle数字编码。
SELECT obj# ,col# ,segcol# ,name ,default$ ,type# FROM sys.col$
WHERE obj# IN (SELECT object_id FROM dba_objects WHERE owner = USER AND object_name = 'T') ORDER BY col#;
OBJ# COL# SEGCOL# NAME DEFAULT$ TYPE#
---------- ---------- ---------- ----- --------- -----
252074 1 1 A 2
252074 2 2 B 1 2
3.继续测试：
XXXX> alter table t add ( c number default 1 );
Table altered.
--//注意没有not null的约束。
--//12c以上这样修改表结构，不会修改表对应数据块。通过特殊的方式增加一个隐含字段里面的bit来表示这些字段从那里来读取.
--//后续的插入即使insert不带这些字段,其缺省值也插入数据块中.
--//可以参考链接：http://blog.itpub.net/267265/viewspace-1335561/ =>[20141116]12c下增加字段与缺省值.txt
XXXX> select * from t where a=1 and b=1 and c=1 and rownum=1 ;
A B C
---------- ---------- ----------
1 1 1
XXXX> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID c45j01802z7rh, child number 0
-------------------------------------
select * from t where a=1 and b=1 and c=1 and rownum=1
Plan hash value: 508354683
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 9 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - filter(("A"=1 AND NVL("B",1)=1 AND
DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("C",1),'0',NVL(
"C",1),'1',"C")=1))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "A"[NUMBER,22], NVL("B",1)[22],
DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("C",1),'0',NVL(
"C",1),'1',"C")[22]
2 - "A"[NUMBER,22], NVL("B",1)[22],
DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("C",1),'0',NVL(
"C",1),'1',"C")[22]
--//注意看过滤条件以及Column Projection Information部分的显示。
2 - filter(("A"=1 AND NVL("B",1)=1 AND
DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("C",1),'0',NVL(
"C",1),'1',"C")=1))
XXXX> select TABOBJ#,COLNUM,BINARYDEFVAL,GUARD_ID from sys.ecol$ a;
TABOBJ# COLNUM BINARYDEFVAL GUARD_ID
---------- ---------- ------------------------------ ----------
252074 2 C102
252074 4 C102 0
--//你可以发现新增加的字段c编号是4.GUARD_ID=0,也许这是这两者增加字段模式的区别。
SELECT obj# ,col# ,segcol# ,name ,default$ ,type# FROM sys.col$
WHERE obj# IN (SELECT object_id FROM dba_objects WHERE owner = USER AND object_name = 'T') ORDER BY col#;
OBJ# COL# SEGCOL# NAME DEFAULT$ TYPE#
---------- ---------- ---------- ------------ -------- -----
252074 0 3 SYS_NC00003$ 23
252074 1 1 A 2
252074 2 2 B 1 2
252074 3 4 C 1 2
--//增加1个隐含字段SYS_NC00003$，然后才是增加字段C。oracle 12c就是利用了这个隐含字段实现这样修改表结构不修改数据表块的方
--//法.
--//这两者模式带来的缺点就是看过滤条件不容易判断该建立什么样的索引，不过一般问题不会很大，结合谓词条件，一般不会建立索引
--//错误。我开始以为如果建立字段b或者c的索引会有问题(受过滤条件的干扰)，实际上仔细想想不对，建立索引时就将键值写入索引结
--//构里面，即使数据块里面没有信息。通过索引探查就能够知道rowid,定位对应行记录，看看下面的测试，看来我有点过虑了，oracle
--//肯定已经考虑这样的情况。
4.继续测试,看看使用索引的查询情况：
insert into t select rownum+10,0,0 from dual connect by rownum <=1e5-10;
commit ;
create index i_t_b on t(b);
create index i_t_c on t(c);
--//分析略。建立直方图：
BEGIN
sys.DBMS_STATS.gather_table_stats
(
OwnName => USER
,TabName => 't'
,Estimate_Percent => NULL
,Method_Opt => 'FOR ALL COLUMNS SIZE 1 for columns b size 254 for columns c size 254'
,Cascade => TRUE
,No_Invalidate => FALSE
);
END;
/
XXXX> select * from t where b=1 and rownum=1;
A B C
---------- ---------- ----------
1 1 1
XXXX> @ dpc '' advanced
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID dbums5ptbbhd8, child number 0
-------------------------------------
select * from t where b=1 and rownum=1
Plan hash value: 3626036191
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 11 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_T_B | 10 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
3 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."B"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - access("B"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T"."A"[NUMBER,22], "B"[NUMBER,22], DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",
0)),NULL,NVL("T"."C",1),'0',NVL("T"."C",1),'1',"T"."C")[22]
2 - "T"."A"[NUMBER,22], "B"[NUMBER,22], DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",
0)),NULL,NVL("T"."C",1),'0',NVL("T"."C",1),'1',"T"."C")[22]
3 - "T".ROWID[ROWID,10], "B"[NUMBER,22]
--//注意过滤条件是access("B"=1)，看看Column Projection Information (identified by operation id):的一些细节：
3 - "T".ROWID[ROWID,10], "B"[NUMBER,22]
--//扫描索引段获得rowid以及字段B的值，不再出现NVL("B",1)=1的情况。
--//继续Column Projection Information 部分，B字段的取值也没有NVL("B",1)=1，也就是字段B的值来自索引，不再是表。
XXXX> select * from t where c=1 and rownum=1;
A B C
---------- ---------- ----------
1 1 1
XXXX> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID b7q2au1j8qqy1, child number 0
-------------------------------------
select * from t where c=1 and rownum=1
Plan hash value: 2915363186
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 11 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_T_C | 10 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
3 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."C"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - access("C"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T"."A"[NUMBER,22], NVL("T"."B",1)[22], "C"[NUMBER,22]
2 - "T"."A"[NUMBER,22], NVL("T"."B",1)[22], "C"[NUMBER,22]
3 - "T".ROWID[ROWID,10], "C"[NUMBER,22]
--//仔细看Predicate Information部分 3 - access("C"=1)。
--//再看Column Projection Information 部分：
--//不再出现DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("T"."C",1),'0',NVL("T"."C",1),'1',"T"."C")[22]这样
--//的显示,也就是字段C来自索引，再看看B字段明显的标识来自表，出现了NVL("T"."B",1)[22]。
--//再贴一个复杂一点的情况：
XXXX> select * from t where ( c=1 or b=1 )and rownum=1;
A B C
---------- ---------- ----------
1 1 1
XXXX> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 38qc6cvms8a6f, child number 0
-------------------------------------
select * from t where ( c=1 or b=1 )and rownum=1
Plan hash value: 310441820
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 11 | 2 (0)| 00:00:01 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 4 | BITMAP OR | | | | | |
| 5 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 6 | INDEX RANGE SCAN | I_T_C | | | 1 (0)| 00:00:01 |
| 7 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 8 | INDEX RANGE SCAN | I_T_B | | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
BITMAP_TREE(@"SEL$1" "T"@"SEL$1" OR(1 1 ("T"."C") 2 ("T"."B")))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
6 - access("C"=1)
8 - access("B"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T"."A"[NUMBER,22], NVL("T"."B",1)[22],
DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("T"."C",1),'0',NVL("T"."C",1),
'1',"T"."C")[22]
2 - "T"."A"[NUMBER,22], NVL("T"."B",1)[22],
DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("T"."C",1),'0',NVL("T"."C",1),
'1',"T"."C")[22]
3 - "T".ROWID[ROWID,10]
4 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
5 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
6 - "T".ROWID[ROWID,10]
7 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
8 - "T".ROWID[ROWID,10]
--//仔细看Predicate Information以及Column Projection Information部分。
--// id = 6,8 仅仅取了rowid，并不包括字段B，C。这样id = 1,2部分的字段B，C的取值只能来之表，这样有出现了
--//NVL("T"."B",1)[22], DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("T"."C",1),'0',NVL("T"."C",1),'1',"T"."C")[22]
--//这样的函数调用。
XXXX> select * from t where c is null;
no rows selected
XXXX> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID gwnddk9xpymw7, child number 0
-------------------------------------
select * from t where c is null
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 69 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 11 | 69 (2)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("
C",1),'0',NVL("C",1),'1',"C") IS NULL)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T"."A"[NUMBER,22], NVL("T"."B",1)[22],
DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("C",1),'0',NVL(
"C",1),'1',"C")[22]
--//注意Predicate Information (identified by operation id)以及Column Projection Information (identified by operation
--//id):
--//因为字段C允许NULL，这样索引不包括NULL，这样有SYS_OP_VECBIT之类的显示，不展开分析了。
5.如果修改缺省值呢？
--//感觉这样的情况很少遇到不过我还是测试看看，毕竟增加字段没有修改表块。
XXXX> alter table t modify ( c number default 2 );
Table altered.
XXXX> select TABOBJ#,COLNUM,BINARYDEFVAL,GUARD_ID from sys.ecol$ a;
TABOBJ# COLNUM BINARYDEFVAL GUARD_ID
---------- ---------- ------------------------------ ----------
252074 2 C102
252074 4 C102 0
--//这里还是记录缺省值1.
SELECT obj# ,col# ,segcol# ,name ,default$ ,type# FROM sys.col$
WHERE obj# IN (SELECT object_id FROM dba_objects WHERE owner = USER AND object_name = 'T') ORDER BY col#;
OBJ# COL# SEGCOL# NAME DEFAULT$ TYPE#
---------- ---------- ---------- ------------ -------- -----
252074 0 3 SYS_NC00003$ 23
252074 1 1 A 2
252074 2 2 B 1 2
252074 3 4 C 2 2
--//仅仅改动sys.col$表。这样插入时不包括c字段，实际上插入时2.而在表块里面没有字段c的情况下，实际上对应值1.
XXXX> insert into t(a) select 1e5+1 from dual ;
1 row created.
XXXX> commit ;
Commit complete.
XXXX> select t.*,SYS_NC00003$ c20 from t where a=1e5+1;
A B C C20
---------- ---------- ---------- --------------------
100001 1 2 01
--//SYS_NC00003$=01。
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "A"[NUMBER,22], NVL("T"."B",1)[22], "SYS_NC00003$"[RAW,126],
DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("T"."C",1),'0',
NVL("T"."C",1),'1',"T"."C")[22]
6.总结：
--//oracle每种新特性，总是带来新的困惑，这样的方式给运维带来好处，缺点以后增加1个隐含字段，看执行计划时存在一点点困惑。
--//带来表每行记录增加至少2个字节(如果仅仅小于增加8个字段)。我感觉在项目实施或者调试阶段应该关闭这个特性，毕竟那个时候表
--//不是很大。看了半天，也不知道那个隐含参数控制这样的行为，那位知道。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2768847/，如需转载，请注明出处，否则将追究法律责任。
