The Easy Way of Finding Similar SQL Statements

Posted in: Technical Track

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!

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 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:

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(*) "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
email

Interested in working with Maris? Schedule a tech call.

About the Author

Maris Elsins is an experienced Oracle Applications DBA currently working as Lead Database Consultant at The Pythian Group. His main areas of expertise are troubleshooting and performance tuning of Oracle Database and e-Business Suite systems. He is a blogger and a frequent speaker at Oracle related conferences such as UKOUG, Collaborate, Oracle OpenWorld, HotSos, and others. Maris is an Oracle ACE, an Oracle Certified Master, and a co-author of “Practical Oracle Database Appliance” (Apress, 2014). He's also a member of the board at Latvian Oracle User Group.

4 Comments. Leave new

The Easy Way of Finding Similar SQL Statements « Just Another Day of Apps DBA's Life
January 24, 2013 4:48 pm

[…] This post originally appeared at the Pythian blog. If you ‘d like to leave a comment, please comment on the original post here. […]

Reply
How to use SQL Strings transformed to resemble Cursor_sharing
January 30, 2013 8:54 am

[…] this Signature and it is also stored in various internal tables (See Maris Elsins’ blog post here for more details about the SQL […]

Reply

I like to get the past history on a specific time if the count of similar SQLs spiked. Those SQLs that can be candidate to have BIND variable but forced to parse more often as their signatures were different.
below sqls does give from v$SQLAREA but lack of EXACT_MATCHING_SIGNATURE in history table i am unable to write my own. Any AWR tables or HIST table having SQLid and EXACT_MATCHING_SIGNATURE .

select FORCE_MATCHING_SIGNATURE, count(*)
from v$sql
where EXACT_MATCHING_SIGNATURE != FORCE_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(*) > 10
order by count(*) desc

Reply

Hi Nalla,

it is possible, and I have already written about it.
See my blog post “Do AWR Reports Show the Whole Picture?” here: http://www.pythian.com/blog/do-awr-reports-show-the-whole-picture/
If you scroll down, the link to the scripts is in the comments and you want to take a look at the script named “awr_top_by_fms_noinst.sql”

Maris Elsins

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *