08 November 2011

Wrong Workload System Statistics Recorded When Running GATHER_SYSTEM_STATS

For some time now I have been aware that a bug was introduced in 11.2 that recorded the wrong SREADTIM & MREADTIM in AUX_STATS$ - by a factor of 1000x. I.e. the following call for instance:
dbms_stats.GATHER_SYSTEM_STATS(GATHERING_MODE=>'INTERVAL', INTERVAL=>5)
For a while the official Oracle Support line was to not gather workload statistics - but just recently I came across the bug (9842771) note again and noticed that the recommendation now is to manually adjust the values. So here goes:
set serverout on size unlimited
DECLARE
  CURSOR c1 (p_name VARCHAR2) IS 
  SELECT pval1
   FROM sys.AUX_STATS$
  WHERE sname = 'SYSSTATS_MAIN'
    AND pname = p_name;

  stat  NUMBER;
BEGIN
    
  OPEN c1 ('SREADTIM');
  FETCH c1 INTO stat;
  CLOSE c1;
  
  -- Just a simple sanity check in case it was run before (not expecting average times to come down below 0.075ms in the near future)
  IF (stat > 75) THEN
    DBMS_STATS.SET_SYSTEM_STATS('SREADTIM', stat / 1000);
    DBMS_OUTPUT.PUT_LINE('SREADTIM altered to: ' || stat / 1000);
  END IF;

  OPEN c1 ('MREADTIM');
  FETCH c1 INTO stat;
  CLOSE c1;
  
  IF (stat > 75) THEN
    DBMS_STATS.SET_SYSTEM_STATS('MREADTIM', stat / 1000);
    DBMS_OUTPUT.PUT_LINE('MREADTIM altered to: ' || stat / 1000);
  END IF;

END;
/
I should add that the bug is allegedly fixed in 11.2.0.3 - I have not had chance to test this yet.

No comments: