---
title: How to Find Similar SQL Statements, the Easy Way.
description: The proper use of bind variables in SQL statements is a must. How do we find queries that don't use bind variables and are parsed each time they are executed?
---

[Blog | Pythian ](https://www.pythian.com/blog)

# [How to Find Similar SQL Statements, the Easy Way.](https://www.pythian.com/blog/the-easy-way-of-finding-similar-sql-statements)

 Written by [Maris Elsins](https://www.pythian.com/blog/author/maris-elsins) | Jan 24, 2013 5:00:00 AM

 

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! 

## The Evolution of SQL Identification Methods

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.

### Modern Signatures: EXACT vs. FORCE

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!

```
[sourcecode language="sql" wraplines="false"] 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) [/sourcecode]
```

## Analyzing Literal Values and Signatures

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:

```
[sourcecode language="sql" wraplines="true"] 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) [/sourcecode]
```

### Practical Implementation: Finding the Top Offenders

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:

```
[sourcecode language="sql" wraplines="true"] TEST@TEST> col force_matching_signature for 99999999999999999999999999 TEST@TEST> 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 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 [/sourcecode]
```

## Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?

 

 

[View full post](https://www.pythian.com/blog/the-easy-way-of-finding-similar-sql-statements)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Maris Elsins"
  },
  "dateModified" : "2026-03-08T23:45:36.874Z",
  "datePublished" : "2013-01-24T05:00:00Z",
  "headline" : "The Easy Way of Finding Similar SQL Statements",
  "image" : {
    "@type" : "ImageObject",
    "height" : 60,
    "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
    "width" : 60
  },
  "mainEntityOfPage" : "https://www.pythian.com/blog/the-easy-way-of-finding-similar-sql-statements",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60,
      "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
      "width" : 60
    },
    "name" : "Pythian Blog"
  }
}
```