关于exact_matching_signature 和 force_matching_signature

原创谢辉元 2021-03-08

1230

相信大家对SQL_ID的计算方法是非常的熟悉，即根据SQL文本计算出来的一个hash值，这个文本是原始的文本，不会经过任何格式化处理的，也就是说你一条SQL即使是多了少了个空格，某个字母从小写变成大写，都会导致生成新的SQL_ID。
如下测试：

SYS@orcl>select object_id from hr.test where id=1;

 OBJECT_ID
----------
     42785
SYS@orcl>select  object_id from hr.test where id=1;  --在第一条SQL上加了个空格

 OBJECT_ID
----------
     42785
SYS@orcl>Select  object_id from hr.test where id=1;--将上一条SQL首字母改为大写

 OBJECT_ID
----------
     42785
--如下3条SQL，3个SQL_ID
SYS@orcl>set lines 200
SYS@orcl>col sql_text for a45;
SYS@orcl>select sql_id,sql_text from v$sqlarea where sql_text like '%object_id from hr.test where id=1';

SQL_ID        SQL_TEXT
------------- ---------------------------------------------
cstqp838u9fyr select  object_id from hr.test where id=1
djk64wxz6bg50 select object_id from hr.test where id=1
30bzgn3tqvzyw Select  object_id from hr.test where id=1
复制

那除了SQL_ID这个根据SQL文本计算的hash值外，Oracle还有两个值也是根据SQL文本来计算来的（10g之后），这两个值就是今天要说的exact_matching_signature 和 force_matching_signature，只是这两个值会对SQL文本进行一定的处理后，再进行hash运算。
exact_matching_signature 的值是对SQL语句去掉多余的空格，统一大小写后（字符常量保持原样不处理），再进行hash运算如果SQL相同，那么SQL语句的exact_matching_signature就是相同的。
force_matching_signature则是在exact_matching_signature算法的基础上，增加了对变量的处理，处理方式为：去掉多余空格，统一大小写，然后还需对未使用绑定变量的变量（要求这条SQL所有变量都没有使用绑定变量）替换为绑定变量，再进行hash运算，如果这样处理后的SQL文本相同，那么SQL语句的force_matching_signature就是相同的。
如下测试：

HR@orcl>set lines 200;
HR@orcl>col owner for a15
HR@orcl>col object_name for a20
HR@orcl>select *
  2    from (select a.*, rownum rn
  3            from (select owner,object_name
  4                    from test t
  5                   where owner = 'HR'
  6                     and created > to_date('2015-08-24', 'yyyy-mm-dd')
  7                   order by object_id) a
  8           where rownum <= 2) t
  9   where t.rn > 1;

OWNER           OBJECT_NAME                  RN
--------------- -------------------- ----------
HR              EMP_COMP                      2

HR@orcl>Select *   --在上一条SQL基础上将首字母改成了大写
  2    from (select a.*, rownum rn
  3            from (select owner,object_name
  4                    from test t
  5                   where owner = 'HR'
  6                     and created > to_date('2015-08-24', 'yyyy-mm-dd')
  7                   order by object_id) a
  8           where rownum <= 2) t
  9   where t.rn > 1;

OWNER           OBJECT_NAME                  RN
--------------- -------------------- ----------
HR              EMP_COMP                      2

HR@orcl>Select *
  2    from (select a.*, rownum rn
  3            from (select owner,object_name
  4                    from test t
  5                   where owner = 'SYS'  --在上一条SQL基础上将HR改成了SYS
  6                     and created > to_date('2015-08-24', 'yyyy-mm-dd')
  7                   order by object_id) a
  8           where rownum <= 2) t
  9   where t.rn > 1;

OWNER           OBJECT_NAME                  RN
--------------- -------------------- ----------
SYS             OLAP_DIM_LEVELS$              2

