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
Answer: According to Oracle, these dictionary
columns can be used to determine if a specific SQL statement is
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
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.
Internally, the force_matching_signature
column derives from
column of the x$kglcursor_child
These signature parameters can be used to indicate the status of the
When Cursor_sharing=force ==>
When Cursor_sharing=exact ==>
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:
variables are being used by the SQL statement.
exact_matching_signature<>force_matching_signature, then the
SQL will be reentrant (reusable), provided that
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
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:
exact_matching_signature for 99999999999999999999999999
col sql_text for a50
UPPER(sql_text) like '%MYTABNAME%'
Oracle Training from Don Burleson
The best on site
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!