Oracle HINTs

Oracle utilizza un ottimizzatore SQL molto sofisticato e potente che e' in grado di eseguire query molto complesse. Tuttavia qualche volta l'ottimizzatore di Oracle ha bisogno di un suggerimento: questa breve paginetta riporta alcune indicazioni per usare gli HINT in Oracle.

Optimizer

L'SQL e' un linguaggio di quarta generazione, ovvero consente di dire cosa si vuole ma non come trovarlo. Gli statement SQL vengono parsificati (che e' una semplice verifica sintattica e sull'accesso ai dati) e quindi vengono analizzati dall'optimizer. L'optimizer ha il compito di decidere quale sia i modo migliore per estrarre i dati richiesti.
L'optimizer Oracle e' cost based, ovvero utilizza le statistiche disponibili sulle tabelle e sugli indici e sofisticati algoritmi per elaborare un plan (piano di esecuzione) ottimizzato dell'SQL richiesto [NdA l'ottimizzatore RBO (Rule Based Optimizer) non e' piu' supportato dalla 10.2 e comunque l'ottimizzatore CBO (CBO Based Optimizer), introdotto nella 7.0 (1992) era da tempo preferito].

Nonostante le informazioni raccolte tuttavia qualche volta l'ottimizzatore non trova il piano d'esecuzione migliore per eseguire uno statement ed e' possibile suggerire un consiglio con la sintassi degli HINT.

HINT

Gli HINT sono una feature non standard SQL di Oracle... ma molti altri RDBMS li hanno imitati! Dal punto di vista sintattico gli hint sono molto semplici perche' si tratta di commenti con l'aggiunta del segno + e dell'indicazione del commento. Quindi se all'interno sono errati o vengono eseguiti su una base dati che non li supporta semplicemente non succede nulla!

Ecco la semplice sintassi di un HINT: 


SELECT /*+ HINT */ ...

Facciamo ora qualche semplice esempio:

SELECT /*+ FULL */ FROM EMP; SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b) USE_NL (glcc glf) USE_MERGE (gp gsb) */ b.application_id , b.set_of_books_id , b.personnel_id, p.vendor_id Personnel, p.segment1 PersonnelNumber, p.vendor_name Name FROM jl_br_journals j, jl_br_balances b, gl_code_combinations glcc, fnd_flex_values_vl glf, gl_periods gp, gl_sets_of_books gsb, po_vendors p ...

L'hint e' un'indicazione su come eseguire uno statement SQL. Nel primo esempio e' suggerito di eseguire una FULL TABLE SCAN sulla tabella.
E' possibile indicare piu' hint in sucessione ed ORDERED indica che vanno svolti nell'ordine indicato. Alcuni suggerimenti richiedono dei parametri come il nome della tabella o dell'indice che vanno indicati tra parentesi. Se e' presente va usato l'eventuale alias della tabella.

L'elenco degli HINT puo' essere suddiviso in categorie: 

-- Optimization Goal 
ALL_ROWS
FIRST_ROWS(n)

-- Access Path 
FULL
ROWID
CLUSTER
HASH
INDEX
INDEX_ASC
INDEX_COMBINE
INDEX_JOIN
INDEX_DESC
INDEX_FFS
INDEX_SS
INDEX_SS_ASC
INDEX_SS_DESC
AND_EQUAL

-- Query Transformation 
NO_QUERY_TRANSFORMATION
USE_CONCAT
NO_EXPAND
REWRITE
EXPAND_GSET_TO_UNION
MERGE
STAR_TRANSFORMATION
FACT
UNNEST

-- Join Order 
LEADING
ORDERED
STAR

-- Join Operations 
USE_NL
USE_NL_WITH_INDEX
USE_MERGE
USE_HASH
DRIVING_SITE
HASH_AJ, MERGE_AJ, and NL_AJ
HASH_SJ, MERGE_SJ, and NL_SJ

-- Parallel Execution 
PARALLEL
NOPARALLEL
PQ_DISTRIBUTE
PARALLEL_INDEX
NO_PARALLEL_INDEX

-- Additional 
APPEND
NOAPPEND
CACHE
NOCACHE
PUSH_PRED
PUSH_SUBQ
QB_NAME
ORDERED_PREDICATES
CURSOR_SHARING_EXACT
DRIVING_SITE
DYNAMIC_SAMPLING
MODEL_MIN_ANALYSIS

Oltre agli hint indicati vi e' una serie di hint con il prefisso NO_ che indicano di non usare l'operazione indicata [NdA nell'elenco ne sono riportati alcuni perche' non hanno la versione in positivo].

Altri trucchi

In realta' il trucco sono gli HINT!
Pero' e' vero che vi sono altri modi per indirizzare l'ottimizzatore a fare la scelta giusta nel creare il piano d'esecuzione.

E' possibibile alterare il comportamento dell'ottimizzatore di Oracle mediante:

In questa paginetta abbiamo visto l'utilizzo degli HINT ma e' assolutamente da riportare che con un ottimizzatore cost based la raccolta e la gestione delle statistiche sulle tabelle e sugli indici e' fondamentale perche' vengano trovati i migliori execution path, almeno nella maggioranza dei casi. Ecco come raccogliere le statistiche su uno schema:
 exec DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

Infine da ultimo, ma certo non come importanza perche' sono la prima cosa, il disegno logico ed il disegno fisico di un database relazionale sono il punto di partenza di un progetto efficiente e con buone prestazioni.

Varie ed eventuali...

Pur mantenendo la stessa logica sono significative le differenze tra le versioni di Oracle. Anche gli HINT sono cambiati e cresciuti in numero nel tempo: 9.2, 10gR2, 11g, 11gR2, ...

Titolo: Oracle HINTs
Livello: Avanzato (3/5)
Data: 1 Aprile 2003
Versione: 1.0.2 - 31 Ottobre 2013 🎃
Autore: mail [AT] meo.bogliolo.name