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
