Diagnosing SQL Performance Problems Webinar Followup

Thanks to everyone who joined me for part 1 of my Oracle SQL Performance webinar series! Embarcadero will be posting the recording within the next few days. In the meantime, I thought I'd post the slides and a few scripts that were shown.

Thanks again and I hope to see you again next month for part 2!

Presentation slides (PDF)
Scripts (ZIP)

July 31 Webinar

Please join me for another Embarcadero sponsored Community Webinar on July 31. This is the first in a series of three webinars. My topics for this triple feature are:

Diagnosing SQL Performance Problems (July 31)

Making SQL Performance Solutions "Stick"

Making Impactful Performance Changes

Here's a bit about what I'll cover in this first event:

Karen Morton begins her DBA Performance Series with Diagnosing SQL Performance Problems, designed to help DBAs understand the context in which SQL problems arise and present a framework to efficiently diagnose SQL issues, including: immediate items to identify, rule out, and confirm as well as identifying "fast" fixes to get performance back on track quickly.


You'll learn

• How to distinguish between SQL and non-SQL performance optimization issues.

• How to target typical cases that cause SQL to perform poorly and resolve them without rewriting SQL.

• How to identify when a SQL statement needs to be rewritten to permanently solve performance inconsistencies.

Thanks again to Embarcadero for sponsoring the event and I hope to "see" you there!

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!

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

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!

How to Gather SQL Resource Consumption Metrics in Oracle (2012 May 8)

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.

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!