利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL

做为一个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 selectedSQL>select /*test_matching_a*/ * from YOUYUS where t1=2;no rows selectedSQL>select /*test_matching_a*/ * from YOUYUS where t1=3;no rows selectedSQL> 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.00267830752731E19select /*test_matching_a*/ * from YOUYUS where t1=3          4.59124694481197E18      1.61270448861426E19select /*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 selectedSQL>select /*test_matching_a*/ * from YOUYUS where t1=2;no rows selectedSQL>select /*test_matching_a*/ * from YOUYUS where t1=3;no rows selectedSQL> col sql_text for a70SQL> 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 selectedSQL> select  /*test_matching_b*/ * from YOUYUS where t1='1';            //我有引号，我与众不同！no rows selectedSQL> col sql_text for a70SQL> 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.83327833675856E19select  /*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_SIGNATUREhaving count(1) > &a order by 2;Enter value for a: 10old   6: having count(1) > &anew   6: having count(1) > 10FORCE_MATCHING_SIGNATURE   COUNT(1)------------------------ ----------     8.81463386552502E18         12So 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.sqlset serveroutput onset 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.--declareb_myadr VARCHAR2(20);b_myadr1 VARCHAR2(20);qstring VARCHAR2(100);b_anybind NUMBER;cursor my_statement isselect address from v$sqlgroup by address;cursor getsqlcode isselect substr(sql_text,1,60)from v$sqlwhere address = b_myadr;cursor kglcur isselect kglhdadr from x$kglcursorwhere kglhdpar = b_myadrand kglhdpar != kglhdadrand kglobt09 = 0;cursor isthisliteral isselect kkscbndtfrom x$kksbvwhere kglhdadr = b_myadr1;begindbms_output.enable(10000000);open my_statement;loopFetch my_statement into b_myadr;open kglcur;fetch kglcur into b_myadr1;if kglcur%FOUND Thenopen isthisliteral;fetch isthisliteral into b_anybind;if isthisliteral%NOTFOUND Thenopen 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_literalLiteral:select inst_id, java_size, round(java_size / basejava_size,  address: 00000000BC6E94E8Literal:select reason_id, object_id, subobject_id, internal_instance address: 00000000BC5F1D60Literal:select  DBID, NAME, CREATED, RESETLOGS_CHANGE#, RESETLOGS_TI address: 00000000BC6000B0Literal:select di.inst_id,di.didbi,di.didbn,to_date(di.dicts,'MM/DD/ address: 00000000BC530DA8Literal:      declare          vsn  varchar2(20);             begin  address: 00000000BC85A9F8Literal:SELECT INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, PRIO address: 00000000BC829978Literal:select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where  address: 00000000BCA84D00Literal:select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U address: 00000000BC771BF0Literal: select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_S address: 00000000BC4673A8Literal:select streams_pool_size_for_estimate s,           streams_p address: 00000000BCA58848Literal:         select open_mode from v$database address: 00000000BC5DF2D0Literal:select FORCE_MATCHING_SIGNATURE, count(1)   from v$sql  wher address: 00000000BCA91628Literal:select inst_id, tablespace_name, segment_file, segment_block address: 00000000BC66EF38Literal:select sum(used_blocks), ts.ts#   from GV$SORT_SEGMENT gv, t address: 00000000BCAA01B0Literal:BEGIN DBMS_OUTPUT.ENABLE(NULL); END; address: 00000000BC61D2D8Literal:select value$ from props$ where name = 'GLOBAL_DB_NAME' address: 00000000BC570500Literal:select count(*) from sys.job$ where (next_date > sysdate) an address: 00000000BC6C53F8Literal:select java_pool_size_for_estimate s,           java_pool_si address: 00000000BCA65070Literal:select local_tran_id, global_tran_fmt, global_oracle_id, glo address: 00000000BC5900B8Literal:select inst_id,kglnaobj,kglfnobj,kglobt03, kglobhs0+kglobhs1 address: 00000000BC921538Literal:select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname, address: 00000000BCA83E90Literal:SELECT * FROM V$SQL address: 00000000BCA58BC0Literal:SELECT ADDRESS FROM V$SQL GROUP BY ADDRESS address: 00000000BC565BE8Literal:      begin          dbms_rcvman.resetAll;       end; address: 00000000BC759858Literal:declare b_myadr VARCHAR2(20); b_myadr1 VARCHAR2(20); qstring address: 00000000BC928FF8Literal:select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, address: 00000000BC898BF8Literal:select CONF#, NAME, VALUE from GV$RMAN_CONFIGURATION where i address: 00000000BC8CB7F8Literal:select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t address: 00000000BC8CDFE8Literal:select u.name, o.name, trigger$.sys_evts, trigger$.type#  fr address: 00000000BCA877B8Literal:select id, name, block_size, advice_status,                  address: 00000000BC636B38Literal:select incarnation#, resetlogs_change#, resetlogs_time,      address: 00000000BCA94250Literal:select  INSTANCE_NUMBER , INSTANCE_NAME , HOST_NAME , VERSIO address: 00000000BC62A678Literal:select ks.inst_id,ksuxsins,ksuxssid,ksuxshst,ksuxsver,ksuxst address: 00000000BC8E5440Literal:select timestamp, flags from fixed_obj$ where obj#=:1 address: 00000000BC916C78Literal:select size_for_estimate,                      size_factor * address: 00000000BCA5F830Literal:select shared_pool_size_for_estimate s,          shared_pool address: 00000000BCA5A350Literal:select  SQL_TEXT , SQL_FULLTEXT , SQL_ID,  SHARABLE_MEM , PE address: 00000000BC76B3A0Literal:lock table sys.col_usage$ in exclusive mode nowait address: 00000000BCA05978Literal:select 'x' from dual  address: 00000000BC583818Literal:      select name, resetlogs_time,              resetlogs_ch address: 00000000BCA9D430Literal:select inst_id, sp_size, round(sp_size / basesp_size, 4),  k address: 00000000BC65A9F0Literal:select userenv('Instance'),  icrid, to_number(icrls),        address: 00000000BC692260Literal:select shared_pool_size_for_estimate, shared_pool_size_facto address: 00000000BCAE0750Literal:select INST_ID, RMRNO, RMNAM, RMVAL from X$KCCRM where RMNAM address: 00000000BC8CD778Literal:select metadata from kopm$  where name='DB_FDO' address: 00000000BC9EBB98Literal:select java_pool_size_for_estimate, java_pool_size_factor,   address: 00000000BC5B27D0Literal:SELECT INCARNATION#, INCARNATION#, RESETLOGS_CHANGE#, RESETL address: 00000000BC829C48Literal:select file# from file$ where ts#=:1 address: 00000000BC87CF18Literal:select A.inst_id, A.bpid, B.bp_name, A.blksz,                address: 00000000BC802248Literal:lock table sys.mon_mods$ in exclusive mode nowait address: 00000000BC5CBE68Literal: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