The Easy Way of Finding Similar SQL Statements

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!

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:

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: