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.

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.

Don't treat your Data Warehouse as a warehouse!

   It is very common in a large organization an enterprise Data Warehouse to be considered as a "warehouse". In other words, just like in a typical warehouse we store anything that is useless but we cant just discard it - since we need to store it someplace-, or anything that we don't know where else to store it, or anything we just want to get rid of. In the same manner, many people, whenever they have some tables that they need in order to generate some reports, and these tables for X reasons cannot be stored in some transaction system, they choose the enterprise data warehouse as the best place for storing these data. Of course, this means that you treat your data warehouse as if it is the "recycle bin" of your IT systems!