Performance Tuning

Category

Friday December 5th, 2014, 10:00 AM – 11:00 AM CST Register Description: It’s usually not difficult to find SQL that is performing poorly. The hard part is what to do with a bad SQL statement once you have it. In this session, Karen Morton will show you how to use a simple method that is reliable and repeatable for solving any problem… Continue Reading →

Performance Tuning, Webinar

In the final part of this series I want to cover one last source of Parallel Execution Skew: Skew caused by Outer Joins. If you have Outer Joins where the majority of foreign key values is NULL, this can lead to the following symptoms: From a processing point of view for a parallel outer join, the NULL value in the foreign… Continue Reading →

10g, 11g, 12c, All Things Oracle Full Articles, Database Administration, Database Development, Oracle Database, Performance Tuning, Troubleshooting

In this part of the series we will have a look at different approaches to how Parallel Execution Skew can be addressed via manual re-writes of an affected query. Let’s have a look at a couple of options. Approach 1: Mimic the new 12c skew aware feature Looking at the new 12c special skew aware distribution feature that I’ve introduced… Continue Reading →

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

Since Parallel Execution Skew is a common problem that can severely limit the scalability of Parallel Execution (see my previous article for a demonstration), Oracle has introduced a new feature in 12c that can address the problem automatically. That’s great, but unfortunately – at least in the initial 12c release – it only applies to a limited number of scenarios,… Continue Reading →

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

In part 3 of this series we used a simple hash join to introduce the a simple guideline for reading execution plans – the “first child first, recursive descent” method. This allowed us to work out the order in which Oracle produced rowsources and (implicitly) the order in which it visited the different physical objects in the query. At the… Continue Reading →

All Things Oracle Full Articles, Oracle Database, Performance Tuning

In the first two articles in this series we listed a couple of methods for generating or retrieving execution plans and learned a few extra steps that could increase our confidence that we were using the right environment to investigate any problems we might have with a plan. In this article we’re going to become acquainted with a basic (though, as… Continue Reading →

All Things Oracle Full Articles, Oracle Database, Performance Tuning

After providing a baseline for Parallel Execution working as expected in the introduction of this series, in this part I’ll demonstrate how things can go wrong with the work distribution. A Simple Example Of Parallel Execution Skew One common source of Parallel Execution Skew is a skewed foreign key, which means that the value distribution of the foreign key column… Continue Reading →

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

It is often easy to forget this, but in many ways it is after we hit the execute button that the really exciting stuff starts with our code. A number of engines silently spring into action; including the optimizer. The optimizer analyses your SQL statement and decides the most efficient way to execute it based on the objects involved in… Continue Reading →

All Things Oracle Full Articles, Performance Tuning

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