Teaches you to apply the techniques discussed in the previous sections to help diagnose and correct a number of problematic SQL statements.
The final part of the SQL Tuning workshop focuses on applying the techniques discussed in the previous sections to help diagnose and correct a number of problematic SQL statements and shows how you can use SQL Plan Management or a SQL Patch to influence an execution plan.
In a recent chat with Connor McDonald, we discussed if it is realistic to have applications that don’t contain Optimizer hints. Ideally, the answer to this question is “yes”, you don’t need hints if you have a well-written application and you have supplied a representative set of statistics and all the possible constraint information (NOT NULL, Primary keys, Foreign Keys, etc.) to the Optimizer.
But in reality even with all of this in place, there can be cases where something goes wrong with the execution plan for a critical SQL statement and you get called in to fix.
During our chat, Connor used a very apt analogy to describe this situation. He said it was like having a patient arrive in the emergency room, who is bleeding profusely. Your first priority is to stop the patient from bleeding by slapping a band-aid on the wound.
The same is true for our poorly performing SQL statement. Our initial response is to add an optimizer hint to get the SQL statement’s execution plan back to a reasonable response time or acceptable performance.
But once a patient has been stabilized in the emergency room, medical professionals normally take that patient into surgery to make a permanent fix or at the very least stitch up the wound properly.
We need to make sure we do the same thing for our SQL statements.
Rather than leaving a band-aid in the application code in the form of an optimizer hint, we should either fix the root cause or at the very least, make a permanent fix that can be easily traced and ideally can evolve over time.
That’s why you often hear me say, “if you can hint it, you can baseline or patch it”.
What do I mean by that?
I mean we should capture the hinted plan as a SQL plan baseline or at the very least insert the hints via a SQL Patch so that we know that this statement is patched (the use of a SQL patch is visible in the note section of the plan).
Continue reading “How to use a SQL Plan Baseline or a SQL Patch to add Optimizer hints”
Over the years, Oracle has provided a number of techniques to help you control the execution plan for a SQL statement, such as Store Outlines and SQL Profiles but for me, the only feature to truly give you plan stability is SQL Plan Management (SPM). It’s this true plan stability that has made me a big fan of SPM ever since it was introduced in Oracle Database 11g.
With SPM only known or accepted execution plans are used. That doesn’t mean Oracle won’t parse your SQL statements, it will. But before the execution plan generated at parse is used, we will confirm it is an accepted plan by comparing the PLAN_HASH_VALUE to that of the accepted plan. If they match, we go ahead and use that plan.
Continue reading “SQL Plan Management – Selective Automatic Plan Capture Now Available!”