Oracle 利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL

阳光穿透心脏的1/2处 2022-06-07 03:58 123阅读 0赞

做为一个DBA，你大概习惯了定期要抓取数据库中的非绑定变量SQL，这些SQL经常扮演着一箱苹果中蛀虫的角色。  
看到下列SQL你必定觉得眼熟:

SELECT substr(sql_text, 1, 80), count(1)
      FROM v$sql
     GROUP BY substr(sql_text, 1, 80)
    HAVING count(1) > 10
     ORDER BY 2

是的，以上这段抓取literal sql的脚本大约从8i时代就开始流行了，在那时它很popular也很休闲，使用它或许还会给你的雇主留下一丝神秘感。不过今天我要告诉你的是，它彻底过时了，落伍了，已经不是fashion master了。  
10g以后v$SQL动态性能视图增加了FORCE\_MATCHING\_SIGNATURE列，其官方定义为”The signature used when the CURSOR\_SHARING parameter is set to FORCE”，也就是Oracle通过将原SQL\_TEXT转换为可能的FORCE模式后计算得到的一个SIGNATURE值。这么说可能不太形象，我们来具体看一下:

SQL> create table YOUYUS (t1 int);
    Table created.
    
    SQL> alter system flush shared_pool;
    System altered.
    
    SQL>select /*test_matching_a*/ * from YOUYUS where t1=1;
    no rows selected
    
    SQL>select /*test_matching_a*/ * from YOUYUS where t1=2;
    no rows selected
    
    SQL>select /*test_matching_a*/ * from YOUYUS where t1=3;
    no rows selected
    
    SQL> col sql_text format a55;
    SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
      2    FROM V$SQL
      3   WHERE sql_text like '%test_matching_a%'
      4     and sql_text not like '%like%';
    
    SQL_TEXT                                                FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
    ------------------------------------------------------- ------------------------ ------------------------
    select /*test_matching_a*/ * from YOUYUS where t1=2          4.59124694481197E18      1.00267830752731E19
    select /*test_matching_a*/ * from YOUYUS where t1=3          4.59124694481197E18      1.61270448861426E19
    select /*test_matching_a*/ * from YOUYUS where t1=1          4.59124694481197E18      1.36782048270058E18
    
    /*以上将变量硬编码至SQL中的游标，FORCE_MATCHING_SIGNATURE值完全相同，而EXACT_MATCHING_SIGNATURE值各有不同。FORCE_MATCHING_SIGNATURE值相同说明在游标共享FORCE模式下，这些游标满足CURSOR SHARING的条件 */
    
    SQL> alter system flush shared_pool;
    System altered.
    SQL> alter session set cursor_sharing=FORCE;
    Session altered.
    
    SQL>select /*test_matching_a*/ * from YOUYUS where t1=1;
    no rows selected
    
    SQL>select /*test_matching_a*/ * from YOUYUS where t1=2;
    no rows selected
    
    SQL>select /*test_matching_a*/ * from YOUYUS where t1=3;
    no rows selected
    
    SQL> col sql_text for a70
    SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
      2    FROM V$SQL
      3   WHERE sql_text like '%test_matching_a%'
      4     and sql_text not like '%like%';
    
    SQL_TEXT                                                               FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
    ---------------------------------------------------------------------- ------------------------ ------------------------
    select /*test_matching_a*/ * from YOUYUS where t1=:"SYS_B_0"                4.59124694481197E18      4.59124694481197E18
    
    /*FORCE模式下将SQL文本中的变量值转换成了:SYS_B形式，EXACT_MATCHING_SIGNATURE也随之等同于FORCE_MATCHING_SIGNATURE了*/

以上演示说明了FORCE\_MATCHING\_SIGNATURE列可以帮助我们找出那些潜在可以共享的游标(也包括了因非绑定问题造成的游标无法共享)，现在我们利用它来完善捕获非绑定变量SQL的脚本：

