PGA UGA OLAP Page Pool Hit Miss Ratio and Size by User

[ola...@gmail.com]

-------------------------
-- NON RAC -- STANDALONE
--------------------------

set lines 110 pages 500

col username format a8 heading "User"
col sid format 999 heading "SID"
col serial# format 99999 heading "Serial#"
col pga_used format a9 heading "PGA Used"
col pga_max format a9 heading "PGA Max"
col olap_pp format a7 heading "OLAP PP"
col olap_ratio format a10 heading "OLAP Ratio"

select vs.username, vs.sid, vs.serial#,
round(max(pga_used_mem)/1024/1024,1)||'M' pga_used,
round(max(pga_max_mem)/1024/1024,1)||'M' pga_max,
round(max(pool_size)/1024/1024,1)||'M' olap_pp,
round(100*(max(pool_hits)-max(pool_misses)
)/max(pool_hits),1)||'%' olap_ratio
from v$process vp, v$session vs, v$aw_calc va
where session_id=vs.sid and addr = paddr
group by vs.username, vs.sid, vs.serial#
order by vs.username, vs.sid, vs.serial#;

User SID Serial# PGA Used PGA Max OLAP PP OLAP Ratio
-------- ---- ------- --------- --------- ------- ----------
SCOTT 121 21254 150.38M 151.28M 94.99M 99.34%

--------------------------
-- RAC -- CLUSTERS
--------------------------

set lines 110 pages 500

col username format a8 heading "User"
col sid format 999 heading "SID"
col serial# format 99999 heading "Serial#"
col pga_used format a9 heading "PGA Used"
col pga_max format a9 heading "PGA Max"
col olap_pp format a7 heading "OLAP PP"
col olap_ratio format a10 heading "OLAP Ratio"
col instance format a10 heading "Instance"

select gvs.username, gvs.sid, gvs.serial#, gvi.instance_name
instance,
round(max(pga_used_mem)/1024/1024,1)||'M' pga_used,
round(max(pga_max_mem)/1024/1024,1)||'M' pga_max,
round(max(pool_size)/1024/1024,1)||'M' olap_pp,
round(100*(max(pool_hits)-max(pool_misses)
)/max(pool_hits),1)||'%' olap_ratio
from gv$process gvp, gv$session gvs, gv$aw_calc gva, gv$instance gvi
where session_id=gvs.sid and addr=paddr and gvp.inst_id=gvs.inst_id
and gva.inst_id=gvs.inst_id and gvp.inst_id=gvi.inst_id
group by gvs.username, gvs.sid, gvs.serial#, gvi.instance_name
order by gvs.username, gvs.sid, gvs.serial#, gvi.instance_name;

User SID Serial# Instance PGA Used PGA Max OLAP PP OLAP Ratio
-------- ---- ------- ---------- --------- --------- ------- ----------
SCOTT 121 21254 olaprac1 150.4M 151.3M 95M 99.3%
SYS 114 64 olaprac2 5.5M 10.8M .3M 97.3%