a-better-way-to-find-literal-sqls-in-oracle-10g/

Not sure if it is news, but here we go …

I was recently looking at hard parsing SQL statements overtaking one of my systems and needed to find literal SQLs that caused the problem.

The traditional way to look for literal SQLs in ORACLE (at least as it was for me) is to search for statements that are exactly the same in the first N symbols, something like:

As you can see, this search is not very precise – i.e. what value of N should you choose to be certain that you found ALL“duplicated” SQLs that are only different in literals ?

It turns out that ORACLE 10g introduced a couple of new columns in v$sql view (as well as some related views) that can help pinpoint literal SQLs more precisely. The two new columns are: force_matching_signature andexact_matching_signature.

The same value in exact_matching_signature column marks SQLs that ORACLE considers the same after making some cosmetic adjustments to it (removing white space, uppercasing all keywords etc). As the name implies, this is what happens when parameter cursor_sharing is set to EXACT.

Consequently, the same value in force_matching_signature (excluding 0) marks SQLs that ORACLE will consider the same when it replaces all literals with binds (that is, if cursor_sharing=FORCE).

Obviously, if we have multiple SQLs that produce the same force_matching_signature we have a strong case for literal laden SQL that needs to undergo our further scrutiny. Of course, we need to remember to filter out SQLs where force_matching_signature = exact_matching_signature as these do NOT have any literals (however, if we have many of those – this can become interesting as well – why do we have many versions of the same “non literal” SQL ?)

Anyway, here is a slightly better version of literal SQL finder in ORACLE 10g:

Special thanks to Rostyslav Polishchuck who pointed me originally in this direction.