SQL> alter system flush shared_pool;
    System altered.
    
    SQL> select  /*test_matching_b*/ * from YOUYUS where t1=1;
    no rows selected
    
    SQL> select  /*test_matching_b*/ * from YOUYUS where t1='1';            //我有引号，我与众不同！
    no rows selected
    
    SQL> col sql_text for a70
    
    SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
      2    FROM V$SQL
      3   WHERE sql_text like '%test_matching_b%'
      4     and sql_text not like '%like%';
    
    SQL_TEXT                                                               FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
    ---------------------------------------------------------------------- ------------------------ ------------------------
    select  /*test_matching_b*/ * from YOUYUS where t1='1'                      1.43666633406896E19      1.83327833675856E19
    select  /*test_matching_b*/ * from YOUYUS where t1=1                       1.43666633406896E19      8.05526057286178E18
    
    /*多余的引号也会导致游标无法共享，此时的FORCE_MATCHING_SIGNATURE 也会是一致的*/
    
    select FORCE_MATCHING_SIGNATURE, count(1)
      from v$sql
     where FORCE_MATCHING_SIGNATURE > 0
       and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
     group by FORCE_MATCHING_SIGNATURE
    having count(1) > &a
     order by 2;
    
    Enter value for a: 10
    old   6: having count(1) > &a
    new   6: having count(1) > 10
    
    FORCE_MATCHING_SIGNATURE   COUNT(1)
    ------------------------ ----------
         8.81463386552502E18         12
    
    So We find it!

在这里再推荐一种来自MOS，find Literal SQL的方法:

> ## **How to Find Literal SQL in Shared Pool** ##
> 
> ##     ##
> 
> ## Applies to: ##
> 
> PL/SQL – Version: 8.1.7 to 10.2  
> Information in this document applies to any platform.
> 
> ## Goal ##
> 
> There is no direct way to query the dictionary for literal SQL only.
> 
> However the following example will try to exclude all SQL statements in the  
> shared pool that do use bind variables.
> 
> There still might be situations, with statements using subqueries, where the  
> example still will show SQL statements using bind variables.
> 
> ## Solution ##
> 
> Create the following PL/SQL block:

