Re: DB performance after upgrade from 9i to 11gR2

There are simply way too many things that could explain what you see. You have two options here:

1. Manually verify one by one all the possible causes (trial and error); OR
2. Find the root cause

If you decide for the 2nd then I offer to help. Simply send me a SQLT XTRACT report for ONE or TWO queries from each of these two environments and I will offer to diagnose your issue. SQLT is available under MOS 215187.1

Cheers — Carlos

On May 8, 2014, at 2:39 AM, Bheemsen Aitha <baitha_at_itradenetwork.com> wrote:

> 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

--
http://www.freelists.org/webpage/oracle-l