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/
24 January 2017
24 January 2017

Snapshots

3
While looking at the results from a query against the dynamic performance view v$active_session_history during a recent trouble-shooting assignment I noticed a result that was so unusual that I decided I needed to get some information from the v$ash_info view over an interval of about 3 minutes – and it didn’t matter which three minutes in particular, any three (consecutive)… Read more
3
24 January 2017
24 January 2017

Index Costing Threat

1
In the previous post I described a problem with the strategy that the optimizer uses for costing an index fast full scan, and the alternative strategy that Oracle had supplied at some point in the 10g timeline to allow a more rational cost to be calculated in special cases. In an earlier post still I had described the problem wit… Read more
1
20 January 2017
20 January 2017

Problems with Indexes

1
In my book “Cost Based Oracle – Fundamentals” (Apress 2005) I described a problem with the cost calculation for the index fast full scan: When you generate statistics for a table, one of the results is the number of blocks below the high water mark. When you generate the statistics for an index, you get the number of leaf blocks… Read more
1
09 January 2017
09 January 2017

Investigating Indexes – 2

1
In an earlier article on investigating indexes I described some methods for looking at the internal structure of an index to get an idea of its current state of health. Although this type of knowledge helps you to make informed decisions about which indexes might need special treatment (like being permanently dropped, perhaps) it doesn’t immediately answer questions like: “what’s… Read more
1
08 December 2016
08 December 2016

Unpivot

0
One of the sessions at the recent annual UKOUG technical conference (Tech16) was a panel session on optimisation for which I had posted a request for a few advance questions, and one of the questions that appeared in my inbox was as follows: I have a report which is a union all of 27 versions of the statement: select ‘ColumnX_name’… Read more
0
03 October 2016
03 October 2016

Hacking Indexes

0
Indexes are expensive to maintain so we want to get the greatest benefit we can from them and make sure that Oracle uses them whenever it’s appropriate. Sometimes, though, the optimizer seems to ignore indexes that we think would be very effective and when this happens it’s often because the statistics that Oracle has gathered do not reflect the quality… Read more
0
28 September 2016
28 September 2016

Investigating Indexes

0
Rebuilding indexes is an activity that you shouldn’t need to do often. There are always a few special cases – like when you’ve moved or recreated a table or if you’ve done a massive delete on a table – when it’s probably a reasonable idea but, in general, there are very few cases where there’s any great benefit to b… Read more
0
05 September 2016
05 September 2016

Text Indexes

1
The Oracle database has many features that are not spoken of frequently and a recent note by Connor McDonald reminded me that there are a couple of notes about optimising Text indexes that have been on my “must write” list for several years. If you’re not familiar with what text indexes can do for you, here’s a thumbnail sketch: creat… Read more
1
05 September 2016
05 September 2016

Massive Deletes – Part 4

3
In the previous installment of this series I produced some figures highlighting the main differences between doing a large delete by tablescan and doing a large delete by index range scan. Depending on the data patterns involved the correct choice of strategy could make a significant difference to the number of random I/Os, the volume of redo generated, and t… Read more
3
05 April 2016
05 April 2016

Massive Deletes – Part 3

2
In the previous installment of this series we looked at methods for seeing the pattern of the changes that would appear in a table and its indexes after a large delete. In this installment we move on to the workload implied by different patterns, then think about strategies or, indeed, the need for strategies for reducing that workload at t… Read more
2
17 February 2016
17 February 2016

Massive Deletes – Part 2

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
2
29 January 2016
29 January 2016

Massive Deletes – Part1

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
3
14 January 2016
14 January 2016

Dropping Columns

2
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
2
04 November 2015
04 November 2015

Basics of the Cost Based Optimizer – part 6

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
09 July 2015
09 July 2015

Basics of the Cost Based Optimizer – Part 4

1
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
1
17 June 2015
17 June 2015

Basics of the Cost Based Optimizer – Part 2

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