Oracle Scratchpad

January 12, 2010

force_match

Filed under: Infrastructure,Performance — Jonathan Lewis @ 7:19 pm GMT Jan 12,2010

If you have looked at SQL Profiles (see for example Kerry Osborne’s blog) then you may have come across the force_match option for enabling or importing a SQL profile. I received an email recently asking a few questions about this feature.

I have an Oracle 10.2.0.4 situation where I am testing using DBMS_SQLTUNE.IMPORT_SQL_PROFILE to effectively create what I might call “selective cursor sharing equals FORCE” for a given SQL (select) query(ies). The instance has cursor_sharing set to EXACT, which, I am not inclined to modify.

After running the aforementioned procedure and verifying that it is correctly set up by querying DBA_SQL_PROFILES, when I try submitting additional similar queries (with only the literal value changed), I see in V$SQL that the sql_text for the additional queries does not use a system generated bind variable, rather, it uses the exact literal value as when submitted the queries. I had expected it to behave as if cursor_sharing were set to FORCE and replace the literals with a system generated bind variable. The additional queries do, however, have the same value for the FORCE_MATCHING_SIGNATURE attribute of V$SQL.

Given the above, I need to know if I am doing everything properly.

Should Oracle be replacing the new literal values with a system generated bind variable?
Does the fact that these additional similar queries have the same FORCE_MATCHING_SIGNATURE value tell me this technique is in fact working?
Does this technique eliminate or greatly reduce hard parsing for the affected SQL statements?

I don’t usually reply to private requests for assistance with Oracle – it’s not an efficient use of my time. But occasionally a question appears that is sufficiently generic that it’s worth answering in public. In this case it’s worth making the point that force_match and cursor_sharing = force are completely unrelated.

So, in order:

1. Should Oracle be replacing the new literal values with a system generated bind variable?

No. force_match simply means the profile (i.e. set of hints) will be applied to the SQL even though the literals do not match. The optimizer will then optimize the statement in the light of those hints.

2. Does the fact that these additional similar queries have the same FORCE_MATCHING_SIGNATURE value tell me this technique is in fact working?

Yes, probably – though technically it’s indicating that the matching should have applied to these statement – but you could use dbms_xplan.display_cursor() to check whether the notes (in column xml_other of v$sql_plan) includes a comment like: ‘SQL_PROFILE XXXXXX used for this query’ if you wanted to be sure.

3. Does this technique eliminate or greatly reduce hard parsing for the affected SQL statements?

It probably reduces the work done during hard parsing if your constructed profile include hints like leading(), index() and so on – but the optimizer still has to hard parse the “profile-hinted” SQL. Any saving comes from the number of execution paths the optimizer avoids considering because the hints block them.  Note: a SQL profile generated by the dbms_sqltune package will only include opt_estimate() hints, which change the arithemetic but don’t limit choice of execution paths, so a “genuine” profile could actually mean the optimizer does more work optimising a statement that it would without the hints.

8 Comments »

  1. Jonathan
    Everytime I read your post, my respect to you and oracle increased.

    Thanks,

    Fudong

    Comment by fudongli — January 13, 2010 @ 5:23 pm GMT Jan 13,2010 | Reply

  2. Hi, Jonathan,

    Here is some material for thought. The queries below have different FORCE_MATCHING_SIGNATURES.
    Would not you agree that this is plainly wrong?

    select 2+2 from dual
    /
    select 2+2 c1 from dual
    /
    select 2+2 from dual /**/
    /
    select 2+2 /**/ from dual
    /
    The real problem I am having is application layer generating non-deterministic aliases for columns (that’s Hybernate I’m talking about). As the result, there is no way to match the queries – for comparison of tests, for instance.

    I wonder what your opinion is on this matter

    Comment by Alex Khrissanov — January 29, 2010 @ 1:07 am GMT Jan 29,2010 | Reply

    • Alex,
      There are two ways to interpret your question.

      a) Is it wrong because it’s not following specification – and the answer to that one is no; the definition is that it will match statements that differ only in their constants.

      b) Is it wrong because it would be nice if it managed to see that these statements were the same. In that case we could say yes, it would be nice – but I think it might be quite difficult to achieve perfectly safely.

      Comment by Jonathan Lewis — January 29, 2010 @ 6:22 pm GMT Jan 29,2010 | Reply

      • Jonathan,
        Are you saying that removing comments is difficult to achieve? I would disagree on that point.
        Making queries with different column aliases match is a different matter. Oracle does compile the code – it would not be very hard for them to come up with something, I’m sure.

        BTW: Not only Oracle replaces literal constants with parameters, it considers its type, it seems, for the ‘force_matching_signature’.
        Of course, the question is: what were the requirements at the time when people designed calculation of that ‘force_matching_signature’. So, in a way it’s a deficiency in the requirements, not a bug in the code.

        I was simply looking for ideas when I stumbled upon your page talking of somewhat related matter. I was hoping that you might have run into similar problem with non-matching semantically the same queries.
        Oh well. I guess I better go and fill an enhancement request on Metalink then. Does not help but gives a false feeling of accomplishing something.

        Thanks for your time!

        Comment by Alex Khrissanov — January 29, 2010 @ 10:35 pm GMT Jan 29,2010 | Reply

  3. But do you WANT it to remove comments which may be important optimizer hints ?

    Comment by Gary — January 30, 2010 @ 1:27 am GMT Jan 30,2010 | Reply

  4. […] 3-How does force_match work with SQL_PROFILEs? Jonathan Lewis-force_match […]

    Pingback by Blogroll Report 08/01/2009 – 15/01/2010 « Coskan’s Approach to Oracle — February 2, 2010 @ 7:03 pm GMT Feb 2,2010 | Reply

  5. hi jonathanlewis,

    you mentioned below
    “It probably reduces the work done during hard parsing if your constructed profile include hints like leading(), index() and so on – but the optimizer still has to hard parse the “profile-hinted” SQL. Any saving comes from the number of execution paths the optimizer avoids considering because the hints block them.”

    if lets say its a 2 table join and we are using SQL Profile(just like you mentioned with index hints and all that) on it…how much of the reduction percent wise are we talking here? Are we talking about 40%-50% reduction during the hard parse process or something less?

    Comment by max — November 23, 2013 @ 4:27 am GMT Nov 23,2013 | Reply

    • Max,

      There’s no point in asking that question, and no way that anyone suggest any percentage that might be sensible.

      The only reason to review the option is when you see that a particular statement is spending a significant fraction of its time on parsing and you ask yourself how much effort it would take to experiment with a profile hack, and how much benefit it would give.

      Comment by Jonathan Lewis — November 29, 2013 @ 10:00 am GMT Nov 29,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,521 other followers