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.
TwelveC 
Recent Comments