Oracle – 12) Assistant de tuning (inputs → recommandations)

12.1 Catalogue : waits → hypothèses → actions (safe-first)

I/O (reads)

Wait	Hypothèses	Checks	Actions safe
db file sequential read	index lookup, accès aléatoires, mauvais plan, manque d’index	top SQL_ID + plan + A-Rows, segments reads	index ciblé / rewrite / stats ciblées / plan baseline
db file scattered read	full scans, mauvais join order, lecture large	plans (full scan), taille tables, filtrage	pruning/partition, index, predicate pushdown

Redo / commit

Wait	Hypothèses	Checks	Actions safe
log file sync	commits fréquents, latence redo device, LGWR pressure	commit rate, redo size/s, IO latency	batch commits, app fix, storage redo, redo groups sizing
log file parallel write	latence write redo, storage saturé	redo write latency, I/O queueing	storage/ASM tuning, redo logs sur disques rapides

Locks & contention

Wait	Hypothèses	Checks	Actions safe
enq: TX - row lock contention	hot rows, longs tx, ordre de updates	blocking sessions, objects, SQL_ID	fix app, réduire durée tx, index FK, sharding logique
enq: TM	locks table, DDL concurrent, constraints	DDL jobs, constraints, lock tree	fenêtre DDL, réduire DDL, online ops

Parse / library cache

Wait	Hypothèses	Checks	Actions safe
cursor: mutex X / library cache	hard parse, no binds, churn cursors	parse ratio, executions/calls, top SQL by parse	bind variables, cursor sharing (prudence), pool sizing

TEMP spills

Wait	Hypothèses	Checks	Actions safe
direct path write/read temp	sort/hash spills, PQ agressif, cardinalité fausse	workarea hist, SQL plan, DOP	corriger stats, réduire DOP, rewrite join/agg

12.2 Scoring & priorisation : impact SLA vs risque vs effort

Modèle simple (0-10)

Dimension	Question	Score
Impact	ça touche p99 / DB Time significativement ?	0-4
Preuve	j’ai AWR/ASH/plan qui supporte ?	0-2
Risque	effets globaux / plan tsunami ?	0-2 (inverse)
Effort	temps de mise en œuvre + rollback ?	0-2 (inverse)

Priorité = Impact + Preuve + (2 - Risque) + (2 - Effort)

Risque : ce qui fait baisser la priorité

Changement global optimiseur
Recollect stats massif sans rollback
DOP agressif en coexistence OLTP
SGA/PGA “gros saut” sans headroom OS

Exemples

Case A: log file sync + commits trop fréquents
- Impact: 4 / Preuve: 2 / Risque: 0 / Effort: 1  => très prioritaire (app fix)

Case B: "optimiser" en changeant un optimizer param global
- Impact: 3 / Preuve: 0 / Risque: 2 / Effort: 2 => faible priorité (dangereux)

12.3 Playbooks ready-to-run : OLTP / Batch / OLAP

OLTP (latence p99)

Identifier top waits + top SQL_ID (AWR/ASH)
Vérifier commits (log file sync) & locks (TX)
Stabiliser plans SQL critiques (baseline) si régressions
Actions ciblées : index utile, rewrite, binds
Valider p99 + erreurs + DB Time

Batch / ETL

Analyser TEMP spills & PQ (DOP)
Gérer coexistence : throttle PQ / fenêtre dédiée
Corriger cardinalité (stats ciblées + histo justifiés)
Optimiser I/O séquentiel vs aléatoire

OLAP

Full scans / partition pruning / compression
Plan : hash joins / group by / sort areas
TEMP sizing + workareas
PQ avec caps (DOP) + validation sur storage

A · Logique interne (moteur) : règles “safe-first”

Arbre simplifié (pseudo)

if top_wait in (log file sync, log file parallel write):
    -> focus = "commit/redo"
    -> actions: app commits + redo IO + redo sizing
elif top_wait in (TX/TM locks):
    -> focus = "contention"
    -> actions: blockers + hot rows + app pattern
elif top_wait in (temp read/write):
    -> focus = "TEMP/workareas/PQ"
    -> actions: fix cardinality + reduce DOP + rewrite join
elif top_wait in (mutex/library):
    -> focus = "parse/binds"
    -> actions: bind variables + cursor reuse + investigate churn
elif top_wait in (seqread/scaread):
    -> focus = "IO/plan"
    -> actions: plan + indexes + pruning + stats targeted
else:
    -> focus = "baseline gap"
    -> actions: collect evidence (AWR/ASH minimal set)

Règles de sécurité (anti-incident)

Ne jamais proposer un “knob global” sans preuve (optimizer params, huge memory jumps).
Proposer d’abord : actions ciblées et réversibles.
Toute action inclut : validation + stop rules + rollback.

Format sortie (JSON-like)

{
  "focus": "redo/commit",
  "priority_actions": [
    {"rank":1,"title":"Reduce commit frequency","risk":"low","proof":"log file sync"},
    {"rank":2,"title":"Validate redo device latency","risk":"medium","proof":"parallel write"}
  ],
  "scripts": ["SQL: sessions","SQL: waits","SQL: redo/undo","SQL: temp"],
  "rollback": "documented",
  "stop_rules": ["p99 +30%","TEMP>90%","errors>threshold"]
}

B · Library scripts : minimal set AWR/ASH/V$ (copiable)

Sessions actives

SELECT sid, serial#, username, sql_id, module, action,
       status, event, wait_class, seconds_in_wait
FROM v$session
WHERE status='ACTIVE'
ORDER BY seconds_in_wait DESC;

Top waits (non-idle)

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;

TEMP top consumers

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;

Redo / UNDO signaux

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

-- redo size per interval (approx)
SELECT name, value
FROM v$sysstat
WHERE name IN ('redo size','user commits','user rollbacks');

DBMS_XPLAN (plan)

-- Remplacer :sql_id et :child
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(':sql_id', :child, 'ALLSTATS LAST'));

C · Intégration IDEO-Lab (Roadmap) : de “page JS” à “outil Django”

V1 (rapide)

Form JS côté front (comme ici)
Export texte (copy) + impression
Génération d’un “tuning ticket” (template) rempli

V2 (Django)

POST formulaire → view Django → JSON recommandations
Historique des runs (modèle: TuningAssistantRun)
Export PDF/Markdown

V3 (semi-automatique)

Upload AWR/ASH (ou texte) → parsing
Auto-detection top waits/top SQL
Recommandations + “scripts package”

Pour V3, on évite la magie : parsing robuste + validation humaine (toujours).

🧮 12) Assistant de tuning (inputs → recommandations)

🎛️ Formulaire (inputs)

📌 Résultat (priorités & scripts)

🧾 Scripts (copiable)

Catalogue : waits → actions

Scoring & priorisation

Playbooks “ready-to-run”

Logique interne (moteur)

Library scripts (AWR/ASH/V$)

Intégration IDEO-Lab (Roadmap)