Advanced SQL tips - reading an execution plan

When Codd and Date created the relational data model, the execution plan was an afterthought, largely because the SQL optimizer was always supposed to generate the best execution plan, and hence, there was not real need to understand the internal machinations of Oracle execution plans.

However, in the real world, all SQL tuning experts must be proficient in reading Oracle execution plans and understand the steps within an explain plan and the sequence in which the steps are executed.  To successfully understand an explain plan, you must be able to know the order that the plan steps are executed.  Reading an explain plan is important for many reasons, and Oracle SQL tuning experts use the explain plans to check many things.  For example:

Reading SQL execution plans has always been difficult, but there are some tricks to help determine the correct order that the explain plan steps are executed.

Ordering the sequence of execution plan steps

SQL execution plans are interpreted using a preorder (reverse) transversal algorithm. This is a fancy way of saying:

1 - To read an execution plan, look for the innermost indented statement. That is generally the first statement executed.

2 - In most cases, if there are two statements at the same level, the first statement is executed first.

In other words, execution plans are read inside-out, starting with the most indented operation. There are some general rules for reading an explain plan.

Read on and learn how to read the sequence of steps in any SQL execution plan:

http://www.dba-oracle.com/t_order_sequence_sql_execution_explain_plans_steps.htm

*****************************************
Can you see your database trends?

After several years of hard work, Ion for Oracle is finally available for general release.  Ion is unlike any other Oracle tool on the market, a tool that encapsulates expert techniques to allow a DBA to quickly visualize important trends and signatures.

Predictive analytics if the key to repairing Oracle problems before they cripple your database, and I created Ion for Oracle to allow any DBA to quickly identify changing workloads and unobtrusive patterns of data access.  Follow the link to try Ion for free:

www.ion-dba.com

*****************************************

Need a health Check?

Oracle is the world's most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf