Re: SQL Queries SLOW after Oracle 8i to 10g migration

Blue Crystal Solutions wrote:

>
> I have just migrated from Oracle 8i to Oracle 10g on a Windows 2003
> server. The server is of higher processing power. Several SQL Queries,
> some complex ones are very slow.
>
> Some SQL Queries run faster and some queries run slower.
> Some queries used to run in 6 minutes now take up to 6 hours. Some
> queries run faster.
> The indexes are exactly the same in both versions.
> What has changed in the optimizer when upgrading from 8i to 10G?
> What needs to be done for these queries? And why?

The "why" will probably be answered by explain plans, but you might want to start by looking at optimizer_index_caching, optimizer_index_cost_adj & sort_area_size. These have been the culprits on every 8i to 10g migration I've done that's resulted in performance issues. I usually use 50, 20 & 'something else' respectively as a baseline, but this obviously depends on your particular application. The sort_area_size in particular can cripple the system if you built the 10g database with default parameters then exp/imp.

This obviously isn't the correct way to do things, but if it gives you acceptable performance until you have time to look into it properly, it's good enough, especially if your working environment's anything like mine.

-- 
Preston.