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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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

select
   sql_id,
   exact_matching_signature,
   sql_text
from
   v$sqlarea
where
   UPPER(sql_text) like '%MYTABNAME%'
order by
   UPPER(sql_text);

   
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.