Performance bug on oracle 11g I had been months trying to find a bug in performance after I migrate to 11gr2 and finally I found, in theory it is solved in 11gr2 patch 3

this triggers check and solved the problem, or execute from the beginning, to validate the change.

 select pname,pval1 from sys.aux_stats$
    where pname like '%READTIM';


CREATE OR REPLACE TRIGGER TGR_FIX_SYSTEM_STATS
 AFTER
 LOGON
 ON DATABASE
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;

EXCEPTION WHEN OTHERS THEN
 DAZ.DB_ERR_GRABA(0,'tgr_fix_system_stats'||SQLERRM);
END;
/  ¶ 9:32 AM 0 comments