Put simply, the Oracle CBO is a mathematical model; you feed it inputs (queries, statistics), and it produces outputs (query plans). In order to use the CBO successfully, it is critical that you understand what these inputs are and how the CBO uses them. Consider the following question, one that I’ve tried to answer many times: What is the best way to collect statistics, what statistics should I gather? Seems simple enough, very straightforward—there should be an answer and there is, but it is no “one size fits all” answer. It depends on your circumstances, your data distribution, your queries, the type of system you have (transactional, data warehouse)—a whole host of factors—and it is only through an understanding of how the CBO works and of how these factors affect the CBO that you’ll be able to answer the question for yourself.
|