Jonathan Lewis is a well-known figure in the Oracle world with more than 30 years’ experience using the software. He has published three books about Oracle – the most recent being “Oracle Core” published by Apress Nov 2011 – and contributed to three others. He has posted more than 1,400 articles on his blog and has spent a lot of his time contributing to newsgroups, forums, and User Group magazines and events around the world.
Jonathan has been self-employed for most of his time in the IT industry. For the last 25 years he has specialised in short-term assignments – typically of a design, review, or troubleshooting nature – often spending no more than two or three days on a client site to address problems. After visiting more than 50 countries (and more than a dozen US states) to talk about or troubleshoot Oracle systems, he has finally gone into near-retirement, though still writes articles and does a little consulting over the internet.
Despite the differences in the software, he finds that the fundamental principles of solving performance issues don't really seem to change as you move from Oracle to SQL Server.
His blog can be found at http://jonathanlewis.wordpress.com.
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… Read more
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… Read more
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… Read more
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… Read more
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. Ther… Read more
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’r… Read more
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… Read more
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 referenc… Read more
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 engin… Read more
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… Read more
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 st… Read more
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 t… Read more
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… Read more
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… Read more
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… Read more
In part 2 we looked at the way that Oracle collects and uses height –balanced histograms, and raised the problem of how sensitive they could be to small changes in the sampling, or in the data. We ended the article with a suggestion on how you could generate data for an “approximate” frequency histogram that could be “good enough” to… Read more
In part 1 of this series we discussed the reasons why we might want to create some histograms to help the optimizer deal with skewed data distribution. We used an example of a simple status column in an orders table to demonstrate principles, talked about frequency histograms in particular, and highlighted a couple of problems associated with histograms. In part… Read more
In this short series on histograms we will be looking at the reasons why we need histograms and the methods Oracle has to create them. We will examine the costs of creating them and the potential they introduce for undesirable overheads and then we will review their potential for giving you stability problems in your execution plans. This overview w… Read more
In the previous article in this series we looked at index compression, and the way in which Oracle stored index data for a compressed index, and we discovered that Oracle keeps two row directories the “main” directory and the “prefix” directory with slightly different structures even though there is still only a single “row heap”. We also examined the way… Read more
In the first three parts (Part 1: Basic Table Compression, Part 2: Read-Only Data, Part 3: OLTP Compression) of this series we examined table compression – both basic and the separately licensed OLTP compression. In this article we move on to index compression which, as we shall see, uses the same “deduplication” techniques as table compression but includes a cou… Read more