Jonathan Lewis

Author

In part 9 we saw an example of a simple nested loop join where the basic method for interpreting the numbers in an execution plan started to break down because the arithmetic used by the optimizer to calculate the resource costs for the plan wasn’t consistent with the visual representation of the mechanics of the run-time path. In this instalment we’re… Continue Reading →

All Things Oracle Full Articles, Database Administration, Database Development

In part 8 we looked at a very simple execution plan involving a hash join; but that example was too simple to give us the full flavour of the arithmetic involved in Oracle’s predictions because every operation executed just once. We need to see some plans where each execution of a parent operation could requires multiple executions of its child… Continue Reading →

All Things Oracle Full Articles, Database Administration, Database Development

It’s time to move away from the shape of an execution plan and turn our attention to some of the numerical information we get from the plan. In this article we’re going to look only at the predictions that the optimizer makes (explain plan), postponing any investigation of actual run-time figures (v$sql_plan_statistics_all) for future instalments. Getting Started As a reference… Continue Reading →

All Things Oracle Full Articles, Database Administration, Oracle Database

When we examine an execution plan, we’re usually trying to work out the mechanical steps that Oracle took to produce a result set. When we do this, it’s important to remember that the text that Oracle optimized wasn’t necessarily the same as the statement we originally wrote. Oracle may have transformed our statement before passing it through the optimization engine…. Continue Reading →

All Things Oracle Full Articles, Database Administration, PL/SQL

In part 5 of this series we examined a couple of examples where you had to handle the “first child first” rule with a little care. In part 6 we continue this exploration to look at a general principle – the “pushed subquery” – where the “first child first” could lead you to the wrong conclusion. Access or Filter If… Continue Reading →

All Things Oracle Full Articles, Database Administration, Database Development

I’ve offered up “first child first, recursive descent” as a basic strategy for interpreting execution plans – but it’s not a perfect rule, and it can be easy to lose track of what’s going on even when the basic “first child first” is being obeyed. In this article we’ll be looking at a couple of examples where we will still… Continue Reading →

12c, All Things Oracle Full Articles, Database Administration, Oracle Database

In part 3 of this series we used a simple hash join to introduce the a simple guideline for reading execution plans – the “first child first, recursive descent” method. This allowed us to work out the order in which Oracle produced rowsources and (implicitly) the order in which it visited the different physical objects in the query. At the… Continue Reading →

All Things Oracle Full Articles, Oracle Database, Performance Tuning

In the first two articles in this series we listed a couple of methods for generating or retrieving execution plans and learned a few extra steps that could increase our confidence that we were using the right environment to investigate any problems we might have with a plan. In this article we’re going to become acquainted with a basic (though, as… Continue Reading →

All Things Oracle Full Articles, Oracle Database, Performance Tuning

In part 1 of this series we saw two basic ways of creating, or acquiring, execution plans. The first method gave us the optimizer’s prediction of what it would (possibly) do if you ran the query: explain plan for {sql statement} select * from table(dbms_xplan.display); The second method told us what the optimizer had done after we ran the query…. Continue Reading →

All Things Oracle Full Articles, Database Administration

If you have to tackle performance problems in an Oracle database one of the most important skills you need to acquire is the ability to interpret execution plans, and in this series of articles I’ll be telling you how to do just that. We’ll start with a couple of articles that look at some of the ways we can access… Continue Reading →

All Things Oracle Full Articles, Database Administration, PL/SQL