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

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.

Oracle Magazine useful Links

The following two links contain all the Oracle Magazine articles that have been published by Tom Kyte and Steven Feurstein. The third one, leads to all technical articles (check out Arup Nanda's Performance Tuning Series) and the last link points to all  issues archive.

• Oracle Magazine Ask Tom Columns
• Oracle Magazine PL/SQL Columns by Steven Feuerstein
• Oracle Magazine Technical (Technology and Developer) articles
• Oracle Magazine Issue Archive

enjoy
Oracle Learner

Oracle Internals Presentations

Check out this great set of Oracle Internals presentations by Julian Dyke. The presentations are all very comprehensive and I really like the animations that help you easily understand the sequence of events going on in the database.

Enjoy
Oracle Learner.