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?
Blue Crystal Solutions 30 August 2005 06:13:42 [ permanent link ]
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?
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?
Interesting how you can take the time to post the exact same message twice but cannot find the time to trace the session or sessions running these queries or generate explain plans for these suspect queries. In the absence of query plans, session traces and the complete version numbers for the 8i database with which you started and the 10g database to which you migrated an answer your question is, at best, exceedingly difficult. And, it will be even less likely to receive an answer if you haven't any query plans from the 8i database, even with plans from 10g and the version information as there will be nothing available with which to compare previous query paths to current ones, making any attempt to find that which has changed impossible.
Asking such a question without providing necessary information is an exercise in futility requiring a crystal ball. As none are available here provide the requested information. We don't read minds and we can't divine answers from the ether.
Blue Crystal Solutions apparently said,on my timestamp of 30/08/2005 12:13 PM:
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?
If all else recommended in the manuals fails (running traces, making sure the stats are gathered the right way, checking the obvious parameters), you can always set the optimizer back to 8i compatibility and live with it, no?
-- Cheers Nuno Souto in sunny Sydney, Australia wizofoz2k@yahoo.com.au.nospam
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.
The guy who posted the last comment is a real {censored}. A lot has changed since 8i to 10g. The major change is the optimizer mode, which is now cost based and not rule based like 8i. Instead of using rules to tune queries you will need to gather stats for the cost based optimizer. After you gather stats you will need to examine the explain plans and tune based on the plans. Quest SQL optimizer is a really good tool to use. It will suggest different alternative ways to write the query, changing the explain plan. It will also suggest indexes. This will be a good place for you to start tuning. Hope this helps.
Hi, I am also planning to migrate our database from 8i to 10g.But I am not getting from where should I start? Can anybody suggest me the steps of migration of oracle database from 8i to 10g on windows 2003 server.
If you would like to report an abuse of our service, such as a spam message, please . Если Вы хотите пожаловаться на содержимое этой страницы, пожалуйста .
