OVERVIEW

BY NEED

BY ROLE

BY INDUSTRY

Jonathan Lewis

Jonathan Lewis is well-known in the Oracle world as a freelance consultant with 22 years of experience with the Oracle RDBMS engine. His specialist skills are in the area of physical database design, and solving performance issues. 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.

Jonathan is the author of 'Cost Based Oracle – Fundamentals' published by Apress, and 'Practical Oracle 8i – Designing Efficient Databases' published by Addison-Wesley, and has contributed to three other books about Oracle. His blog is at http://jonathanlewis.wordpress.com, where his first note on SQL Server 2008 appears at: http://jonathanlewis.wordpress.com/2010/02/04/sql-server/
11 March 2015
11 March 2015

Execution Plans Part 13: SQL Trace

2
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
2
17 December 2014
17 December 2014

Execution Plans Part 11: Actuals

1
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
1
09 September 2014
09 September 2014

Execution Plans Part 10: Guesswork

1
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
1
29 August 2014
29 August 2014

Execution Plans Part 9: Multiplication

1
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
1
19 August 2014
19 August 2014

Execution Plans Part 8: Cost, time, etc.

6
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
6
09 July 2014
09 July 2014

Execution Plans Part 7: Query Blocks and Inline Views

3
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
3
16 June 2014
16 June 2014

Execution Plans Part 6: Pushed Subqueries

1
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
1
14 May 2014
14 May 2014

Execution Plans Part 4: Precision and Timing

4
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
4
25 April 2014
25 April 2014

Execution Plans Part 3: “The Rule”

4
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
4
26 March 2014
26 March 2014

Execution Plans Part 2: Things to see

8
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
8
12 March 2014
12 March 2014

Execution Plans: Part 1 Finding plans

7
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
7
16 October 2013
16 October 2013

Histograms Part 3 – When?

0
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
0
01 October 2013
01 October 2013

Histograms Part 2

5
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
5
28 August 2013
28 August 2013

Histograms Part 1 – Why?

6
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
6
27 February 2013
27 February 2013

Compression in Oracle – part 5: Costs of Index Compression

1
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
1
13 February 2013
13 February 2013

Compression in Oracle – part 4: Basic Index Compression

2
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
2