Contents Lab128 - Tools for Advanced Oracle Tuning and Monitoring. Reference Guide.

Stage and Fix SQL Statement

The purpose of this tool is to change the plan of the query without changing its text. This happens when database application cannot be changed, or it cannot be changed right away, but the query needs to be fixed rather very quickly. Quite often suboptimal plan is caused by incomplete or outdated table and column statistics. In order to fix the bad plan you have two options:

This tool focuses on second option. The was another way to change the plan using Stored Outlines which are still supported up to the latest version 12c. if you are on pre-10g Oracle, Stored Outline is the only way to hack the plan using undocumented swap technique. Stored Outlines will be de-supported in future versions. For this reason and because the alternatives, especially Baselines, are more sophisticated, Stored Outlines are not covered in this tool.

The Concept Used by Stage and Fix

You probably already know that Oracle hints can be used to give extra directives to SQL optimizer affecting the resulting execution plan. There are 277 hints in and the number is growing. You can see these hints by querying v$sql_hint view; Lab128 provides report on them: Main menu | Reports | SQL Hints. SQL Optimizer hints are very powerful. They are often used by sophisticated developers to force things the way they consider beneficial to performance and stability of the query. As any powerful means, hints can be easily misused, and Oracle corporation generally discourage using them. There is another function of hints, used by Stability Plan Management (SPM), to preserve plans from changing caused by multitude of reasons - from changes in statistics to upgrade to a newer version. On every parse the SQL Optimizer produces execution plan. Also as byproduct, it produces a set of hints which can be used in another parse effectively guaranteeing same plan. These hints are called "Outline data" and can be found in OTHER_XML column of plan table or v$sql_plan view. By default this set of hints is not used and thrown away. This can be changed by turning on automatic SQL Plan Baselines collection (11g+) or Stored Outlines collection (pre-11g). Now hints are stored and with proper settings can be used on subsequent hard parses providing plan stability.

The plan of existing query can be changed through the hints stored in Baselines or Stored Outlines. In case of Stored Outlines where was undocumented hack (described in Metalink back then) to swap hints between two SQL statements. For the Baselines, Oracle Corporation recognized the need for occasional fix of plans and provided documented way of adding another set of hints by adding another baseline and elevating its priority. Starting with 10g, Oracle added mechanism of adding hints to specific query through SQL Profiles, which was amended by SQL Patches in 11g. SQL Profiles and SQL Patches are similar. They both work differently than Baselines or Outlines and they don't need Baselines or Stored Outlines. How they work is very simple: when hard parsing SQL statement, SQL Optimizer checks if corresponding SQL Profile or SQL Patch exists for this statement. If it does, the hints from SQL Profile or SQL Patch are retrieved and SQL Optimizer continues as if the statement was hinted by developer in the first place. How does Oracle identify the Profile or Patch? It uses hash function (MD5, see details in All These Oracle SQL Statement IDs) on normalized SQL text, the resulting value is 64-bit integer and it is called SQL Signature. Starting with Oracle 11g, there is another ID called SQL Handle, which is derived from Signature as 'SQL_'||'hexadecimal value in lower case', for example SQL statement with Signature=11441060725077731689 will become 'SQL_9ec6d01cb3ee6969'. SQL Handle is used as identifier for a family of SQL Plan Baselines used by this SQL. As of now, SQL Handles and Signature are used in parallel having one-to-one relationship (11g, 12c). This can become broken in later versions.

Despite a relatively simple concept, implementing the strategies described above can be mundane and laborious. Therefore we tried to simplify the process as much as possible moving routine part into the tool. The "Stage and Fix" is activated from several places in Lab128. Generally these places are the reports on captured bind variables. Even if the query was not using binds, it still can be staged and fixed using these reports as starting point. Once you identified bad query, jump to these reports (see next chapter) to run Stage and Fix. Below is detailed description on how to fix the plan using Stage and Fix.

Launch Stage and Fix Window

Because bind variables are important, Stage and Fix is available in reports where Bind variables were captured. These reports are "SQL Statistics" (scroll down to "Peeked Bind Variables" section) and "SQL Bind Variables Capture" - click on "Stage and Fix" link. If you need to stage an arbitrary SQL statement, use SQL Central: in the SQL text editor place a cursor on desired SQL statement, or select a section of text. Then right-click to call a pop-up menu and select "Stage and Fix SQL".

When Stage and Fix window opens, it presents the staged SQL text. The goal is to make modifications to the text by adding hints or doing other compatible changes, then make a test run and confirm that new plan is optimal. The original SQL text (also called Target SQL text), its Signature value and SQL Handle are stored behind the scene. They are not affected by editing and will be used when it comes to creating an SPM object. The target attributes cannot be changed. The only way to change them is by closing SQL and Stage window and reopening it for another target SQL.

Step 1. Add Hints and Make Other Changes

Make whatever is necessary to change the plan the way you want. This can be as simple as adding "LEADING" hint to change the order of joins, or changing parameters of the current session. If the query is using views and the hint is needed inside the view, you can replace view with its text and then place hints. Or, even better, use Query Block Names. The art of changing plan is quite large topic deserving a dedicated chapter. There are many books and online articles covering this topic.

Step 2. Test Run of the Changed Query

Next is running the query. Press Execute button or F9. If the query is going to run long, you can interrupt it pressing Interrupt button. Sometimes all what we need is checking the actual plan of executed query to confirm intended changes.

Step 3. Check The Plan of Test Run

After execution finishes or is interrupted, click on "Last SQL" link to open Explain Plan window. This is a normal regular Explain Plan window used in Lab128 with one difference: it should have "Fix Plan" tab enabled. More about Fix Plan will be covered later. For now, check the plan and see if desired changes were achieved. If not, go to previous Step 1, make more changes, then execute and check the plan again. This can be an iterative process. At this point all extra SQL troubleshooting features of Explain Plan are also available, such as SQL Plan Statistics, for example.

Step 4. Fix The Query

Once you have plan that you like, make this plan linked to the Target SQL statement. In the Explain Plan window click on "Fix Plan" tab. There are several options to link the plan. If you are connected to Oracle 11g or newer, these options are:

For 10g, there will be Create SQL Profiles option only. If your database has SQL Baselines enabled (optimizer_use_sql_plan_baselines = TRUE), then Baseline is preferred way. If not, use SQL Patch or SQL Profile, they are very similar (note that SQL Profiles may need extra license from Oracle).

It is safe to see what is in every option because nothing will be done right away. When you try one option or another, you will be presented with SQL command creating corresponding SPM object. You can see what exactly is going to be created and make additional research and make changes. In case of Baselines, the creation is straight forward, the target SQL will borrow the plan from the parsed cursor in shared pool. This will be cursor and the plan of last execution in Step 3. The baseline will be created with FIXED = 'YES' which means it will receive the highest priority and other baselines with another plans for the same SQL statement will not be used by the optimizer.

For SQL Patch or SQL Profile, the proposed text will include all SQL Hints generated by Oracle for this plan. This is because the tool doesn't keep track of your changes and don't attempt to deduct which hints you added. Instead it can easily extract all hints from Outline Data (see above). Presumably these hints combined would keep new plan. Usually between them you can find your hints as well. So you can leave the set of hints unchanged (so Optimizer will attempt to apply all hints), or just keep your own hints removing others.

Note. Oracle 11g+ provides DBMS_SPM package to create/modify SQL plan baselines. If you will be using Oracle account which doesn't have DBA role, grant ADMINISTER SQL MANAGEMENT OBJECT system privilege to this user. For more details see Installation and Setup / Required minimum privileges.

Post-Fix Control Run

Once the fix is created, it would be desirable to check if the original query will use a new plan. You can stage it again, run and see the plan of last execution. You should also see in the Explain Plan if the Baseline/Patch/Profile/Outline was used in the last run (Lab128 and XPLAN tabs). If not, check if Baselines are enabled. If profile was created, check that corresponding category is enabled (for example in system parameters sqltune_category=DEFAULT.

Please note, that sometimes (less than 1%) despite the creation of SQL Baseline, the Optimizer may actually generate and use a different plan. This mostly happens because the set of hints failed to secure the plan and generally it is a bug in SQL optimizer. This can be seen in "REPRODUCED" column of dba_sql_plan_baselines view - the value is "NO". This means that SQL Optimizer was parsing the query applying all hints but hash value of resulting plan was not the same as hash value of the parse when these hints were captured. There can be legitimate situations for non-reproducible baseline though, for example, the index used by the plan was dropped, so the plan cannot be reproduced anymore. But this hardly applies to just created baseline. Just pay attention to "REPRODUCED" attribute of baseline as it can explain unexpected behavior.