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 columns&emdash;and 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. You'll 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 Lewis&emdash;one of the foremost authorities in this field&emdash;describes the most commonly used parts of the model, what the optimizer does with your statistics, and why things go wrong. With this information, you'll be in a position to fix entire problem areas, not just single SQL statements, by adjusting the model or creating more truthful statistics.

DESCRIPTION:

Binding: Paperback
Dewey Decimal Number: 005.7585
EAN: 9781590596364
ISBN: 1590596366
Label: Apress
Manufacturer: Apress
Number Of Items: 1
Number Of Pages: 536
Publication Date: 2005-10-31
Publisher: Apress
Release Date: 2009-05-04
Studio: Apress


ACCESSORIES:

• Forecasting Oracle Performance
• Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions


SIMILAR ITEMS:

• Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions
• Effective Oracle by Design (Osborne ORACLE Press Series)
• Oracle Database 10g Performance Tuning Tips & Techniques (Osborne ORACLE Press Series)
• Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning (Osborne ORACLE Press Series)
• Troubleshooting Oracle Performance


CUSTOMER REVIEWS:

Customer Rating:
Summary: A Fantastic Book with a Very Deceptive Name
Comment: I waited nearly three years after the first cover to cover read through of this book, and two years after the second cover to cover read through of this book to write this review - just in case my opinion of the book changed. I enjoyed the first pass through the book, but became increasingly concerned as just weeks earlier I had migrated the most critical database at the company from Oracle 8.1.7.3 to 10.2.0.2. It seemed every turn of the page brought new understanding of what could have gone wrong during and after the migration, not only what might happen, but also most importantly WHY it might happen. Appendix A of the book quickly summarizes what one might expect when moving from one database release to another - that section of the book, for me, was worth several times the price of the book. I might also add that this book provided the tools and techniques necessary to troubleshoot and resolve a session crashing issue triggered by the application of the October 2006 patch for 10.2.0.2, as well as another odd problem which appeared when OPTIMIZER_FEATURES_ENABLE was set to a value greater than 10.1.0.4.

