Using SQL Plan Baselines to Revert to an Older Plan

It's 10:00 AM and your nightly ETL flows are still running! Τhe daily reports must have been sent to the management from 8:00! From you investigation you find out that a specific statement (e.g., one that corresponds to an ETL mapping) is taking much too long than usual (it used to run in minutes and now it is running for 4 hours!). Your first thought is that some change in the execution plan must have taken place. How do you fix this? Or better, how do you fix this fast? (remember the management is waiting for the report)?

The Problem

SQL performance degradation due to a change of plan is one problem, fixing the problem fast so as to allow production ETL flows to unstack is another one, ensuring stability for the execution plan of a statement is yet another one! In a previous post, we discussed a method for detecting and verifying a change of plan. So we assume that we have verified the change of plan and we have also identified some older plan as the "good plan" that we would like to revert to.

In this post, we would like to deal with the case of how we can quickly revert to an older plan, (one that hopefully lies in the cursor cache or/and in the AWR repository) and thus fix the production problem efficiently and in a limited time-window. To this end, we will use the "SQL Plan Management" features of Oracle 11g, which apart from solving the above problem, will also ensure the required stability of the execution plan.

Detecting a change in the execution plan of a query

My report used to complete in seconds and now is running for hours! Do you know what is going on?

Sounds familiar? It is very common in Data Warehouses to experience a sudden performance degradation in the execution time of a report, or of an individual ETL mapping. The query was running fast for months and then one day everything changed!

Such a behavior is a sign of a change in the execution plan. Of course there are a million other reasons that might have caused the performance degradation but a change in the execution plan is one of the most common. The reasons for such a change are numerous (stale statistics, change triggered by some DDL -e.g. a new index created, a change in the optimizer environment -e.g., by changing the value of some relevant  parameter at the session level etc.)

In this post, we want to show how easy it is to detect a change in the execution plan of a statement (technically represented by a unique sql id) with the help of historic data maintained by Oracle in the AWR. To this end we will present some examples and some simple scripts to do it. However, we will not deal at all with the reasons that might trigger the change of plan. This is a long discussion ...

SQL Plan Management / SQL Plan Baselines Material

SQL Plan Management is the elegant solution 11g offers for achieving plan stability on the one hand, and on the other offer you a controlled manner for evolving your execution plans for achieving better performance. The main vehicle for achieving plan stability in 11g is the SQL Plan Baseline, which essentially comprises a set of accepted execution plans.

In this post I have gathered the MUST-READ material for SQL Plan Management:

• Maria Colgan's 4-part blog series on SQL Plan Management
• part 1 - Creating SQL Plan Baselines
• part 2 - SPM Aware Optimizer
• part 3 - Evolving SQL Plan Baselines
• part 4 - User Interfaces and Other Features
• SQL Plan Management Oracle white paper
• Carlos Sierras' post on how to create a baseline for an SQL with an accepted plan based on a modified SQL (e.g., a hinted version of the original SQL). The baseline (an thus the corresponding accepted plan) will be applied to the original SQL and not to the modified one.
• All relevant posts from the Oracle Optimizer Blog
• And of course the Oracle Performance Tuning Guide's corresponding chapter.

Enjoy!

OL

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.

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.

Hints from an SQL Profile

A SQL profile is a set of auxiliary information specific to a SQL statement. It is an object containing information that helps the query optimizer find an efficient execution plan for a specific SQL statement. It provides information about the execution environment, object statistics, and corrections related to the estimations performed by the query optimizer.

One of its main advantages is the ability to influence the query optimizer without modifying the SQL statement or the execution environment of the session executing it. In other words, it is transparent to the application connected to the database engine.

Conceptually, a SQL profile is to a SQL statement what statistics are to a table or
index. The database can use the auxiliary information to improve execution plans.