Using SQL Profiles for Forcing a Specific Execution Plan

In a previous post, we briefly discussed about SQL Profiles. In this post, we will discuss the use of SQL Profiles in order to quickly remedy an SQL statement that has suddenly chosen the "wrong" execution plan and its performance has been deteriorated dramatically. There might be many reasons why an execution plan has changed (statistics, changes in the schema, an upgrade to the optimizer code etc.) and indeed the root cause of the change is something that must be investigated. However, sometimes (and especially in production environments) the first priority is to cure the problem and then try to find what happened, so as to provide a more long-term solution.

SQL Profiles give exactly this fast remedy in the case of a problematic execution plan. As long as you know the SQL statement (i.e., the SQL_ID) and an old good plan (i.e., the plan_hash_value), then is a piece of cake to revert to the old plan. Please note, that this is completely transparent to the end users - no deployments, no bug fixing, no coding, nothing. Just create the SQL Profile for the specific SQL_ID and the optimizer is forced to use the old plan. Lets start with a small intro.

[Update]: In this newer post about how to revert to an older plan using SQL Plan Baselines, we describe how one can achieve the same thing with an SQL Plan Baseline.

Cardinality Feedback

Cardinality Feedback  (CFB) is  a  feature of the Oracle Optimizer for correcting cardinality estimation (i.e., number of rows returned by each operation) in execution plans. Rather than going to endless complications to determine the right cardinality, we just wait for the result of each step in the execution plan, store it in the shared pool and reference it on subsequent executions, in the hope that the information will give us a good idea of how well  we did the last time.

CFB is a feature of Oracle 11gR2. For more details read the following post.

In this post, we will show CFB in action.

Adaptive Cursor Sharing

Adaptive Cursor Sharing is a new feature of Oracle database 11g Release 2 that tries to cure the problem of when you have a cursor with bind variables and some columns have data skew, then a suboptimal plan might be chosen by the optimizer. Adaptive cursors have the ability to be "bind-aware" and choose a different plan for a different bind value.

Check out this great article by Maria Colgan to learn what it is and how it works. Also check out the relevant Oracle Performance Tuning Guide paragraph.

If you want to remember what is cursor sharing and the meaning of the values of the CURSOR_SHARING parameter, then check out our relevant post. Also to understand CURSOR_SHARING combined with Adaptive Cursor Sharing, then read this article.

Enjoy!
Oracle Learner.   

Find all available Execution Plans for a specific SQL statement

When you have a specific SQL statement that is been running in your database (e.g., it might be part of nightly ETL flow that is used for loading your Data Warehouse), then how can you get a list of all available execution plans that the optimizer has chosen for this specific statement?

Each statement is uniquely identified by an SQL_ID and whenever the statement undergoes a hard parsing, then an execution plan is generated, which is identified uniquely by a hash value, called plan_hash_value.

Child Cursors

If you want to understand what is a child cursor, (like the ones you see when you query V$SQL, and which are identified by a unique pair of (SQL_ID, CHILD_NUMBER)), and why they are created, then check out these two great explanations by Tom Kyte:

1. From the asktom site
2. From Oracle Magazine

Keep in mind that:

• A parent cursor essentially represents the text of an SQL statement and is uniquely identified by an SQL_ID (prior to 10g was identified by address and hash_value). Parent cursors are externalized via dynamic view V$SQLAREA
• A child cursor represents the execution plan as well as the execution environment for a specific SQL_ID (i.e., a parent cursor) and it is uniquely identified by SQL_ID and CHILD_NUMBER (address, hash_value and child_number in older versions). Child cursors are externalized via V$SQL.
  
  From the 2nd point we infer that whenever the execution plan changes for a specific SQL statement (i.e., a parent cursor), then a new child cursor is created. However, this is true per database instance. Because in a RAC enviroinment (e.g., Exadata) where there exist multiple instances, if you query the dynamic view GV$SQL (which covers all available instances - instead of V$SQL), then you might see that a new execution plan might be executed by the same child cursor but in a different instance. In other words, for the same (SQL_ID, child_number) pair you might see more than one plan_hash_values (i.e., different execution plans) but each such pair (i.e., child cursor), will be loaded in the library cache of a different instance (therefore, the value in the GV$SQL.INST_ID column will be different).

Enjoy!
Oracle Learner.

Code Instrumentation

Here is a very nice introduction to code instrumentation from David Njoku. Instrumentation is very important for your code and read this article to learn why. Of course it is well-known that Oracle database is one of the most well-instrumented software in the world, but instrumentation is equally important for software of any scale.

The article does not give many details on the "how" part of instrumentation, as it puts more weight on the "why it is necessary". Although it gives a brief outline.

An open source solution for instrumenting your PL/SQL code is Logger.  This is extremely useful for APEX applications where debugging can be really difficult  if your code does not leave the appropriate traces behind in order to find out what has happened (i.e., it has not been instrumented!). Check it out.

Enjoy!
Oracle Learner