Jonathan Lewis

Author

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… Continue Reading →

10g, 11g, 12c, All Things Oracle Full Articles, Oracle Database, Troubleshooting

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… Continue Reading →

All Things Oracle Full Articles, Oracle Database, Performance Tuning, Troubleshooting

Unpivot

Jonathan Lewis on 08 December 2016 with 0 comments

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’… Continue Reading →

All Things Oracle Full Articles

Hacking Indexes

Jonathan Lewis on 03 October 2016 with 3 comments

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… Continue Reading →

All Things Oracle Full Articles, Performance Tuning, Troubleshooting

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 be… Continue Reading →

All Things Oracle Full Articles, Oracle Database, Troubleshooting

Text Indexes

Jonathan Lewis on 05 September 2016 with 4 comments

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: create… Continue Reading →

All Things Oracle Full Articles

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 the… Continue Reading →

12c, All Things Oracle Full Articles

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 the… Continue Reading →

All Things Oracle Full Articles, Performance Tuning

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 have… Continue Reading →

12c, All Things Oracle Full Articles

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 the… Continue Reading →

12c, All Things Oracle Full Articles