Blog Archives

January 20, 2017

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

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