Cacher is the code snippet organizer for pro developers

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;

COL area           FORMAT A6
COL metric         FORMAT A35
COL metric_value   FORMAT A20
COL interpretation FORMAT A70

WITH
pga AS (
  SELECT
    MAX(CASE WHEN name = 'aggregate PGA target parameter' THEN value END) AS pga_target,
    MAX(CASE WHEN name = 'total PGA allocated'            THEN value END) AS pga_alloc,
    MAX(CASE WHEN name = 'maximum PGA allocated'          THEN value END) AS pga_max,
    MAX(CASE WHEN name = 'cache hit percentage'           THEN value END) AS pga_hit,
    MAX(CASE WHEN name = 'over allocation count'          THEN value END) AS pga_overalloc,
    MAX(CASE WHEN name = 'extra bytes read/written'       THEN value END) AS pga_extra_rw,
    MAX(CASE WHEN name = 'global memory bound'            THEN value END) AS pga_gmb
  FROM v$pgastat
),
sga AS (
  SELECT
    ROUND(SUM(CASE
                WHEN pool = 'shared pool' AND name = 'free memory'
                THEN bytes
                ELSE 0
              END) / 1024 / 1024, 2) AS shared_free_mb
  FROM v$sgastat
),
lc AS (
  SELECT ROUND(SUM(reloads) / NULLIF(SUM(pins),0) * 100, 2) AS reload_pct
  FROM v$librarycache
),
rc AS (
  SELECT ROUND(SUM(getmisses) / NULLIF(SUM(gets),0) * 100, 2) AS rowcache_miss_pct
  FROM v$rowcache
),
hp AS (
  SELECT
    MAX(CASE WHEN name = 'parse count (hard)'  THEN value END) AS hard_parse,
    MAX(CASE WHEN name = 'parse count (total)' THEN value END) AS total_parse
  FROM v$sysstat
  WHERE name IN ('parse count (hard)', 'parse count (total)')
),
bc AS (
  SELECT
    SUM(CASE WHEN name = 'physical reads'  THEN value ELSE 0 END) AS physical_reads,
    SUM(CASE WHEN name = 'db block gets'   THEN value ELSE 0 END) AS db_block_gets,
    SUM(CASE WHEN name = 'consistent gets' THEN value ELSE 0 END) AS consistent_gets
  FROM v$sysstat
  WHERE name IN ('physical reads', 'db block gets', 'consistent gets')
)
SELECT area,
       metric,
       metric_value,
       interpretation
