Re: Flumoxed - CBO/RBO

From: Preston Kemp <dontwantany_at_nowhere.invalid>
Date: Tue, 25 Nov 2003 00:52:56 -0000
Message-ID: <MPG.1a2cb8413859b68f9896aa@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. Received on Mon Nov 24 2003 - 18:52:56 CST