Mental note posts are just for me (or others in instance) to remember things that i usually forget. Today mental note is a little script i’ve found in my collection to know the size of ASH circular buffers in SGA and the ASH Window time.
Here it is (not sure why i did it in PL/SQL, maybe just for fun):
set serveroutput on DECLARE ash_size number; ash_window varchar2(30); BEGIN -- statical info about ash select bytes/(1024*1024) into ash_size from v$sgastat where name = 'ASH buffers'; select EXTRACT(HOUR FROM (MAX(sample_time) - MIN(sample_time)))||' hours '|| EXTRACT(MINUTE FROM (MAX(sample_time) - MIN(sample_time)))||' mins' into ash_window from v$active_session_history; dbms_output.put_line('ASH Size in SGA: ' || ash_size || 'MB'); dbms_output.put_line('ASH Window is: ' || ash_window); END; /
ASH Size in SGA: 2MB ASH Window is: 7 hours 54 mins