The Easy Way of Finding Similar SQL Statements

As we all know, the 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 are parsed each time they are executed? There is a 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 a 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, and 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 a “signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.” It also says that FORCE_MATCHING_SIGNATURE is a “signature used when the CURSOR_SHARING parameter is set to FORCE“. The signature seems to be just another hash value calculated from SQL statement, but this time, it’s a hash of normalized (removed spaces, etc.) SQL statement. Let’s have a look!

[email protected]> select 1 from dual where DUMMY=’B’;
no rows selected
[email protected]> select 1 from dual where DUMMY=’A’;
no rows selected
[email protected]> select 1 from dual where DUMMY= ‘A’;
no rows selected
[email protected]> select 1 from dual where dummy=  ‘A’;
no rows selected
[email protected]> col exact_matching_signature for 99999999999999999999999999
[email protected]> col sql_text for a50
[email protected]> 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 that 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:

[email protected]> col force_matching_signature for 99999999999999999999999999
[email protected]> 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:

[email protected]> col force_matching_signature for 99999999999999999999999999
[email protected]> select * from (select force_matching_signature, count(*) "Count" 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
[email protected]> set long 99999
[email protected]> 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