oracle literal 传参数,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 SQLstatements 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:

SELECT substr(sql_text, 1, 80), COUNT(1)

FROM v$sql

GROUP BY substr(sql_text, 1, 80)

HAVING COUNT(1) > 10

ORDER BY 2

/

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 ORACLEconsiders 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 ORACLEwill 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 SQLthat 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 NOThave 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 SQLfinder in ORACLE 10g:

SELECT force_matching_signature, COUNT(1)

FROM v$sql

WHERE force_matching_signature > 0

AND force_matching_signature <> exact_matching_signature

GROUP BY force_matching_signature

HAVING COUNT(1) > 10

ORDER BY 2

/

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