Intermediate SQL Color Coded SQL, UNIX and Database Essays

30Apr/106

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:

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

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.

Comments (6) Trackbacks (0)
  1. So this way is more accurate. Your work is valuable!

  2. 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?

  3. 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 …

  4. 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?

  5. Thanks for this. I will actually be trying this out this week so Ill make sure to post with my experience and results.

  6. Just a small variation of your statement which prints a bit more information:

    SELECT force_matching_signature, COUNT variations, sum(EXECUTIONS) execs, max(substr(sql_text,1,200)) “SQL”
    FROM v$sql
    WHERE force_matching_signature > 0
    AND force_matching_signature exact_matching_signature
    GROUP BY force_matching_signature
    HAVING COUNT > 2
    ORDER BY 3 DESC


Leave a comment

No trackbacks yet.