Re: tuning problem

I think I might have solved my problem. However, I'm still in the progress of checking all the queries.

After an exhaustive reading of all the init.ora parameter descriptions. I found something called "hash_area_size." I never set this parameter on either the new server or the dev server.

The Oracle documentation, however says that this parameter if not set, defaults to sort_area_size * 2.

Since this new machine is so much more powerful (more/faster ram, more/faster disks, more/faster processors) than the dev machine, I had in fact increased the sort_area_size. I had increased it to be double the value that it was on the development server.

In my init.ora, I then went and specifically set the hash_area_size to be the same as the sort_area_size.

I've done a test with one query and now the CBO seems to generate a better plan. For one, nested loops are being favored. I still have one query where it appears the wrong driving table is being used, however the overall cost figures and consistent gets are lower than the plan the dev system generates.

I'm going to keep testing this with every query in our system today.

If I find more questionable plans being generated. I will post the plans as you've asked for.

The Oracle documentation says nothing about hash_area_size affecting the way in which the CBO selects it's join strategy. It does. I've just proved it.

Thanks everyone for the help, will keep you posted.

Gavin

In article <981879549.29910.0.nnrp-07.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> Have you dumped and compared the statistics
> on the two sets of tables and indexes.
>
> Although your compatibility (from your next
> post) does change some 'hidden' features,
> the first check has to be the numbers.
>
> I would guess that the clustering factor on some
> of the indexes varies significantly. There may also
> be large enough differences in free-space below hwm
> and freed-space in indexes.
>
> Can you send us the plan(s) that you get on production,
> and the plan you would like to see (by hinting it) so we
> can see the paths AND COSTS that occur on production;
> and then the plan that appears on DEV.
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
> Publishers: Addison-Wesley
>
> Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
>

Sent via Deja.com
http://www.deja.com/ Received on Sun Feb 11 2001 - 12:53:44 CST