Database

Calculate Oracle Shared Pool

Sample to calculate the Oracle Shared Pool on IRBS2 scheme

— Select SID from User Process

select sid from v$process p, v$session s
where p.addr=s.paddr and s.username=’IRBS2′;

— Get the maximum session memory for this session:

select sum(value) from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = ‘session uga memory max’
and sid = [SID which is get from the top query]

— Get total session memory for this session:
select sum(value) from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = ‘session uga memory max’
and sid in(select sid from v$process p, v$session s
where p.addr=s.paddr and s.username=’IRBS2′);

— Get the total shared SQL area:
select sum(sharable_mem) from v$sqlarea;

— Get the PLSQL sharable memory area:
select sum(sharable_mem) from v$db_object_cache;

— Get existing shared pool values

select
  sum(bytes)
from
  v$sgastat
where
  pool =’shared pool’;

Advertisements

Discussion

No comments yet.

Leave a Reply

Please log in using one of these methods to post your comment:

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

Archives

Twitter Updates

Error: Please make sure the Twitter account is public.

%d bloggers like this: