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.
17 February 2016
17 February 2016

Massive Deletes – Part 2

0
2
In Part 1 of this short series I provided an informal description of a couple of scenarios where we might do a large-scale delete from a table. Without a concrete example though, it can be hard to imagine how the nature of the data deleted and the access paths available could affect the impact a large delete operation could hav… Read more
0
2
29 January 2016
29 January 2016

Massive Deletes – Part1

0
3
In a recent article about dropping columns from a table I left hanging the question of what you might do after you have dropped the column, because dropping a column is really nothing more than a special case of the “massive delete”. In this article, then, I’m going to lay the groundwork for how we ought to think around t… Read more
0
3
14 January 2016
14 January 2016

Dropping Columns

0
5
I have my Oracle Support account configured to send me a daily email of “hot topics”, and I’ve set it up so that the email lists the 100 bugs that have been updated most recently by Oracle support. One of the bugs that came up on 9th Jan was described as: “Bug 18700681 : DROP COLUMNS USING ALTER TABLE VERY… Read more
0
5
04 November 2015
04 November 2015

Basics of the Cost Based Optimizer – part 6

0
0
In part 5 of this series I started working through a list of queries designed to demonstrate ways in which the optimizer can produce bad cardinality estimates. In this installment I’m going work through the remainder of the list. The outstanding queries are as follows: select count(*) from t1 where trunc_5 > ( select max(trunc_5) from t1 where mod_200 =… Read more
0
0
09 July 2015
09 July 2015

Basics of the Cost Based Optimizer – Part 4

0
3
In the first three installments of this series I outlined the mechanisms used by Oracle to analyze a very simple query. Sticking with the informal approach, I’m going to look at the high-level strategy the optimizer takes when handling a more complex query. To do this I’ll be looking at query blocks, scratching the surface of query transformation, explaining why it’s a… Read more
0
3
17 June 2015
17 June 2015

Basics of the Cost Based Optimizer – Part 2

0
0
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… Read more
0
0
10 June 2015
10 June 2015

Basics of the Cost Based Optimizer – Part 1

0
5
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
0
5
26 March 2015
26 March 2015

Execution Plans Part 14: SQL Monitoring

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

Execution Plans Part 13: SQL Trace

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

Execution Plans Part 11: Actuals

0
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
0
3
09 September 2014
09 September 2014

Execution Plans Part 10: Guesswork

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

Execution Plans Part 9: Multiplication

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

Execution Plans Part 8: Cost, time, etc.

0
9
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
0
9
09 July 2014
09 July 2014

Execution Plans Part 7: Query Blocks and Inline Views

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