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* >> >> >> > >