rudydelhaye
10/30/2019 - 4:10 PM

ORCL - Memoire

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;