with PGA_GB as (
SELECT VALUE/(1024*1024*1024) PGA_GB
FROM V$PARAMETER WHERE NAME = 'pga_aggregate_target'),
subpools as (
SELECT COALESCE(SUM(VALUE/(1)), NULL) subpools
FROM V$PARAMETER WHERE NAME = '_kghdsidx_count'),
SGA_GB as (
select (trunc(sum(value)/1024/1024/1024,2)) SGA_GB
from v$sga),
SHARED_POOL_GB as (
SELECT VALUE/(1024*1024*1024) SHARED_POOL_GB
FROM V$PARAMETER WHERE NAME = 'shared_pool_size'),
DB_CACHE_SIZE as (SELECT VALUE/(1024*1024*1024) DB_CACHE_GB
FROM V$PARAMETER WHERE NAME = 'db_cache_size')
select PGA_GB, SGA_GB, DB_CACHE_GB,SHARED_POOL_GB,(trunc(SHARED_POOL_GB/DB_CACHE_GB*100,2)) as PERCENT,subpools
from SHARED_POOL_GB, DB_CACHE_SIZE, SGA_GB,PGA_GB,subpools;
PGA_GB SGA_GB DB_CACHE_GB SHARED_POOL_GB PERCENT SUBPOOLS
---------- ---------- ----------- -------------- ---------- ----------
