Re: Flumoxed - CBO/RBO

"Preston Kemp" <dontwantany_at_nowhere.invalid> wrote in message news:MPG.1a2cb8413859b68f9896aa_at_text.news.ntlworld.com...
> In article <1069706082.136597_at_yasure>, Daniel Morgan says...
> > >
> <snip tale of woe>
> > >
> >
> > This might well be a case where an outline would be of value. Can you
> > create an outline on the old server? Alternatively ... create a new one.
>
> Thanks for the reply Daniel. I could create outlines, but not easily.
> The 'test' screen has 3 sections, each with their own main queries, &
> several additional queries to generate virtual columns. Not to mention
> the internal queries that USoft generates to check relationships, plus a
> stored proc the screen fires when it loads.
>
> I decided to build a new database on my server using the same settings,
> and managed to reproduce the problem after much tweaking of parameters.
> It appears the stored proc the screen calls is the culprit, or rather
> the way in which Oracle is optimising it (contradiction in terms!). The
> hash_area_size is the same as their old server, which has the same
> amount of RAM (albeit with NT & 7.3.3 rather than Win2K & 8.1.7), but
> for some reason it seems to be upsetting things on the new server.
> Setting hash_join_enabled to false has made it all much happier.
>
> It feels like a bit of a bodge, but if it brings the performance back &
> keeps the users happy it'll do for the time being. I'll worry about the
> why's & wherefore's when I have time, unless somebody knows off the top
> of their head?
>
> Regards,
>
> Preston.

Preston, I need to guess w/out seeing the trace file...

But thinking your CBO may be doing a full table scan on a very large table, where you were using index access before....

(but it could be the opposite)

try this with cost based optimizer

 OPTIMIZER_INDEX_COST_ADJ=20; And if it works then find the number that is correct for your system.

You can test with an alter session. Received on Mon Nov 24 2003 - 19:10:47 CST