OVERVIEW

BY NEED

BY ROLE

BY INDUSTRY

Jonathan Lewis

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.
10 June 2015
10 June 2015

Basics of the Cost Based Optimizer – Part 1

3
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
3
26 March 2015
26 March 2015

Execution Plans Part 14: SQL Monitoring

0
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
0
11 March 2015
11 March 2015

Execution Plans Part 13: SQL Trace

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

Execution Plans Part 11: Actuals

3
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
3
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.

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

Execution Plans Part 7: Query Blocks and Inline Views

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

Execution Plans Part 6: Pushed Subqueries

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

Execution Plans Part 4: Precision and Timing

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

Execution Plans Part 3: “The Rule”

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

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