O'Reilly logo

Oracle SQL Tuning Pocket Reference by Mark Gurry

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Cost-Based Optimizer Problems and Solutions

The cost-based optimizer has been significantly improved from its initial inception. My recommendation is that every site that is new to Oracle should be using the cost-based optimizer. I also recommend that sites currently using the rule-based optimizer have a plan in place for migrating to the cost-based optimizer. There are, however, some issues with the cost-based optimizer that you should be aware of. Table 1-3 lists the most common problems I have observed, along with their frequency of occurrence.

Table 1-3. Common cost-based optimizer problems

Problem

% Cases

1. The skewness problem

30%

2. Analyzing with wrong data

25%

3. Mixing the optimizers in joins

20%

4. Choosing an inferior index

20%

5. Joining too many tables

< 5%

6. Incorrect INIT.ORA parameter settings

< 5%

Problem 1: The Skewness Problem

Imagine that we are consulting at a site with a table TRANS that has a column called STATUS. The column has two possible values: `O' for Open Transactions that have not been posted, and `C' for closed transactions that have already been posted and that require no further action. There are over one million rows that have a status of `C', but only 100 rows that have a status of `O' at any point in time.

The site has the following SQL statement that runs many hundreds of times daily. The response time is dismal, and we have been called in to "make it go faster."

SELECT acct_no, customer, product, trans_date, amt FROM trans ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required