The SYS statistics ? did you do them
"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 Sun Feb 08 2004 - 11:16:51 CST
