Gli Access Path, cosa sono

Gli Access Path sono le modalità con cui i dati sono recuperati da un database Oracle. 

Volendone fare un elenco, abbiamo:

1. Full Table Scans
2. Rowid Scans
3. Index Scans
• Assessing I/O for Blocks, not Rows
• Index Unique Scans
• Index Range Scans
• Index Range Scans Descending
• Index Skip Scans
• Full Scans
• Fast Full Index Scans
• Index Joins
• Bitmap Indexes
4. Cluster Access
5. Hash Access
6. Sample Table Scans

Full Table Scans

Questo tipo di scansione legge tutte le righe della tabella filtrando quelle che non rispondono al criterio di selezione dello statement. Oracle legge i blocchi di dati sequenzialmente e siccome questi sono adiacenti, riesce a leggere più blocchi contigui in una singola chiamata I/O grazie al parametro di inizializzazione DB_FILE_MULTIBLOCK_READ_COUNT. 

Oracle utilizza questo tipo di access path nei seguenti casi:

- Mancanza di Indici

- Grandi quantità di dati: se l’ottimizzatore rileva che saranno usati la maggioranza dei blocchi della tabella, allora userà un Full Table Scan anche in presenza di indici.

- Piccole tabelle: quando una tabella occupa meno blocchi di quelli indicati nel parametro DB_FILE_MULTIBLOCK_READ_COUNT sotto l’high water mark, allora il database può leggere i dati in una singola chiamata I/O. 

- Alto grado di parallelismo

Un alto grado di parlallelismo per una tabella costringe l’ottimizzatore a un full table scan. In questo caso esaminare il campo DEGREE della ALL_TABLES per determinare tale grado. 

- Si usa l’hint FULL(alias tabella) 

In questo caso è possibile usare anche l’hint CACHE o NOCACHE per indicare all’ottimizzatore dove mettere i dati recuperati. L’opzione CACHE pone i blocchi nel buffer-cache alla fine dell’LRU list.

- Si esegue la query in parallelo

Rowid Scan

Il rowid è un identificativo di una riga in cui è specificato il datafile e il data-block contenente la riga. Per accedere a una tabella by rowid, oracle prima li deve ottenere dalle righe selezionate dalla clausola where dello statement attraverso un index-scan di uno o più indici della tabella. Dopodiché localizza ogni riga basandosi sul suo rowid. Quindi l’ottimizzatore utilizza un rowid generalmente dopo averlo ricavato da un indice.

Index Scan

In questo metodo, una riga è recuperata scansionando l’indice, usando i valori delle colonne specificate nello statement. Se lo statement richiede solo le colonne dell’indice, allora oracle legge i dati direttamente dall’indice senza accedere alla tabella.  Un indice contiene non solo il valore della colonna indicizzata, ma anche il rowid delle righe della tabella che hanno quel valore. 

Un index scan può essere dei seguenti tipi:

- Index Unique Scan

Questa scansione ritorna al massimo un singolo rowid. Viene eseguito se lo statement contiene un vincolo UNIQUE o PRIMARY KEY che garantisce l’accesso a una sola riga. Può essere utilizzato anche quando nello statement è specificato l’hint INDEX(alias index_name). In tale caso però si specifica di usare l’indice ma non la modalità di accesso (range scan o unique scan).

- Index Range Scan

E’ la classica operazione di accesso ai dati. Essa può essere limitata o non limitata. I dati sono restituiti in ordine ascendente rispetto alle colonne indicizzate. Più righe con gli stessi valori delle colonne sono ordinate in modo ascendente secondo il rowid. Se si utilizza la clausola ORDER BY l’ottimizzatore verifica se l’ordinamento corrisponde a quello di base dell’access path, se è così allora evita il sort. L’ottimizzatore usa un range scan quando trova una o più colonne principali di un indice specificate nella condizione del WHERE. La condizione colonna1 like ‘A%’ non si traduce mai in un range scan. E’ possibile utilizzare l’hint INDEX_DESC() per ottenere un range scan ordinato in senso discendente. 

L’ottimizzatore è influenzato nella sua decisione della percentuale di blocchi a cui accedere, non dal numero di righe. Questa percentuale è detta index clustering factor (indice del fattore di raggruppamento). Questo index clustering factor è una proprietà dell’indice e si riferisce alla diffusione dei valori di una colonna dentro un data-block di una tabella. Un basso indice indica che singole righe sono concentrate in pochi blocchi, al contrario un alto indice indica che singole righe sono “spalmate” randomicamente su più blocchi. Quindi un elevato index clustering factor comporta un aumento del costo del range scan dello statement.

- Index Skip Scan

Spesso la scansione dei blocchi di un indice è più veloce che la scansione di blocchi di tabelle. Questo tipo di access path consente di suddividere un indice composito in sottoindici più piccoli. Nello skip scan la colonna iniziale dell’indice non viene indicata nello statement, quindi il database determina un numero di sottoindici logici per il numero di valori distinti della colonna iniziale. E’ vantaggioso quando la colonna principale dell’indice, quella che viene saltata, non contiene molti valori.

- Full Scan

Un full index scan elimina le operazioni di ordinamento. Esso legge dai singoli blocchi. Oracle può usare tale access path nelle seguenti situazioni: 

•  Una clausola Order By che corrisponde alle seguenti richieste è presente nella query:
  - Tutte le colonne in Order By sono indicizzate
  - L’ordine delle colonne in Order By è lo stesso delle colonne dell’indice
  - La clausola Order By contiene tutte le colonne dell’indice o un suo sott’insieme.
