只有字符变量不同的sql为什么force_matching_signature不同？

repentance

问题描述:
下面这几条sql的文本出去变量外完全相同，plan_hash_value也相同，为什么force_matching_signature不同?
SQL> SELECT SQL_ID, ADDRESS, PLAN_HASH_VALUE, FORCE_MATCHING_SIGNATURE
2 FROM V$SQL A
3 WHERE A.SQL_TEXT LIKE '%insert into T_CUST_DEPOSIT_LOG%'
4 AND ROWNUM<9;

SQL_ID ADDRESS PLAN_HASH_VALUE FORCE_MATCHING_SIGNATURE
--------------- ---------------- --------------- ------------------------
avgtavdyc404t 070000078B88A7C0 4090803621 1.09805004265827E19
bp55xgyuyn4gc 07000007886D9FB8 4090803621 3.84302087730842E18
3q4ztrn82s6gg 070000064679B050 4090803621 4.90469998755562E18
049uuvah8ha9s 0700000626E82190 4090803621 1.53135495846178E19
19puc8y7awfka 07000006851B52E0 4090803621 5.0888780603819E18
4rm8wxaxw4jkw 0700000677500B28 4090803621 1.15282384692596E19
2cmsmprwh8p20 070000060E5ECE98 4090803621 1.34194569999554E19
4vwsvq79a0tsd 07000006A2472DC8 4090803621 1.03086469085271E19

9 rows selected

--SQLID:avgtavdyc404t 对应的SQL文
SQL> select sql_fulltext from v$sql a where a.sql_id='avgtavdyc404t';

SQL_FULLTEXT
----------------------------------------------------------------------
insert into T_CUST_DEPOSIT_LOG(CHANGE_ID,OLD_NEW,ACCOUNT_ATT_TYPE,ACCO
UNT_CODE,ACCOUNT_NAME,BALANCE_DATE,BALANCE_YEAR,BONUS_BAL_DATE,CANCEL_
DATE,CAPITAL_BALANCE,COMPANY_ID,DEPOSIT_DATE,DEPOSIT_TYPE,DEPOST_STATE
,GROUP_ITEM_ID,GURNT_BAL_DATE,INDIV_ATT_RATE,INSERT_DATE,INSERT_PERSON
,INTEREST_BALANCE,INTEREST_CAPITAL,INTEREST_SUM,ITEM_ID,LA_BAL_ID,MONE
Y_ID,POLICY_ID,SETTLE_DATE,UPDATER_ID,UPDATE_TIME) select 265958831,2,
ACCOUNT_ATT_TYPE,ACCOUNT_CODE,ACCOUNT_NAME,BALANCE_DATE,BALANCE_YEAR,B
ONUS_BAL_DATE,CANCEL_DATE,CAPITAL_BALANCE,COMPANY_ID,DEPOSIT_DATE,DEPO
SIT_TYPE,DEPOST_STATE,GROUP_ITEM_ID,GURNT_BAL_DATE,INDIV_ATT_RATE,INSE
RT_DATE,INSERT_PERSON,INTEREST_BALANCE,INTEREST_CAPITAL,INTEREST_SUM,I
TEM_ID,LA_BAL_ID,MONEY_ID,POLICY_ID,SETTLE_DATE,UPDATER_ID,UPDATE_TIME
from T_CUST_DEPOSIT where POLICY_ID=:POLICY_ID and account_code=to_ch
ar(28547158)

--SQLID:bp55xgyuyn4gc 对应的SQL文
SQL> select sql_fulltext from v$sql a where a.sql_id='bp55xgyuyn4gc';

