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:
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 and exact_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:
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.
August 15th, 2010 - 00:20
So this way is more accurate. Your work is valuable!
June 16th, 2011 - 11:56
Can you explain in which scenarios are the values of force_matching_signature and exact_matching_signature set to ’0′? I see some sqls in v$sql where these columns have a value of 0. What makes these to be calculated as 0?
June 16th, 2011 - 14:31
That’s a very good question and the honest answer is: I don’t know …
From the look of the statements, most of them (at least in my databases), appear to be the ones that are SYS-owned and access dictionary tables …
But I also see some “perfectly good” statements by regular users that also have their signatures as 0 …
Still a mystery …
June 16th, 2011 - 15:51
Thanks. Doesn’t look like Oracle has any documentation on this either. I created a sql tuning set to load into the dba_sql_plan_baselines and noticed that the ones that had force_matching_signature set to 0 didnt make it into the dba_sql_plan_baseline table. I can’t understand why these didn’t make it. Do you have any explanation for this?
July 10th, 2011 - 11:19
Thanks for this. I will actually be trying this out this week so Ill make sure to post with my experience and results.