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.

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.

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.   

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.