Analyzing and Troubleshooting Oracle Parallel Execution

Randolf Geist on 17 April 2013 with 4 comments

  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 →

HAVING Cardinality

Randolf Geist on 21 January 2013 with 0 comments

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 →

Oracle Cost-Based Optimizer Advanced

Randolf Geist on 02 August 2012 with 2 comments

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 →

Incremental Partition Statistics Review

Randolf Geist on 01 August 2012 with 0 comments

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 →

Free Webinar: Oracle Cost-Based Optimizer Advanced Session

Randolf Geist on 27 July 2012 with 2 comments

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 →

Dynamic Sampling (III) – Real-Life Data – Part II

Randolf Geist on 05 July 2012 with 0 comments

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 →

Dynamic Sampling (III) – Real-Life Data – Part I

Randolf Geist on 20 June 2012 with 2 comments

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 →

Nested Loop Join Costing

Randolf Geist on 14 June 2012 with 0 comments

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 →

Oracle Cost-Based Optimizer Basics

Randolf Geist on 10 April 2012 with 2 comments

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

Dynamic Sampling (II) – Controlling the Activity – Part 2

Randolf Geist on 15 March 2012 with 1 comments

In the first part of this post I’ve provided a table that summarizes the Dynamic Sampling levels on session / system level. In the “Blocks Sampled” column there were numbers in brackets for the level 3 and 4 description. In the second part of this post I’ll explain why. DYNAMIC_SAMPLING_EST_CDN In the presence of statistics Dynamic Sampling behaves differently: For… Continue Reading →