Oracle DB — Cheat Sheet

Tag: oracle, sql, plsql, database, rdbms, performance-tuning, indexing

Livello: intermedio

TL;DR: Architettura Oracle DB, gestione transazioni con MVCC, ottimizzazione query con Execution Plan e costrutti PL/SQL essenziali.

Concetti chiave

Separazione logica e fisica: tablespace logici mappati su datafile fisici del sistema operativo.

Memoria divisa principalmente tra SGA (condivisa tra processi) e PGA (privata per sessione).

Concorrenza gestita tramite MVCC: i lettori non bloccano gli scrittori e viceversa.

I segmenti UNDO garantiscono consistenza in lettura e supporto al rollback.

I file REDO log registrano ogni modifica per garantire durabilità e crash recovery.

Ottimizzatore basato sul costo (CBO): sceglie il piano di esecuzione usando statistiche aggiornate.

B-Tree index e Bitmap index coprono carichi OLTP ad alta cardinalità e carichi DWH a bassa cardinalità.

Il Locking a livello di riga è automatico durante le operazioni DML.

PL/SQL integra logica procedurale ed esecuzione SQL direttamente dentro il motore del database.

Le viste V$ e DBA_ espongono lo stato runtime, le metriche e i dizionari dati.

Domande tipiche e risposte pronte

Q: Qual è la differenza tra SGA e PGA in Oracle?R: La SGA è la memoria condivisa tra tutti i processi e include il Buffer Cache e il Shared Pool; la PGA è allocata privatamente per ogni sessione per ordinamenti e hash join.

Q: Come gestisce Oracle la consistenza di lettura durante modifiche concorrenti?R: Usa i dati nei segmenti UNDO per ricostruire l'immagine coerente del blocco al momento dell'inizio della query, senza bloccare la tabella.

Q: Quando conviene usare un indice Bitmap rispetto a un B-Tree?R: Uso Bitmap in scenari data warehouse su colonne a bassa cardinalità e query con combinazioni AND/OR; evito Bitmap su sistemi OLTP per il lock sui blocchi.

Q: Come analizzi una query che risulta lenta in produzione?R: Estraggo l'Execution Plan con EXPLAIN PLAN o consulto V$SQL_PLAN, verifico i Full Table Scan inattesi, il costo stimato e aggiorno le statistiche dell'ottimizzatore.

Q: Che differenza c'è tra TRUNCATE e DELETE?R: DELETE è DML, genera record REDO e UNDO riga per riga ed è rollbackabile; TRUNCATE è DDL, dealloca i blocchi, resetta l'High Water Mark ed è immediata.

Insidie e errori comuni

ORA-01555 "snapshot too old": causato da UNDO tablespace insufficiente o transazioni lunghe sovrascritte.

Omettere il raccoglitore di statistiche (DBMS_STATS), portando il CBO a piani subottimali.

Chiamare funzioni PL/SQL riga per riga nelle clausole SELECT, generando elevato overhead di context-switch.

Usare costrutti su colonne indicizzate nella clausola WHERE invalidando l'uso del B-Tree ordinario.

Mancata gestione delle transazioni: dimenticare il COMMIT o ROLLBACK esplicito lasciando blocchi aperti.

Snippet e comandiSQL

```-- Generazione e visualizzazione del piano di esecuzione

EXPLAIN PLAN FOR

SELECT employee_id, last_name, salary

FROM employees

WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Raccolta statistiche aggiornate su schema o tabella

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(

ownname => 'HR',

tabname => 'EMPLOYEES',

cascade => TRUE

);

END;

/

-- Paginazione efficiente nativa (Oracle 12c+)

SELECT employee_id, first_name, last_name

FROM employees

ORDER BY employee_id

OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Blocco PL/SQL con gestione cursore e BULK COLLECT

DECLARE

TYPE t_emp_list IS TABLE OF employees%ROWTYPE;

v_emps t_emp_list;

BEGIN

SELECT * BULK COLLECT INTO v_emps

FROM employees

WHERE department_id = 20;

FORALL i IN 1..v_emps.COUNT

UPDATE employees_archive

SET salary = v_emps(i).salary

WHERE employee_id = v_emps(i).employee_id;

COMMIT;

END;

/

```

Da ripassare

[ ] Sintassi e utilizzo di analytical window functions (ROW_NUMBER, DENSE_RANK, LEAD, LAG)

[ ] Meccanismo di High Water Mark (HWM) e compattazione segmenti

[ ] Partizionamento tabelle: Range, List, Hash e Composite

[ ] Piani di esecuzione: differenza tra Index Unique Scan, Index Range Scan e Full Table Scan

[x] Differenze e casi d'uso tra B-Tree e Bitmap Index

[x] Architettura di memoria: componenti di SGA e PGA