Overall PGA and OLAP Page Pool Consumption

[ola...@gmail.com]

This script will work in 10g and later instances to determine the
amount of PGA that the OLAP Page Pool is consuming for all users at
this point in time.

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

col info heading "Instance (standalone) Information"

select 'OLAP Pages Occupying: '||
round((((select sum(nvl(pool_size,1))
from v$aw_calc)) / (select value from v$pgastat
where name = 'total PGA inuse')),2)*100||'%' info from dual
union
select 'Total PGA Inuse Size: '||value/1024||' KB' info
from v$pgastat where name = 'total PGA inuse'
union
select 'Total OLAP Page Size: '||
round(sum(nvl(pool_size,1))/1024,0)||' KB' info
from v$aw_calc
order by info desc;

Instance (standalone) Information
------------------------------------
Total PGA Inuse Size: 320070 KB
Total OLAP Page Size: 97576 KB
OLAP Pages Occupying: 30%

------------------------------
-- RAC -- CLUSTERS -- DATABASE
------------------------------

col info heading "Database (RAC) Information"

select 'OLAP Pages Occupying: '||
round((((select sum(nvl(pool_size,1)) from gv$aw_calc)) /
(select sum(value) from gv$pgastat
where name = 'total PGA inuse')),2)*100||'%' info from dual
union
select 'Total PGA Inuse Size: '||sum(value)/1024||' KB' info
from gv$pgastat where name = 'total PGA inuse'
union
select 'Total OLAP Page Size: '||
round(sum(nvl(pool_size,1))/1024,0)||' KB' info
from gv$aw_calc
order by info desc;

Database (RAC) Information
---------------------------------
Total PGA Inuse Size: 511207 KB
Total OLAP Page Size: 97576 KB
OLAP Pages Occupying: 19%