JL Computer Consultancy

Cost Based Oracle: Fundamentals
Publisher: Apress


 

(In association with Amazon.com and Amazon.co.uk).

If you want to understand how Oracle’s Cost Based Optimizer works, you will want to read this book. To get you started, there is a pdf of Chapter 5 (Clustering Factor) that you can download from Apress.

With special thanks to the technical reviewers: Wolfgang Breitling and Christian Antognini. Thanks also to Tom Kyte for the foreword.

The CBO is an extraordinary piece of software, but it depends on a model and it depends on a number of assumptions about how that model will behave at run-time. Sometimes the model isn’t realistic enough; sometimes the run-time activity doesn’t quite match the model. And just to make things that little bit harder to understand, sometimes the model, the run-time action, or both, will change as you upgrade your version of Oracle.

The first volume of Cost Based Oracle covers the fundamentals of Oracle’s optimizer. The topic is just too big for me to be able to tell you all you need to know about every possible circumstance and every version – but this book gets you going with 90% of what you need to know for 90% of the SQL you will write. Not only does it explain the models used, the calculations, and the things that can happen at run-time; it gives you scripts so that you can reproduce the test-cases on your own systems and see the changes that appear as you change parameter values, enable or disable features, add hints, or upgrade your version of Oracle.

The book is backed by a ‘code-depot’ of the sample scripts that belong with each chapter. On publication, the scripts will be available as a single zip file (approx. 500KB) on the Apress website and on my own website. Note – this file will be updated from time to time with any interesting odds and ends that I get as feedback (last update 30th Nov 2005). I will also be publishing the scripts chapter by chapter in each of the chapter headings below – but only as I update anything in any of the chapters.


Cost Based Oracle: Fundamentals

(9th Jan 2006: Now available as an e-book from Apress)

Addenda and Errata

Chapter

Last addendum

Title

Comments

Preface

22nd Nov 2005

Never say “never again”

When I finished Practical Oracle 8i, I said “never again”. But I succumbed to temptation. It’s taken me nearly two years to write this book; Oracle Corp. has brought out a whole new version whilst I was doing it – and things keep changing. The preface exists to tell you what’s not in Volume 1.

Chapter 1

27th Feb 2006

What do you mean by cost?

You have to be very careful when talking about optimization and query performance; it’s too easy to mean two different things when you talk about the cost – the actual resource consumption, or the estimate of resource consumption. So this chapter is a little warning about the need for careful language.

Chapter 2

12th June 2006

Tablescans

It’s amazing how much you can say about tablescans (and index fast full scans); and surprising how much of the whole optimization strategy you can learn with this most simple of execution plans.

Chapter 3

1st April 2006

Single table selectivity

How many rows will a simple query produce? This is one of the cornerstones of picking a good execution plan. The cardinality (number of rows) is derived from the selectivity (fraction of rows) – and it is surprising how many variations there are in the calculation of selectivity – even for a single table.

Chapter 4

24th March 2006

Simple B-tree access

If you’ve got the selectivity right, you can probably make a sensible decision about whether or not to use an indexed access path or a tablescan. This chapter gets you going with simple B-tree indexes.

Chapter 5

10th Feb 2006

The Clustering Factor

The most critical measure of the perceived usefulness of a B-tree index is the clustering factor. This chapter tells you what it is, how Oracle calculates it, how the calculation can be totally misleading, and what to do if it is.

Chapter 6

26th Aug 2012

Selectivity Issues

Chapter three was a simple introduction to selectivity – but there are plenty of quirks, oddities and traps. This chapter looks at the commonest ways that Oracles produces an unsuitable selectivity, and how the code keeps changing with each version of the optimizer.

Chapter 7

13th Sept 2012

Histograms

There are good reasons for creating histograms on a few columns in the database. Unfortunately, the commonest reason quoted for creating them is not the best reason for creating them. Histograms can help the optimizer handle some of the situations where the normal selectivity calculations go wrong – it’s much more subtle than just choosing between indexes and tablescans.

Chapter 8

30th Dec 2005

Bitmap Indexes

There are a couple of critical differences between B-tree indexes and Bitmap indexes. It’s easy to overlook these differences – but the most significant one is that the clustering_factor for bitmap indexes does not mean the same thing as it does for B-trees – so something has to replace it. Oracle then has a special strategy for combining bitmap indexes that needs its own costing model.

Chapter 9

24th March 2006

Query Transformation

Before the optimizer starts to do arithmetic, it may restructure your query in a variety of ways. This restructuring is generally known as query transformation. This chapter describes a few of those transformations – and then points out that one of the enhancements in 10g means that the optimizer might actually do several sets of arithmetic with a single query: some before, and some after transforming your query.

Chapter 10

1st Dec 2009

Join Cardinality

Finally, 10 chapters into a book on optimization, we get to a table join. There really is that much to do in preparation for this event. This chapter examines the way that the cardinality (rather than the selectivity, but the change of target is not significant) is worked out. The cardinality doesn’t change with the join method, of course. Each join method gets its own chapter where we can focus exclusively on cost.

Chapter 11

10th Jan 2006

Nested Loops

The simplest join mechanism to understand – it’s just a short chapter, but even here there are a couple of surprises that you may not have come across before.

Chapter 12

 

Hash Joins

Although the hash join appeared in Oracle long after the sort/merge join, I’ve tackled it before the sort/merge – partly because it’s easier to deal with, partly because it seems to be used so much more than the sort/merge in modern systems. In this chapter, we see that the model doesn’t really match the run-time mechanism; moreover that the cost calculations change quite sharply with version and enabled features.

Chapter 13

1st Dec 2009

Sorting and Merge Joins

Sort/Merge joins exhibit features that you can’t get with hash joins. You can do Cartesian merge joins (and sometimes the optimizer does, even when you don’t think your SQL is supposed to); and you can do range-based joins with sort/merge joins. The cost calculations for such things show some surprising behavior. But sort/merge joins are just like hash joins in some ways: the cost calculations don’t match the run-time mechanism, and the results change dramatically with version and enabled features.

Chapter 14

 

The 10053 trace file

A sample trace file from 10g (release 1), showing the output from a join of four tables, with commentary explaining many of the details of the output.

Appendix A

1st April 2006

Upgrade Headaches

This may be the most important bit of the book for many people. There are many changes to the optimizer as you upgrade from 8i to 9i, and again from 9i to 10g. Some of these changes make a surprising difference to the execution paths you will see. Many of the critical changes are described throughout the book, but this appendix pulls all those changes into one brief summary.

Appendix B

25th May 2010

Optimizer Parameters:

There are numerous parameters that have some impact on the optimizer. But you get different lists depending on the method you use to ask for them. This appendix is a quick reference to the most significant parameters, and the way their defaults change across versions of Oracle.

Back to Home Page.