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:
-
When Cursor_sharing=force ==>
force_matching_signature applies
-
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!
|
|
|
|
