Randolf Geist

Author

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

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

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

All Things Oracle Full Articles

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

Database Development, Oracle Database, Performance Tuning, Troubleshooting, Webinar

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

Database Development, Performance Tuning

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

Database Development, Oracle Database, Performance Tuning, Video, Webinar

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

11g, Database Administration, Database Development, Oracle Database

On Wednesday, 1st of August, I’ll be presenting another free webinar hosted by AllThingsOracle.com. Although it is called “Oracle Cost-Based Optimizer Advanced Session“, don’t be mislead by the title. It is not a truly “advanced” session, but rather I’ll try to delve into various topics that I could only mention briefly or had to omit completely during the first webinar on the Cost-Based Optimizer…. Continue Reading →

Database Development, Performance Tuning, Webinar