How to calibrate the CBO

We migrated a DB Oracle application from 8.1.7 to 9.2.0.4 on Solaris, and since then we are having complains about some specific batch jobs running about 7x longer (which means that instead of 2 hours now it takes 14 - does not fit in a dayly schedule anymore).

The developers see this as a DB Admin issue, they did not change the code and it ran well prior to the migration after all.

I have used statspack, trace files with tkprof, profilers, investigated the execution plans... These statements are quite long and complex, so the execution plans too.

But since I kept the old version of the DB (using a different name), I just started it on the same host to track the cause of the problem. It turns out that Oracle 9 chooses an execution plan with far less cost than the Oracle 8 version, but runs much slower.

What I did was to create a stored outline in the 8i version and transfer it to the 9i instance, so the same execution plan was forced. Now costs are "high enough" again and I expect a better performance, I will know tomorrow for sure.

Evidently, Oracle 9i is mislead by a wrong cost calculation when choosing the execution plan. My question is, is there a way to adjust the magnitudes used for cost calculation? Can I then get costs that are proportional to execution time?

Perhaps it is possible to execute customized statements to generate specific load on the database and so to calibrate the corresponding Oracle "currency". For example, force a full table scan on a 1 Mio rows table, then repeat with 2,3,6,10 Million rows, and measure execution times. The result would be a rough (but realistic) estimate of costs of FTS/row in this particular environment.

I lack enough experience in order to be able to tune without measuring results - not a funny task at a 14 hours rate, so I depend strongly on a reasonable cost calculation.

Is it a common practice to adjust the CBO cost calculations? How?

Bye
Rick Denoire Received on Fri Feb 06 2004 - 17:28:40 CST