🧠⚙️ 8) Mémoire & I/O (SGA / PGA / Storage)

Mesures indispensables (avant)

Playbook sizing

1. Définir fenêtre + workload (pic vs normal).
2. Identifier cause #1 (AWR) + confirmer (ASH).
3. Si mémoire suspectée : isoler “le manque” (cache miss, spills, parsing).
4. Changer petit → mesurer → rollback si pas de gain.
5. Valider : DB Time ↓ + pas d’effet secondaire (swap, OS cache, etc.).

Risques

• SGA trop grande → OS cache réduit → I/O paradoxalement pire.
• PGA trop permissive → overcommit → OS pressure/swap → catastrophe.
• Masquer un problème (SQL/design) plutôt que le corriger.

Shared pool : parsing & contention

• Hard parse = coût CPU + contention (library cache / mutex).
• SQL non bindé → explosion de curseurs → shared pool pressure.
• Invalidations (DDL, stats) → reparse → instabilité.

Indices à collecter

-- Vue synthèse mémoire SGA
SELECT * FROM v$sga;

-- Parsing / cursors (pistes)
-- (à compléter selon votre standard / AWR)
          

Le plus important : corréler avec AWR/ASH (DB CPU, parsing, mutex waits).

Actions (ordre recommandé)

1. Réduire hard parse : binds, normalisation SQL, réduire SQL “uniques”.
2. Limiter invalidations (change management sur DDL/stats).
3. Si preuve : ajuster SGA (progressif) et re-valider (DB Time).

Signaux typiques

• Waits : direct path read/write (temp).
• AWR : workarea executions “onepass/multipass”.
• Plans : gros hash join avec A-Rows énormes (souvent cardinalité fausse).

Mesures rapides

SELECT * FROM v$pgastat;

-- (selon version) workarea hist:
-- dba_hist_sqlstat + plan stats / AWR sections
          

Actions (pragmatiques)

1. D’abord réduire A-Rows (filtrer plus tôt, meilleurs joins/index/stats).
2. Ensuite ajuster PGA/workareas si preuve (pas au feeling).
3. Surveiller TEMP et OS (swap) après changement.

UNDO : stabilité & long queries

• UNDO sert aux lectures cohérentes et rollback.
• Transactions longues + grosses modifications = pression UNDO.
• Peut impacter des requêtes longues (cohérence) si rétention insuffisante.

SELECT begin_time, undoblks, txncount, maxquerylen
FROM v$undostat
ORDER BY begin_time DESC FETCH FIRST 12 ROWS ONLY;
          

Erreurs typiques

• ORA-01652 : TEMP insuffisante (ou spills incontrôlés).
• ORA-01555 : snapshot too old (souvent UNDO / long query / DML).

Actions (ordre)

1. Corriger SQL/plans/stats pour réduire sorts/hash.
2. Contrôler parallel / batch windows.
3. Ajuster TEMP/UNDO sizing si preuve (et monitoring).

Causes fréquentes

• Commits trop fréquents (commit par ligne).
• Redo logs sur stockage à latence élevée / saturé / partagé.
• Génération redo excessive (sur-indexation, patterns DML).

Actions

1. Optimiser commit côté app (batch commit).
2. Isoler/optimiser stockage redo (faible latence).
3. Réduire redo généré (design/index).

Validation

Comparer avant/après (fenêtre comparable) :
- log file sync ↓
- log file parallel write ↓
- p95/p99 commit/app ↓
          

ASM : points sensibles

• Diskgroups : distribution I/O et risque “hot disks”.
• Rebalance : consomme I/O → à planifier / limiter.
• Diskgroup proche full : allocations moins souples.

SELECT name, state, type, total_mb, free_mb
FROM v$asm_diskgroup;
          

Preuve “DB vs infra”

1. AWR/ASH : waits I/O dominants ? quels SQL_ID ?
2. Mapper files/tablespaces → diskgroups/LUN.
3. Comparer latence entre fichiers : hotspot ou global ?

Actions (ordre)

1. Réduire I/O via SQL/index/partition pruning.
2. Réduire TEMP spill (PGA + rewrite + stats).
3. Optimiser ASM/distribution/rebalance uniquement après preuve.

SGA/PGA → TEMP → I/O

flowchart TD
  Q[SQL] --> A[Cardinalité/plan]
  A --> B[Workareas (PGA)]
  B -->|insuffisant| T[TEMP spill]
  T --> IO[I/O ↑]
  IO --> DT[DB Time ↑]
  A --> C[Reads (buffer cache)]
  C -->|miss| PR[Physical reads]
  PR --> IO
          

PGA

SELECT * FROM v$pgastat;

-- repérer si "over allocation count" et autres signaux
          

TEMP & UNDO

-- TEMP per session
SELECT s.sid, s.serial#, s.username, s.sql_id,
       tu.tablespace, tu.segtype, tu.blocks
FROM v$tempseg_usage tu
JOIN v$session s ON s.saddr = tu.session_addr
ORDER BY tu.blocks DESC;

-- UNDO stats
SELECT begin_time, undoblks, txncount, maxquerylen
FROM v$undostat
ORDER BY begin_time DESC FETCH FIRST 12 ROWS ONLY;
          

Redo waits / ASM

-- waits system
SELECT event, total_waits, time_waited/100 time_s
FROM v$system_event
WHERE wait_class <> 'Idle'
ORDER BY time_waited DESC FETCH FIRST 25 ROWS ONLY;

-- ASM diskgroup (si ASM)
SELECT name, state, type, total_mb, free_mb
FROM v$asm_diskgroup;