SQL_FULLTEXT
----------------------------------------------------------------------
insert into T_CUST_DEPOSIT_LOG(CHANGE_ID,OLD_NEW,ACCOUNT_ATT_TYPE,ACCO
UNT_CODE,ACCOUNT_NAME,BALANCE_DATE,BALANCE_YEAR,BONUS_BAL_DATE,CANCEL_
DATE,CAPITAL_BALANCE,COMPANY_ID,DEPOSIT_DATE,DEPOSIT_TYPE,DEPOST_STATE
,GROUP_ITEM_ID,GURNT_BAL_DATE,INDIV_ATT_RATE,INSERT_DATE,INSERT_PERSON
,INTEREST_BALANCE,INTEREST_CAPITAL,INTEREST_SUM,ITEM_ID,LA_BAL_ID,MONE
Y_ID,POLICY_ID,SETTLE_DATE,UPDATER_ID,UPDATE_TIME) select 265958831,1,
ACCOUNT_ATT_TYPE,ACCOUNT_CODE,ACCOUNT_NAME,BALANCE_DATE,BALANCE_YEAR,B
ONUS_BAL_DATE,CANCEL_DATE,CAPITAL_BALANCE,COMPANY_ID,DEPOSIT_DATE,DEPO
SIT_TYPE,DEPOST_STATE,GROUP_ITEM_ID,GURNT_BAL_DATE,INDIV_ATT_RATE,INSE
RT_DATE,INSERT_PERSON,INTEREST_BALANCE,INTEREST_CAPITAL,INTEREST_SUM,I
TEM_ID,LA_BAL_ID,MONEY_ID,POLICY_ID,SETTLE_DATE,UPDATER_ID,UPDATE_TIME
from T_CUST_DEPOSIT where POLICY_ID=:POLICY_ID and account_code=to_ch
ar(18242150)

[ 本帖最后由 repentance 于 2012-7-31 10:54 编辑 ]

Maclean Liu(刘相兵

看看下面的演示：

[oracle@nas ~]$ sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 31 05:26:02 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> variable maclean number;
SQL>
SQL>
SQL>
SQL> select /* maclean_test */ 1 from dual where 1=1985 and 2=:maclean;

no rows selected



SQL> select /* maclean_test */ 1 from dual where 1=1986 and 2=:maclean;

no rows selected

SQL> col FORCE_MATCHING_SIGNATURE for 99999999999999999999
SQL> set linesize 200 pagesize 2000
SQL>  select sql_fulltext,FORCE_MATCHING_SIGNATURE from v$sql where sql_fulltext like '%maclean_test%';

SQL_FULLTEXT                                                                     FORCE_MATCHING_SIGNATURE
-------------------------------------------------------------------------------- ------------------------
select /* maclean_test */ 1 from dual where 1=1985 and 2=:maclean                    16761900744069262711
select /* maclean_test */ 1 from dual where 1=1986 and 2=:maclean                    15578332772800985567





QL> select /* maclean_test */ 1 from dual where 1=1985;

no rows selected

SQL> select /* maclean_test */ 1 from dual where 1=1986;

no rows selected

SQL> select /* maclean_test */ 1 from dual where 2=:maclean;

no rows selected

SQL> select /* maclean_test */ 1 from dual where 1=:maclean;

no rows selected

SQL>  select sql_fulltext,FORCE_MATCHING_SIGNATURE from v$sql where sql_fulltext like '%maclean_test%';

SQL_FULLTEXT                                                                     FORCE_MATCHING_SIGNATURE
-------------------------------------------------------------------------------- ------------------------
select /* maclean_test */ 1 from dual where 1=1985 and 2=:maclean                    16761900744069262711
select /* maclean_test */ 1 from dual where 1=1986 and 2=:maclean                    15578332772800985567
select /* maclean_test */ 1 from dual where 1=1985                                    5375134780712093832
select /* maclean_test */ 1 from dual where 2=:maclean                               17808249526363495366
select /* maclean_test */ 1 from dual where 1=:maclean                                9498205601478576981
select /* maclean_test */ 1 from dual where 1=1986                                    5375134780712093832


在 原始SQL中使用常量 + 绑定变量的组合时可能产生 FORCE_MATCHING_SIGNATURE不一样的 现象

repentance

这些都是literal啊，force_matching不是将literal排除了吗

anbob

note:
sql1 :select 265958831,2,

sql 2 : select 265958831,1,