Thursday, May 17, 2012

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

8 comments:

Anonymous said...

Karen, Great article. It saved my day. Do you know of any other way to force the sql profile to be shared other than force_matching?

Anonymous said...

Karen, Great article. It saved my day. Do you know of any other way to force the sql profile to be shared other than force_matching?

Karen said...

No, I don't...not if what you want to share is SQL with changing literal text. Force matching is currently the only way that I know of to share that single SQL profile.

Michael Fontana said...

This is an excellent article. I wish it would apply to my case, but I have the exact opposite problem (sigh)!

All the SQL generated by my application is the same, and uses bind variables, but the bind variable name itself changes each time!

This makes the use of sql profiles irrelevent, unfortunately.

Michael Fontana said...

This is an excellent article. I wish it would apply to my case, but I have the exact opposite problem (sigh)!

All the SQL generated by my application is the same, and uses bind variables, but the bind variable name itself changes each time!

This makes the use of sql profiles irrelevent, unfortunately.

Unknown said...

Hi Karen,

Great post, i have been playing around with Profiles lately as the app we use dose not use bind variables and we do not want to turn cursor_sharing parameter on...so i figured i can use SQL Profiles to get this done...one question do i have is...lets say i create a SQL Profile and the app generates its SQL with literal values..... and profile is used...as its using the profile...would that be consider soft parse or hard parse???

we are seeing a lot of hard parsing issue on our system and i wanted to see if i can elimiate that using SQL Profile...

Thanks

Karen said...

Abdul, The SQL Profile would be used during a hard parse to establish the plan for that particular SQL_ID. From that point forward, that same SQL_ID would soft parse until aged out of the shared pool. But since the SQL Profile would actually be applicable to any SQL text that differs only by literals, I think you'd still get the hard parse of the various literal SQL statements but as they hard parsed, they'd all end up with the same plan via the SQL Profile. So, the bottom-line is that I don't think the use of profiles will eliminate the hard parsing. I'd advise doing a few simple tests to verify. Just execute a few SQL statements that differ only by literals and check to see if they hard parse....then you'll know for sure!

test said...

Hi Karen,

You are absolutely correct, i ran a quick test by creating the profile. The profile gets picked up each time but anytime there is a new literal value it dose a Hard Parse...

my situation is the SQL runs pretty fast(about less than 5 secs for a dataware house system) but because of the literal values it dose a lot of hard parse...if i look at FORCE_MATCHING_SIGNATURE in v$sql for this SQL there are 100's of them and i thought maybe i could use SQL profile and avoid the hard parsing, but guess not...

any suggestions on how to fix the parsing issue and unfortunately the code cannot be changed for this app and we do not want to turn on cursor_sharing parameter at system wide level as i mentioned this is a DW system and might have some bad effect on some star transformation queries.