A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

Oracle

Details of PGA, SGA, db_cache, shared_pool and db/sp%, subpools

1 min read

asrblogger

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
---------- ---------- ----------- -------------- ---------- ----------