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.

Code Instrumentation

Here is a very nice introduction to code instrumentation from David Njoku. Instrumentation is very important for your code and read this article to learn why. Of course it is well-known that Oracle database is one of the most well-instrumented software in the world, but instrumentation is equally important for software of any scale.

The article does not give many details on the "how" part of instrumentation, as it puts more weight on the "why it is necessary". Although it gives a brief outline.

An open source solution for instrumenting your PL/SQL code is Logger.  This is extremely useful for APEX applications where debugging can be really difficult  if your code does not leave the appropriate traces behind in order to find out what has happened (i.e., it has not been instrumented!). Check it out.

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.

Hints from an SQL Profile

A SQL profile is a set of auxiliary information specific to a SQL statement. It is an object containing information that helps the query optimizer find an efficient execution plan for a specific SQL statement. It provides information about the execution environment, object statistics, and corrections related to the estimations performed by the query optimizer.

One of its main advantages is the ability to influence the query optimizer without modifying the SQL statement or the execution environment of the session executing it. In other words, it is transparent to the application connected to the database engine.

Conceptually, a SQL profile is to a SQL statement what statistics are to a table or
index. The database can use the auxiliary information to improve execution plans.

Exadata Storage Indexes

Storage Indexes is one of the most significant features of Exadata that run at the storage cells. However, is also the feature which is the least (if at all) controllable by the user (developer/dba). You cannot control when they will be build, for which columns they will be build for, and  for which columns the storage indexes will remain as the database workload evolves. So it is a true "black box" and totally different to what we have been used from good old database indexes.

The following links from Richard Foote's Oracle Blog are very insightfull on how storage indexes really work:
First goes the introductory series of posts:

• Exadata Strorage Indexes - part I
• Exadata Strorage Indexes - part II
• Exadata Strorage Indexes - part III
• Exadata Strorage Indexes - part IV
• Exadata Strorage Indexes - part V

And then is the comparison series between SIs and database indexes (check out the last one on the "magic" 8-column limit):

• Storage Indexes vs Database Indexes - part I
• Storage Indexes vs Database Indexes - part II
• Storage Indexes vs Database Indexes - part III
• Storage Indexes vs Database Indexes - part IV

Enjoy,
Oracle Learner.

Oracle Trace Files

In this post I will give an overview of Oracle trace files. The following are a collection of information from several books (especially [1]). Check out the References section at the end of this post.

Oracle trace files are text files that contain session information for the process that created
them. Trace files can be generated by the Oracle background processes, through the use of
trace events, or by user server processes. These trace files can contain useful information
for performance tuning and system troubleshooting.

Big Data Intro

Here is a great post from Arup Nanda with introductory material on Big Data and all the relevant buzzwords headed at Oracle professionals!

Enjoy!
Oracle Learner