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.

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.

Cardinality Feedback

Cardinality Feedback  (CFB) is  a  feature of the Oracle Optimizer for correcting cardinality estimation (i.e., number of rows returned by each operation) in execution plans. Rather than going to endless complications to determine the right cardinality, we just wait for the result of each step in the execution plan, store it in the shared pool and reference it on subsequent executions, in the hope that the information will give us a good idea of how well  we did the last time.

CFB is a feature of Oracle 11gR2. For more details read the following post.

In this post, we will show CFB in action.

Adaptive Cursor Sharing

Adaptive Cursor Sharing is a new feature of Oracle database 11g Release 2 that tries to cure the problem of when you have a cursor with bind variables and some columns have data skew, then a suboptimal plan might be chosen by the optimizer. Adaptive cursors have the ability to be "bind-aware" and choose a different plan for a different bind value.

Check out this great article by Maria Colgan to learn what it is and how it works. Also check out the relevant Oracle Performance Tuning Guide paragraph.

If you want to remember what is cursor sharing and the meaning of the values of the CURSOR_SHARING parameter, then check out our relevant post. Also to understand CURSOR_SHARING combined with Adaptive Cursor Sharing, then read this article.

Enjoy!
Oracle Learner.   

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?