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… Read more
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… Read more
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,… Read more
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… Read more
This is the first part of a mini-series about Parallel Execution Skew and how to address it. Introduction So what does “Parallel Execution Skew” mean? With Oracle Enterprise Edition you can have Oracle automatically spread the processing of a single SQL statement across several worker processes in the hope to complete the processing in less elapsed wall-clock time. Note: T… Read more
Watch video The video above is accompanied by the following resources: Analysing and Troubleshooting Parallel Execution presentation slides (.pdf) Further reading: Calculating Parameter Values for Parallelism Auto DOP and Concurrency Auto DOP and Queuing Parameter Settings Auto DOP and Parallel Statement Queuing Workload Management – Statement Queuing Workload Management – A Simple (but real) Example Parallel_degree_limit hierarchy – CPU,… Read more
When performing aggregate GROUP BY operations an additional filter on the aggregates can be applied using the HAVING clause. Usually aggregates are one of the last steps executed before the final result set is returned to the client. However there are various reasons, why a GROUP BY operation might be somewhere in the middle of the execution plan operation, for example it might be part of… Read more
This video and set of resources is a follow up to the webinar broadcast by Red Gate on 01 August 2012. You may want to watch Oracle Cost-Based Optimizer Basics before you view this video Watch video The video above is accompanied by the following downloadable resources: Read me Presentation slides – PDF version Presentation slides – PowerPoint version concurrent_execution_inserts_check_results.sq… Read more
Here is a summary of the findings while evaluating Incremental Partition Statistics that have been introduced in Oracle 11g. The most important point to understand is that Incremental Partition Statistics are not “cost-free”, so anyone who is telling you that you can gather statistics on the lowest level (partition or sub-partition in case of composite partitioning) without any noticeable overhead… Read more
In the previous installment of this series we saw that Dynamic Sampling wasn’t of great help when dealing with certain types of data patterns. We tried to add a suitable index to improve the Dynamic Sampling results, but no luck so far. The solution in this case is that with statistics in place the index only gets used when adding… Read more
In the previous parts of the series I outlined the basics on Dynamic Sampling. Let’s see how Dynamic Sampling deals with typical real-life data patterns. As you might be aware, quite often real data follows certain patterns for how data is clustered together, usually related to how the data arrives. This clustering (or scattering) of data within a table can… Read more
The basic formula for calculating the costs of a Nested Loop Join is pretty straightforward and has been described and published several times. In principle it is the cost of acquiring the driving row source plus the cost of acquiring the inner row source of the Nested Loop as many times as the driving row source dictates via the cardinality… Read more
This video and set of resources is a follow up to the webinar broadcast by Red Gate on 11 Apr 2012. Watch video The video above is accompanied by the following downloadable resources: Read me Presentation slides – PDF version Presentation slides – PowerPoint version Screenshots – Demo 1 (.pdf) Screenshots – Demo 2 (.pdf) Screenshots – Demo 3 (.pdf)… Read more
If you follow me on my blog you might wonder if I let you get away without an actual test case, and of course I won’t do so. Here is a simple test case that demonstrates Dynamic Sampling in action: set echo on timing on time on drop table t; purge table t; — Create a sample table create tab… Read more
Welcome to my first post on AllThingsOracle.com! This is the initial part of a series highlighting different aspects of “Dynamic Sampling”. So what is Dynamic Sampling? Probably most of you have already heard of it as a feature that deals with tables that don’t have any statistics gathered for – so it’s a feature of the Cost-Based Optimizer that has been… Read more