Editorial Review
The insights that Jonathan provides into the workings of the cost-based optimizer will make a DBA a better designer, and a Developer a better SQL coder. Both groups will become better troubleshooters.
— Thomas Kyte, VP (Public Sector), Oracle Corporation
The question, "Why isn't Oracle using my index?" must be one of the most popular (or perhaps unpopular) questions ever asked on the Oracle help forums. You've picked exactly the right columns, you've got them in the ideal order, you've computed statistics, you've checked for null columnsand the optimizer flatly refuses to use your index unless you hint it. What could possibly be going wrong?
If you've suffered the frustration of watching the optimizer do something completely bizarre when the best execution plan is totally obvious, or spent hours or days trying to make the optimizer do what you want it to do, then this is the book you need. Youll come to know how the optimizer "thinks," understand why it makes mistakes, and recognize the data patterns that make it go awry. With this information at your fingertips, you will save an enormous amount of time on designing and trouble-shooting your SQL.
The cost-based optimizer is simply a piece of code that contains a model of how Oracle databases work. By applying this model to the statistics about your data, the optimizer tries to efficiently convert your query into an executable plan. Unfortunately, the model can't be perfect, your statistics can't be perfect, and the resulting execution plan may be far from perfect.
In Cost-Based Oracle Fundamentals, the first book in a series of three, Jonathan Lewisone of the foremost authorities in this fielddescribes the most commonly used parts of the model, what the optimizer does with your statistics, and why things go wrong. With this information, youll be in a position to fix entire problem areas, not just single SQL statements, by adjusting the model or creating more truthful statistics.
Cached date: AWS Called=true
Customer Reviews
Excellent Book! Recommended! 
2006-09-14
I bought the book "Cost-Based Oracle Fundamentals" and I found it extremely helpful and I've been recommending to other people - both DBA's and SQL writers. I've been pretty good at tuning problem SQL in the past mostly by knowing (or learning) the data and forcing the best plan using hints. But the row estimates/cost figures that the optimizer comes up with have always been a mystery to me. This book explains a lot of what I have been seeing. (The answer is 5%!)
The book is very well laid out and it meant to conceptually show how the optimizer makes decisions and how it can be mislead into making wrong decisions. It also spells out many situations where the optimizer is making a SWAG and indicates where you should pay attention to the explain plans and perhaps hint them so they run properly.
With the suggestions in the book, I have made great strides in fixing our database so that we get rational explain plans from the optimizer. However, we have so many queries based on SYSDATE calculations that I now realize that many of the row estimates (5%) are crap and that frequently this is what has been causing us issues. (I knew that the row estimates were the issue. I just never knew where the harebrained estimates actually came from. Now I can say, "Oh, 7 million. That's 5% of 152 million rows. The optimizer doesn't know how many rows you're bringing back for SYSDATE and SYSDATE-1, so it's using 5%")
Thanks to this book, I can now also show that half the time the CBO has been picking the right plan for the wrong reasons! Who says, "Two wrongs don't make a right?" They do sometimes. Now, as I untangle all this, I have to worry about Oracle suddenly selecting the wrong plan for only one wrong reason! But I've at least been able to explain why the performance of some SQL seems to periodically go haywire and then fix itself without any changes having been made to the SQL.
I have so many more answers and explanations now that when people see the yellow and black book on my desk, they ask me, "Is this the book?" I look forward to the next two volumes in the series.
How much of it is useful ??? 
2006-07-08
I took two days and went through the book. Sounds more like
R&D book rather than something which I am going to use. If you want to learn good SQL tuning, stick with Guy Harrison - it is more practical.
deep and detailed, yet surprisingly easy to follow 2006-06-13
Outstanding and excellent book - and I'm saying this after six months of reading and re-reading, and running and studying the provided scripts, and even performing my own investigations starting from the book's test cases.
It has been surprisingly easy to understand the material, even if I didn't know most of the things discussed. Reading has flown seamlessly, not exactly like reading a novel, but close enough for a technical book about the very complex CBO - and that was something I didn't expect at all, a very very nice surprise.
The most fundamental topics (the first ones are selectivity, cardinality, etc) being right at the beginning, I didn't need to read the whole book to start using the new knowledge effectively in practice, thus getting the "morale boost" that motivated me to read the next chapter, and then the next ... adding layer after layer of knowledge.
I also *loved* the high level of detail, which is absolutely necessary to understand the complex CBO, and the precise and succinct writing style as well, which makes for an easier and quicker learning. When speaking about mathematical subjects (since the CBO is just that of course - a mathematical model), it's mandatory to be precise and detailed: vague statements would only add confusion or, worstly, misunderstanding.
To recap - I've got home a vastly improved understanding of the CBO - and for a fraction of the effort I thought it would have taken.
Got my money's worth the first day 
2006-05-05
I just bought this book and already got my money's worth just from the information on transitive closure. I've run into this problem a couple times already and am glad to have a better understanding of it now. Looking forward to reading the rest . . .
(I know my rating is premature considering I've only read a few pages so far - that's why I didn't give it 5 stars, but it's off to a very strong start so I felt that 4 stars was fair at this point. How often do you pick up a new book and learn something valuable that you can apply immediately on day 1? I may raise or lower my rating later if my opinion changes after reading more.)
Johathan has done it again!!! 2006-03-22
I have over years collected, bought many oracle books. This book is really a gift, an invaluable tools for us who are in the field and want know the ins and outs of the oracle optimizer. I respect, Jonathan because he has proven many times what he actualy said.
I have watched his skills and tuning method. To put it simply, your style of learning works for me. Thank you Jonathan. I can't wait for the others yet to come( you said this is 1 of 3).
Invaluable Oracle Resource 2008-07-15
Jonathan Lewis has perfomed an invaluable service to the Oracle community. He doesn't just offer opinions of how the CBO makes decisions from his vast experience. He also offers the scripts he used to verify behavior of the Oracle CBO. This equips the user to replicate Jonathan's work with newer versions of oracle and adapt the scripts to your circumstances.
Dennis Williams
I was disappointed 
2007-11-08
It doesn't mean you won't love the book. I found it to be far too narrow and, even as an Oracle OCP, boringly written. That may say more about me than it does about Jonathin Lewis, who I know is brilliant and has written brilliant stuff before. I just didn't get as much out of it as I thought I would.
Jonathan is a tease... but it's a great book! 2007-08-24
Each quarter my team of DBA's has one book that we read and discuss in team meetings. This quarter, Cost-Based Oracle Fundamentals is it. This book is a wholly remarkable book for the moderate to advanced DBA. It is a deep dive into the Oracle optimizer, providing some wonderful insights into not only how it works, but why. It is not a book for the faint of hart, or those waiting breathlessly for retirement. It is a book for those who want to understand the CBO in more depth.
Within the book, Jonathan provides insights into how the CBO *should* work. He follows up often by demonstrating the maturing (or devolving as the case may be) of the CBO from version to version. Beyond his own experimentation and results, he leads the reader into the process that he has followed to come to understand the internals of the CBO, providing a road map allowing the reader to continue to explore the optimizer in future releases.
However, I must take Jonathan to task, for he is a nasty tease. This book was released about two years ago. In it, he leads us on with promises of forthcoming volumes 2 and 3, dangling the carrot out there for us to follow. Sadly, these volumes are yet to be seen. Jonathan! Quit running to and fro filling our heads with knowledge at various conferences and write those books man!
A caution for the new DBA or the cursory SQL developer, you may find this book overwhelming at first glance. For the new or even intermediate DBA, it might be hard to derive practical application of what Jonathan is teaching at first glance. Take your time with the material and the insight that you will gain will be invaluable.
Overall, if you want a book with meat, then this is the book for you. It will expand your mind and the way you look at the Oracle CBO.
Invaluable Knowledge and Applicability 2006-11-28
This book gets a prized spot on my technical shelf. It is a challenging read, and has taken the most time of any book I have to get through, but it is quite good. The optimizer is such a core part of what makes Oracle worth the investment, that any DBA who does not understand it is doing a disservice to the company investing such capital into the product.
Lewis' use of proof through example is second to none, and leaves me feeling confident about the knowledge I've gained. His insights are not only theoretical, but quite applicable. I learned much about manipulating and using the DBMS_STATS package, about data model design and why data knowledge is so critical to the DBA.
My nervousness about hints and about upgrades was reinforced with concrete examples that have improved my test plans, upgrade methodologies and overall made me a more competent DBA. I've directly used this knowledge to support my clients in better ways than I would have before it, and that is well worth the price of admission and the time to get through such an in-depth work.
Thanks Jonathan!
Another brilliant book by Jonathan Lewis 2006-10-18
This is the book that one definitely needs to read (and understand) in order to get an idea what the CBO is all about.
Jonathan's examples and presentation is excellent. Though the topic is quite intense, the book is quite pleasant to read. This book is a worthy follow up the classic "Practical Oracle8i".
As regards the usefulness of the book in real world scenarios, I can only say that if one wants to approach SQL tuning as a science, one needs to learn the CBO, and this book is essential.
This is volume 1 or 3. I will definitely be eagerly waiting for the next volumes.
