Memory Tuning: SGA & PGA

SELECT name, value, description
FROM   v$parameter
WHERE  name IN (
  'memory_target', 'memory_max_target',
  'sga_target',    'sga_max_size',
  'pga_aggregate_target',
  'db_cache_size', 'shared_pool_size',
  'large_pool_size', 'streams_pool_size'
)
ORDER BY name;

-- Quick mode diagnosis:
-- memory_target  > 0              → AMM  (Oracle controls SGA + PGA)
-- sga_target     > 0, memory = 0  → ASMM (Oracle controls SGA pools only)
-- both = 0                        → MSMM (fully manual)

-- Enable advisor if off
-- ALTER SYSTEM SET db_cache_advice = ON;

SELECT size_for_estimate          AS cache_mb,
       size_factor,
       estd_physical_read_factor  AS read_factor,
       estd_physical_reads        AS estd_reads,
       ROUND((1 - estd_physical_read_factor) * 100, 1) AS pct_io_reduction
FROM   v$db_cache_advice
WHERE  name       = 'DEFAULT'
AND    block_size = (SELECT TO_NUMBER(value)
                     FROM   v$parameter
                     WHERE  name = 'db_block_size')
ORDER BY size_for_estimate;

-- Apply (dynamic with ASMM — no restart needed)
ALTER SYSTEM SET db_cache_size = 12G SCOPE=BOTH;

-- Verify the change landed
SELECT component,
       current_size/1024/1024/1024      AS current_gb,
       min_size/1024/1024/1024         AS min_gb,
       last_oper_type
FROM   v$sga_dynamic_components
ORDER BY current_size DESC;

-- Which segments are consuming the most buffer cache?
SELECT o.object_name, o.object_type,
       b.cnt * (SELECT TO_NUMBER(value)
                FROM v$parameter
                WHERE name = 'db_block_size') / 1024 / 1024 AS cached_mb
FROM  (SELECT obj, COUNT(*) cnt FROM v$bh WHERE dirty='N' GROUP BY obj) b
JOIN   dba_objects o ON o.data_object_id = b.obj
WHERE  b.cnt > 100
ORDER BY b.cnt DESC
FETCH FIRST 20 ROWS ONLY;

-- Buffer cache hit ratio (target: > 95% on OLTP)
SELECT ROUND(
  (1 - (p.value / (c.value + g.value))) * 100, 2
) AS hit_ratio_pct
FROM   v$sysstat p, v$sysstat c, v$sysstat g
WHERE  p.name = 'physical reads'
AND    c.name = 'db block gets'
AND    g.name = 'consistent gets';

-- Physical reads per second since startup (the absolute measure)
SELECT ROUND(p.value /
       GREATEST(((SYSDATE - s.startup_time) * 86400), 1)) AS phys_reads_per_sec
FROM   v$sysstat p, v$instance s
WHERE  p.name = 'physical reads';

-- What is eating the shared pool?
SELECT name, bytes/1024/1024 AS mb,
       ROUND(bytes * 100 / SUM(bytes) OVER (), 1) AS pct
FROM   v$sgastat
WHERE  pool = 'shared pool'
ORDER BY bytes DESC
FETCH FIRST 15 ROWS ONLY;

-- Critical: free memory in shared pool
SELECT bytes/1024/1024 AS free_mb
FROM   v$sgastat
WHERE  pool = 'shared pool'
AND    name = 'free memory';
-- Below 50MB on a busy system = risk of ORA-04031

-- Shared pool advisor
SELECT shared_pool_size_for_estimate  AS pool_mb,
       estd_lc_size                   AS lib_cache_mb,
       estd_lc_memory_object_hits     AS estimated_hits,
       estd_lc_time_saved_factor      AS time_saved_factor
FROM   v$shared_pool_advice
ORDER BY shared_pool_size_for_estimate;

-- avg_execs_per_sql < 2 = bind variable problem / library cache bloat
SELECT COUNT(DISTINCT sql_id)           AS unique_sqls,
       SUM(executions)                    AS total_execs,
       ROUND(SUM(executions) /
         COUNT(DISTINCT sql_id), 1)      AS avg_execs_per_sql
FROM   v$sqlarea
WHERE  parsing_schema_name NOT IN ('SYS','SYSTEM','DBSNMP');

-- Root cause 1: bind variable problem — library cache bloat
-- Look for force_matching_signature with thousands of variants
SELECT force_matching_signature,
       COUNT(DISTINCT sql_id)  AS variants,
       SUM(sharable_mem)/1024/1024 AS total_mb,
       MIN(SUBSTR(sql_text,1,80)) AS sample_sql
FROM   v$sqlarea
WHERE  parsing_schema_name NOT IN ('SYS','SYSTEM')
GROUP BY force_matching_signature
HAVING COUNT(DISTINCT sql_id) > 100
ORDER BY total_mb DESC
FETCH FIRST 10 ROWS ONLY;

-- Root cause 2: large non-shareable objects eating shared pool
SELECT name, namespace,
       sharable_mem/1024  AS kb,
       loads, executions, invalidations
FROM   v$db_object_cache
WHERE  sharable_mem > 102400   -- > 100KB objects
AND    type NOT IN ('CURSOR','PACKAGE','PROCEDURE','FUNCTION')
ORDER BY sharable_mem DESC;

-- Fix: pin critical packages to prevent aging out
EXEC DBMS_SHARED_POOL.KEEP('APPS.ORDER_PROCESSING_PKG', 'P');
EXEC DBMS_SHARED_POOL.KEEP('APPS.PRICING_ENGINE_PKG',   'P');

-- Verify what is pinned
SELECT owner, name, namespace, kept
FROM   v$db_object_cache
WHERE  kept = 'YES';