HR@orcl>
--查询3条SQL的SQL_ID,FORCE_MATCHING_SIGNATURE,FORCE_MATCHING_SIGNATURE的值，可以发现3条SQL_ID 都不相同，第1、2条的FORCE_MATCHING_SIGNATURE值是相同的，3条SQL的FORCE_MATCHING_SIGNATURE 都是相同的。
SYS@orcl>select sql_text,sql_id,t.EXACT_MATCHING_SIGNATURE,t.FORCE_MATCHING_SIGNATURE from v$sql t where t.sql_text like '%from test t%order by object_id%'   and t.sql_text not like '%v$sql%';

SQL_TEXT                                      SQL_ID          EXACT_MATCHING_SIGNATURE   FORCE_MATCHING_SIGNATURE
--------------------------------------------- ------------- -------------------------- --------------------------
Select *   from (select a.*, rownum rn        92ng3twr8s1yu        3899251560693746018       14846743636801793784
    from (select owner,object_name
        from test t                  where ow
ner = 'HR'                    and created > t
o_date('2015-08-24', 'yyyy-mm-dd')
       order by object_id) a          where r
ownum <= 2) t  where t.rn > 1

select *   from (select a.*, rownum rn        8dz6vnpktk8vb        3899251560693746018       14846743636801793784
    from (select owner,object_name
        from test t                  where ow
ner = 'HR'                    and created > t
o_date('2015-08-24', 'yyyy-mm-dd')
       order by object_id) a          where r
ownum <= 2) t  where t.rn > 1

Select *   from (select a.*, rownum rn        2fpzfwv6gz7fm       11906985963311308817       14846743636801793784
    from (select owner,object_name
        from test t                  where ow
ner = 'SYS'                    and created >
to_date('2015-08-24', 'yyyy-mm-dd')
        order by object_id) a          where
rownum <= 2) t  where t.rn > 1

复制

注意上面提到了一点，关于force_matching_signature值的计算方式要求是该SQL中所有变量都没有使用绑定变量才行，即使有一个变量使用到了绑定变量，那么处理方式也是同exact_matching_signature一样的了，即可以看到在有绑定变量的情况下，exact_matching_signature和force_matching_signature的值都是相同的了。
如下测试：

SYS@orcl>select sql_text,sql_id,t.EXACT_MATCHING_SIGNATURE,t.FORCE_MATCHING_SIGNATURE from v$sql t where t.sql_text like '%from test t%order by object_id%'   and t.sql_text not like '%v$sql%';

SQL_TEXT                                      SQL_ID          EXACT_MATCHING_SIGNATURE   FORCE_MATCHING_SIGNATURE
--------------------------------------------- ------------- -------------------------- --------------------------
select *   from (select a.*, rownum rn        fx9pdmw785q32        3036252742642974967        3036252742642974967
    from (select owner,object_name
        from test t                  where ow
ner = 'HR'                    and created > t
o_date('2015-08-24', 'yyyy-mm-dd')
       order by object_id) a          where r
ownum <= :p1) t  where t.rn >:p2

Select *   from (select a.*, rownum rn        d19bkr6h9367m        9198190902493728563        9198190902493728563
    from (select owner,object_name
        from test t                  where ow
ner = 'SYS'                    and created >
to_date('2015-08-24', 'yyyy-mm-dd')
        order by object_id) a          where
rownum <= :p1) t  where t.rn >:p2

Select *   from (select a.*, rownum rn        b9x97ns67r792        3036252742642974967        3036252742642974967
    from (select owner,object_name
        from test t                  where ow
ner = 'HR'                    and created > t
o_date('2015-08-24', 'yyyy-mm-dd')
       order by object_id) a          where r
ownum <= :p1) t  where t.rn >:p2
复制

那么对于这点会有什么影响呢
有时候我们的系统很少用绑定变量，而导致硬解析很高时，我们可以设置个参数即cursor_sharing的值设置为force,这样可以让系统强制将所有变量都转换为系统生成的绑定变量，以此来达到降低硬解析的目的。
但如果大量的SQL只是部分用到了绑定变量（比如分页使用的是分页框架生成的SQL，里面有绑定变量，而其他都没有绑定变量），那么将cursor_sharing这个参数设置为force，那么能不能共享呢。
如下测试：

