前言:
以前一直觉得Hint加了不会影响sql执行状态或者结果,然后其实是会的。
因为涉及到很多的知识点,研究了还是挺有意义的。在这里探讨一下。
先看现象:
12c可以正常运行不报错,但是11g里边却是报了错误。
原因是因为11g的执行计划id为3的谓词条件为"filter ( TO_NUMBER ( "KSPPSTVL" ) = 8192 ) ",是在X$KSPPCV上过滤的,自然含有不能to_number的值,比如含有字母的字符串。
12C做了优化可以运行,这条语句在11G运行不了,需要构造子查询/视图与外部查询,并加一个连接条件,才能尝试不用谓词推入。
实验过程:
这里将sql改写:
从结果看,运行还是出现错误,但是"
select
value
from
v$parameter p
where
p
.
name
=
'db_block_size'
;
"执行结果只有一个"8192",不应该出现ora1722错误。
看一眼执行计划就明白了:
尝试加Hint不做谓词推入,不过前提是不做视图合并:
这里的no_merge的hint是生效了,不过no_push_pred却没有。
原因是这个sql的改写也有问题,push_pred/no_push_pred的应用场景是视图和外部查询之间有连接条件,对于push_pred,优化器会把原本处于该视图外部查询中和该视图之间的连接条件推入到该视图定义的SQL语句内部,这样做是为了能使用上该视图内部相关基表的索引,进而走出基于索引的嵌套循环连接。
需要连接条件的话,要有构造另外一张辅助表:
sql改写:select a.value from (select value from v$parameter p where p.name='db_block_size') a,test b where to_number(id||a.value)=8192;
该sql的执行计划以及执行结果如下:
从执行计划上看,id为4的那步,在X$KSPPCV过滤
to_number
(
id
|
|
a
.
value
)
=
8192的数据,自然是有问题的。
添加Hint强制不做谓词推入,如下:
从
执行计划上看
,做to_number转换的对象是在test表上,自然就是可以运行出结果了。
PS:
该语句也是一样的: select a . value from ( select /*+ no_merge no_push_pred*/ value from v$parameter p where p . name = 'db_block_size' ) a , test b where to_number ( id | | a . value ) = 8192 ;
问题延伸:
为什么语句“
select
value
from
t
where
name
=
'db_block_size'
and
to_number
(
value
)
=
8192
;
”执行成功,换成v$parameter却报错。
该问题在另外一篇做研究。
这里附上链接http://blog.itpub.net/30174570/viewspace-2148607/
以前一直觉得Hint加了不会影响sql执行状态或者结果,然后其实是会的。
因为涉及到很多的知识点,研究了还是挺有意义的。在这里探讨一下。
先看现象:
- --11.2.0.4.0
- SYS@proc> select name from v$parameter where name='db_block_size' and to_number(value)=8192;
- select name from v$parameter where name='db_block_size' and to_number(value)=8192
- *
- ERROR at line 1:
- ORA-01722: invalid number
-
-
- SYS@proc> explain plan for
- 2 select name from v$parameter where name='db_block_size' and to_number(value)=8192;
-
- Explained.
-
- SYS@proc> select * from table(dbms_xplan.display());
-
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------
- Plan hash value: 1128103955
-
- -------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 2115 | 0 (0)| 00:00:01 |
- |* 1 | HASH JOIN | | 1 | 2115 | 0 (0)| 00:00:01 |
- |* 2 | FIXED TABLE FULL| X$KSPPI | 1 | 81 | 0 (0)| 00:00:01 |
- |* 3 | FIXED TABLE FULL| X$KSPPCV | 1 | 2034 | 0 (0)| 00:00:01 |
- -------------------------------------------------------------------------------
-
-
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 1 - access("X"."INDX"="Y"."INDX")
- filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
- "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
- 2 - filter("KSPPINM"='db_block_size' AND
- "X"."INST_ID"=USERENV('INSTANCE') AND BITAND("KSPPIFLG",268435456)=0
- AND TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%')
- 3 - filter(TO_NUMBER("KSPPSTVL")=8192)
-
- 21 rows selected.
- ---12C---
- SQL> select name from v$parameter where name='db_block_size' and to_number(value)=8192;
-
- NAME
- --------------------------------
- db_block_size
- SQL> set autotrace traceonly
- SQL> select name from v$parameter where name='db_block_size' and to_number(value)=8192;
-
- 执行计划
- ------------------------------------------------------------
- Plan hash value: 185276389
-
- ------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 55 | 0 (0)| 00:00:01 |
- | 1 | NESTED LOOPS | | 1 | 55 | 0 (0)| 00:00:01 |
- |* 2 | FIXED TABLE FIXED INDEX| X$KSPPI (ind:1) | 1 | 37 | 0 (0)| 00:00:01 |
- |* 3 | FIXED TABLE FIXED INDEX| X$KSPPCV (ind:1) | 1 | 18 | 0 (0)| 00:00:01 |
- ------------------------------------------------------------------------------------------------
-
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 2 - filter("KSPPIM"='db_block_size' AND TRANSLATE("KSPPINM",'_','$') NOT LIKE
- '$$%' AND "X"."INST_ID"=USERENV('INSTANCE') AND BITAND("KSPPIFLG",268435456)=0)
- 3 - filter("X"."INDX"="Y"."INDX" AND TO_NUMBER("KSPPSTVL")=8192 AND
- (TRANSLATE("KSPPINM",'_','$') NOT LIKE '$%' OR "KSPPSTDF"='FALSE' OR
- BITAND("KSPPSTVF",5>0))
原因是因为11g的执行计划id为3的谓词条件为"filter ( TO_NUMBER ( "KSPPSTVL" ) = 8192 ) ",是在X$KSPPCV上过滤的,自然含有不能to_number的值,比如含有字母的字符串。
12C做了优化可以运行,这条语句在11G运行不了,需要构造子查询/视图与外部查询,并加一个连接条件,才能尝试不用谓词推入。
实验过程:
这里将sql改写:
- SYS@proc> select value from (select value from v$parameter p where p.name='db_block_size') a where to_number(a.value)=8192;
- select value from (select value from v$parameter p where p.name='db_block_size') a where to_number(a.value)=8192
- *
- ERROR at line 1:
- ORA-01722: invalid number
-
-
- SYS@proc> select value from v$parameter p where p.name='db_block_size';
-
- VALUE
- --------------------------------------------------------------------------------
- 8192
看一眼执行计划就明白了:
- SYS@proc> explain plan for select value from (select value from v$parameter p where p.name='db_block_size') a where to_number(a.value)=8192;
-
- Explained.
-
- SYS@proc> set linesize 200
- SYS@proc> select * from table(dbms_xplan.display());
-
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------
- Plan hash value: 1128103955
-
- -------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 2115 | 0 (0)| 00:00:01 |
- |* 1 | HASH JOIN | | 1 | 2115 | 0 (0)| 00:00:01 |
- |* 2 | FIXED TABLE FULL| X$KSPPI | 1 | 81 | 0 (0)| 00:00:01 |
- |* 3 | FIXED TABLE FULL| X$KSPPCV | 1 | 2034 | 0 (0)| 00:00:01 |
- -------------------------------------------------------------------------------
-
-
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 1 - access("X"."INDX"="Y"."INDX")
- filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
- "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
- 2 - filter("KSPPINM"='db_block_size' AND
- "X"."INST_ID"=USERENV('INSTANCE') AND BITAND("KSPPIFLG",268435456)=0
- AND TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%')
- 3 - filter(TO_NUMBER("KSPPSTVL")=8192)
-
- 21 rows selected.
- SYS@proc> select /*+ no_merge(a) no_push_pred(a) */value from (select value from v$parameter p where p.name='db_block_size') a where to_number(a.value)=8192;
- select /*+ no_merge(a) */value from (select value from v$parameter p where p.name='db_block_size') a where to_number(a.value)=8192
- *
- ERROR at line 1:
- ORA-01722: invalid number
-
-
- SYS@proc> explain plan for select /*+ no_merge(a) no_push_pred(a) */value from (select value from v$parameter p where p.name='db_block_size') a where to_number(a.value)=8192;
-
- Explained.
-
- SYS@proc> select * from table(dbms_xplain.display());
- select * from table(dbms_xplain.display())
- *
- ERROR at line 1:
- ORA-00904: "DBMS_XPLAIN"."DISPLAY": invalid identifier
-
-
- SYS@proc> select * from table(dbms_xplan.display());
-
- PLAN_TABLE_OUTPUT
- -------------------------------------------------------------------------------------------
- Plan hash value: 3035854849
-
- -------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 2002 | 0 (0)| 00:00:01 |
- | 1 | VIEW | | 1 | 2002 | 0 (0)| 00:00:01 |
- |* 2 | HASH JOIN | | 1 | 2115 | 0 (0)| 00:00:01 |
- |* 3 | FIXED TABLE FULL| X$KSPPI | 1 | 81 | 0 (0)| 00:00:01 |
- |* 4 | FIXED TABLE FULL| X$KSPPCV | 1 | 2034 | 0 (0)| 00:00:01 |
- -------------------------------------------------------------------------------
-
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------
-
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 2 - access("X"."INDX"="Y"."INDX")
- filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
- "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
- 3 - filter("KSPPINM"='db_block_size' AND
- "X"."INST_ID"=USERENV('INSTANCE') AND BITAND("KSPPIFLG",268435456)=0
- AND TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%')
- 4 - filter(TO_NUMBER("KSPPSTVL")=8192)
-
- 22 rows selected.
原因是这个sql的改写也有问题,push_pred/no_push_pred的应用场景是视图和外部查询之间有连接条件,对于push_pred,优化器会把原本处于该视图外部查询中和该视图之间的连接条件推入到该视图定义的SQL语句内部,这样做是为了能使用上该视图内部相关基表的索引,进而走出基于索引的嵌套循环连接。
需要连接条件的话,要有构造另外一张辅助表:
- SYS@proc> create table test(id int);
-
- Table created.
-
- SYS@proc> insert into test values(null);
-
- 1 row created.
-
- SYS@proc> commit;
-
- Commit complete.
该sql的执行计划以及执行结果如下:
- SYS@proc> select a.value from (select value from v$parameter p where p.name='db_block_size') a,test b where to_number(id||a.value)=8192;
- select a.value from (select value from v$parameter p where p.name='db_block_size') a,test b where to_number(id||a.value)=8192
- *
- ERROR at line 1:
- ORA-01722: invalid number
-
-
- SYS@proc> explain plan for select a.value from (select value from v$parameter p where p.name='db_block_size') a,test b where to_number(id||a.value)=8192;
-
- Explained.
-
- SYS@proc> select * from table(dbms_xplan.display());
-
- PLAN_TABLE_OUTPUT
- -------------------------------------------------------------------------------------------
- Plan hash value: 662541633
-
- --------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 2128 | 2 (0)| 00:00:01 |
- |* 1 | HASH JOIN | | 1 | 2128 | 2 (0)| 00:00:01 |
- | 2 | NESTED LOOPS | | 1 | 2047 | 2 (0)| 00:00:01 |
- | 3 | TABLE ACCESS FULL| TEST | 1 | 13 | 2 (0)| 00:00:01 |
- |* 4 | FIXED TABLE FULL | X$KSPPCV | 1 | 2034 | 0 (0)| 00:00:01 |
- |* 5 | FIXED TABLE FULL | X$KSPPI | 1 | 81 | 0 (0)| 00:00:01 |
- --------------------------------------------------------------------------------
-
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 1 - access("X"."INDX"="Y"."INDX")
- filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
- "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
- 4 - filter(TO_NUMBER(TO_CHAR("ID")||"KSPPSTVL")=8192)
- 5 - filter("KSPPINM"='db_block_size' AND
- "X"."INST_ID"=USERENV('INSTANCE') AND BITAND("KSPPIFLG",268435456)=0
- AND TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%')
-
- Note
- -----
- - dynamic sampling used for this statement (level=2)
-
- 27 rows selected.
添加Hint强制不做谓词推入,如下:
- SYS@proc> select /*+ no_merge(a) no_push_pred(a)*/ a.value from (select value from v$parameter p where p.name='db_block_size') a,test b where to_number(id||a.value)=8192;
-
- VALUE
- -----------------------------------------------------------------------------------------------
- 8192
-
- SYS@proc> explain plan for select /*+ no_merge(a) no_push_pred(a)*/ a.value from (select value from v$parameter p where p.name='db_block_size') a,test b where to_number(id||a.value)=8192;
-
- Explained.
-
- SYS@proc> select * from table(dbms_xplan.display());
-
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------------
- Plan hash value: 742769369
-
- ------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 2015 | 2 (0)| 00:00:01 |
- | 1 | NESTED LOOPS | | 1 | 2015 | 2 (0)| 00:00:01 |
- | 2 | VIEW | | 1 | 2002 | 0 (0)| 00:00:01 |
- |* 3 | HASH JOIN | | 1 | 2115 | 0 (0)| 00:00:01 |
- |* 4 | FIXED TABLE FULL| X$KSPPI | 1 | 81 | 0 (0)| 00:00:01 |
- | 5 | FIXED TABLE FULL| X$KSPPCV | 100 | 198K| 0 (0)| 00:00:01 |
- |* 6 | TABLE ACCESS FULL | TEST | 1 | 13 | 2 (0)| 00:00:01 |
- ------------------------------------------------------------------------------
-
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 3 - access("X"."INDX"="Y"."INDX")
- filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
- "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
- 4 - filter("KSPPINM"='db_block_size' AND
- "X"."INST_ID"=USERENV('INSTANCE') AND BITAND("KSPPIFLG",268435456)=0
- AND TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%')
- 6 - filter(TO_NUMBER(TO_CHAR("ID")||"A"."VALUE")=8192)
-
- Note
- -----
- - dynamic sampling used for this statement (level=2)
-
- 28 rows selected.
PS:
该语句也是一样的: select a . value from ( select /*+ no_merge no_push_pred*/ value from v$parameter p where p . name = 'db_block_size' ) a , test b where to_number ( id | | a . value ) = 8192 ;
问题延伸:
- SYS@proc> create table t as select * from v$parameter;
-
- Table created.
-
- SYS@proc> select value from t where name='db_block_size' and to_number(value)=8192;
-
- VALUE
- --------------------------------------------------------------------------------
- 8192
-
- SYS@proc> select value from v$parameter where name='db_block_size' and to_number(value)=8192;
- select value from v$parameter where name='db_block_size' and to_number(value)=8192
- *
- ERROR at line 1:
- ORA-01722: invalid number
该问题在另外一篇做研究。
这里附上链接http://blog.itpub.net/30174570/viewspace-2148607/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30174570/viewspace-2148592/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30174570/viewspace-2148592/