Dear Sirs, I am retiring in one month. Please remove me from your email list. Thank you for all the support over the years. I think you have a great setup! Bob Bob Causey Database Administrator Phone: 404-264-5919 Fax: 404-238-8369 -----Original Message----- From: FreeLists Mailing List Manager [mailto:ecartis@xxxxxxxxxxxxx] Sent: Sunday, May 11, 2014 1:05 AM To: oracle-l digest users Subject: oracle-l Digest V11 #134 oracle-l Digest Sat, 10 May 2014 Volume: 11 Issue: 134 In This Issue: Re: DB performance after upgrade from 9i to 11gR2 RE: DB performance after upgrade from 9i to 11gR2 ---------------------------------------------------------------------- Date: Sat, 10 May 2014 19:52:03 -0400 Subject: Re: DB performance after upgrade from 9i to 11gR2 From: Wayne Smith <wts@xxxxxxxxx> The bug Juan describes is 9842771. 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@xxxxxxxxx> 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@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* >> >> >> > > ------------------------------ From: Bheemsen Aitha <baitha@xxxxxxxxxxxxxxxxx> Subject: RE: DB performance after upgrade from 9i to 11gR2 Date: Sat, 10 May 2014 23:58:57 +0000 "ØbžJ.®ö¥¹ë,yé¡·œ¢ºÞrßÒ@¿ìzWœ¶™Ú™ï齩u~º&³+ jì ²Ö³ü!z·¬?©ž ------------------------------ End of oracle-l Digest V11 #134 ******************************* ��i��0���zX���+��n��{�+i�^