Il query optimizer di Oracle

Il query optimizer di Oracle (l'ottimizzatore) è un software adibito a determinare il modo più efficiente per eseguire uno statement SQL.

Uno statement SQL infatti può essere eseguito in molti modi, in full-table scan, index scan, nested loop, hash join e l'ottimizzatore sceglie qual è la via migliore per eseguirlo.

Quando un utente lancia uno statement SQL,  l'ottimizzatore compie i seguenti 3 passi:

1) genera un set di potenziali piani di esecuzione basandosi sui percorsi (o metodi) per raggiungere i dati (access paths) e hints.

2) stima i costi di ogni piano di esecuzione elaborato basandosi sulle statistiche del dizionario dati.

3) paragona i diversi piani di esecuzione e sceglie quello con costo minore.

Quindi il suo output è il piano di esecuzione migliore, ovvero una sequenza di combinazioni di passi che il database compirà per eseguire lo statement.

Nel compiere i 3 steps indicati, l'ottimizzatore effettua le seguenti operazioni:

1) Valuta le espressioni e le condizioni dello statement che contengono delle costanti

2) Trasforma lo statement in uno equivalente e più efficiente

3) Sceglie l'obiettivo dell'ottimizzazione

4) Sceglie gli access path per raggiungere i blocchi di dati

5) Sceglie le priorità rispetto alle condizioni di join, ovvero nel caso di statement con condizioni di join, determina quali coppie di tabelle mettere in join prima e quali dopo.

Il componente dell'ottimizzatore che si occupa di trasformare gli statement è il cosiddetto trasformer.

Per l'operazione di trasformazione dello statement in uno equivalente, il trasformer utilizza molte tecniche tra cui:

1) il view merging

2) il predicate pushing

3) il subquery unnesting

Nel view merging ogni vista a cui si fa riferimento nello statement viene espansa dal parser in un blocco query separato. Il blocco rappresenta essenzialmente la definizione della vista, e quindi il risultato di essa. Una delle possibilità per l'ottimizzatore è quello di analizzare questo blocco separatamente e generare un sotto-piano per la vista. L'ottimizzatore elabora quindi il resto della query utilizzando il sotto-piano al fine di generare un piano di esecuzione globale. Questa tecnica di solito porta ad un piano di esecuzione non ottimale perché la vista viene ottimizzata separatamente.

Ad esempio, se per la seguente vista:

CREATE VIEW employees_50_vw AS

SELECT employee_id, last_name, job_id, salary, commission_pct, department_id

FROM employees

WHERE department_id = 50;

si lancia questo statement:

SELECT employee_id

FROM employees_50_vw

WHERE employee_id > 150;

l'ottimizzatore può usare il view marging per trasformare la query su employees_50_vw nella seguente query:

SELECT employee_id

FROM employees

WHERE department_id = 50

AND employee_id > 150;

In pratica si applica alla vista il contenuto di ogni selezione, proiezione o join.

Nel Predicate pushing l'ottimizzatore spinge "pushes" i predicati dal blocco principale al blocco della vista. Ad esempio, si supponga di avere una vista che referenzia due tabelle:

CREATE VIEW all_employees_vw AS

( SELECT employee_id, last_name, job_id, commission_pct, department_id

FROM employees )

UNION

( SELECT employee_id, last_name, job_id, commission_pct, department_id

FROM contract_workers );

lo statement

SELECT last_name

FROM all_employees_vw

WHERE department_id = 50;

viene trasformato nel seguente:

SELECT last_name

FROM ( SELECT employee_id, last_name, job_id, commission_pct, department_id

FROM employees

WHERE department_id=50

UNION

SELECT employee_id, last_name, job_id, commission_pct, department_id

FROM contract_workers

WHERE department_id=50 );

Nel subquery unnesting l'ottimizzatore in pratica trasforma una query innestata in una equivalente non innestata.

Ad esempio lo statement:

SELECT *

FROM sales

WHERE cust_id IN ( SELECT cust_id FROM customers );

viene trasformato nel seguente equivalemte:

SELECT sales.*

FROM sales, customers

WHERE sales.cust_id = customers.cust_id;

Dopo la trasformazione dello statement, vengono generati i diversi piani di esecuzione e l'ottimizzatore provvede al calcolo del costo computazionale dello statement mediante una stima.

Questa stima genera tre differenti tipi di misure:

1) Selectivity

Rappresenta una porzione di righe in un insieme di righe. Essa è legata al predicato della query, ad esempio last_name='Smith', o a una combinazione di predicati. Un predicato filtra uno specifico numero di righe di un insieme. Il valore della Selectivity può variare da 0.0 a 1.0. Il valore 0.0 indica che non ci sono righe che rispondono ai predicati, mentre un valore di 1.0 indica che tutte le righe dell'insieme rispondono ai predicati.

2) Cardinality

Rappresenta il numero di righe in un insieme. L'insieme può essere una tabella, una vista, o il risultato di una join o di un operatore GROUP BY.

3) Cost

Rappresenta l'unità di lavoro o di risorsa utilizzato in una operazione. Una operazione è da intendersi come una scansione di una tabella, l'accesso alle righe di una tabella mediante indice, il join tra due tabelle, o l'ordinamento di un insieme. L'ottimizzatore considera l'I/O, l'uso di CPU, l'uso della memoria come unità di lavoro. Quindi il costo è il numero di unità di lavoro che si prevede di sostenere quando il database esegue una query producendone i risultati.

Il piano di esecuzione che ha un costo minore sarà quello scelto dall'ottimizzatore per eseguire lo statement.