| |
|
|
|
|
|
|
|
|
Wednesday, October 28th 2009 |
|
|
|
|
|
8:00 AM - 9:00 AM |
|
Check-in and complimentary breakfast |
|
|
|
|
|
|
|
|
|
9:00 AM - 10:30 AM |
|
Why isn't Oracle using my index? - 1.5 hours |
|
|
|
|
In this session we aim to acquire a visual, rather than highly mathematical, understanding of when Oracle will ignore an index. We start with a discussion of the strategic direction that we should be going with the cost based optimizer and then examine a simple example to pin-point the traditional reasons why the cost based optimizer in versions prior to 9.0 could so easily produce inappropriate execution plans. We examine a couple of commonly used tuning parameters which give Oracle better information about our system and allow it to identify the appropriate execution path more frequently, identifying the risks of using these parameters too extravagantly, and end with a demonstration of how using CPU costing solves the traditional problems with much less risk of error. |
|
|
|
|
|
|
|
|
|
10:30 AM - 11:00 AM |
|
Coffee and informal discussion: - 30 minutes |
|
|
|
|
|
|
|
|
|
11:00 AM - 12:30 PM |
|
Mechanisms of Joins - 1.5 hours |
|
|
|
|
We start by asking why it is so important to examine and understand join mechanisms and examine some of the transformations that convert complex queries to simple join forms. Then we look at details (and anomalies) of the nested loop, sort-merge, and hash joins; identifying strengths, weaknesses, and costs of each in turn. In the case of sort-merge and hash joins, we examine trace events, and their output, that allow us to investigate what is happening when response times become extreme. |
|
|
|
|
|
|
|
|
|
12:30 PM - 1:30 PM |
|
Lunch and informal discussion: - 1 hour |
|
|
|
|
|
|
|
|
|
1:30 PM - 3:00 PM |
|
Selectivity, Joins, and Hints: - 1.5 hours |
|
|
|
|
In this session we move on from the simple example of the first session to investigate what happens in the more general cases of using an index. We extend the arithmetic to the calculations involved in joining tables, and note some of the anomalies and problems that joins can cause. Finally we ask the question "What is a hint?" and try to answer the question by examining what really happens inside the optimizer when we start adding hints to our SQL. |
|
|
|
|
|
|
|
|
|
3:00 PM - 3:30 PM |
|
Coffee and informal discussion: - 30 minutes |
|
|
|
|
|
|
|
|
|
3:30 PM - 5:00 PM |
|
Maximising the Truth: - 1.5 hours |
|
|
|
|
If there is any information we can give the optimizer about our data, we should do so; otherwise it will be less able to produce the appropriate execution.
We start this session by showing how a little extra information can help the optimizer find new execution paths. Then we examine the optimizer's dependence on statistics, and its need to get a correct numeric representation of your data. We move on to see how strange data patterns, and bad database design, can stop the optimizer from choosing a sensible execution path, and end with a couple of techniques for encouraging the optimizer to do what we want. |
|
|
|
|
|
|
|
|
View all Seminars |