[maclean@rh2 bin]$ cat  find_literal.sql
    
    set serveroutput on
    set linesize 120
    --
    -- This anonymous PL/SQL block must be executed as INTERNAL or SYS
    -- Execute from : SQL*PLUS
    -- CAUTION:
    -- This sample program has been tested on Oracle Server - Enterprise Edition
    -- However, there is no guarantee of effectiveness because of the possibility
    -- of error in transmitting or implementing it. It is meant to be used as a
    -- template, and it may require modification.
    --
    declare
    b_myadr VARCHAR2(20);
    b_myadr1 VARCHAR2(20);
    qstring VARCHAR2(100);
    b_anybind NUMBER;
    
    cursor my_statement is
    select address from v$sql
    group by address;
    
    cursor getsqlcode is
    select substr(sql_text,1,60)
    from v$sql
    where address = b_myadr;
    
    cursor kglcur is
    select kglhdadr from x$kglcursor
    where kglhdpar = b_myadr
    and kglhdpar != kglhdadr
    and kglobt09 = 0;
    
    cursor isthisliteral is
    select kkscbndt
    from x$kksbv
    where kglhdadr = b_myadr1;
    
    begin
    
    dbms_output.enable(10000000);
    
    open my_statement;
    loop
    Fetch my_statement into b_myadr;
    open kglcur;
    fetch kglcur into b_myadr1;
    if kglcur%FOUND Then
    open isthisliteral;
    fetch isthisliteral into b_anybind;
    if isthisliteral%NOTFOUND Then
    open getsqlcode;
    fetch getsqlcode into qstring;
    dbms_output.put_line('Literal:'||qstring||' address: '||b_myadr);
    close getsqlcode;
    end if;
    close isthisliteral;
    end if;
    close kglcur;
    Exit When my_statement%NOTFOUND;
    End loop;
    close my_statement;
    end;
    /
    
    /*尝试执行*/
    
    SQL> @find_literal
    Literal:select inst_id, java_size, round(java_size / basejava_size,  address: 00000000BC6E94E8
    Literal:select reason_id, object_id, subobject_id, internal_instance address: 00000000BC5F1D60
    Literal:select  DBID, NAME, CREATED, RESETLOGS_CHANGE#, RESETLOGS_TI address: 00000000BC6000B0
    Literal:select di.inst_id,di.didbi,di.didbn,to_date(di.dicts,'MM/DD/ address: 00000000BC530DA8
    Literal:      declare          vsn  varchar2(20);             begin  address: 00000000BC85A9F8
    Literal:SELECT INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, PRIO address: 00000000BC829978
    Literal:select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where  address: 00000000BCA84D00
    Literal:select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U address: 00000000BC771BF0
    Literal: select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_S address: 00000000BC4673A8
    Literal:select streams_pool_size_for_estimate s,           streams_p address: 00000000BCA58848
    Literal:         select open_mode from v$database address: 00000000BC5DF2D0
    Literal:select FORCE_MATCHING_SIGNATURE, count(1)   from v$sql  wher address: 00000000BCA91628
    Literal:select inst_id, tablespace_name, segment_file, segment_block address: 00000000BC66EF38
    Literal:select sum(used_blocks), ts.ts#   from GV$SORT_SEGMENT gv, t address: 00000000BCAA01B0
    Literal:BEGIN DBMS_OUTPUT.ENABLE(NULL); END; address: 00000000BC61D2D8
    Literal:select value$ from props$ where name = 'GLOBAL_DB_NAME' address: 00000000BC570500
    Literal:select count(*) from sys.job$ where (next_date > sysdate) an address: 00000000BC6C53F8
    Literal:select java_pool_size_for_estimate s,           java_pool_si address: 00000000BCA65070
    Literal:select local_tran_id, global_tran_fmt, global_oracle_id, glo address: 00000000BC5900B8
    Literal:select inst_id,kglnaobj,kglfnobj,kglobt03, kglobhs0+kglobhs1 address: 00000000BC921538
    Literal:select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname, address: 00000000BCA83E90
    Literal:SELECT * FROM V$SQL address: 00000000BCA58BC0
    Literal:SELECT ADDRESS FROM V$SQL GROUP BY ADDRESS address: 00000000BC565BE8
    Literal:      begin          dbms_rcvman.resetAll;       end; address: 00000000BC759858
    Literal:declare b_myadr VARCHAR2(20); b_myadr1 VARCHAR2(20); qstring address: 00000000BC928FF8
    Literal:select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, address: 00000000BC898BF8
    Literal:select CONF#, NAME, VALUE from GV$RMAN_CONFIGURATION where i address: 00000000BC8CB7F8
    Literal:select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t address: 00000000BC8CDFE8
    Literal:select u.name, o.name, trigger$.sys_evts, trigger$.type#  fr address: 00000000BCA877B8
    Literal:select id, name, block_size, advice_status,                  address: 00000000BC636B38
    Literal:select incarnation#, resetlogs_change#, resetlogs_time,      address: 00000000BCA94250
    Literal:select  INSTANCE_NUMBER , INSTANCE_NAME , HOST_NAME , VERSIO address: 00000000BC62A678
    Literal:select ks.inst_id,ksuxsins,ksuxssid,ksuxshst,ksuxsver,ksuxst address: 00000000BC8E5440
    Literal:select timestamp, flags from fixed_obj$ where obj#=:1 address: 00000000BC916C78
    Literal:select size_for_estimate,                      size_factor * address: 00000000BCA5F830
    Literal:select shared_pool_size_for_estimate s,          shared_pool address: 00000000BCA5A350
    Literal:select  SQL_TEXT , SQL_FULLTEXT , SQL_ID,  SHARABLE_MEM , PE address: 00000000BC76B3A0
    Literal:lock table sys.col_usage$ in exclusive mode nowait address: 00000000BCA05978
    Literal:select 'x' from dual  address: 00000000BC583818
    Literal:      select name, resetlogs_time,              resetlogs_ch address: 00000000BCA9D430
    Literal:select inst_id, sp_size, round(sp_size / basesp_size, 4),  k address: 00000000BC65A9F0
    Literal:select userenv('Instance'),  icrid, to_number(icrls),        address: 00000000BC692260
    Literal:select shared_pool_size_for_estimate, shared_pool_size_facto address: 00000000BCAE0750
    Literal:select INST_ID, RMRNO, RMNAM, RMVAL from X$KCCRM where RMNAM address: 00000000BC8CD778
    Literal:select metadata from kopm$  where name='DB_FDO' address: 00000000BC9EBB98
    Literal:select java_pool_size_for_estimate, java_pool_size_factor,   address: 00000000BC5B27D0
    Literal:SELECT INCARNATION#, INCARNATION#, RESETLOGS_CHANGE#, RESETL address: 00000000BC829C48
    Literal:select file# from file$ where ts#=:1 address: 00000000BC87CF18
    Literal:select A.inst_id, A.bpid, B.bp_name, A.blksz,                address: 00000000BC802248
    Literal:lock table sys.mon_mods$ in exclusive mode nowait address: 00000000BC5CBE68
    Literal:lock table sys.mon_mods$ in exclusive mode nowait address: 00000000BC5CBE68
    
    原文地址：http://www.askmaclean.com/archives/%E5%88%A9%E7%94%A8force_matching_signature%E6%8D%95%E8%8E%B7%E9%9D%9E%E7%BB%91%E5%AE%9A%E5%8F%98%E9%87%8Fsql.html