set lines 200
SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||
nvl(lower(ssn.machine), ins.host_name) "SESSION",
to_char(prc.spid, '999999999') "PID/THREAD",
to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE",
to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE"
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
v$instance ins, v$statname stat1, v$statname stat2
WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'
AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max'
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND ssn.paddr = bgp.paddr (+)
AND ssn.paddr = prc.addr (+);
SELECT sum(value)/1024/1024 "TOTAL SGA (MB)" FROM v$sga;
select sum(pga_max_mem)/1024/1024 "TOTAL MAX PGA (MB)" from v$process;
SESSIONS = (1.5 * PROCESSES) + 22
PROCESSES = (SESSIONS - 22) / 1.5
pga_aggregate_limit = 5mb * PROCESSES
PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET
SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB",
shared_pool_size_factor "Size Factor",
estd_lc_time_saved "Time Saved in sec"
FROM v$shared_pool_advice;
SELECT * from v$db_cache_advice;
select ROUND(pga_target_for_estimate /(1024*1024)) PGA_TARGET, PGA_TARGET_FACTOR, BYTES_PROCESSED, ESTD_TIME, ESTD_PGA_CACHE_HIT_PERCENTAGE from v$pga_target_advice;
SELECT
pool,
SUM(bytes/1024/1024) AS "Shared Pool Size MB"
FROM
v$sgastat
WHERE
pool = 'shared pool'
GROUP BY
pool;
SELECT
pool,
name,
SUM(bytes/1024/1024) AS "free Shared Pool MB"
FROM
v$sgastat
WHERE
pool = 'shared pool' AND name = 'free memory'
GROUP BY
pool, name;