signature=a689942569729355b8b370b29761b62a,利用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 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