-- Work area mode breakdown — the most important PGA metric
SELECT name, value,
       CASE
         WHEN name = 'work areas executed in optimal mode'
           THEN 'No spill — all in memory'
         WHEN name = 'work areas executed in onepass mode'
           THEN 'One temp spill — tolerable'
         WHEN name = 'work areas executed in multipass mode'
           THEN 'ALARM — heavy disk spill'
       END AS meaning
FROM   v$pgastat
WHERE  name LIKE 'work areas%';

-- Current PGA allocation snapshot
SELECT name, value/1024/1024 AS mb
FROM   v$pgastat
WHERE  name IN (
  'aggregate PGA target parameter',
  'total PGA inuse',
  'total PGA allocated',
  'maximum PGA allocated'
);

-- PGA target advisor — find the elbow
SELECT pga_target_for_estimate/1024/1024  AS pga_target_mb,
       pga_target_factor,
       estd_pga_cache_hit_percentage       AS estd_hit_pct,
       estd_overalloc_count
FROM   v$pga_target_advice
ORDER BY pga_target_for_estimate;

-- Apply (dynamic — no restart needed)
ALTER SYSTEM SET pga_aggregate_target = 8G SCOPE=BOTH;

-- 12c+: set a hard ceiling to prevent runaway PGA
ALTER SYSTEM SET pga_aggregate_limit = 16G SCOPE=BOTH;

-- Sessions currently spilling to temp
SELECT s.sid, s.username, s.sql_id,
       t.tablespace,
       t.blocks * 8 / 1024  AS temp_mb_used
FROM   v$session       s
JOIN   v$tempseg_usage t ON t.session_addr = s.saddr
ORDER BY temp_mb_used DESC;

-- Historical: SQL with the most disk sorts
SELECT sql_id, sorts, disk_sorts,
       ROUND(disk_sorts * 100 / NULLIF(sorts, 0), 1) AS pct_disk_sorts,
       ROUND(elapsed_time/1e6/NULLIF(executions,0), 2) AS avg_ela_sec
FROM   v$sqlarea
WHERE  disk_sorts > 0
AND    executions > 10
ORDER BY disk_sorts DESC
FETCH FIRST 15 ROWS ONLY;

-- Temp usage by user right now
SELECT u.username,
       SUM(s.blocks) * 8 / 1024  AS temp_mb_used
FROM   v$sort_usage s
JOIN   dba_users    u ON u.user_id = s.user#
GROUP BY u.username
ORDER BY temp_mb_used DESC;

SELECT component,
       current_size/1024/1024/1024       AS current_gb,
       user_specified_size/1024/1024/1024 AS specified_gb,
       last_oper_type, last_oper_time
FROM   v$sga_dynamic_components
ORDER BY current_size DESC;

-- Step 1: Calculate required HugePages from SQL*Plus
SELECT SUM(value)/1024/1024         AS sga_mb,
       CEIL(SUM(value) / 2097152)  AS hugepages_needed
FROM   v$sga;

-- Step 2: Check current HugePages on OS (run as root)
-- grep -i hugepages /proc/meminfo
-- HugePages_Total should be non-zero and >= hugepages_needed above

-- Step 3: Set HugePages in /etc/sysctl.conf (OS level)
-- vm.nr_hugepages = <hugepages_needed>
-- sysctl -p   (apply without reboot)

-- Step 4: Disable AMM if currently using it
ALTER SYSTEM SET memory_target      = 0  SCOPE=SPFILE;
ALTER SYSTEM SET memory_max_target  = 0  SCOPE=SPFILE;
ALTER SYSTEM SET sga_target         = 40G SCOPE=SPFILE;
ALTER SYSTEM SET sga_max_size       = 48G SCOPE=SPFILE;
-- Restart required for SGA_MAX_SIZE and to activate HugePages

SELECT 'Buffer Cache Hit Ratio' AS metric,
       ROUND((1-(p.value/(c.value+g.value)))*100,2)||'%' AS value,
       CASE WHEN (1-(p.value/(c.value+g.value)))*100 < 90
            THEN 'WARNING — increase DB_CACHE_SIZE'
            ELSE 'OK' END AS status
FROM   v$sysstat p, v$sysstat c, v$sysstat g
WHERE  p.name='physical reads'
AND    c.name='db block gets'
AND    g.name='consistent gets'
UNION ALL
SELECT 'Shared Pool Free %',
       ROUND(f.bytes*100/t.bytes,1)||'%',
       CASE WHEN f.bytes*100/t.bytes < 10
            THEN 'WARNING — ORA-04031 risk'
            ELSE 'OK' END
FROM  (SELECT SUM(bytes) bytes FROM v$sgastat
       WHERE pool='shared pool' AND name='free memory') f,
      (SELECT SUM(bytes) bytes FROM v$sgastat
       WHERE pool='shared pool') t
UNION ALL
SELECT 'PGA Optimal Work Areas %',
       ROUND(opt.value*100/NULLIF(tot.value,0),1)||'%',
       CASE WHEN opt.value*100/NULLIF(tot.value,0) < 90
            THEN 'WARNING — increase PGA_AGGREGATE_TARGET'
            ELSE 'OK' END
FROM   v$pgastat opt, v$pgastat tot
WHERE  opt.name = 'work areas executed in optimal mode'
AND    tot.name = 'work areas executions - total'
UNION ALL
SELECT 'Hard Parse Ratio %',
       ROUND(h.value*100/NULLIF(t.value,0),1)||'%',
       CASE WHEN h.value*100/NULLIF(t.value,0) > 10
            THEN 'WARNING — check bind variables'
            ELSE 'OK' END
FROM   v$sysstat h, v$sysstat t
WHERE  h.name='parse count (hard)'
AND    t.name='parse count (total)';