Force Matching Signature

It amazes and amuses me how many times I remember something that I used to know but forgot due to lack of use. I'm not sure if it's because I'm just getting older or what. :)

I had just created a SQL Profile and set the force_matching option to yes so that any SQL that had the same signature would be covered by this profile. If you need a reminder, what the force matching signature does is to treat literals in the SQL like binds (think along the lines of cursor_sharing = force). When force matching is in place, all the literals are treated as binds and the same signature is generated for any SQL that differs only by their literals (white space and case are handled already).

In my case, the force matching signature option works great when I'm working with SQL that has been generated from a reporting tool (like OBIEE) and is formulated with all literals. So, I had this gnarly OBIEE generated SQL statement that was running for over an hour that I needed to "fix". I could see what needed to happen to get a better plan, but I wasn't able to make changes to the model to get it to generate the SQL I wanted. However, I was able to produce a better plan by rewriting the SQL. Once I rewrote it, I decided to create a SQL Profile for the "bad" SQL and attach the "good" plan from my rewritten version of the query to it. I got the profile created and ran a test and everything looked good. The profile kicked in and the SQL used my "good" plan and it took just a few seconds to complete. Cool. All done, right? Well...not really.

A bit later, the same query was executed and instead of using the profile, it didn't and took over an hour again. Sigh...

What happened? Well, the SQL was the same except for a change to a literal. So, it's not really the same, but with force matching turned on, it should've been OK, right? That would've been true but, as it turned out, there was actually a single bind variable in the SQL statement in addition to all the literals. And, it was the presence of that single bind that caused the force matching signatures to be different and therefore my profile was ignored.

But why did the single bind variable mess the force matching signature up? The bind would be the same for every SQL statement, so shouldn't force matching simply convert the literals to binds and I'd be good to go? Something was tickling at the back of my brain and after a brief check of the documentation about force matching, I found this:

By setting force_match to TRUE, the SQL profile additionally targets all SQL statements that have the same text after normalizing literal values to bind variables. This setting may be useful for applications that use only literal values because it allows SQL with text differing only in its literal values to share a SQL profile. If both literal values and bind variables are in the SQL text, or if force_match is set to FALSE (default), then literal values are not normalized.

Ding, ding, ding. There it was. If both literals and binds are present, force matching won't kick in. Just to prove it to my brain, and hopefully make it stick a little better for next time, I did this simple test.

1. Run two queries that differ only by a single literal.
2. Verify that the force_matching_signature for both is the same.
3. Add a predicate that uses a bind variable to each query.
4. Execute each and check the signatures again.

SQL>variable v1 varchar2(10)
SQL>exec :v1 := 'Sunday';

PL/SQL procedure successfully completed.

SQL>
SQL>select /* kmfmtst00 */ count(*) ct from km1 where mcal_year = 2011 ;

                  CT
--------------------
                 365

SQL>select /* kmfmtst00 */ count(*) ct from km1 where mcal_year = 2012 ;

                  CT
--------------------
                 366

SQL>
SQL>select /* getfm */ sql_id, plan_hash_value, force_matching_signature, substr(sql_text,1,200) sql_text
  2  from v$sql
  3  where upper(sql_text) like '%KMFMTST00%'
  4  and sql_text not like '%/* getfm */%' ;

SQL_ID             PLAN_HASH_VALUE FORCE_MATCHING_SIGNATURE SQL_TEXT
------------- -------------------- ------------------------ -------------------------------------------------------------------------------
6sz5sqg1yu2u7           3996576519      9139782190997132164 select /* kmfmtst00 */ count(*) ct from km1 where mcal_year = 2011
88bgq57sjbtkt           3996576519      9139782190997132164 select /* kmfmtst00 */ count(*) ct from km1 where mcal_year = 2012

SQL>
SQL>select /* kmfmtst00 */ count(*) ct from km1 where mcal_year = 2011 and mcal_day_name = :v1 ;

                  CT
--------------------
                  52

SQL>select /* kmfmtst00 */ count(*) ct from km1 where mcal_year = 2012 and mcal_day_name = :v1 ;

                  CT
--------------------
                  53

SQL>
SQL>select /* getfm */ sql_id, plan_hash_value, force_matching_signature, substr(sql_text,1,200) sql_text
  2  from v$sql
  3  where upper(sql_text) like '%KMFMTST00%'
  4  and sql_text not like '%/* getfm */%' ;

SQL_ID             PLAN_HASH_VALUE FORCE_MATCHING_SIGNATURE SQL_TEXT
------------- -------------------- ------------------------ -------------------------------------------------------------------------------
6sz5sqg1yu2u7           3996576519      9139782190997132164 select /* kmfmtst00 */ count(*) ct from km1 where mcal_year = 2011
88bgq57sjbtkt           3996576519      9139782190997132164 select /* kmfmtst00 */ count(*) ct from km1 where mcal_year = 2012
48rxh2r545xqy           3996576519      5839486434578375421 select /* kmfmtst00 */ count(*) ct from km1 where mcal_year = 2011 and mcal_day_name = :v1
6q610fykrr4d3           3996576519      8791659410855071518 select /* kmfmtst00 */ count(*) ct from km1 where mcal_year = 2012 and mcal_day_name = :v1

As you can see, the first two statements that differed by just a single literal shared the same signature. But, for the two statements that had the bind added, the signatures were different. That's exactly what happened to me for the SQL profile I had created. When I ran my test after creating it, I didn't check the query execution for different literal values so I didn't catch this.

So, in the end, I remembered something I forgot about force matching *and* I got a good mental slap about thorough testing. I assumed I knew how something was going to work and didn't use due diligence to test and confirm. Want to bet that next time I'll remember? I can't be 100% certain, but I'd say the odds are in favor that I will.  :)