A Practical Guide to SQL Tuning for Day-to-Day Data Warehouse Support

In real-world large DWs solving SQL performance problems is a daily task. A task that quite often must be performed during "anti-social" hours (i.e., too late at night, or too early in the morning, or during weekends) and under significant time pressure.

In the following article, we provide a practical guide to SQL tuning for day-to-day DW support. We describe the most popular methods that DW engineers use today, in order to solve SQL performance problems, based on our real-world experience. We point out pros and cons of each method, and provide some best-practice recommendations.

The intended audience is DW support engineers, DW developers and DBAs. The methods presented are not DW-specific and pertain to SQL tuning in general, so this article should be useful to a broader audience of database professionals with interest in SQL tuning and not only to Data Warehouse professionals.

You can download the article from here.

Update:
Note that this article has been published in the following two well-known Oracle technical journals:
* ODTUG Technical Journal, May 6, 2016
* RMOUG Newsletter, Nov 2017

Efficient Distributed Queries for Oracle Data Warehouses

One of the most typical query patterns for a data warehouse is to “select” data from a remote site (i.e., a data source) and insert them into a local staging table. Also, in the majority of the cases, this statement will include some joins to local tables as well as some filter predicates on both local and remote tables. Let’s say that a typical query pattern, in its most basic form will be something like the following:

INSERT INTO <local staging table>

SELECT <columns of interest>

FROM   <remote_table>@<remote_site> t_remote

       <local_table> t_local

WHERE

       <join condition between t_remote and t_local>

       <filter predicates on t_local>

Very often, the table at the remote site has a significant number of rows. For example, a very common scenario is that the remote table is a large table containing transaction data (e.g., order lines, Call Detail Records, invoice lines etc.) with a timestamp. The goal of the distributed statement is to select only the rows that correspond to a specific time period (e.g., the transactions of the last day, a.k.a. “the daily delta”) and insert them into a local staging table. In order to achieve this, we maintain locally (i.e., at the data warehouse site) a very small “control table” that holds the current data warehouse “running date” (i.e., the date of interest for transaction data).

Therefore conceptually, we have a join of the very small (local) control table to the very large (remote) transactional table, and this join yields a relatively small amount of rows (i.e., the daily delta of transactional data, which is small, compared to the whole transactional table). Now, what is the most efficient method to execute this distributed query?

Oracle SQL Tuning for Day-to-Day Data Warehouse Support

How many times have you been called for a Data Warehouse running task that is taking too long? The underlying SQL statement has been running for some hours now and the SLA to the business is under risk! Does it sound familiar?

If you have ever worked as a DBA, Support Engineer, or even a Developer for a production data warehouse then the above situation must sound quite familiar. Actually in real-world large data warehouses solving SQL performance problems is a daily task. A task that quite often we must perform during "anti-social" hours (e.g., after midnight or very early in the morning, or during weekends etc.).

Right-Deep Join Trees and Star Schema Queries

There are many trees out there, but what is your favorite join-tree?

Join trees are a graphical representation of the way a series of joins (an N-way join as it is called scientifically) is executed. The most common form of join trees are the left-deep join trees. Actually, these are the only ones that the CBO (i.e., the Oracle  Cost Based Optimizer) considers when it chooses a join method other than a Hash Join (i.e., Nested Loops, Merge Join and Cartesian Joins). For Hash Joins in particular, the CBO also considers right-deep join trees from 11g and onward.

In this post, we will describe why right-deep join trees are important for the execution of star schema queries, which are the bread and butter of Data Warehouses. We will show that right-deep join trees make a more efficient use of the workareas (i.e., the memory area used in the PGA, when a hash join, group by, sorting etc operation is executed) during the execution of a series of hash-joins included in a typical star schema query.

Join hints and join ordering, or, why is the optimizer ignoring my join hint?

Have you ever tried to use a join hint (e.g., USE_NL, or USE_HASH) in a query, only to realize that the query optimizer stubbornly refuses to obey?

No matter how frustrating this might be for you, there is an explanation for this behavior. One of the main reasons for such an event is the following rule:

Oracle first decides on the join order and then on the join method to use.

In this post, we will describe how join ordering can be the reason for the optimizer to ignore a hint for forcing a specific join method (e.g., nested loops, hash join, sort merge join etc.) Moreover, we will show how one can use the USE_NL or USE_HASH hints (or any hint that forces a specific join method) in combination with the LEADING (or ORDERED) hint (i.e., hints that force a specific join order).

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

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!

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.

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.

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.

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.

Oracle Trace Files

In this post I will give an overview of Oracle trace files. The following are a collection of information from several books (especially [1]). Check out the References section at the end of this post.

Oracle trace files are text files that contain session information for the process that created
them. Trace files can be generated by the Oracle background processes, through the use of
trace events, or by user server processes. These trace files can contain useful information
for performance tuning and system troubleshooting.

parallel hint with no degree specified

In this post we try to investigate what degree of parallelism (DOP) Oracle assigns to DML operations when a parallel hint is used.

Update your stale statistics

Object statistics are very important for your data warehouse. Tables and indexes must have updated statistics in order for the query optimizer to choose the best execution plan for each query. So you must continously gather objects statistics (especially after ETL jobs have modified significantly the data).

The easiest way to do this is to utilize Oracle's automatic statistics gathering task. This can be easily configured to run at a specific maintenance window (that you will define, unless you want to use the default) from Enterprise Manager (Administration --> Automated Maintenance Tasks --> Optimizer Statistics Gathering).

Note that you should use an appropriate degree of parallelism so that the stats gathering process runs fast enough to fit in the maintenance window. You may have to experiment a bit with this, in order to find what is appropriate for your case.

But how can you monitor if the automated task has succesfully managed to gather statistics in the defined maintenance window?

Use ASH for troubleshooting a long running query

You wont believe it how useful ASH (Active Session History) can be.

Step 1: Find the sql_id of the long_running query (the following lists the top 100 most -busy queries. I.e., queries that are in the most ASH samples either on cpu or actively waiting on something) :

[Update]: In this newer post we describe how one can use ASH and SQL Monitoring to tune a complex query that does not even finish!