EXACT_MATCHING_SIGNATURE and FORCE_MATCHING_SIGNATURE Explained

Oracle June 18, 2011

Let us start with the definition of the columns. As per Oracle Documentation EXACT_MATCHING_SIGNATURE is a  signature calculated on the normalized SQL text. The normalization includes the removal of white space and the upper casing of all non-literal strings. This signature is used when  CURSOR_SHARING parameter is set to EXACT where as FORCE_MATCHING_SIGNATURE is the signature that Oracle calculates when the CURSOR_SHARING parameter is set to FORCE.

Now lets discuss some  of the values that these columns can have and their significance.

Case-1: When  EXACT_MATCHING_SIGNATURE  and FORCE_MATCHING_SIGNATURE=0.  This is the scenario when the command type is PL/SQL package, INSERT,ALTER INDEX,LOCK, SET ROLE and SELECTS/UPDATES from actual SYS  table($, # and  not views)  or internal Oracle statements. There may be more commands but in general , If Oracle thinks that SQL cannot be reused, then values are marked as 0.

Example-1: In the example below, I am  inserting some dummy values  into test table and then check the column values for EXACT_MATCHING_SIGNATURE and FORCE_MATCHING_SIGNATURE

SQL>create table test (col1 number, col2 number);

Table created.

SQL>insert into test values (1,1);

1 row created.

SQL>insert into test values (2,2);

1 row created.

SQL>insert into test values (3,3);

1 row created.

SQL>commit;

SQL>select sql_id, sql_text from v$sql where sql_text like ‘insert%test%’;

SQL_ID         SQL_TEXT
————-  ————————————————————————————-
1mqr33wjhx7ym     insert into test values (3,3)
9263ja18wuf3x      insert into test values (1,1)
0mjxttvc7zcrg          insert into test values (2,2)

The query below checks  the column values for EXACT_MATCHING_SIGNATURE and FORCE_MATCHING_SIGNATURE;  As discussed earlier they should be 0.

SQL>SELECT sql_id, exact_matching_signature, force_matching_signature from v$sql where sql_id=’1mqr33wjhx7ym’;

SQL_ID         EXACT_MATCHING_SIGNATURE     FORCE_MATCHING_SIGNATURE

————–  ————————-    ——————————
1mqr33wjhx7ym     0                 0

Case-2: when EXACT_MATCHING_SIGNATURE=FORCE_MATCHING_SIGNATURE. This is the scenario when bind variables are used.

Example-2:The goal of this example is to prove that  EXACT_MATCHING_SIGNATURE is equal to  FORCE_MATCHING_SIGNATURE when bind variables are used. In order to achieve that, I  have defined variable VAR_OBJECT_ID and then  execute  the select statement below multiple times with different values VAR_OBJECT_ID  from multiple sessions.

SQL>column EXACT_MATCHING_SIGNATURE format 99999999999999999999999999999999999999999999999
SQL>column FORCE_MATCHING_SIGNATURE format 99999999999999999999999999999999999999999999999

SQL>VARIABLE VAR_OBJECT_ID NUMBER;
SQL>EXEC :VAR_OBJECT_ID:=345339;

SQL>SELECT /* With Bind Value */ OBJECT_NAME FROM MYOBJECTS WHERE OBJECT_ID=:VAR_OBJECT_ID;

OBJECT_NAME
——————————————————————————————————————————–
DBA_HIST_LIBRARYCACHE

SQL>EXEC :VAR_OBJECT_ID:=345348

PL/SQL procedure successfully completed.

SQL>
SQL>SELECT /* With Bind Value */ OBJECT_NAME FROM MYOBJECTS WHERE OBJECT_ID=:VAR_OBJECT_ID;

OBJECT_NAME
——————————————————————————–
DBA_HIST_SGA

SQL>EXEC :VAR_OBJECT_ID:=345342

PL/SQL procedure successfully completed.

SQL>SELECT /* With Bind Value */ OBJECT_NAME FROM MYOBJECTS WHERE OBJECT_ID=:VAR_OBJECT_ID;

OBJECT_NAME
——————————————————————————–
DBA_HIST_DB_CACHE_ADVICE

The query below checks  the column values for EXACT_MATCHING_SIGNATURE and FORCE_MATCHING_SIGNATURE;  As discussed earlier they should match because of using bind variables

SQL>SELECT sql_id, executions, exact_matching_signature, force_matching_signature from v$sql where sql_id=’anh9f1x3qfsc7′;

SQL_ID        EXECUTIONS                          EXACT_MATCHING_SIGNATURE                          FORCE_MATCHING_SIGNATURE
————- ———- ————————————————- ————————————————-
anh9f1x3qfsc7          3                               5933628278648932257                               5933628278648932257

Case-3: when EXACT_MATCHING_SIGNATURE<>FORCE_MATCHING_SIGNATURE. This is the scenario  when different literals are used in  the SQL statement.  SQL statement will be shared if CURSOR_SHARING is set to FORCE.

Example-3: The goal of this example is to prove that  EXACT_MATCHING_SIGNATURE is NOT equal to FORCE_MATCHING_SIGNATURE when bind variables are NOT  used. In order to achieve that, I am  executing  the select statement below multiple times with different literal values.

Please note that I have NOT set CURSOR_SHARING to FORCE.

SQL>SELECT /* Without Bind Value */ OBJECT_NAME FROM MYOBJECTS WHERE OBJECT_ID=345335;

OBJECT_NAME
——————————————————————————–
DBA_HIST_LATCH_PARENT

SQL>SELECT /* Without Bind Value */ OBJECT_NAME FROM MYOBJECTS WHERE OBJECT_ID=345338;

OBJECT_NAME
——————————————————————————–
DBA_HIST_LATCH_MISSES_SUMMARY

SQL>SELECT /* Without Bind Value */ OBJECT_NAME FROM MYOBJECTS WHERE OBJECT_ID=345339;

OBJECT_NAME
——————————————————————————–
DBA_HIST_LIBRARYCACHE

SQL>SELECT /* Without Bind Value */ OBJECT_NAME FROM MYOBJECTS WHERE OBJECT_ID=345340;

OBJECT_NAME
——————————————————————————–
DBA_HIST_LIBRARYCACHE

The query below checks  the column values for EXACT_MATCHING_SIGNATURE and FORCE_MATCHING_SIGNATURE;  As discussed earlier they should NOT match because of using literals.

SQL>SELECT SQL_ID, SQL_TEXT, EXACT_MATCHING_SIGNATURE,FORCE_MATCHING_SIGNATURE FROM V$SQL WHERE SQL_TEXT LIKE ‘%Without Bind Value%’;

SQL_ID        SQL_TEXT                                            EXACT_MATCHING_SIGNATURE          FORCE_MATCHING_SIGNATURE
————- —————————————- ———————————– ———————————
3mh63hsg30ry0 SELECT /* Without Bind Value */                         15981458635320564018              14612835381060113580
OBJECT_NAME FROM MYOBJECTS WHERE
OBJECT_ID=345339

bykcv3pwz1bqq SELECT /* Without Bind Value */                          9089800361790826758              14612835381060113580
OBJECT_NAME FROM MYOBJECTS WHERE
OBJECT_ID=345338

fb40628mbxu49 SELECT /* Without Bind Value */                           550928455875090853              14612835381060113580
OBJECT_NAME FROM MYOBJECTS WHERE
OBJECT_ID=345340

dwjac8zk268r8 SELECT /* Without Bind Value */                          1750778464710645288              14612835381060113580
OBJECT_NAME FROM MYOBJECTS WHERE
OBJECT_ID=345335

SQL>SELECT SQL_ID FROM V$SQL WHERE FORCE_MATCHING_SIGNATURE=14612835381060113580;

SQL_ID
————-
3mh63hsg30ry0
bykcv3pwz1bqq
fb40628mbxu49
dwjac8zk268r8

How useful are these column values?
1. To find SQL statements that are identical but do not using bind variables. Prior to 10g , there was no accurate way getting  this result set.  From 10 onwars , run the following query.

SQL>column sql_text format a50
SQL>set linesize 150 pagesize 2000

SQL> set long 3000

SQL>column FORCE_MATCHING_SIGNATURE format 99999999999999999999999999999999999999999
SELECT SQL_ID , SQL_TEXT, force_matching_signature FROM V$SQL WHERE force_matching_signature IN (SELECT force_matching_signature
FROM v$sql
WHERE force_matching_signature <> 0
AND force_matching_signature <> exact_matching_signature
GROUP BY force_matching_signature
HAVING COUNT(*) > 1)
ORDER BY 3

2.  This column is also very useful to check if  SQL statements are being reused when CURSOR_SHARING is set to FORCE.

Tagged: , , , , , , , , , ,

%d bloggers like this: