Oracle 8i orientato al datawarehouse

 II
-  progettazione del data warehouse; 
-  progettazione delle basi di dati multidimensionali; 
-  processo di ricerca delle informazioni. 
 
Oggi, sempre di più le decisioni vengono prese servendosi di sofisticata 
intelligence dei mercati.  
Oracle8i cambia il modo di gestire e accedere alle informazioni al fine di 
fornire una gestione dei dati efficiente, affidabile e sicura per l'elaborazione di 
volumi elevati di transazioni in linea (OLTP, On-Line Transactional Processing) e 
le applicazioni di DataWarehouse ad elevata frequenza di query.  
In particolare per il Data Warehouse, Oracle8i dispone di funzioni sofisticate di 
gestione riepilogativa con cui memorizzare le entità più comunemente interrogate, 
in modo da ridurre sensibilmente i tempi di elaborazione delle query.  
Infatti, tra le operazioni più comuni in un sistema di data warehousing  vi sono 
quelle che ritornano dati aggregati come totali, medie o similari; tali interrogazioni 
al DB generano mediamente un elevato overhead nel processamento dei dati p 
ortando a tempi di risposta inaccettabili e cali nelle prestazioni dell'intero sistema. 
 Per evitare che questa situazione si presenti ogni volta che si riesegue una 
query di questo tipo, se ne memorizzano i risultati in tabelle aggregate dette viste 
materializzate che in combinata con il tool di Summery Management possono 
essere aggiornate, modificate e sottoposte ad un refresh automatico. 
 Si introduce inoltre il concetto di dimensione che rappresenta il modo in cui 
Oracle porta a conoscenza dell’ottimizzatore il legame gerarchico-funzionale dei 
dati; l’ottimizzatore si arricchisce dunque di ‘intelligenza’ avendo un quadro del 
modello E-R sottostante. 
La vera novità consiste però in una potente tecnologia di trasformazione delle 
query che integra tutte queste componenti nella funzionalità di query-rewrite: 
quando un utente richiede pesanti operazioni sulle tabelle (come join e funzioni 
aggregate), se i dati richiesti sono già disponibili in alcune delle viste materializzate 
allora l’ottimizzatore può riscrivere la query sottoposta, in maniera del tutto 
trasparente all’utente, per velocizzarne l’esecuzione. 
L’ottimizzatore usa diversi metodi per riscrivere una query che permettono 
di utilizzare le viste materializzate per recuperare anche solo una parte dei dati 
richiesti o per recuperarli, eventualmente, anche in formato diverso al fine di 
ripristinare successivamente quello desiderato attraverso la conoscenza fornita dalle 
dimensioni.  
 III
Si deduce quindi che sebbene non sia obbligatorio definire delle dimensioni 
in un data warehousing, esse aiutano l’ottimizzatore a capire lo schema e la 
relazione tra i dati per riscrivere ed eseguire query sempre più complesse nel minor 
tempo possibile. 
Si espande quindi la funzionalità di fascia alta di Oracle8 offrendo 
prestazioni più elevate e maggiore facilità di gestione introducendo funzioni più 
ricche di analisi, nonché nuovi livelli di trasparenza per l'ottimizzazione delle query 
e l’uso di aggregate predefinite. Tali funzioni vengono utilizzate in maniera 
trasparente dai tool Oracle Business Intelligence, Oracle Discoverer e prodotti 
Express, onde garantire prestazioni più elevate e migliore produttività, a vantaggio 
di un migliore e più rapido supporto decisionale. 
Tutte queste nuove feature,  in modo particolare il query-rewrite, sono state 
implementate e testate in un ambiente reale di Internal Auditing per la creazione di 
un datawarehouse, sia attraverso la componente grafica del Discoverer che dalla 
linea di comando del Worksheet. 
Con l’ausilio del tool SQL Trace Facility è stato possibile monitorare 
l’esecuzione di alcune query sul database in presenza o meno delle relative viste 
materializzate con risultati più che soddisfacenti. 
Si è osservato come l’utilizzo della vista materializzata da parte 
dell’ottimizzatore riduca fortemente  
- itempi di utilizzo di CPU fino a circa il 60%; 
- i tempi di elapsed di circa il 95%; 
- il numero di accessi a disco per il 98.4%; 
- il numero di buffer impegnati in operazioni di SELECT  
il tutto a fronte di un reperimento di 89.474 record! 
Molte le novità anche a livello di programmazione PL, tra cui 
l’introduzione del concetto di transazione autonoma, la costruzione di indici basati 
su funzioni, il binding di un’intera collezione di dati in un’unica operazione detta 
‘bulk bind’, mentre l’SQL si arricchisce di utili funzioni di aggregazione tra i dati 
quali CUBE e ROLLUP. 
 
  
 
 
 
 
 
 
Finalmente giunta al traguardo di questo mio percorso formativo, lungo e 
impegnativo, voglio con queste poche righe dedicare un pensiero a tutti coloro che 
mi hanno accompagnata in questo cammino, contribuendo, nel bene o nel male, ad 
arricchire le mie esperienze di vita e a fortificare il mio carattere. 
 Ringrazio il mio relatore per i suoi consigli e la sua disponibilità, l’Ing. 
Franco Chiesa, Renzo Cravero,  Adriano Policastro per la loro considerazione, gli 
amici della CCS, il mio tutor aziendale dott. Carlo Masera per avermi seguita in 
tutto questo periodo e per gli utili suggerimenti, e tutti coloro che hanno permesso 
lo svolgimento di questo lavoro di tesi nonché contribuito alla mia crescita 
professionale. 
 Voglio ricordare i miei amici Alessandra, Davide D., Davide Z., Domenico, 
Enrico, Flora e Leonardo, Luca, Luca D., Marco P., Massimo, Pe’, Salvo, Silvia, 
Stella, Titti per le risate e l’allegria e tutti coloro che hanno condiviso con me 
questa esperienza chiamata ‘Politecnico’. 
 Un grazie particolare lo rivolgo a Riccardo che tanto ha significato per me 
in questi anni, presente nella maggiorparte del tempo nella mia vita di tutti i giorni. 
 A mio padre e a mia madre che hanno creduto in me, appoggiando, 
sostenendo e incoraggiando in ogni momento le mie iniziative, a loro dico  
‘grazie di cuore’. 
 
 
Anna Gasparo. 
1 
 
 
 
 
 
 
 
 
1.1 Introduzione 
 
 
Oracle è un sistema complesso costituito da una base di dati di tipo relazionale e 
da un insieme di programmi software in cui è possibile distinguere un kernel e i 
tool.  
Il kernel è da considerarsi come una sorta di filtro tra le richieste utente 
(rivolte in linguaggio SQL) e i dati memorizzati nella base di dati. 
I tool sono invece strumenti di aiuto per la creazione di applicazioni e per la 
gestione del database stesso. 
Il database relazionale Oracle è quindi definibile come un insieme di tavole 
contenenti le informazioni di interesse la cui individuazione avviene riferendosi 
alle caratteristiche logiche e non fisiche dei dati stessi. 
Infatti, non occorrerà riferirsi a puntatori e indirizzi fisici ma sarà sufficiente 
individuare il nome della tavola e delle sue colonne; ciò garantisce la totale 
indipendenza delle applicazioni dai dati e dalla loro locazione fisica. 
Una vasta serie di operatori consente inoltre di combinare i dati in vario  modo 
e di navigare agevolmente tra i dati di più tabelle. 
Ovviamente, in Oracle oltre alle tavole esistono altri tipi di oggetti quali, ad 
esempio, indici e cluster (il cui scopo è quello di velocizzare la ricerca dei dati), 
rollback segment (il cui scopo è quello di memorizzare i dati nella versione 
precedente la modifica). 
 
1 – Architettura e struttura fisica di Oracle 
 
 2
Tra le caratteristiche tecniche di Oracle ritroviamo: 
- supporto efficiente della multiutenza: cioè utenti diversi possono 
condividere i dati con la sicurezza della consistenza in lettura, ottenuta 
senza sacrificare le qualità delle performance. Inoltre il lock a livello di 
riga consente l’aggiornamento concorrente di record diversi della stessa 
tavola da parte di processi utente differenti. 
- La consistenza in lettura garantisce: 
1. la possibilità di leggere i dati in uno stato immutato per tutta la 
durata dell’esecuzione della lettura stessa, nonostante le 
operazioni di modifica che possono aver luogo su quei dati; 
2. la possibilità di leggere i dati del database senza dover 
attendere il termine di operazioni di manipolazione sugli stessi; 
-    L’accesso controllato ai dati è garantito attraverso la creazione di utenti 
ORACLE con precisi privilegi. 
 Inoltre in ogni database Oracle esiste sempre il cosidetto Data Dictionary, 
ovvero un insieme di tavole, da usare in sola lettura, contenente informazioni di 
varia natura circa il database stesso (nomi di tavole e colonne esistenti nel 
database, username e privilegi degli utenti, spazi liberi e allocati nel database, 
ecc.).  
 In realtà il dizionario dati è costituito anche da molte viste (cioè tavole 
logiche che proiettano delle combinazioni di dati estratti dalle tabelle vere e 
proprie allo scopo di migliorarne l’interpretazione o di restringere l’accesso ai 
dati stessi). Una parte di tali viste è visibile solo al DBA. 
 
 
 
 
 
 
 
 
 
 
 
 
 
1 – Architettura e struttura fisica di Oracle 
 
 3
 
1.2 Componenti dell’architettura 
 
La struttura fisica di Oracle si compone di tre parti fondamentali [1]: 
- Strutture di memoria per la memorizzazione di dati e applicativi: Oracle 
richede alcune strutture di memoria quali la SGA, la PGA e le context 
aree. La System Global Area (SGA) contiene dati condivisi tra tutti gli 
utenti e le informazioni di controllo per il database; la Program Global 
Area (PGA) contiene informazioni di controllo per un singolo processo 
utente; la context area è una porzione di memoria che contiene uno 
statement SQL. 
- i processi di background: gestiscono il database stesso; alcuni di questi 
hanno importanza vitale per il DB. 
- i files: un database Oracle si compone di uno o più file di sistema 
operativo. Questi file contengono tutti i dati del database e non occorre 
che siano contigui su disco.  
 
 
1.2.1 La memoria SGA 
 
Esistono due tipi di memoria in Oracle: una condivisa da tutti gli utenti ed una 
dedicata per ogni singolo utente. 
L’SGA (System Global Area) e’ la regione di memoria condivisa da tutti gli 
utenti connessi al database che contiene dati e informazioni di controllo sull’istanza 
RDBMS; si compone delle seguenti parti: 
 
 
Fig. 1.1 System Global Area 
 
1 – Architettura e struttura fisica di Oracle 
 
 4
• La Shared Pool Area composta da : 
Fig. 1.2   Shared Pool Area 
 

Data Dictionary : colleziona informazioni nel dizionario dati del motore 
(nomi di tutte le tavole e le viste usate da DB, nomi di tutte le colonne con 
relativi datatype, privilegi di tutti gli utenti Oracle). 

Shared SQL Areas: contiene informazioni usate per l’esecuzione dei singoli 
statement SQL (testi degli statement SQL, PL/SQL e relativi piani di 
esecuzione,…); 
L’esecuzione di uno statement SQL e si compone delle seguenti parti 
principali:        
      -     analisi sintattica; 
- analisi della nomenclatura tramite il Data  
          Dictionary; 
- controllo dei privilegi per l’accesso ai dati; 
- individuazione del piano di esecuzione tramite  
          l’ottimizzatore delle query. 
I processi server utilizzano l’area condivisa di Pool nella SGA per 
parsificare ed eseguire lo statement. 
Una richiesta di esecuzione di uno statement SQL corrisponde ad una 
chiamata di parse inoltrata al server durante la quale esso procede ad 
analizzarne la sintassi, la nomenclatura e i privilegi. Successivamente controlla 
le aree SQL condivisa nella memoria di Pool; ogni area di questo tipo contiene 
un unico statement SQL già parsificato. 
Fig. 1.3 Operazioni di parse 
1 – Architettura e struttura fisica di Oracle 
 
 5
Il processo server cerca nelle aree SQL uno statement SQL parsificato 
identico (in termini anche di spazi tra le parole e di case sensitive) a quello 
che vuole gestire e se esiste lo esegue immediatamente, altrimenti deve 
parsificarlo e creare una nuova area SQL condivisa. 
Dopo aver eseguito lo statement SQL, il processo server legge i dati 
per l’operazione richiesta dal Buffer Cache e ritorna i risultati direttamente 
all’utente (nel caso di ambiente dedicato) o alla coda di risposta del 
dispatcher (nel caso di ambiente multi-threaded). 
 
 
• Il Data Buffer e’ l’area tampone per la migrazione dei dati veri e propri gestito 
secondo l’algoritmo LRU; rappresenta la parte di memoria a cui destino più 
spazio. 
 
 
Fig. 1.4 Data Buffer Cache 
 
 
 
• Il Redo Logo Buffer  tiene traccia delle attività svolte, gestito secondo una 
struttura a coda circolare. 
 
Fig. 1.5 Redo Log Buffer 
 
 
1 – Architettura e struttura fisica di Oracle 
 
 6
1.2.2   I processi di background 
 
I processi di background scambiano informazioni con la System Global Area per 
gestire e ottimizzare le performance  del DB [1]. 
In Oracle esistono più processi di background, di cui quattro obbligatori per la 
sua esistenza: 
• DBWR (Database Writer)  : Obbligatorio. Gestisce  il Buffer Cache in modo  
che i processi utenti possano sempre trovare buffer 
liberi. Riporta i buffer modificati sui data files e 
aggiorna il control file; 
 
• LGWR (Log Writer)           : Obbligatorio. Scarica le informazioni contenute 
nel Log Buffer sugli on-line log files ogni quanto 
di  tempo (per saturazione o in base alla volontà 
dell’amministratore del sistema). 
 
• Smon (System Monitor)     : Obbligatorio. Lavora in collaborazione con il  
Log Buffer (se ancora vivo) e con gli on-line Log 
files (per problemi   più gravi) per ripristinare il 
sistema in caso di crash.  
 
• Pmon (Process Monitor)    : Obbligatorio. Lavora in collaborazione con il  
Log Buffer (se ancora vivo) e con gli on-line Log 
files (per problemi   più gravi) per ripristinare il 
sistema in caso di crash. 
Esegue il rollback delle transazioni non 
committate e gestisce il rilascio delle risorse 
detenute dal processo che ha subito il failure. 
 
• ARCH (Archivier)   : Archivia i file di redo log on-line  saturi per  
     usarli in caso di recovery. 
 
• LCKn     : Usato in ambiente Parallel Server per la gestione  
    dei lock dei processi. 
• CKPT      : Aggiorna le intestazioni dei file del database  
1 – Architettura e struttura fisica di Oracle 
 
 7
dopo un checkpoint; se tale processo non è 
configurato se ne occupa il Log Writer; 
 
• SNPn    : Usato in ambiente distribuito per la gestione  
   degli Snapshot (repliche di tabelle); 
 
 
 
Fig. 1.6 Processi di background 
 
• USER Process (lato Client) : Quando un utente si connette al database viene  
creato automaticamente un processo utente per 
l’esecuzione degli applicativi; gli utenti 
comunicano  con il DB attraverso un processo 
Server a cui vengono passati gli statement SQL . 
Il processo User attende dunque i risultati e li 
gestisce; 
• SERVER Process                 : Parsifica gli statement SQL, li esegue, legge i  
blocchi di dati da disco nella SGA ed invia i 
risultati al processo USER. 
 
  
                            Fig. 1.7 Processo User e Server 
 
 
1 – Architettura e struttura fisica di Oracle 
 
 8
1.2.3   I file 
 
Oracle utilizza i seguenti files che formano il database del sistema: 
• Data files               :   contengono tutti i dati organizzati in strutture logiche  
      come indici e tabelle;  
 
