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.