During the second read through of the book I found a number of extremely useful pieces of information which were somehow missed during the previous pass through the book (I recorded much more detailed notes the second time). A brief sampling of some of the more personally useful pieces of information from the book:
* The filter lines in 10053 traces and XPLANS can show implicit conversions from varchar to number. (It also shows when a commercially developed program repeatedly defines bind variables as VARCHAR2 when those bind variables should have been defined as numeric.)
* Bind variable peeking is not used with explain plan or autotrace. (Believing explain plans for SQL statements with bind variables seems to be a common problem on Oracle related forums.)
* In an execution plan, the access predicates show the predicates used to generate the start and stop keys for the index, but the filter predicates show the predicates that cannot be used until the leaf blocks have been reached. (That's why my query is running slow..., slapping hand to forehead.)
* OPTIMIZER_INDEX_COST_ADJ reduces the cost of single block reads, which tends to reduce the tendency of excessive tablescans. The downside is that due to rounding errors, the optimizer may decide to use the wrong index. (I have seen this happen, Oracle decides to use an index with just two distinct values, rather than a high precision index with many distinct values - the primary key index.)
* Constraints on tables can generate additional predicates due to transitive closure. (That's were those access and filter predicates came from...)
* Bind variable peeking can introduce poor execution plans if a typical value is not specified for bind variables on the first hard parse. (Such as a commercial product submitting NULL values for the bind variables values during the initial hard parse, followed by extremely unpopular values, followed by somewhat popular values in a tight loop, causing performance problems.)
* With the evolution of the optimizer, Oracle is increasingly likely to eliminate subqueries by using various transformations. (Hence the reason why code that ran slowly against an Oracle 8.1.7.3 database might run quickly against a 10.2.0.2 database, and why other code that ran quickly on Oracle 8.1.7.3 takes orders of magnitude longer on 10.2.0.2, without some assistance.)
* Setting the value of SORT_AREA_RETAINED_SIZE to a value other than that of SORT_AREA_SIZE (or 0) may cause Oracle to spill the sort results to the temp tablespace, even if the sort completed in memory, and this will not be reported as a sort to disk. (This flies somewhat counter to a recommendation of my favorite Oracle wait event book from 2001 which suggested that as SORT_AREA_SIZE is increased to allow sorts to complete in memory, that SORT_AREA_RETAINED_SIZE should be decreased.)
* Extended coverage of 10053 trace file capturing and analysis.
* Description of several hints which might be used to better control the execution plans.
* Extensive descriptions of several of the hidden (underscored) parameters which control the optimizer's behavior and how the value for OPTIMIZER_FEATURES_ENABLE automatically adjusts some of these hidden parameters.
* Mathematical logic behind the cost calculations used by the optimizer, and how the calculated cost influences the execution plans generated by the optimizer.

The author maintains an extremely helpful Oracle blog which seems to be a direct extension of his book, helping to extend the analysis performed in the book to more recent versions of Oracle, and explore other areas of potential concern to database administrators. On his website the author also maintains a complete list of book errata, additional information learned since the publication date, and how the book's contents need to be reworded to be applicable to more recent versions of Oracle - in all cases giving credit to the person who identified the potential rewording. Directly from the author's errata pages: "I spotted the first error in Practical Oracle 8i a few seconds after the first copy had landed on my front door-step. I spotted the first error in Cost Based Oracle before the printer had even finished printing it - I woke up at three a.m. a few days before the book came out, realizing that I had introduced an error in the last pass of proof-reading the galley pages." As infrequently as this author makes mistakes, it is good to see that the author is humble about the mistakes, without attempting to sweep those mistakes into a dark corner while scolding the person who identified the mistakes.

You will not find page after page of wide margin, 14 point font sized text interspersed between cartoon drawings in the book. You will not find wild assumptions in this book. You will not find cases where the author must have been hand waiving (with a third hand) while composing the book. You will not find untested scripts scattered through the pages. You will find, with a little bit of effort, the reason why the Oracle database instance did not behave as expected.

Prior to purchasing this author's book, I performed a fairly extensive search on the Internet to make certain that the author's book would not do more damage than good to my comprehension of what makes an Oracle database function. I found that the author created Usenet group postings in Oracle related forums, helping other users, dating back to 1994; and in checking the accuracy of those post contents, what was stated is just about as accurate now as it was in 1994. I also found that the author had participated in several technical discussions on the Oracle-L list serve. Following the purchase of the book I noticed that the author set up one of the most technical Oracle related blogs on the Internet, and started participating in Oracle's OTN forums. Since that time I have had the opportunity to converse directly with the author a couple times on the OTN forums and Usenet, where I have always found him to be helpful, knowledgeable, and humble if he missed/overlooked a small detail (and humble when pointing out a small or significant detail which I missed).

In short, my opinion of the book has not changed since the first read through nearly three years ago. "Cost-Based Oracle Fundamentals" was, and still is, the most useful Oracle performance related book which I have found. The book, however, is closely followed in usefulness, in no particular order, by "Troubleshooting Oracle Performance", "Optimizing Oracle Performance", "Expert Oracle Database Architecture" and "Oracle Performance Tuning 101".

Customer Rating:
Summary: A Classic
Comment: Well, you can read this and update with new features for 10g and 11g. Or you can read 1200, maybe 1500 pages of documentation and try to tie everything together. Gawd, going with Lewis is easier. *** Also, he's great at detailing where rule-of-thumb and common misconceptions go wrong. *** This author is brilliant. Lots and lots of pro conversations include references to what he said in the (this) CBO book.

Customer Rating:
Summary: Great Book!
Comment: This book contains a wealth of information about how to evaluate and test the Oracle Cost Based Optimizer. Jonathan presents the material in a very easy to follow style. All the examples and scripts that he provides work as written. I would highly recommend this book.

Customer Rating:
Summary: Invaluable Oracle Resource
Comment: 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

Customer Rating:
Summary: I was disappointed
Comment: 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.

---

NOTE: All online transactions are processed at Amazon.com's secure server, using the latest technology on internet's secure transactions.
| Blocks | Building Sets | Model Toys | Puzzles | Figures & Playsets | Toys by Age Ranges |