做為一個DBA，你大概習慣了定期要抓取資料庫中的非綁定變量SQL，這些SQL經常扮演着一箱蘋果中蛀蟲的角色。
看到下列SQL你必定覺得眼熟:
<code>SELECT</code> <code>substr(sql_text, 1, 80), </code><code>count</code><code>(1)</code>
<code> </code><code>FROM</code> <code>v$sql</code>
<code> </code><code>GROUP</code> <code>BY</code> <code>substr(sql_text, 1, 80)</code>
<code>HAVING</code> <code>count</code><code>(1) > 10</code>
<code> </code><code>ORDER</code> <code>BY</code> <code>2</code>
是的，以上這段抓取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值。這麼說可能不太形象，我們來具體看一下:
<code>SQL> </code><code>create</code> <code>table</code> <code>YOUYUS (t1 </code><code>int</code><code>);</code>
<code>Table</code> <code>created.</code>
<code>SQL> </code><code>alter</code> <code>system flush shared_pool;</code>
<code>System altered.</code>
<code>SQL></code><code>select</code> <code>/*test_matching_a*/ * </code><code>from</code> <code>YOUYUS </code><code>where</code> <code>t1=1;</code>
<code>no</code> <code>rows</code> <code>selected</code>
<code>SQL></code><code>select</code> <code>/*test_matching_a*/ * </code><code>from</code> <code>YOUYUS </code><code>where</code> <code>t1=2;</code>
<code>SQL></code><code>select</code> <code>/*test_matching_a*/ * </code><code>from</code> <code>YOUYUS </code><code>where</code> <code>t1=3;</code>
<code>SQL> col sql_text format a55;</code>
<code>SQL> </code><code>select</code> <code>sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE</code>
<code> </code><code>2 </code><code>FROM</code> <code>V$SQL</code>
<code> </code><code>3 </code><code>WHERE</code> <code>sql_text </code><code>like</code> <code>'%test_matching_a%'</code>
<code> </code><code>4 </code><code>and</code> <code>sql_text </code><code>not</code> <code>like</code> <code>'%like%'</code><code>;</code>
<code>SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE</code>
<code>------------------------------------------------------- ------------------------ ------------------------</code>
<code>select</code> <code>/*test_matching_a*/ * </code><code>from</code> <code>YOUYUS </code><code>where</code> <code>t1=2 4.59124694481197E18 1.00267830752731E19</code>
<code>select</code> <code>/*test_matching_a*/ * </code><code>from</code> <code>YOUYUS </code><code>where</code> <code>t1=3 4.59124694481197E18 1.61270448861426E19</code>
<code>select</code> <code>/*test_matching_a*/ * </code><code>from</code> <code>YOUYUS </code><code>where</code> <code>t1=1 4.59124694481197E18 1.36782048270058E18</code>
<code>/*以上将變量寫死至SQL中的遊标，FORCE_MATCHING_SIGNATURE值完全相同，而EXACT_MATCHING_SIGNATURE值各有不同。FORCE_MATCHING_SIGNATURE值相同說明在遊标共享</code><code>FORCE</code><code>模式下，這些遊标滿足</code><code>CURSOR</code> <code>SHARING的條件 */</code>
<code>SQL> </code><code>alter</code> <code>session </code><code>set</code> <code>cursor_sharing=</code><code>FORCE</code><code>;</code>
<code>Session altered.</code>
<code>SQL> col sql_text </code><code>for</code> <code>a70</code>
<code>SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE</code>
<code>---------------------------------------------------------------------- ------------------------ ------------------------</code>
<code>select</code> <code>/*test_matching_a*/ * </code><code>from</code> <code>YOUYUS </code><code>where</code> <code>t1=:</code><code>"SYS_B_0"</code> <code>4.59124694481197E18 4.59124694481197E18</code>
<code>/*</code><code>FORCE</code><code>模式下将SQL文本中的變量值轉換成了:SYS_B形式，EXACT_MATCHING_SIGNATURE也随之等同于FORCE_MATCHING_SIGNATURE了*/</code>
以上示範說明了FORCE_MATCHING_SIGNATURE列可以幫助我們找出那些潛在可以共享的遊标(也包括了因非綁定問題造成的遊标無法共享)，現在我們利用它來完善捕獲非綁定變量SQL的腳本：
<code>SQL> </code><code>select</code> <code>/*test_matching_b*/ * </code><code>from</code> <code>YOUYUS </code><code>where</code> <code>t1=1;</code>
<code>SQL> </code><code>select</code> <code>/*test_matching_b*/ * </code><code>from</code> <code>YOUYUS </code><code>where</code> <code>t1=</code><code>'1'</code><code>; //我有引号，我與衆不同！</code>
<code> </code><code>3 </code><code>WHERE</code> <code>sql_text </code><code>like</code> <code>'%test_matching_b%'</code>
<code>select</code> <code>/*test_matching_b*/ * </code><code>from</code> <code>YOUYUS </code><code>where</code> <code>t1=</code><code>'1'</code> <code>1.43666633406896E19 1.83327833675856E19</code>
<code>select</code> <code>/*test_matching_b*/ * </code><code>from</code> <code>YOUYUS </code><code>where</code> <code>t1=1 1.43666633406896E19 8.05526057286178E18</code>
<code>/*多餘的引号也會導緻遊标無法共享，此時的FORCE_MATCHING_SIGNATURE 也會是一緻的*/</code>
<code>select</code> <code>FORCE_MATCHING_SIGNATURE, </code><code>count</code><code>(1)</code>
<code> </code><code>from</code> <code>v$sql</code>
<code> </code><code>where</code> <code>FORCE_MATCHING_SIGNATURE > 0</code>
<code> </code><code>and</code> <code>FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE</code>
<code> </code><code>group</code> <code>by</code> <code>FORCE_MATCHING_SIGNATURE</code>
<code>having</code> <code>count</code><code>(1) > &a</code>
<code> </code><code>order</code> <code>by</code> <code>2;</code>
<code>Enter value </code><code>for</code> <code>a: 10</code>
<code>old 6: </code><code>having</code> <code>count</code><code>(1) > &a</code>
<code>new 6: </code><code>having</code> <code>count</code><code>(1) > 10</code>
<code>FORCE_MATCHING_SIGNATURE </code><code>COUNT</code><code>(1)</code>
<code>------------------------ ----------</code>
<code> </code><code>8.81463386552502E18 12</code>
<code>So We find it!</code>
在這裡再推薦一種來自MOS，find Literal SQL的方法:
PL/SQL – Version: 8.1.7 to 10.2 Information in this document applies to any platform.
<code>[maclean@rh2 bin]$ cat find_literal.sql</code>
<code>set</code> <code>serveroutput </code><code>on</code>
<code>set</code> <code>linesize 120</code>
<code>--</code>
<code>-- This anonymous PL/SQL block must be executed as INTERNAL or SYS</code>
<code>-- Execute from : SQL*PLUS</code>
<code>-- CAUTION:</code>
<code>-- This sample program has been tested on Oracle Server - Enterprise Edition</code>
<code>-- However, there is no guarantee of effectiveness because of the possibility</code>
<code>-- of error in transmitting or implementing it. It is meant to be used as a</code>
<code>-- template, and it may require modification.</code>
<code>declare</code>
<code>b_myadr VARCHAR2(20);</code>
<code>b_myadr1 VARCHAR2(20);</code>
<code>qstring VARCHAR2(100);</code>
<code>b_anybind NUMBER;</code>
<code>cursor</code> <code>my_statement </code><code>is</code>
<code>select</code> <code>address </code><code>from</code> <code>v$sql</code>
<code>group</code> <code>by</code> <code>address;</code>
<code>cursor</code> <code>getsqlcode </code><code>is</code>
<code>select</code> <code>substr(sql_text,1,60)</code>
<code>from</code> <code>v$sql</code>
<code>where</code> <code>address = b_myadr;</code>
<code>cursor</code> <code>kglcur </code><code>is</code>
<code>select</code> <code>kglhdadr </code><code>from</code> <code>x$kglcursor</code>
<code>where</code> <code>kglhdpar = b_myadr</code>
<code>and</code> <code>kglhdpar != kglhdadr</code>
<code>and</code> <code>kglobt09 = 0;</code>
<code>cursor</code> <code>isthisliteral </code><code>is</code>
<code>select</code> <code>kkscbndt</code>
<code>from</code> <code>x$kksbv</code>
<code>where</code> <code>kglhdadr = b_myadr1;</code>
<code>begin</code>
<code>dbms_output.enable(10000000);</code>
<code>open</code> <code>my_statement;</code>
<code>loop</code>
<code>Fetch</code> <code>my_statement </code><code>into</code> <code>b_myadr;</code>
<code>open</code> <code>kglcur;</code>
<code>fetch</code> <code>kglcur </code><code>into</code> <code>b_myadr1;</code>
<code>if kglcur%FOUND </code><code>Then</code>
<code>open</code> <code>isthisliteral;</code>
<code>fetch</code> <code>isthisliteral </code><code>into</code> <code>b_anybind;</code>
<code>if isthisliteral%NOTFOUND </code><code>Then</code>
<code>open</code> <code>getsqlcode;</code>
<code>fetch</code> <code>getsqlcode </code><code>into</code> <code>qstring;</code>
<code>dbms_output.put_line(</code><code>'Literal:'</code><code>||qstring||</code><code>' address: '</code><code>||b_myadr);</code>
<code>close</code> <code>getsqlcode;</code>
<code>end</code> <code>if;</code>
<code>close</code> <code>isthisliteral;</code>
<code>close</code> <code>kglcur;</code>
<code>Exit </code><code>When</code> <code>my_statement%NOTFOUND;</code>
<code>End</code> <code>loop;</code>
<code>close</code> <code>my_statement;</code>
<code>end</code><code>;</code>
<code>/</code>
<code>/*嘗試執行*/</code>
<code>SQL> @find_literal</code>
<code>Literal:</code><code>select</code> <code>inst_id, java_size, round(java_size / basejava_size, address: 00000000BC6E94E8</code>
<code>Literal:</code><code>select</code> <code>reason_id, object_id, subobject_id, internal_instance address: 00000000BC5F1D60</code>
<code>Literal:</code><code>select</code> <code>DBID, </code><code>NAME</code><code>, CREATED, RESETLOGS_CHANGE#, RESETLOGS_TI address: 00000000BC6000B0</code>
<code>Literal:</code><code>select</code> <code>di.inst_id,di.didbi,di.didbn,to_date(di.dicts,</code><code>'MM/DD/ address: 00000000BC530DA8</code>
<code>Literal: declare vsn varchar2(20); begin address: 00000000BC85A9F8</code>
<code>Literal:SELECT INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, PRIO address: 00000000BC829978</code>
<code>Literal:select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where address: 00000000BCA84D00</code>
<code>Literal:select SYS_CONTEXT('</code><code>USERENV</code><code>', '</code><code>SERVER_HOST</code><code>'), SYS_CONTEXT('</code><code>U address: 00000000BC771BF0</code>
<code>Literal: </code><code>select</code> <code>sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_S address: 00000000BC4673A8</code>
<code>Literal:</code><code>select</code> <code>streams_pool_size_for_estimate s, streams_p address: 00000000BCA58848</code>
<code>Literal: </code><code>select</code> <code>open_mode </code><code>from</code> <code>v$</code><code>database</code> <code>address: 00000000BC5DF2D0</code>
<code>Literal:</code><code>select</code> <code>FORCE_MATCHING_SIGNATURE, </code><code>count</code><code>(1) </code><code>from</code> <code>v$sql wher address: 00000000BCA91628</code>
<code>Literal:</code><code>select</code> <code>inst_id, tablespace_name, segment_file, segment_block address: 00000000BC66EF38</code>
<code>Literal:</code><code>select</code> <code>sum</code><code>(used_blocks), ts.ts# </code><code>from</code> <code>GV$SORT_SEGMENT gv, t address: 00000000BCAA01B0</code>
<code>Literal:</code><code>BEGIN</code> <code>DBMS_OUTPUT.ENABLE(</code><code>NULL</code><code>); </code><code>END</code><code>; address: 00000000BC61D2D8</code>
<code>Literal:</code><code>select</code> <code>value$ </code><code>from</code> <code>props$ </code><code>where</code> <code>name</code> <code>= </code><code>'GLOBAL_DB_NAME'</code> <code>address: 00000000BC570500</code>
<code>Literal:</code><code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>sys.job$ </code><code>where</code> <code>(next_date > sysdate) an address: 00000000BC6C53F8</code>
<code>Literal:</code><code>select</code> <code>java_pool_size_for_estimate s, java_pool_si address: 00000000BCA65070</code>
<code>Literal:</code><code>select</code> <code>local_tran_id, global_tran_fmt, global_oracle_id, glo address: 00000000BC5900B8</code>
<code>Literal:</code><code>select</code> <code>inst_id,kglnaobj,kglfnobj,kglobt03, kglobhs0+kglobhs1 address: 00000000BC921538</code>
<code>Literal:</code><code>select</code> <code>o.owner#,o.</code><code>name</code><code>,o.namespace,o.remoteowner,o.linkname, address: 00000000BCA83E90</code>
<code>Literal:</code><code>SELECT</code> <code>* </code><code>FROM</code> <code>V$SQL address: 00000000BCA58BC0</code>
<code>Literal:</code><code>SELECT</code> <code>ADDRESS </code><code>FROM</code> <code>V$SQL </code><code>GROUP</code> <code>BY</code> <code>ADDRESS address: 00000000BC565BE8</code>
<code>Literal: </code><code>begin</code> <code>dbms_rcvman.resetAll; </code><code>end</code><code>; address: 00000000BC759858</code>
<code>Literal:</code><code>declare</code> <code>b_myadr VARCHAR2(20); b_myadr1 VARCHAR2(20); qstring address: 00000000BC928FF8</code>
<code>Literal:</code><code>select</code> <code>/*+ </code><code>rule</code> <code>*/ bucket_cnt, row_cnt, cache_cnt, null_cnt, address: 00000000BC898BF8</code>
<code>Literal:</code><code>select</code> <code>CONF#, </code><code>NAME</code><code>, VALUE </code><code>from</code> <code>GV$RMAN_CONFIGURATION </code><code>where</code> <code>i address: 00000000BC8CB7F8</code>
<code>Literal:</code><code>select</code> <code>f.file#, f.block#, f.ts#, f.length </code><code>from</code> <code>fet$ f, ts$ t address: 00000000BC8CDFE8</code>
<code>Literal:</code><code>select</code> <code>u.</code><code>name</code><code>, o.</code><code>name</code><code>, </code><code>trigger</code><code>$.sys_evts, </code><code>trigger</code><code>$.type# fr address: 00000000BCA877B8</code>
<code>Literal:</code><code>select</code> <code>id, </code><code>name</code><code>, block_size, advice_status, address: 00000000BC636B38</code>
<code>Literal:</code><code>select</code> <code>incarnation#, resetlogs_change#, resetlogs_time, address: 00000000BCA94250</code>
<code>Literal:</code><code>select</code> <code>INSTANCE_NUMBER , INSTANCE_NAME , HOST_NAME , VERSIO address: 00000000BC62A678</code>
<code>Literal:</code><code>select</code> <code>ks.inst_id,ksuxsins,ksuxssid,ksuxshst,ksuxsver,ksuxst address: 00000000BC8E5440</code>
<code>Literal:</code><code>select</code> <code>timestamp</code><code>, flags </code><code>from</code> <code>fixed_obj$ </code><code>where</code> <code>obj#=:1 address: 00000000BC916C78</code>
<code>Literal:</code><code>select</code> <code>size_for_estimate, size_factor * address: 00000000BCA5F830</code>
<code>Literal:</code><code>select</code> <code>shared_pool_size_for_estimate s, shared_pool address: 00000000BCA5A350</code>
<code>Literal:</code><code>select</code> <code>SQL_TEXT , SQL_FULLTEXT , SQL_ID, SHARABLE_MEM , PE address: 00000000BC76B3A0</code>
<code>Literal:lock </code><code>table</code> <code>sys.col_usage$ </code><code>in</code> <code>exclusive mode nowait address: 00000000BCA05978</code>
<code>Literal:</code><code>select</code> <code>'x'</code> <code>from</code> <code>dual address: 00000000BC583818</code>
<code>Literal: </code><code>select</code> <code>name</code><code>, resetlogs_time, resetlogs_ch address: 00000000BCA9D430</code>
<code>Literal:</code><code>select</code> <code>inst_id, sp_size, round(sp_size / basesp_size, 4), k address: 00000000BC65A9F0</code>
<code>Literal:</code><code>select</code> <code>userenv(</code><code>'Instance'</code><code>), icrid, to_number(icrls), address: 00000000BC692260</code>
<code>Literal:</code><code>select</code> <code>shared_pool_size_for_estimate, shared_pool_size_facto address: 00000000BCAE0750</code>
<code>Literal:</code><code>select</code> <code>INST_ID, RMRNO, RMNAM, RMVAL </code><code>from</code> <code>X$KCCRM </code><code>where</code> <code>RMNAM address: 00000000BC8CD778</code>
<code>Literal:</code><code>select</code> <code>metadata </code><code>from</code> <code>kopm$ </code><code>where</code> <code>name</code><code>=</code><code>'DB_FDO'</code> <code>address: 00000000BC9EBB98</code>
<code>Literal:</code><code>select</code> <code>java_pool_size_for_estimate, java_pool_size_factor, address: 00000000BC5B27D0</code>
<code>Literal:</code><code>SELECT</code> <code>INCARNATION#, INCARNATION#, RESETLOGS_CHANGE#, RESETL address: 00000000BC829C48</code>
<code>Literal:</code><code>select</code> <code>file# </code><code>from</code> <code>file$ </code><code>where</code> <code>ts#=:1 address: 00000000BC87CF18</code>
<code>Literal:</code><code>select</code> <code>A.inst_id, A.bpid, B.bp_name, A.blksz, address: 00000000BC802248</code>
<code>Literal:lock </code><code>table</code> <code>sys.mon_mods$ </code><code>in</code> <code>exclusive mode nowait address: 00000000BC5CBE68</code>
本文轉自maclean_007 51CTO部落格，原文連結:http://blog.51cto.com/maclean/1278974
![JDBC連接配接資料庫（statement）[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)