Re: DB performance after upgrade from 9i to 11gR2
- From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- To: baitha@xxxxxxxxxxxxxxxxx
- Date: Thu, 8 May 2014 09:19:55 +0200
Hello
Start first by settting the optimizer back to its old version and
re-requery to see if you get back to your original 9i execution times.
Something like this:
11gR2> alter session set optimizer_features_enable='9.0.1'; -- put your
exact release
Session altered.
11gR2> run your query
If you get acceptable response times then compare the new execution plan
with the sub-optimal one for a clue about what went wrong after the upgrade.
Best regards
Mohamed Houri
www.hourim.wordpress.com
2014-05-08 8:39 GMT+02:00 Bheemsen Aitha <baitha@xxxxxxxxxxxxxxxxx>:
> Hello DBA gurus,
>
>
>
> We recently upgraded our database from 9i to 11gR2 on Solaris 11 64-bit,
> Veritas File system (single instance). We tested thoroughly in our QA
> environment before the upgrade. But after the upgrade we are seeing
> terrible performance in production with many of the queries. Even though
> the query plan looks good with very low cost, the queries are just sitting
> there and not finishing. Some of the queries have differences in plan (FTS
> to index scans on small tables) when compared to QA environment. We
> gathered stats with default auto sample size and default method_opt
> options. I opened a ticket with oracle on the performance issue, but I
> doubt if I can get a solution on this. The QA and prod environments are
> exactly same, but the queries are running are faster in QA. Did anyone come
> across similar problem in the past? Any inputs are greatly appreciated.
>
>
>
>
>
> Thanks
>
> *BA*
>
>
>
--
Bien Respectueusement
Mohamed Houri
Other related posts:
