Wednesday, June 6, 2012
How do you want it? Fast, Cheap, Great, Free?
Found a link to this on Pinterest. This was created in reference to graphic design but it struck me as being very appropriate for so much I see in my field of work. I think some adjustments could definitely be made to make it more specific to my field, but it struck me enough that I had to share!
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:
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. :)
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 settingDing, 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.force_matchtoTRUE, 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 ifforce_matchis set toFALSE(default), then literal values are not normalized.
- Run two queries that differ only by a single literal.
- Verify that the force_matching_signature for both is the same.
- Add a predicate that uses a bind variable to each query.
- 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. :)
Tuesday, May 8, 2012
All Things Oracle Webinar
Thanks to everyone who attended my webinar sponsored by Red Gate and All Things Oracle. My topic was "How to Gather SQL Resource Consumption Metrics in Oracle". The webinar recording has been posted at AllThingsOracle as of May 9 but I thought I'd also make the materials available here for anyone interested. In the zip file, you'll find a PDF of the presentation slides, several example reports (ASH, AWR, SQL Monitor) as well as several scripts I used and a text file containing all the demo queries and output.
I was also asked a couple of questions that I wanted to follow up on. First, someone asked about the use of dbms_sqltune. As it turns out, it does require a license for the Oracle Tuning Pack in order to be able to legally use that package. That means that SQL Monitor reports are off limits without that license. Sigh...
Another person asked about how to create a SQL Profile and I included a script I use that was created by Kerry Osborne called create_sql_profile_awr.sql. You can get that script and several others along with a great overview of SQL Profiles at Kerry's blog.
Again, thanks to everyone who attended and I hope you found it interesting and helpful. Thanks also to the folks at Red Gate and particularly James Murtaugh who leads the charge at AllThingsOracle.com. Keep your eyes on the site for upcoming webinars, great articles and more!
I was also asked a couple of questions that I wanted to follow up on. First, someone asked about the use of dbms_sqltune. As it turns out, it does require a license for the Oracle Tuning Pack in order to be able to legally use that package. That means that SQL Monitor reports are off limits without that license. Sigh...
Another person asked about how to create a SQL Profile and I included a script I use that was created by Kerry Osborne called create_sql_profile_awr.sql. You can get that script and several others along with a great overview of SQL Profiles at Kerry's blog.
Again, thanks to everyone who attended and I hope you found it interesting and helpful. Thanks also to the folks at Red Gate and particularly James Murtaugh who leads the charge at AllThingsOracle.com. Keep your eyes on the site for upcoming webinars, great articles and more!
How to Gather SQL Resource Consumption Metrics in Oracle (2012 May 8)
Saturday, May 5, 2012
Love this quote
Tom Kyte has joined the fun on Twitter (@OracleAskTom) and tweeted this photo that I just had to steal and post here so I'd have it "on file" for future use. :)
Great quote!
Thanks for this one Tom...and welcome to the madness that is Twitter.
Great quote!
Thanks for this one Tom...and welcome to the madness that is Twitter.
Monday, April 16, 2012
The Magic of Doing One Thing At A Time
I just read an article from the Harvard Business Review entitled "The Magic of Doing One Thing at a Time" and thought it was excellent. I highly recommend reading the whole article (it's not long) but here are a few "policies worth promoting" from the article:
1. Maintain meeting discipline.
Among other things, this includes starting and ending meetings at a "precise" time and insisting that all digital devices be turned off throughout the meeting. Amen!
2. Stop demanding or expecting instant responsiveness at every moment of the day.
Boy, do I agree with this one! When did we get to the point that people must be available NOW, regardless of when now is? I understand that our world is often a 24/7 venture when it comes to technology, but if we never give people a chance to "turn off" what results are we really creating?
3. Encourage renewal.
Again, I really believe in this one. People arrive at the workplace after perhaps being up half the night dealing with a problem, eat lunch at their desks, and often never move until they finally leave the office hours after the normal end of the day (again, due to some problem or another). We all need to truly stop and decompress every now and then. I took up meditation several years ago and find that in 20 minutes I can feel more alert and focused than I did before I unplugged. Whatever way you find that works to help you get some distance and take a real break a couple of times a day, I think it's essential and am a firm believer in the power of renewal.
There's a lot more good stuff in the article and I hope you'll take a few minutes to read the whole thing. It sure rang a bell for me!
1. Maintain meeting discipline.
Among other things, this includes starting and ending meetings at a "precise" time and insisting that all digital devices be turned off throughout the meeting. Amen!
2. Stop demanding or expecting instant responsiveness at every moment of the day.
Boy, do I agree with this one! When did we get to the point that people must be available NOW, regardless of when now is? I understand that our world is often a 24/7 venture when it comes to technology, but if we never give people a chance to "turn off" what results are we really creating?
3. Encourage renewal.
Again, I really believe in this one. People arrive at the workplace after perhaps being up half the night dealing with a problem, eat lunch at their desks, and often never move until they finally leave the office hours after the normal end of the day (again, due to some problem or another). We all need to truly stop and decompress every now and then. I took up meditation several years ago and find that in 20 minutes I can feel more alert and focused than I did before I unplugged. Whatever way you find that works to help you get some distance and take a real break a couple of times a day, I think it's essential and am a firm believer in the power of renewal.
There's a lot more good stuff in the article and I hope you'll take a few minutes to read the whole thing. It sure rang a bell for me!
Saturday, April 7, 2012
Thanks for attending the webinar!
Thanks to everyone who attended my two Embarcadero sponsored webinar sessions. I promised to make the slides and some scripts demonstrated in the slides available so here they are:
Slide deck (pdf format)
Scripts (zip format)
Keep an eye out for more webinars I'll be conducting in the coming months!
Slide deck (pdf format)
Scripts (zip format)
Keep an eye out for more webinars I'll be conducting in the coming months!
Saturday, March 31, 2012
Best practices? I'm not so sure.
I'll be delivering a webinar this week that has the words "Best Practices" in the presentation title. As I've been preparing, it has really struck me how my definition of a best practice is not the traditional IT definition. Wikipedia says
I think a best practice is really a contextual practice. I'm borrowing this term from Eric Ries, author of The Lean Startup. Ries writes that we should strive to understand the context which we find ourselves in, and then apply the practice which is best within that context. I couldn't agree more. It's not about blindly following steps or guidelines that may, or may not, be applicable and particularly useful in your situation. It's about understanding. You've got to understand your situation and take steps that make sense in that context. Not simply take steps that worked for some other person, in some other context, at some other time, just because it is labeled as a "best practice".
My goal for my webinar's list of "best practices" is to offer ways to gain understanding and clarify the context of your specific situation when developing SQL. With a clearer understanding and proper context, you can have confidence to make the right choices that lead to optimal results. Come join me for the webinar and see if I succeed!
A best practice is a method or technique that has consistently shown results superior to those achieved with other means, and that is used as a benchmark.OK. That sounds reasonable, right? So, what's my problem? Well, I think that if this definition is the traditionally accepted standard definition, there's something missing. What about context? I suppose you could infer that a specific context is implied for any best practice, but the inference isn't enough. An unfortunate side effect the label "best practice" has when it is attached to any method or technique is that it makes people blind to the context in which the practice should/could be applied. Can you think of any examples where a practice that is "best" in one context is questionable within another? Sure you can (feel free to share yours in the comments!). But, so often, many people will take a best practice at face value without considering the context of their own situation.
I think a best practice is really a contextual practice. I'm borrowing this term from Eric Ries, author of The Lean Startup. Ries writes that we should strive to understand the context which we find ourselves in, and then apply the practice which is best within that context. I couldn't agree more. It's not about blindly following steps or guidelines that may, or may not, be applicable and particularly useful in your situation. It's about understanding. You've got to understand your situation and take steps that make sense in that context. Not simply take steps that worked for some other person, in some other context, at some other time, just because it is labeled as a "best practice".
My goal for my webinar's list of "best practices" is to offer ways to gain understanding and clarify the context of your specific situation when developing SQL. With a clearer understanding and proper context, you can have confidence to make the right choices that lead to optimal results. Come join me for the webinar and see if I succeed!
Subscribe to:
Posts (Atom)


