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:
Post a Comment