Oracle memory report

Oracle memory report
Oracle SGA report

@mem.sql


from Web


prompt
prompt 10g Memory Report
prompt

set linesize 120
set pagesize 100
set echo off
set trimspool on

rem create dated and instance named spool file
column file_name new_value spool_name
set heading off

select 'mem10g_'||to_char(sysdate,'mmdd')||'_'||name as file_name
from v$database;

set heading on

spool &spool_name..log

prompt memory report
prompt
column today format a30 heading "Todays Date"
select to_char(sysdate,'HH24:MI:SS ddth Monthfm YYYY') today
from sys.dual
/

col host_name for a20
col uptime for a20
col startup_time for a17
col version for a10
col instance_number for 9,990 heading 'Inst.'
col days for a5
col HRS for a4
col mins for a4

select instance_number
, instance_name
, decode
( instr (host_name,'.')
, 0 , host_name
, substr (host_name, 0, instr (host_name,'.')-1)
) as host_name
, version
, to_char(startup_time,'Dy dd-Mon hh24:mi') as startup_time
, to_char (floor (sysdate - startup_time), '000') as DAYS
, to_char (mod (floor ((sysdate - startup_time) * 24 ), 24), '00') as HRS
, to_char (mod (floor ((sysdate - startup_time) * (24*60)), 60), '00') as MINS
from v$instance
/

-- prompt SGA
-- show sga

col pname for a30 heading 'Parameter'
col pvalue for a20 heading 'Value'

select name as pname
, value as pvalue
, isdefault as def
, isses_modifiable as ses
, issys_modifiable as sys
, ismodified as mod
, isadjusted as adj
from v$parameter
where name in
('buffer_pool_keep'
,'buffer_pool_recycle,
,'db_block_size'
,'hash_area_size'
,'java_pool_size'
,'large_pool_size'
,'shared_pool_size'
,'sort_area_size'
)
or name like '%cache_size%'
order by name
-- useful for debug only /

set serveroutput on size 100000
set feedback off

declare

blk_size number;
anum number;
bnum number;
param_num number;
param_str varchar2(100);
param_name varchar2(50);

cursor c_sga is
select name, value from v$sga;

cursor c_free is
select pool, bytes from v$sgastat where name = 'free memory' and bytes > 0;

cursor c_cache is
select name from v$parameter where name like 'db_%cache_size' and value<>'0' order by 1;

function bytes_to_mb (p_num in number) return varchar2 is
s_str varchar2 (100);
begin
s_str := to_char (round (p_num/(1024*1024)), '999,999,990') || ' Mb';
return s_str;
exception
when others then return p_num;
end bytes_to_mb;

function bytes_to_kb (p_num in number) return varchar2 is
s_str varchar2 (100);
begin
s_str := to_char (round (p_num/1024), '999,999,990') || ' Kb';
return s_str;
exception
when others then return p_num;
end bytes_to_kb;

procedure get_val (p_name in varchar2) is
begin
select value, name
into param_str, param_name
from v$parameter
where upper(name) = upper(p_name);

begin
-- translate parameter string value to a number of bytes
if rtrim (upper (param_str),'K ') <> upper (param_str) then
param_num := 1024 * to_number (rtrim (upper (param_str),'K '));
elsif rtrim (upper (param_str),'M ') <> upper (param_str) then
param_num := 1024 * 1024 * to_number (rtrim (upper (param_str),'M '));
elsif rtrim (upper (param_str),'G ') <> upper (param_str) then
param_num := 1024 * 1024 * 1024 * to_number (rtrim (upper (param_str),'G '));
else
param_num := to_number (param_str);
end if;

-- translate number of bytes to a formated string value
if param_num < 2048 then
param_str := to_char (param_num, '999,999,999,990');
elsif param_num < (2048*1024) then
param_str := bytes_to_kb (param_num);
else
param_str := bytes_to_mb (param_num);
end if;
exception
when others then null;
end;
exception
when others then
param_str := 'Invalid Parameter';
param_num := NULL;
param_name := p_name;
end get_val;

procedure trace (p_indent in integer, p_name in varchar2, p_val in varchar2) is
s_str varchar2(100);
begin
s_str := rpad ('.',p_indent*3)||rpad (p_name,30)||'-'||lpad (p_val, 20);
dbms_output.put_line (s_str);
end trace;

procedure trace (p_indent in integer, p_name in varchar2) is
s_str varchar2(100);
begin
s_str := rpad ('.',p_indent*3)||rpad (p_name,30);
dbms_output.put_line (s_str);
end trace;

begin

get_val ('db_block_size');
blk_size := param_num;
trace (2,param_name, to_char (param_num,'999,990'));

trace (1,'SGA');
anum :=0;
for r_sga in c_sga
loop
trace (2,r_sga.name, to_char (r_sga.value, '999,999,999,990'));
anum := anum + r_sga.value;
end loop;
trace (2,'TOTAL SGA', bytes_to_mb (anum));
get_val ('sga_target');
trace (2, param_name, param_str);
get_val ('sga_max_size');
trace (2, param_name, param_str);
if param_num < (128*1024*1024)
then trace (2, 'Granule Size','4 Mb');
else trace (2, 'Granule Size','16 Mb');
end if;

trace (1,'SGA caches');
for r_cache in c_cache
loop
get_val (r_cache.name);
trace (2, param_name, param_str);
end loop;
get_val ('log_buffer');
trace (2, param_name, param_str);
get_val ('large_pool_size');
trace (2, param_name, param_str);
get_val ('shared_pool_size');
trace (2, param_name, param_str);
get_val ('java_pool_size');
trace (2, param_name, param_str);

trace (1, 'PGA');
get_val ('sort_area_size');
anum := param_num;
trace (2, param_name, param_str);
get_val ('hash_area_size');
bnum := param_num;
trace (2, param_name, param_str);
bnum := (anum + bnum) + (1024*1024);
trace (2, 'nominal PGA per session', bytes_to_kb (bnum));
get_val ('pga_aggregate_target');
trace (2, param_name, param_str);
get_val ('workarea_size_policy');
trace (2, param_name, param_str);

select max (value)
into anum
from v$sesstat s
, v$statname n
, v$session ses
where n.name = 'session pga memory max'
and n.statistic# = s.statistic#
and ses.sid = s.sid
and ses.type = 'USER';
trace (2, 'Peak PGA for one session', bytes_to_kb (anum));

select sessions_highwater
into anum
from v$license;
trace (2, 'peak number sessions', to_char (anum, '999,990'));

trace (2, 'nominal peak PGA demand', bytes_to_mb (anum*bnum));

trace (1, 'Free SGA Memory');
anum := 0 ;
for r_free in c_free
loop
anum := anum + r_free.bytes;
trace (2, r_free.pool, to_char(r_free.bytes,'9,999,999,990'));
end loop;
trace (2, 'TOTAL FREE SGA MEMORY',to_char (anum, '9,999,999,990'));

end;
/

col memory for a7 heading 'Memory'
col name for a35 heading 'Memory Area/Usage'
col ratio for 990.000 heading 'Efficiency%'
col mb for a15 heading 'Size (Mb)'
select 'SGA' as memory
, name||' '||block_size as name
, 100 * (1 - (physical_reads/(db_block_gets+consistent_gets))) as ratio
from v$buffer_pool_statistics
where ( consistent_gets + db_block_gets ) !=0
union
select 'SGA', 'Library Cache'
, 100 - (sum(reloads)/sum(pins) *100)
from v$librarycache
union
select 'SGA', 'Dictionary Cache'
, 100 - (sum(getmisses)/sum(gets) * 100)
from v$rowcache
union
select 'PGA', 'Sorting'
, (mem/(disk+mem))*100
from
( select value as disk from v$statname a , v$sysstat
where a.statistic# = v$sysstat.statistic#
and a.name = 'sorts (disk)'),
( select value as mem from v$statname a , v$sysstat
where a.statistic# = v$sysstat.statistic#
and a.name = 'sorts (memory)'
)
order by 1,2
/
select 'PGA' as memory, name
, to_char (value/(1024*1024), '999,990.0')||' Mb' as mb
from v$pgastat
where name like '%PGA allocated'
or name like '%auto workareas'
order by memory, value desc, name
/

prompt PGA_TARGET_ADVICE
col PGA_TARGET_FOR_ESTIMATE for 999,999,999,990 heading 'PGA_TARGET'
col PGA_TARGET_FACTOR for 99.0 heading 'Factor'
col BYTES_PROCESSED for 999,999,999,999,990 heading 'Bytes Processed'
col ESTD_EXTRA_BYTES_RW for 999,999,999,999,990 heading 'Est. Bytes|r/w'
col ESTD_PGA_CACHE_HIT_PERCENTAGE for 9,990 heading 'Est. PGA|hit rate'
col ESTD_OVERALLOC_COUNT for 999,990 heading 'Est.|overalloc'
select PGA_TARGET_FOR_ESTIMATE
, PGA_TARGET_FACTOR
, BYTES_PROCESSED
, ESTD_EXTRA_BYTES_RW
, ESTD_PGA_CACHE_HIT_PERCENTAGE
, ESTD_OVERALLOC_COUNT
from v$pga_target_advice
order by PGA_TARGET_FACTOR
/

col name for a20 heading 'Buffer Cache'
col size_for_estimate for 999,990 heading 'Size (Mb)'
col size_factor for 90.000 heading 'Size|Factor'
col estd_physical_read_factor for 90.000 heading 'Est. rd|Factor'
col estd_physical_reads for 999,999,990 heading 'Est. physical|Reads'
select name||' '||block_size as name
, size_for_estimate
, size_factor
, decode (size_factor, 1, '*', ' ') as c
, estd_physical_read_factor
, estd_physical_reads
from v$db_cache_advice
order by name, block_size, size_factor
/

-- @@dba_pga_usage

prompt
spool off

set feedback 6