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

As MOS describes it as fixed in 11.2.0.3 and the reporter's system has compatibility set to 11.2.0 4, I doubt this is the problem.

Cheers, Wayne
On May 9, 2014 7:01 PM, "Juan Carlos Reyes Pacheco" <jcdrpllist_at_gmail.com> wrote:

> Which version of 11g?, there is a bug on performance. it's not difficult
> to identify check your SYS.AUX_STATS$ and see if they are not multiplied by
> 10000, in metalink you can find more information.
> If you run this you can fix it.
>
> BEGIN
> FOR A IN ( SELECT PNAME,PVAL1
> FROM SYS.AUX_STATS$
> WHERE SNAME = 'SYSSTATS_MAIN'
> AND PNAME IN ('SREADTIM','MREADTIM') ) LOOP
> IF (A.PVAL1 > 75) THEN
> DBMS_STATS.SET_SYSTEM_STATS(A.PNAME, a.PVAL1 / 10000);
> DBMS_OUTPUT.PUT_LINE('MREADTIM ALTERED TO: ' || a.PVAL1 / 10000);
> END IF;
> END LOOP;
> COMMIT;
> END;
> /
>
>
>
> 2014-05-08 2:39 GMT-04:00 Bheemsen Aitha <baitha_at_itradenetwork.com>:
>
>> 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