vermem

scripts>vermem

 

set echo off
set timing off
set pagesize 1000
set feedback off
prompt
prompt MEMORIA – @vermem
prompt
set heading off
col NAME||’:’ format a21
column name format a20
col value format a12
prompt
prompt Memory Summary
prompt
select name ||’: ‘,value/1024/1024 “SIZE MB”
from v$parameter
where name in (‘sga_max_size’,’pga_aggregate_target’,’db_cache_size’,’shared_pool_size’,’large_pool_size’,’log_buffer’,’java_pool_size’,’sort_area_size’)
/
set heading on
select round((sum(decode(name,’free memory’,bytes,0))/sum(bytes))*100,2) “% Free SharedPool” from v$sgastat
/
select name,pool, bytes/1024/1024 “SIZE MB”
from v$sgastat
where name = ‘free memory’
union all
select name,pool, bytes/1024/1024 “SIZE MB”
from v$sgastat
where name in (‘library cache’,’row cache’,’sql area’,’dictionary cache’)
/
select name, bytes/1024/1024 “SIZE MB”
from v$sgainfo
where name in (‘Shared Pool Size’,’Large Pool Size’,’Java Pool Size’)
/
set pagesize 132

column owner format a16
column name format a36
column sharable_mem format 999,999,999
column executions format 999,999,999
prompt
prompt Top10 Memory Usage of Shared Pool Order – Biggest First
prompt
column name format a45
select owner, name||’ – ‘||type name, sharable_mem from v$db_object_cache
where sharable_mem > 10000
and type in (‘PACKAGE’, ‘PACKAGE BODY’, ‘FUNCTION’, ‘PROCEDURE’)
and rownum < 11
order by sharable_mem desc
/
prompt
prompt Loads into Shared Pool – Most Loads First
prompt
select owner, name||’ – ‘||type name, loads , sharable_mem from v$db_object_cache
where loads > 3
and type in (‘PACKAGE’, ‘PACKAGE BODY’, ‘FUNCTION’, ‘PROCEDURE’)
order by loads desc
/
prompt
prompt Executions of Objects in the Shared Pool – Most Executions First
prompt
select owner, name||’ – ‘||type name, executions from v$db_object_cache
where executions > 100
and type in (‘PACKAGE’, ‘PACKAGE BODY’, ‘FUNCTION’, ‘PROCEDURE’)
order by executions desc
/

set echo on

Comment

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

WordPress.com Logo

You are commenting using your WordPress.com 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