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.

Parallel Refresh of a Materialized View

This is a quick post regarding materialized views refresh.

Refresh is invoked with a call to procedure DBMS_MVIEW.REFRESH. For large MVs it is highly desired that the refresh takes place in parallel. The REFRESH procedure parameter "parallelism" makes you think that this is the right tool for this job. Nope!

As much attractive as it might seen, this parameter does not help towards the parallelism of the refresh. You have to either include a parallel hint in the SELECT part of the definition statement of the MV (create materialized view) or/and include a PARALLEL DEGREE > 1 to the base tables of the MV.

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

How to effectively tune a query that does not even finish (SQL Monitoring and ASH in action)

Have you ever been called to tune a query that runs forever?

Even worse, what if the query is very complex, you don't know anything about the "logic" behind the SQL - since it was written by someone else - and when you see the execution plan it is several pages long?
Moreover, what if the query is for retrieving a list of customers for a business critical campaign that has to run asap, and therefore the beloved management is over your shoulder, waiting for you to solve the problem!

Problem Definition

Never ending queries, or at least queries that cannot finish within an acceptable time frame are quite common in large Data Warehouses. Unfortunately, in this situation one cannot use methods where execution statistics are gathered first and then analysis of the execution steps can take place. Simply because the query does not finish!

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

Edition-Based Redefinition links

Oracle Database 11g Release 2 introduces edition-based redefinition, a revolutionary new capability that allows online application upgrade with uninterrupted availability of the application. Below we note some great links with in-depth information for anyone who want to learn this great feature:

• Great article by Tom Kyte in Oracle Magazine
• Part 1
• Part 2
• Part 3
• OTN white-paper by Bryn Llewellyn
• Great blog-post by Tim Hall
• Oracle Documentation

Enjoy!
OL

Histograms Intro

The following are three great posts by Jonathan Lewis explaining all about histograms up to all Oracle versions prior to 12c. You will learn about the two main types of histograms: frequency histograms and height-balanced histograms, what they are, how you create them, and how they are used.

• Histograms - Part 1
• Histograms - Part 2
• Histograms - Part 3

Also, a great article about optimizer statistics in general but including a nice intro on histograms also, can be found in the following OTN white paper "Understanding Optimizer Statistics".

Enjoy!

Oracle Learner

CURSOR_SHARING explained

In Oracle8i, release 2 (version 8.1.6), Oracle introduced a new feature called CURSOR_SHARING. Cursor sharing is an 'auto binder' of sorts. It causes the database to rewrite your query using bind variables before parsing it. This feature will take a query such as:

select * from emp where ename = 'KING'; 

and will automatically rewrite it as: 

select * from emp where ename = :SYS_B_0

CURSOR_SHARING was introduced to help relieve pressure put on the shared pool, specifically the cursor cache, from applications that use literal values rather than bind variables in their SQL statements. It achieves this by replacing the literal values with system generated bind variables thus reducing the number of (parent) cursors in the cursor cache.

In this great article by Maria Colgan the possible values of CURSOR_SHARING are explained.

Also check out this great collection of blog posts related to cursor sharing from the Oracle Optimizer blog.

Enjoy!
Oracle Learner.

Using automatic SQL tuning and SQL Profiles for fast ETL performance troubleshooting

In the previous post we have showed how we can use SQL Profiles and a script that we can download from Metalink in order to fix the execution plan of a query to a specific one that we have found from the available plans in AWR (or Library Cache) that has a better elapsed.

In this post, we will continue our discussion on SQL Profiles and show another way that we can exploit them in order to very fast troubleshoot SQL tuning issues, without the need to write code, deploy new code into production etc.