This blog post was originally released on Pythian blog

As we all know proper use of bind variables in SQL statements is a must to make transaction processing applications scalable. So how do we find the queries that don’t use bind variables and have to be parsed each time they are executed? There is number of ways, but this article is all about the most effective way I know. If you have a better one - let me know please!

I still remember the days when I used the method given us by Tom Kyte on asktom.oracle.com - and it worked perfectly! The basic idea was to capture all SQL statements from v$sqlarea, remove constants from the SQL text and then count the occurrences of the same SQL. Would I use the same method now? Unlikely. The post is antique - it was written back in year 2000, the database software has evolved since then and we’ve been given more effective means to achieve similar results.

Starting with 10gR2, two interesting columns where introduced in number of views and tables - EXACT_MATCHING_SIGNATURE and FORCE_MATCHING_SIGNATURE. I could find them in at least V$SQLAREA, V$SQL, STATS$SQL_SUMMARY, DBA_HIST_ACTIVE_SESS_HISTORY, DBA_HIST_SQLSTAT and I bet there are more. As you see, they are present all over the place - Shared Pool, ASH, AWR, Statspack (This means we have a good choice of sources to look for problematic SQLs)

Documentation says EXACT_MATCHING_SIGNATURE is “Signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings” and FORCE_MATCHING_SIGNATURE is “Signature used when the CURSOR_SHARING parameter is set to FORCE”. It also appears the signature is just another hash value calculated from SQL statement, but this time it’s a hash of normalized (removed spaces, etc.) SQL statement. Lets’s have a look!

TEST@TEST> select 1 from dual where DUMMY='B';

no rows selected

TEST@TEST> select 1 from dual where DUMMY='A';

no rows selected

TEST@TEST> select 1 from dual where DUMMY= 'A';

no rows selected

TEST@TEST> select 1 from dual where dummy=  'A';

no rows selected

TEST@TEST> col exact_matching_signature for 99999999999999999999999999
TEST@TEST> col sql_text for a50
TEST@TEST> select sql_id, exact_matching_signature, SQL_TEXT from v$sqlarea where UPPER(sql_text) like '%DUMMY%' order by UPPER(sql_text);

SQL_ID           EXACT_MATCHING_SIGNATURE SQL_TEXT
------------- --------------------------- --------------------------------------------------
6vum4z2c1rpua        13015969835749972382 select 1 from dual where dummy=  'A'
b8fj5dkrqzkrq        13015969835749972382 select 1 from dual where DUMMY= 'A'
gfrsz0vuczzag        13015969835749972382 select 1 from dual where DUMMY='A'
18k1ys5nhrrbk         1525540498770831959 select 1 from dual where DUMMY='B'
ckzhurpxb9utu         5788880286169998087 select sql_id, exact_matching_signature, SQL_TEXT
                                          from v$sqlarea where UPPER(sql_text) like '%DUMMY%
                                          ' order by UPPER(sql_text)

It’s easy to notice all SQL_IDs are different, but EXACT_MATCHING_SIGNATURE is the same for 3 of statements because of normalization.

As noted before, FORCE_MATCHING_SIGNATURE is calculated from SQL text as if CURSOR_SHARING would be set to TRUE (you don’t have to set it to TRUE to get the signature values). CURSOR_SHARING=FORCE forces SQL statements to share cursors by replacing constants with bind variables, so all statements that differ only by constants share the same cursor. Let’s have a look at FORCE_MATCHING_SIGNATURE values for the same SQLs:

TEST@TEST> col force_matching_signature for 99999999999999999999999999
TEST@TEST> select sql_id, force_matching_signature, SQL_TEXT from v$sqlarea where UPPER(sql_text) like '%DUMMY%' order by UPPER(sql_text);

SQL_ID           FORCE_MATCHING_SIGNATURE SQL_TEXT
------------- --------------------------- --------------------------------------------------
6vum4z2c1rpua        13154199455204052618 select 1 from dual where dummy=  'A'
b8fj5dkrqzkrq        13154199455204052618 select 1 from dual where DUMMY= 'A'
gfrsz0vuczzag        13154199455204052618 select 1 from dual where DUMMY='A'
18k1ys5nhrrbk        13154199455204052618 select 1 from dual where DUMMY='B'
ckzhurpxb9utu         8230152823949618578 select sql_id, exact_matching_signature, SQL_TEXT
                                          from v$sqlarea where UPPER(sql_text) like '%DUMMY%
                                          ' order by UPPER(sql_text)

2fxmcn8hvv59p         8805530522791470645 select sql_id, force_matching_signature, SQL_TEXT
                                          from v$sqlarea where UPPER(sql_text) like '%DUMMY%
                                          ' order by UPPER(sql_text)

I think you know what happens next - finding similar statements becomes as easy as querying the chosen data source (shared pool, AWR, ASH, Statspack) and grouping statements by FORCE_MATCHING_SIGNATURE. Here’s an example for finding one of the top statements not using bind variables properly:

TEST@TEST> col force_matching_signature for 99999999999999999999999999
TEST@TEST> select * from (select force_matching_signature, count(*) &quot;Count&quot; from v$sqlarea group by force_matching_signature order by 2 desc) where rownum<=3;

 FORCE_MATCHING_SIGNATURE        Count
--------------------------- ----------
 3832233612528870918             13251
 7415896326081021278              1772
 12487066559404946962             1642

TEST@TEST> set long 99999
TEST@TEST> select sql_fulltext from v$sql where force_matching_signature=7415896326081021278 and rownum=1;
SQL_FULLTEXT
--------------------------------------------------------------------------------
select account_id from accountequip where accountequip.equipment_id = 1279768275
448 and accountequip.uninstalltime is null