Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> CBO Bug--Please Help!!!

CBO Bug--Please Help!!!

From: Brad Skiles <bwskiles_at_adpc.purdue.edu>
Date: 1997/05/13
Message-ID: <337888B8.524E@adpc.purdue.edu>#1/1

I'm trying to identify what has triggered a Cost Based Optimizer bug on our data warehouse. We've operated successfully for about a year with CBO, but this month we've had severe problems.

We've contacted technical support and been told it's a bug with the CBO, and it will be fixed in version 8. But something must have triggered the bug after the last refresh of our data warehouse, and I'd like to know what it is.

The symptoms are that we see CPU utilization reach 100% and disk IO hit 0% for the affected queries. The queries *never* return any data.

Deleting the statistics for a table, or specifying, using a hint, that the query is to use RULEs optimization, allows the query to execute successfully. We do ad hoc querying on the databases, and it's not practical to have every user learn to do hints if their query doesn't seem to work.

Here are the changes we made.

  1. Upgraded our HP9000 from 2 420 processors to 2 460 processors and installed the appropriate software for the new patches. (NOTE: this was done a few days before the problems were noticed with no apparent impact on CBO. We also removed several of the software patches which failed to fix the problem.)
  2. Refreshed the database. Simply added or replaced rows. This is done every month. No peculiarities with the data have been found.
  3. Changed analyzer parameters. Previously executed ANALYZE TABLE PAYROLL_CHARGE COMPUTE STATISTICS; This time used, ANALYZE TABLE PAYROLL_CHARGE COMPUTE STATISTICS FOR TABLE FOR ALL COLUMNS SIZE 250; This struck me as the likely candidate for the problem, so I deleted the statistics and went back to the original analyze, but we still encountered the problem. I also tried to estimate the statistics, but the problem still occurred.

In addition, immediately after the data refresh completed, the users reported no problems. Two days later, system monitors and users reported the problem with one specific table. In the few days since then, the problem appears to have "spread" to other tables that worked for awhile since the refresh, and are now going out to lunch.

Could someone please help me identify what has caused this bug to start? Knowing it will be fixed in version 8 just isn't sufficient. Something caused this bug to kick off now, and I need to know what.

Thanks!

--brad skiles, DBA
Purdue University
bwskiles_at_adpc.purdue.edu Received on Tue May 13 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US