Oracle Cost-Based Optimizer Basics

Randolf Geist on 10 April 2012 with 0 comments

This video and set of resources is a follow up to the webinar broadcast by Red Gate on 11 Apr 2012. 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) optimizer_basics_inter_table_clustering_testcase.sql optimizer_basics_join_cardinality_testcase.sql… 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 →

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

Randolf Geist on 08 March 2012 with 2 comments

I’ve closed the introduction to Dynamic Sampling with an odd test case result: Although all table data was read by Dynamic Sampling the cardinality estimate of the optimizer was still way off – not quite what one might expect. The reason for this odd behaviour can be explained by the fact that Dynamic Sampling behaves differently depending on whether statistics… Continue Reading →

Dynamic Sampling (I), an Introduction – Part 2

Randolf Geist on 08 February 2012 with 0 comments

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

Dynamic Sampling (I), an Introduction – Part 1

Randolf Geist on 30 January 2012 with 0 comments

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