Re: How to calibrate the CBO

Hello Rick,

   Tuning CBO correctly is relatively difficult task.

   Optimizer uses many documented and undocumented parameter that impact it's decision tree.

   One of the important parameters to tune is "optimizer_index_cost_adj".

   Check below link for some good recommendations: http://www.dba-oracle.com/oracle_tips_cost_adj.htm

    Mark Rittman's Oracle Weblog has good info as well - http://www.rittman.net/archives/000701.html

    For really deep-into the internals CBO analysis you can use event 10053.

    check on Google (use "10053 event oracle" to search".)

    Sorry if advice is too generic - IMHO CBO tuning is very site-specific.

Regards,

Support

DBA Infopower

https://www.dbainfopower.com

The advice provided by DBA Infopower are provided "as is" with no warranty. DBA Infopower expressly disclaims any warranty, regarding the advice including any implied warranty of merchant fitness for a particular purpose aviating course of dealing and/or performance. DBA Infopower does not warrant that the advices provided by DBA Infopower will be free from bias, detests, errors, eavesdropping or listening. DBA Infopower shall not be responsible for the quality of information or the authentication of the services or details given by experts on the advice. By using this advice the user of the advice accepts the terms and conditions of this statement.

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:8l68201dujlnijl0tkv0sa1c7op3bges09_at_4ax.com...  > 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 Sat Feb 07 2004 - 00:20:02 CST