Oracle Scratchpad

Explain Plan

Back to main Tutorial Page

Explain Plan – Agenda

One of the critical skills for tuning SQL lies in understanding what is happening, and what is possible. Oracle gives you several tools for examining (or predicting) the execution path for a query.

In this tutorial we look at the available options, examine their strengths and weaknesses, and learn how easy it is to understand what an execution plan is telling you..

Session 1
1.5 hours

How to find execution paths.

There are several different methods for generating, or examining, execution paths, from the simple autotrace in SQL*Plus through to interrogating the dynamic performance views such as v$sql_plan_statistics_all in great detail. The different methods have their own strengths and weaknesses, and these vary with version of Oracle. In this first session, we discuss the different options, what’s visible in them, and to choose the most appropriate one for the task in hand.

Break – coffee and informal discussion: 30 minutes
Session 2

1.5 hours

Intepreting Plans

Once you’ve got an execution plan, how do you decide what it means ? An often-quoted clue is that ‘it starts near the top and over to the right’. This hint is a consequence of a simple algorithm for reading execution plans – no matter how complex – in a fashion that can be described in just two steps.

Break – Lunch and informal discussion: 1 hour
Session 3

1.5 hours

Problems with Plans

Although there are several ways of acquiring execution plans, none of them is perfect. Some options simply omit critical details; some options are unable of guaranteeing the truth. In this session, we look at the various omissions and errors that can make you jump to the wrong conclusion when you are trying to understand how a query might run.

Break – coffee and informal discussion: 30 minutes
Session 4

1.5 hours

Advanced Features

Once you have mastered the standard features of execution plans, you can easily extend your understanding to any new feature that Oracle throws at you. In this session, we take a look some of the features that are not seen so commonly in production systems, namely , distributed joins,  parallel execution, and partitioned tables.


Back to main Tutorial Page

Leave a Comment »

No comments yet.

RSS feed for comments on this post.

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by