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!
[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]
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]
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]
Ready to optimize your Oracle Database for the future?