Jonathan Lewis

Author

In the second installment of this series we looked at individual access paths for the tables in a simple join query to highlight an important flaw in the default model that the optimizer uses for indexes. Having taken advantage of a recent enhancement that addresses that flaw we are now ready to move onto the problems that appear with the… Continue Reading →

All Things Oracle Full Articles, Oracle Database

In the first installment of this series I gave an informal description of how the optimizer would consider the possibilities when choosing an execution path for a simple two-table join between an orders table and an order_lines table that I had described. In that installment I supplied the DDL for the tables and its indexes; in this installment I’m going… Continue Reading →

All Things Oracle Full Articles, Oracle Database

This series on Oracle’s Cost Based Optimizer is aimed at the less experienced DBAs and developers to help them understand what the optimizer is trying to achieve, how it arrives at an execution plan, why it makes mistakes and, perhaps most importantly, how to recognize the source of those mistakes and so address the resulting problems in an appropriate fashion…. Continue Reading →

All Things Oracle Full Articles, Oracle Database, Performance Tuning

This is the last part of my series on Execution plans, and features an option which is only available if you have licensed the Diagnostic and Performance Packs. It’s a feature that allows you to watch the flow of data through an execution plan as the query is running, typically through the graphic interface supplied by Enterprise Manager  (or Grid… Continue Reading →

All Things Oracle Full Articles, Database Administration, Database Development, Oracle Database

In parts 11 and 12 of this series I described the “rowsource execution statistics” that we can collect as Oracle runs a query, then described a strategy for generating and accessing these statistics in a way that was particularly convenient if you could use your own session to run the SQL you wanted to analyze. In this article we’re going… Continue Reading →

11g, 12c, All Things Oracle Full Articles, Database Administration, Database Development, Oracle Database

In the previous instalment of this series I introduced three ways of accessing the run-time statistics for a query and described, for one of the methods, the basics of the information we can get and how we can use it. In this article I want to expand on the use of one method to show it can help use identify… Continue Reading →

All Things Oracle Full Articles, Database Administration, Database Development

So far in this series we’ve talked about interpreting the shape of an execution plan and understanding the meaning of the predictions that the optimizer has made about cost and cardinality. It’s finally time to see how Oracle gives us execution plans that show us how well the optimizer’s estimates match the actual work done as the query ran. There… Continue Reading →

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

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