• La query richiede un sort merge join. In questo caso il database può eseguire un full index scan al posto di un full table scan, seguito da un ordinamento. Ciò può avvenire nei seguenti casi:
  - Tutte le colonne nella query sono anche nell’indice.
  - L’ordine delle colonne indicate nella query è lo stesso delle colonne principali dell’indice. 
  - Una clausola Group By è presente nella query e le colonne indicate sono presenti nell’indice.

- Fast Full Index Scan

Rappresenta un’alternativa al full table scan quando l’indice contiene tutte le colonne che sono necessarie alla query e almeno una colonna ha un vincolo NOT NULL. Questo tipo di scansione prende i dati dall’indice stesso senza accedere alla tabella. Il database legge l’intero indice mediante letture multiblocco e diversamente dal full index scan, può essere eseguito in parallelo.

E’ possibile specificare il fast full index scan nel parametro di inizializzazione OPTIMIZER_FEATURES_ENABLE oppure con l’hint INDEX_FFS. 

- Index Joins

Un index Joins rappresenta un hash-join di molti indici che insieme contengono tutte le colonne della tabella indicate nella query. Se il database usa questo access path allora l’accesso alla tabella non è necessario in quanto tutti i dati possono essere recuperati dai valori delle colonne degli indici. Non è possibile usare un index join per eliminare le operazioni di ordinamento. L’hint per abilitare tale access path è INDEX_JOIN.

- Bitmap Indexes

Un Bitmap Indexes utilizza un bitmap per i valori chiave e una funzione di mappatura che converte ogni posizione del bit in un rowid. 

Cluster Access

Il cluster scan è usato per recuperare tutte le righe che hanno lo stesso cluster key value da una tabella memorizzata in un cluster indicizzato. In un cluster indicizzato, il database memorizza tutte le righe con lo stesso cluster key value in uno stesso blocco dati. Nell’eseguire un cluster scan, Oracle prima prende i rowid da uno delle righe selezionate dalla scansione sul cluster index, poi localizza le righe basandosi su questo rowid.

Hash Access

In un hash cluster tutte le righe con lo stesso hash value sono memorizzate nello stesso blocco dati. Oracle utilizza un hash scan per localizzare le righe in un hash cluster basandosi su un hash value. Oracle prima calcola l’hash value applicando la funzione di hash al cluster key value specificato nello statement, poi scansione i blocchi contenenti le righe con quell’hash value.

Sample Table Scan

Si tratta di una scansione “a campione” dei dati di una tabella oppure di un complesso statement. Questo access path viene utilizzato quando la clausola from di uno statement include la clausola SAMPLE oppure SAMPLE BLOCK. Per eseguire questa modalità il database legge una specifica percentuale di righe (o blocchi nel caso SAMPLE BLOCK)  della tabella. Esempio: lo statement SELECT * FROM employees SAMPLE BLOCK (1); legge l’1% dei blocchi della tabella.

L’ottimizzatore sceglie l’access path da applicare basandosi su 2 fattori:

1. Disponibilità dell’access path per quello statement
2. Stima dei costi di esecuzione usando ogni access path disponibile o una combinazione di questi.

Viene scelto l’access path o combinazione di essi con costo minore.

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.

Hard parse e Soft parse

Quando un codice applicativo viene eseguito, Oracle verifica se questi sia già stato usato precedentemente verificandone la presenza nella library cache. Se esiste e può essere condiviso allora Oracle riusa il codice già esistente. Questo approccio è detto soft parse o library cache hit.

Se, al contrario, il codice non c'è, allora il database deve costruire un nuova versione eseguibile di tale codice. Questo approccio è detto hard parse o library cache miss.

Il library cache miss può verificarsi sia in fase di analisi che in fase di esecuzione. 

Quando un'applicazione effettua una parse call per uno statement, se la parsed representation dello statement non esiste nella library cache, allora Oracle analizza il nuovo statement e memorizza la sua parsed form nella shared pool. 

E' possibile ridurre il library cache miss sulle parse call assicurandosi che tutti gli statement SQL siano presenti nella shared pool, ogni volta che sia possibile.

Se un'applicazione esegue una execute call di un statement SQL e se la porzione eseguibile dello statement, costruita precedentemente, è stata deallocata dalla library cache per fare spazio a un altro statement, allora Oracle rianalizza lo statement implicitamente creando una nuova shared SQL area e quindi lo esegue.

E' possibile ridurre il library cache miss sulle execution calls allocando più memoria alla library cache.

L'hard parse consuma molte più risorse del soft parse.

Le risorse richieste per un soft parse sono:

CPU, library cache latch gets.

Le risorse richieste per un soft parse sono:

CPU, library cache latch gets, shared pool latch gets.

Schematicamente, si può così sintetizzare quanto detto:
1) parsing statement (controllo sintassi e semantica)
2) hashing statement (restituisce un hash value)
3) controllo esistenza hash value in library cash
..3.1) se l'hash value esiste allora Oracle verifica se lo statement in library cash è identico a quello in analisi
......3.1.1) i due statement sono identici:
soft parse: esegui lo statement mediante il piano di esecuzione già associato.
......3.1.2) i due statement non sono identici:
hard parse: genera alberatura e piano di esecuzione associato allo statement
..3.2) se l'hash value non esiste
hard parse: genera alberatura e piano di esecuzione associato allo statement
..3.3) esegui statement mediante piano di esecuzione associato.