Randolf Geist

Author

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

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

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

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

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

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

11g, Database Development, Oracle Database