Jonathan Lewis

Author

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

All Things Oracle Full Articles, Database Administration

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

All Things Oracle Full Articles, Database Administration, PL/SQL

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

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

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

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

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 review their potential for giving you stability problems in your execution plans. This overview will be limited… Continue Reading →

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

  Watch now While the primary focus of both Oracle and SQL Server is on relational database management, there are a number of tools that come with each software package that are focused around monitoring and investigating the performance of the servers. These monitoring tools vary wildly between the two platforms, yet still have a lot in common because of… Continue Reading →

Database Administration, Database Development, Webinar

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

All Things Oracle Full Articles, Database Administration, Database Development, Oracle Database, Performance Tuning

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

11g, All Things Oracle Full Articles, Database Administration, Database Development, Oracle Database, Performance Tuning

In part two of this series we examined the effect of updates on compressed data when using basic compression, and saw how Oracle will do some “decompression” of a row before updating it with the effect that highly compressed rows might easily become so much larger that even a small amount of change could lead to a large number of… Continue Reading →

11g, All Things Oracle Full Articles, Database Development, Oracle Database, Performance Tuning

In part one of this series we saw that the mechanics of basic table compression came in to play only for direct path inserts, CTAS, and “alter table move”. We also saw that Oracle would, by default, set the free space percentage (pctfree) for a table to zero if the table were defined as compressed – giving us a hint… Continue Reading →

11g, All Things Oracle Full Articles, Database Development, Oracle Database, Performance Tuning