• On-line Log Files   :  ogni DB deve essere composto di almeno due gruppi di  
on-line log files; essi servono a memorizzare  tutte le 
modifiche apportate ai dati (backup dei dati in memoria). 
Se ne mantengono più copie per prevenire una situazione 
di failure e la decisione sul numero di gruppi da 
mantenere spetta al DBA. 
Quando si apre il DB vengono aperti anche questi files, 
per default almeno due gruppi con due membri ognuno. 
 
• Archived Log Files:   usato per l’archiviazione dei files di log. 
 
• Control file             :   ne esiste uno e contiene informazioni vitali per il  
database (struttura, nome, dimensione e locazione di ogni 
data file e di ogni redo log file). 
Per motivi di sicurezza viene clonato e le copie 
mantenute su dischi diversi; la scelta sul numero di copie 
e sulla loro locazione spetta ovviamente al DBA. Oracle 
permette di aprire ed aggiornare concorrentemente le 
diverse copie del control file (detti files multiplexati o di 
mirroring). 
Creato automaticamente quando il DBA usa il comando 
CREATE DATABASE, non può essere successivamente 
editato. 
Utilizzato per l’avvio ed il recovery del DB. 
1 – Architettura e struttura fisica di Oracle 
 
 9
 
Fig. 1.8 Architettura Oracle 
 
L’insieme dell’area di SGA + i processi di background formano la cosidetta 
ISTANZA di Oracle (motore), mentre i data files rappresentano il database su cui 
lavorare.  
Si potrebbe fare la seguente analogia: l’istanza Oracle è l’equivalente di un 
motore per una macchina; senza di esso non si potrebbe fare nulla. Come per il 
motore esistono delle specifiche sulla potenza anche l’istanza è caratterizzabile 
attraverso dei parametri di controllo presenti nel parameter file init.ora, anch’esso 
facente parte dell’istanza Oracle. 
 Quando Oracla esegue lo startup di un’istanza, va a leggere tale file per 
configurarla [1]. 
 Esso stabilisce ad esempio: 
 
- la dimensione della SGA ed il numero di buffer in essa presenti; 
- identifica il control file per lo startup; 
- il numero di utenti concorrenti che possono accedere al database; 
- il numero di transazioni concorrenti che il sistema può 
processare. 
- può essere personalizzato. 
 
 
 
 
 
 
 
1 – Architettura e struttura fisica di Oracle 
 
 10
1.3 Tablespace, data file ed extent. 
 
Il Sistema Operativo organizza i dati in strutture dette data files, mentre Oracle  
raggruppa i data file  in strutture chiamate TABLESPACES, cioe’ in strutture 
logiche non visibili al Sistema Operativo.  
 
 
Fig. 1.9 Struttura logica e fisica di un database Oracle 
 
Oracle vede il DB sia dal punto di vista logico (in termini di tablespaces) che 
dal punto di vista fisico (in termini di data file). 
Un DB ha uno o più tablespaces ad esso associato, ognuno di quali consta di 
almeno un data file. 
 
 
Fig. 1.10  Gestione del DB in tablespace 
 
1 – Architettura e struttura fisica di Oracle 
 
 11
Quando si crea un database, Oracle crea automaticamente una tablespace 
SYSYEM contenente uno o più data file; System contiene informazioni sugli 
statemente PL/SQL ed il Data Dictionary per cui deve essere sempre  on-line. 
Fig. 1.11 Tablespace System 
 
Poiché il S.O. riconosce solo datafile, il DBA crea sia il tablespace che il 
datafile nello stesso comando. 
I tablespace permettono di gestire il database e lo spazio di sistema 
controllandone lo spazio, allocando nuovi datafile; inoltre possono essere portati 
nello stato off-line per scopi di backup o recovery. 
 Le strutture di base utilizzate in un database sono i blocchi e gli extent per 
contenere gli oggetti (o segmenti) memorizzati nei datafile. Al momento della 
creazione di un datafile o di un oggetto se ne specifica la dimensione. 
Fig. 1.12  Blocchi ed extents 
 
 L’unità più piccola di memorizzazione è ovviamente il blocco, la cui 
dimensione è definita da DBA al momento della creazione del database.  
     
Fig. 1.13  Organizzazione di un blocco Oracle