FROM (
  SELECT 'PGA' AS area,
         'PGA target (MB)' AS metric,
         TO_CHAR(ROUND(pga.pga_target / 1024 / 1024, 2)) AS metric_value,
         CAST(NULL AS VARCHAR2(70)) AS interpretation
  FROM pga
  UNION ALL
  SELECT 'PGA',
         'Max PGA allocated (MB)',
         TO_CHAR(ROUND(pga.pga_max / 1024 / 1024, 2)),
         CASE
           WHEN pga.pga_target IS NULL OR pga.pga_max IS NULL THEN 'Indetermine'
           WHEN pga.pga_max > pga.pga_target THEN 'Pic au-dessus de la cible PGA'
           WHEN pga.pga_max > pga.pga_target * 0.85 THEN 'Pic proche de la cible PGA'
           ELSE 'Marge PGA correcte'
         END
  FROM pga
  UNION ALL
  SELECT 'PGA',
         'PGA cache hit (%)',
         TO_CHAR(ROUND(pga.pga_hit, 2)),
         CASE
           WHEN pga.pga_hit IS NULL THEN 'Indetermine'
           WHEN pga.pga_hit >= 98 THEN 'Tres bon'
           WHEN pga.pga_hit >= 90 THEN 'Acceptable'
           ELSE 'Faible'
         END
  FROM pga
  UNION ALL
  SELECT 'PGA',
         'Over allocation count',
         TO_CHAR(pga.pga_overalloc),
         CASE
           WHEN pga.pga_overalloc IS NULL THEN 'Indetermine'
           WHEN pga.pga_overalloc = 0 THEN 'Aucune surallocation'
           ELSE 'PGA trop juste'
         END
  FROM pga
  UNION ALL
  SELECT 'PGA',
         'Extra read/write (MB)',
         TO_CHAR(ROUND(pga.pga_extra_rw / 1024 / 1024, 2)),
         CASE
           WHEN pga.pga_extra_rw IS NULL THEN 'Indetermine'
           WHEN pga.pga_extra_rw < 100 * 1024 * 1024 THEN 'Faible'
           WHEN pga.pga_extra_rw < 1024 * 1024 * 1024 THEN 'A surveiller'
           ELSE 'Important : tris/hash debordent sur disque'
         END
  FROM pga
  UNION ALL
  SELECT 'PGA',
         'Global memory bound (MB)',
         TO_CHAR(ROUND(pga.pga_gmb / 1024 / 1024, 2)),
         CASE
           WHEN pga.pga_gmb IS NULL THEN 'Indetermine'
           WHEN pga.pga_gmb >= 50 * 1024 * 1024 THEN 'Bon'
           WHEN pga.pga_gmb >= 10 * 1024 * 1024 THEN 'Moyen'
           ELSE 'Bas : pression PGA possible'
         END
  FROM pga
  UNION ALL
  SELECT 'SGA',
         'Shared pool free memory (MB)',
         TO_CHAR(sga.shared_free_mb),
         CASE
           WHEN sga.shared_free_mb IS NULL THEN 'Indetermine'
           WHEN sga.shared_free_mb < 20 THEN 'Faible marge'
           ELSE 'Marge visible'
         END
  FROM sga
  UNION ALL
  SELECT 'SGA',
         'Library cache reloads (%)',
         TO_CHAR(lc.reload_pct),
         CASE
           WHEN lc.reload_pct IS NULL THEN 'Indetermine'
           WHEN lc.reload_pct <= 0.1 THEN 'Tres bon'
           WHEN lc.reload_pct <= 1 THEN 'A surveiller'
           ELSE 'Elevé : shared pool possiblement trop petit'
         END
  FROM lc
  UNION ALL
  SELECT 'SGA',
         'Row cache miss (%)',
         TO_CHAR(rc.rowcache_miss_pct),
         CASE
           WHEN rc.rowcache_miss_pct IS NULL THEN 'Indetermine'
           WHEN rc.rowcache_miss_pct <= 0.1 THEN 'Tres bon'
           WHEN rc.rowcache_miss_pct <= 1 THEN 'A surveiller'
           ELSE 'Elevé'
         END
  FROM rc
  UNION ALL
  SELECT 'SGA',
         'Hard parse (%)',
         TO_CHAR(ROUND(hp.hard_parse / NULLIF(hp.total_parse, 0) * 100, 2)),
         CASE
           WHEN hp.hard_parse IS NULL OR hp.total_parse IS NULL OR hp.total_parse = 0 THEN 'Indetermine'
           WHEN hp.hard_parse / hp.total_parse * 100 <= 3 THEN 'Bon'
           WHEN hp.hard_parse / hp.total_parse * 100 <= 10 THEN 'A surveiller'
           ELSE 'Elevé : shared pool ou SQL mal mutualisé'
         END
  FROM hp
  UNION ALL
  SELECT 'SGA',
         'Buffer cache hit (%)',
         TO_CHAR(ROUND((1 - (bc.physical_reads / NULLIF(bc.db_block_gets + bc.consistent_gets, 0))) * 100, 2)),
         CASE
           WHEN bc.physical_reads IS NULL
             OR bc.db_block_gets IS NULL
             OR bc.consistent_gets IS NULL
             OR (bc.db_block_gets + bc.consistent_gets) = 0
             THEN 'Indéterminé'
           WHEN (1 - (bc.physical_reads / (bc.db_block_gets + bc.consistent_gets))) * 100 >= 95 THEN 'Bon'
           WHEN (1 - (bc.physical_reads / (bc.db_block_gets + bc.consistent_gets))) * 100 >= 90 THEN 'Moyen'
           ELSE 'Faible'
         END
  FROM bc
)
ORDER BY 1, 2;