set serveroutput on
DECLARE
object_mem number;
shared_sql number;
cursor_mem number;
used_pool_size number;
free_mem number;
pool_size varchar2(100);
BEGIN
select sum(sharable_mem) into object_mem from v$db_object_cache;
select sum(sharable_mem) into shared_sql from v$sqlarea;
select sum(250*users_opening) into cursor_mem from v$sqlarea;
select bytes into free_mem from v$sgastat
where name = 'free memory';
used_pool_size := round(1.3*(object_mem+shared_sql+cursor_mem));
select value into pool_size from v$parameter where name =
'shared_pool_size';
dbms_output.put_line('Obj mem: '|| to_char(object_mem) || ' bytes');
dbms_output.put_line('Shared sql: '|| to_char(shared_sql) || '
bytes');
dbms_output.put_line('Cursor: '|| to_char(cursor_mem) ||' bytes');
dbms_output.put_line('Free memory: '|| to_char(free_mem) || ' bytes '
|| '(' || to_char(round(free_mem/1024/1024,2)) || 'M)');
dbms_output.put_line('Shared pool utilization (total): '||
to_char(used_pool_size) || ' bytes ' || '(' ||
to_char(round(used_pool_size/1024/1024,2)) || 'M)');
dbms_output.put_line('Share pool allocation (actual): '||
pool_size || ' bytes
' || '(' || to_char(round(pool_size/1024/1024,2)) ||
'M)');
dbms_output.put_line('Percentage Utilized: '||
to_char(round(used_pool_size/pool_size*100)) || '%');
END;
/
set serveroutput off
PROMPT The free memory reported in this table is not like free Memory
reported
PROMPT by O/S statistics. Free memory is more properly thought as 'waste
memory'
PROMPT You would rather see this value be low than high. In fact, a high
value
PROMPT for free memory is sometimes a symptom of a lot of objects been aged
PROMPT out of the shared pool.
Received on Wed Nov 25 1998 - 06:00:33 CST
