SQLs not using bind variables

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>