Mental Note: ASH size in SGA and ASH Window

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
ash_size number;
ash_window varchar2(30);

-- 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);


Output is:

ASH Size in SGA: 2MB
ASH Window is: 7 hours 54 mins

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s