Re: How to calibrate the CBO

From: Volker Hetzer <volker.hetzer_at_ieee.org>
Date: Mon, 9 Feb 2004 17:43:23 +0100
Message-ID: <c08dbc$d4t$1@nntp.fujitsu-siemens.com>

"Rick Denoire" <100.17706_at_germanynet.de> schrieb im Newsbeitrag 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.
You did analyze the schema in question?
Like dbms_stats.gather_schema_stats or something like this?

Depending on thesize of the schema the first run can take several hours, maybe even days and heavily impacts on the dp performance. But subsequent calls (with the right options) only check some changed tables and run un much less time. You can also gather statistics on single tables and so distribute the load for the initial analysis over several nights or other quiet hours.

The CBO doesn't work well without statistics.

Lots of Greetings!
Volker Received on Mon Feb 09 2004 - 10:43:23 CST