To capture an SQL statement for a non-binding variable

Previously, SQL was used to view non-binding variables, but no

Select Hash_value, substr (Sql_text, 1, a) from V$sqlarea where substr (Sql_text, 1, +) in (select substr (Sql_text, 1, max) from V$sqlarea have count (*) > 1 GROUP by substr (Sql_text, 1, 40)); SELECT substr (Sql_text, 1, N), COUNT (1) from V$sql GROUP by substr (Sql_text, 1, N) have count (1) > 1 ORDER by 2;

After 10g, Oracle changed the V$sql view and added a new field force_matching_signature the field Oracle interprets it as the signature used when the Cursor_ Sharing parameter is set to force

The initial understanding should be that the value computed when the cursor_sharing of the database is force is assumed,

And Exact_matching_signature's explanation is signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.

The personal understanding is that when the SQL statement enters the database, the Force_matching_signature value for some SQL that can potentially be shared or because the cursor is not shared by the binding variable problem is exactly the same, and Exact_matching_ The signature value is different

Here's a test in 11GR2:

[Email protected] sql>select * from test;    deptno dname           loc---------- -------------- ----------- --        10 accounting     new  york        20 research        dallas        30 sales           CHICAGO        40 OPERATIONS      BOSTON[email protected] SQL>alter system flush shared_pool; System altered. [email protected] sql>select * from test where deptno=10;     deptno dname          loc---------- -- ------------ -------------        10 ACCOUNTING      NEW YORK[email protected] SQL>select * from test where  deptno=20;    deptno dname           loc---------- -------------- -------------        20  research       dallas[email protected] sql>select *  from test where deptno=30;    DEPTNO DNAME           loc---------- -------------- -------------         30 SALES           Chicago[email protected] sql>select * from test where deptno= ';     DEPTNO Dname          loc---------- -------------- ------ -------        10 accounting     new  york[email protected] sql>select * from test where deptno= ' 20 ';     DEPTNO DNAME           LOC---------- -------------- -------------        20  research       dallas[email protected] sql>select *  From test where deptno= ';    deptno dname           loc---------- -------------- -------------         30 sales          chicago [Email protected] sql>var v_id number[email protected] sql>exec :v_id := 10pl/sql  Procedure successfully completed. [email protected] sql>select * from test where deptno=:v_id;     deptno dname          loc---------- -- ------------ -------------        10 ACCOUNTING      NEW YORK[email protected] SQL>exec :v_id := 20PL/SQL  Procedure successfully completed. [email protected] sql>select * from test where deptno=:v_id;     deptno dname          loc---------- -- ------------ -------------        20 RESEARCH        dallas[email protected] sql>exec :v_id := 30pl/sql procedure successfully  Completed. [email protected] sql>select * from test where deptno=:v_id;     deptno dname          loc---------- -- ------------ -------------        30 SALES           chicago[email protected] sql>set line 123[ Email protected] sql>col sql_text format a40[email protected] sql>set  numwidth 30[email protected] sql>select sql_text,force_matching_signature,exact_ matching_signature from v$sql where sql_text like  '%select * from  test% '; sql_text                                         force_matching_signature       exact_matching_ SIGNATURE---------------------------------------- ------------------------------ ------------------- -----------select * from test where deptno=20                   1674223644458057282             5701787720123824641select * from test  where deptno= '                  1674223644458057282             6624213459289620561select * from test where deptno= '                  1674223644458057282            15799720645668840753select * from test where deptno= '                  1674223644458057282             7423854019058606662select * from  test where deptno=30                   1674223644458057282             6295409922938069091select * from test where deptno=10                   1674223644458057282             5918141949209886904select *  From test where deptno=:v_id                5038495461207490287            5038495461207490287 [email protected] sql>show parameter cursor_sharname                                   TYPE         value------------------------------------ ----------- ------------------------------Cursor_ sharing                        string      exact

The

can see that the above SQL Force_matching_signature values are the same in SQL that does not use bound variables and Exact_matching_ Signature is different then we can refine the SQL statement to find a binding variable without using the above sql:

[email protected] sql>select *  2    from  (select  sql_text,  3                  row_number ()  over (partition by force_matching_signature order by  force_matching_signature)  rn  4             from v$sql  5            where FORCE_MATCHING_SIGNATURE > 0  6              and force_matching_signature != exact_matching_ SIGNATURE)   7   where rn > 1; sql_text                                                                RN---------------------------------------- ------------------------------select * from  Test where deptno= '                                     2select * from test where deptno= '                                     3select * from test  where deptno=10                        &nbSp;            4select * from test  where deptno=30                                      5select * from test where deptno=20                                      6

Reference: SQL statement on efficient capture of database unbound variables

This article is from the "just out of the shell of the Birds" blog, please be sure to keep this source http://qhd2004.blog.51cto.com/629417/1866030

To capture an SQL statement for a non-binding variable