We all know that proper use of bind
variables in SQL statements is a must to make the transaction processing
application scalable. So how to find the queries that don’t use bind variable
and are parsed each time they are executed? There are number of ways, but this
article shows an efficient way.
Starting with Oracle 10gR2, two interesting columns – FORCE_MATCHING_SIGNATURE
and EXACT_MATCHING_SIGNATURE – were added in a number of tables and views. We
could see them in V$SQL, V$SQLAREA, DBA_HIST_SQLSTAT and DBA_HIST_ACTIVE_SESS_HISTORY
data dictionaries. As you see they are
present all over the place – shared pool, ASH, AWR and Statspack. That means we
have good choice of source to look for problematic sql’s.
EXACT_MATCHING_SIGNATURE means, a signature is calculated on
the normalized sql text. The normalization includes the removal of white space
and uppercasing of all the non-literal strings.
FORCE_MATCHING_SIGNATURE means, a signature used as if CURSOR_SHARING
set to FORCE. It seems to be another hash value calculated from SQL statement.
demo@ORA11G>
select * from dual where dummy='A';
no rows
selected
demo@ORA11G>
select * from dual where dummy= 'A';
no rows
selected
demo@ORA11G>
select * from dual where DUMMY='A';
no rows
selected
demo@ORA11G>
select * from dual where dummy='B';
no rows
selected
demo@ORA11G>
column sql_text format a40
demo@ORA11G>
col exact_matching_signature format 999999999999999999999
demo@ORA11G>
select sql_id,exact_matching_signature,sql_text
2 from
v$sql
3
where lower(sql_text) like 'select * from dual where dummy%';
SQL_ID EXACT_MATCHING_SIGNATURE SQL_TEXT
-------------
------------------------ ----------------------------------------
2sx3rsjythh1y 4901325341701120494 select * from dual
where dummy='B'
4bushd7kct5t7 17152174882085893964 select * from dual
where DUMMY='A'
0kuuh73za9ka5 17152174882085893964 select * from dual
where dummy='A'
1husj2y65zyyd 17152174882085893964 select * from dual
where dummy= 'A'
demo@ORA11G>
It is easy to notice that all the sql_id’s are different. But the EXACT_MATCHING_SIGNATURE
is same for all three statement because of normalization.
As noted before FORCE_MATCHING_SIGNATURE is calculated from sql text as
if CURSOR_SHARING set to FORCE – that forces sql statements as to share cursors
by replacing constants with bind variables, so all statements that differ only
by the constants share the same cursor. Let’s
have a look at the FORCE_MATCHING_SIGNATURE value for the same sql’s.
demo@ORA11G>
col force_matching_signature format 999999999999999999999
demo@ORA11G>
select sql_id,force_matching_signature,sql_text
2 from
v$sql
3
where lower(sql_text) like 'select * from dual where dummy%';
SQL_ID FORCE_MATCHING_SIGNATURE SQL_TEXT
-------------
------------------------ ----------------------------------------
2sx3rsjythh1y 10668153635715970930 select * from dual
where dummy='B'
4bushd7kct5t7 10668153635715970930 select * from dual
where DUMMY='A'
0kuuh73za9ka5 10668153635715970930 select * from dual
where dummy='A'
1husj2y65zyyd 10668153635715970930 select * from dual
where dummy= 'A'
demo@ORA11G>
Now finding similar statements becomes as easy as querying the chosen
data source (Shared pool, AWR, ASH, Statspack) and grouping the statements by
FORCE_MATCHING_SIGNATURE value.
Something like this:
demo@ORA11G>
select *
2 from
(
3
select force_matching_signature,count(*)
4 from
v$sql
5
where child_number = 0
6
group by force_matching_signature
7
having count(*) > 1
8
order by 2 desc
9
)
10
where rownum <= 5 ;
FORCE_MATCHING_SIGNATURE COUNT(*)
------------------------
----------
0 89
10668153635715970930 4
562954948158618121 3
6261972462962078540 3
577209413380651322 2
demo@ORA11G>
select sql_id,child_number,sql_text
2 from
v$sql
3
where force_matching_signature ='562954948158618121';
SQL_ID CHILD_NUMBER SQL_TEXT
-------------
------------ ----------------------------------------
5qz5gdb6n09k4 0 select * from emp where deptno = 30
a4y5kdgz0nw69 0 select * from emp where deptno = 10
84fmz2fg9mdgh 0 select * from emp where deptno = 20
demo@ORA11G>