HR@orcl>alter session set cursor_sharing=force;

Session altered.

HR@orcl>Select *
  2    from (select a.*, rownum rn
  3            from (select owner,object_name
  4                    from test t
  5                   where owner = 'SYS'
  6                     and created > to_date('2015-08-24', 'yyyy-mm-dd')
  7                   order by object_id) a
  8           where rownum <= 2) t
  9   where t.rn > 1;

OWNER           OBJECT_NAME                  RN
--------------- -------------------- ----------
SYS             OLAP_DIM_LEVELS$              2

HR@orcl>Select *
  2    from (select a.*, rownum rn
  3            from (select owner,object_name
  4                    from test t
  5                   where owner = 'HR'
  6                     and created > to_date('2015-08-24', 'yyyy-mm-dd')
  7                   order by object_id) a
  8           where rownum <= 2) t
  9   where t.rn > 1;

OWNER           OBJECT_NAME                  RN
--------------- -------------------- ----------
HR              EMP_COMP                      2

HR@orcl>Select *
  2    from (select a.*, rownum rn
  3            from (select owner,object_name
  4                    from test t
  5                   where owner = 'HR'
  6                     and created > to_date('2015-08-24', 'yyyy-mm-dd')
  7                   order by object_id) a
  8           where rownum <= :p1) t
  9   where t.rn >:p2;

OWNER           OBJECT_NAME                  RN
--------------- -------------------- ----------
HR              SYS_IL0000090443C000          9
                02$$

HR              INTERVAL_SALES               10

HR@orcl>Select *
  2    from (select a.*, rownum rn
  3            from (select owner,object_name
  4                    from test t
  5                   where owner = 'SYS'
  6                     and created > to_date('2015-08-24', 'yyyy-mm-dd')
  7                   order by object_id) a
  8           where rownum <= :p1) t
  9   where t.rn >:p2;

OWNER           OBJECT_NAME                  RN
--------------- -------------------- ----------
SYS             DECIMAL                       9
SYS             V_$MAP_FILE_IO_STACK         10

HR@orcl>

复制

当将cursor_sharing这个参数设置为force时，那么即使一个SQL里使用了部分绑定变量，也可以将剩下为设置为绑定变量的值替换为系统绑定变量，而不用担心因为一条SQL部分使用了绑定变量，而导致没办法共享的问题。

SYS@orcl>select sql_text,sql_id,t.EXACT_MATCHING_SIGNATURE,t.FORCE_MATCHING_SIGNATURE,t.executions from v$sql t where t.sql_text like '%from test t%order by object_id%'   and t.sql_text not like '%v$sql%';

SQL_TEXT                                      SQL_ID          EXACT_MATCHING_SIGNATURE   FORCE_MATCHING_SIGNATURE EXECUTIONS
--------------------------------------------- ------------- -------------------------- -------------------------- ----------
Select *   from (select a.*, rownum rn        8pbnr4y2x4baa       14846743636801793784       14846743636801793784          2
    from (select owner,object_name
        from test t                  where ow
ner = :"SYS_B_0"                    and creat
ed > to_date(:"SYS_B_1", :"SYS_B_2")
         order by object_id) a          where
 rownum <= :"SYS_B_3") t  where t.rn > :"SYS_
B_4"

Select *   from (select a.*, rownum rn        6q721y6buyvgh         117257768961041426         117257768961041426          2
    from (select owner,object_name
        from test t                  where ow
ner = :"SYS_B_0"                    and creat
ed > to_date(:"SYS_B_1", :"SYS_B_2")
         order by object_id) a          where
 rownum <= :p1) t  where t.rn >:p2
复制

oracle

最后修改时间：2021-03-09 10:35:23

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

关于exact_matching_signature 和 force_matching_signature

评论

相关阅读