Jonathan Lewis “Basic Cost Based Optimization”  - July 25, 2013

 

Agenda

 

The webinar is for those who wish to learn more about how the optimizer manages to choose an execution plan and why it can choose the wrong one. In it we will examine a simple data set to highlight just three critical aspects of query optimisation in Oracle:

1    The work done acquiring data is dependent on the volume of the data, the location of the data, and the caching of the data.

2    The Cost Based Optimizer bases its cost calculation on its perception of the volume (cardinality) and location (clustering) of the data – and this dictates its choice of whether or not to use an index to access data, and which index to use.

3    The optimizer’s model is flawed in several critical ways, which leads to poor correlation between the calculated cost of a plan and the resources used executing that plan.

The more we come to recognize the flaws in the model, the easier it becomes to understand why the optimizer had chosen the wrong path.