Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







exact_matching_signature & force_matching_signature Tips

Oracle Database Tips by Donald BurlesonAugust 16,  2015

Question:  I saw a new column in v$sql called force_matching_signature and exact_matching_signature.  What is force_matching_signature and exact_matching_signature used for?  These columns are not described in the 11g documentation.

Answer:  According to Oracle, these dictionary columns can be used to determine if a specific SQL statement is reusable.  The force_matching_signature is a numeric dictionary column that was added in 10g release 2 to indicate a numeric signature that is used for SQL where cursor_sharing=force.  There is also a exact_matching_signature column used to indicate where cursor_sharing=exact.

The force_matching_signature column appears in these dictionary views for AWR view dba_hist_sqlstat, the ASH view dba_hist_active_sess_history and these DBA and v$ views.

  • v$sql
  • v$sqlarea
  • dba_sqlset_binds
  • dba_sqlset_statements
  • dbs_sqlarea_plan_hash
  • dba_sqlset_plans
Internally, the force_matching_signature column derives from the kglobt49 column of the x$kglcursor_child table.

These signature parameters can be used to indicate the status of the cursor_sharing parameter:

  1. When Cursor_sharing=force ==> force_matching_signature applies

  2. When Cursor_sharing=exact ==> exact_matching_signature applies

The force_matching_signature and exact_matching_signature columns correspond to a specific SQL statement within the library cache.  You can interrogate the values as follows:

  • When exact_matching_signature=force_matching_signature, bind variables are being used by the SQL statement.

  • When exact_matching_signature<>force_matching_signature, then the SQL will be reentrant (reusable), provided that cursor_sharing=force.

  • When exact_matching_signature=0 and force_matching_signature=0 are set to zero, the SQL is non-reentrant (no bind variables).

In sum, the exact_matching_signature and force_matching_signature can be compared in conjunction with the values for the cursor_sharing parameter to determine whether a SQL statement is reentrant.

You can also use this column  to find similar SQL statements from within the Oracle library cache.

Jonathan Lewis notes this in force_matching_signature:

exact_matching_signature is a hash value for what the statement would look like if redundant white space were removed.
force_matching_signature is a hash value for what the statement would look like if literals used for column values were replaced by system-generated bind variables and all the redundant white space were removed.  The force_matching_signature is there whether or not cursor_sharing has been set to force (or similar).
There are a number of simple variations you can create to demonstrate how the exact and force matching signatures behave (e.g. the same statement with different literals, the same statement with the same literals but differences in whitespace, etc.)
If cursor_sharing = force then a statement will (usually) be rewritten so that all column values are replaced by bind variables - which means that it's exact_matching_signature will equal its force_matching_signature.

Using force_matching_signature to find similar SQL

You can use force_matching_signature to find similar SQL in ASQL*Plus, like this example:

col exact_matching_signature for 99999999999999999999999999
col sql_text for a50

   UPPER(sql_text) like '%MYTABNAME%'
order by

Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.