AWS RDS e S3

Il titolo di questo post è senz’altro bizzarro, sono tre sigle che a molti potrebbero dire ben poco. AWS sta per Amazon Web Services, si tratta della piattaforma dei servizi cloud fornita da Amazon che è stata la prima a fornire questo tipo di servizi su larga scala, credo che sia ancora la più importante e sicuramente ha una offerta ampia e completa e per quel che è la mia esperienza servizi di alta qualità e affidabilità. Tutti i servizi cloud forniti da AWS sono identificati da sigle, RDS e S3 sono due di questi. RDS sta per Relational Database Service, è un servizio cosiddetto SaaS (per alcuni PaaS, il confine è labile e la stessa Amazon non sembra sbilanciarsi troppo), altra sigla Software As A Service. In pratica RDS è un database server già configurato e gestito automaticamente da AWS per cui l’utente deve solo fare le configurazioni “applicative”, creazione schemi/utenti e poco altro. Sotto c’è sempre una macchina ma non si ha accesso al sistema operativo e questo in rari casi è una limitazione. Non c’è accesso allo storage della macchina, però per le istanze RDS Oracle è predisposta una directory e il corrispondente oggetto Oracle Directory per gestire import/export con Data Pump ed external tables. La modalità di default per accedere a questo filesystem è il package PL/SQL DBMS_FILE_TRANSFER con le procedure GET_FILE e PUT_FILE che permettono di trasferire file da una istanza Oracle all’altra tramite DB LINK. Questa modalità funziona bene per i dump creati da DataPump, non funziona con i file di testo come i log a causa della limitazione che i file devono avere dimensione multipla di 512 byte (poi si parla solo di binary file e non ho capito se anche questo è un vincolo). Per accedere ai file di testo sulle istanze RDS Oracle è a disposizione un package PL/SQL RDSADMIN.RDS_FILE_UTIL con delle procedure o funzioni per mostrare il contenuto di file di testo, elencare i file presenti nella directory e per cancellarli.

S3 è un altro dei servizi AWS, Simple Storage Service, una sorta di filesystem, con diverse funzionalità accessorie. Tramite la “AWS CLI” è facile copiare da e verso “bucket” S3 file. Sulle istanze Oracle RDS è possibile attivare una opzione chiamata S3_INTEGRATION che aggiunge l’accesso a un package PL/SQL RDSADMIN.RDSADMIN_S3_TASKS che fornisce due funzioni per copiare dall’istanza RDS a un bucket S3 e viceversa dei file. Questo rappresenta quindi un modo più comodo per trasferire file da e verso istanze RDS, seppur sempre dovendo “triangolare”. Si tratta poi di gestire con attenzione le “policy” e i permessi vari. Uno dei vincoli da tenere presente nel modulo S3_INTEGRATION è che funziona solo se l’istanza RDS e il bucket S3 sono nella stessa Region, mentre poi, sempre gestendo in modo opportuno le policy dei bucket S3 è possibile copiare file tra bucket in region diverse e anche fra bucket in account diversi tramite CLI ad esempio. Occorre fare attenzione alle “ACL” ovvero ai permessi associati a file copiati nei bucket. Per default se copio da un account un file in un bucket in un altro account (dopo che a questo bucket ho associato una policy che mi permetta di copiarci file) il file mantiene i permessi di chi ha scritto il file, quindi, un po’ sorprendentemente e paradossalmente per me, il proprietario del bucket non può accedere in alcun modo al file. Per cambiare questo comportamento se si copia il file tramite CLI basta aggiungere l’opzione “–acl bucket-owner-full-control” che dice appunto che l’owner del bucket avrà pieno accesso al file copiato. Si può anche associare una policy al bucket che forzi l’uso dell’opzione, nel senso che se qualcuno cerca di copiare un file senza specificarla viene restituito un errore. Non mi risulta esista un modo per renderlo un default del bucket, ovvero fare si che qualunque file copiato nel bucket sia pienamente accessibile al proprietario del bucket.

Nelle funzioni di copia di S3_INTEGRATION non c’è la possibilità di specificare il parametro “–acl bucket-owner-full-control” per cui per copiare i file su bucket S3 di altri account occorre fare ulteriori passaggi. Ad esempio è possibile, una volta trasferito il file nel bucket usare il comando “aws s3api put-object-acl ….–acl bucket-owner-full-control“

Vi è poi un’altra casistica con cui mi sono scontrato e per la quale credo di aver trovato la soluzione ma non ho capito perfettamente la motivazione. Copiando file da un bucket S3 in una regione a un bucket in un’altra regione tramite CLI (aws s3 cp) ricevo un errore del tipo “The eu-south-1 location constraint is incompatible for the region specific endpoint this request was sent to.” Questo mi è capitato con file sopra una certa dimensione e non con file piccoli (mi pare sotto gli 8 MB). Sembra che oltre gli 8 MB per default venga cambiata la modalità di caricamento del file utilizzando una modalità “multipart”, ma poi perché questo scateni l’errore non l’ho capito.

In un primo momento ho trovato (dopo estenuanti ricerche) come soluzione l’aggiunta del parametro “–metadata-directory REPLACE”, poi andando sulla documentazione di AWS CLI V2 ho trovato il suggerimento di usare il parametro “–copy-props”, ho provato con “metadata-directive” e funziona (dovrebbe funzionare anche con “none” ma non sono sicuro di aver provato)

Riporto un esempio di policy che ho utilizzato su un bucket S3 per poterci copiare (e leggere) file da un altro account:

Una delle cose belle, fra tutto il casino e la complessità che c’è dietro è che AWS fornisce fra le altre anche delle librerie per poter fare tutto tramite script in python

Oracle ed Espressioni Regolari, introduzione

Le espressioni regolari sono delle “funzioni” che permettono di identificare delle stringhe tramite semplici regole. Ho difficoltà a spiegarlo bene in italiano quindi farò una piccola deroga alla mia regola di evitare quanto possibile terminologia mista inglese/italiano; regola stabilita quando iniziai a scrivere su questo blog ma che per la verità sopratutto negli ultimi anni ho violato spesso. Dicevo, una espressione regolare serve a definire un “pattern” ovvero una caratteristica particolare di una stringa. Tramite poi questo pattern solitamente poi si va a filtrare un sottoinsieme di stringhe che corrispondono (“match”) a questo pattern da un insieme più grande.

Volendo fare una analogia di quanto si impara già dalle basi dell’SQL le espressioni regolari possono estendere le possibilità fornite dalla clausola LIKE con i caratteri speciali “%” e “_”.

Le espressioni regolari esistono da che esistono i computer e forse da prima; sistemi evoluti (*nix) forniscono da decenni programmi di utilità che implementano ricerche basate su espressioni regolari (grep, sed, ecc). Sono molto potenti ma possono essere anche molto complesse e quindi non è banale conoscerle e utilizzarle.

Oracle ha introdotto, già con la versione 10g, il supporto all’uso di espressioni regolari tramite l’introduzione di specifiche funzioni (che mi sembra siano standard ISO SQL) che sono:

• REGEXP_LIKE
• REGEXP_INSTR
• REGEXP_REPLACE
• REGEXP_SUBSTR

Non può sfuggire che sono l’analogo di funzioni/operatori già esistenti ma più semplici.

Ciclicamente mi capita di dover affrontare problemi in cui una buona conoscenza delle espressioni regolari aiuta molto; ogni volta mi trovo in difficoltà perché non riesco a memorizzare e capire a fondo il loro funzionamento. Il fatto di non usarle con costanza e regolarità non aiuta. Ecco perché a credo almeno 15 anni dalla loro introduzione in Oracle io mi trovo adesso a scrivere un post per spiegarne un po’ di basi, nella speranza che questo mi aiuti la prossima volta che mi serviranno.

Una espressione regolare è formata da uno o più “caratteri” e “metacaratteri”. Nella forma più semplice una espressione regolare può essere formata da da una o più lettere e/o cifre, ad esempio “a”, “a1”, “pappa”. I metacaratteri servono a indicare delle regole con cui processare e interpretare i caratteri della espressione regolare.

Vediamo le regole di base per definire una espressione regolare partendo da esempi semplici.

Il metacarattere “.” Si utilizza per indicare un qualunque carattere (ad esclusione dell’”accapo”). L’espressione “c.t” equivale a una stringa che contenga la lettera “c”, seguita da un qualunque carattere a sua volta seguito dalla lettera “t”. Quindi può corrispondere alla parola “cat”, ma anche “cut”, o “cutter” o “incitare”. Se vogliamo identificare solo parole che iniziano con “c”, seguita da un carattere qualunque seguito a sua volta dalla lettera “t” allora spossiamo usare i metacaratteri “^”, per indicare che il carattere che si trova dopo deve essere all’inizio della stringa e il metacarattere “$” per indicare che il carattere prima si deve trovare alla fine della stringa. Quindi ad esempio l’espressione regolare “^c.t$” corrisponde solo a stringhe di tre caratteri in cui il primo è la lettera “c”, l’ultimo è la lettera “t” e in mezzo va bene un carattere qualunque, come ad esempio “cit”, “c1t”, “cat” ma non “catalogo”.
Per indicare più occorrenze di un carattere si possono usare dei “quantificatori”; si possono usare i metacaratteri “?”, “+” e “*” o la notazione {n,m}, {n}, {n,}. “?” si usa per indicare zero o una occorrenze; “+” per indicare una o più occorrenze; “*” per indicare zero o più occorrenze. {n,m} dove n ed m sono due numeri, indica un numero di occorrenze compreso tra n ed m (estremi inclusi); {n} indica n occorrenze esatte; {n,} indica n o più occorrenze.
Quindi, l’espressione “^c.*t$” indica ora una stringa che inizia con la lettera “c” e finisce con la lettera “t”, in mezzo ci può essere un numero qualunque di qualunque carattere, ad esempio: “ct”, “caat”, “cavallot”, “cavolo t”. All’interno di una espressione regolare possiamo raggruppare sottoespressioni racchiudendole tra parentesi tonde (che sono quindi altri metacaratteri) Possiamo quindi definire l’espressione regolare “^c(as)*t$” che corrisponde ad esempio a “ct”, “cast”, “casast”.
Per definire una lista di caratteri si possono usare le parentesi quadre ([]) per delimitarli, ad esempio: “^c[auoei]t$” corrisponde a “cat”, “cit”,”cot” ma non “cyt”. Questa espressione può essere anche scritta usando le parentesi tonde e il metacarattere “|” che indica alternative, quindi: “^c(a|u|o|e|i)t$”. All’interno di parentesi quadre, all’inizio, il metacarattere “^” assume il significato di negazione, quindi l’espressione “^c[^hjk]t$” indica una stringa che inizia con c e finisce con t e in mezzo ha un qualunque carattere che non sia “h”, “j” o “k”. Fra le parentesi quadre si possono usare “liste” di caratteri usando il metacarattere “-“, ad esempio: “^c[d-z]t$” indica che tra “c” e “t” ci può essere un qualunque carattere compreso tra “d” e “z”. Se il carattere “-“ è all’inizio, come ad esempio “^c[-dys]t$” viene interpretato come carattere e non come metacarattere, quindi l’espressione in questo caso corrisponde ad esempio alla stringa “c-t”.

Esistono dei gruppi di caratteri definiti classi POSIX che vanno inseriti fra parentesi quadre e sono a loro definiti con la notazione di questo tipo: [:class:] , riport una tabellin di quelli più comuni:

Character Class	Description
[:alpha:]	Alphabetic characters
[:lower:]	Lowercase alphabetic characters
[:upper:]	Uppercase alphabetic characters
[:digit:]	Numeric digits
[:alnum:]	Alphanumeric characters
[:space:]	Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed
[:punct:]	Punctuation characters
[:cntrl:]	Control characters (nonprinting)
[:print:]	Printable characters

Classi di caratteri predefinite POSIX

Su wikipedia ce ne sono anche altre ma non ho verificato se Oracle le supporta tutte.

Il manuale Oracle dove vengono spiegate un po’ le espressioni regolari e il loro supporto e uso sul database è “Database Development Guide” a cui rimando per approfondimenti sulle espressioni regolari in genere ma soprattutto per le specificità. Spero di fare ulteriori post per approfondire meglio l’argomento e arrivare ad un uso più avanzato e proficuo di questo potente strumento.

SQLcl 19.4 e break on report

Scrivo un breve post per segnalare una cosa bizzarra che mi è capitata, un bug su SQLcl che mi perseguitava da diverso tempo ma che solo recentemente ho analizzato e individuato. Già qualche settimana fa, dopo che ero convintamente passato a utilizzare SQLcl al posto di SQLPlus mi sono accorto che alcuni miei script non funzionavano bene. Alla fine ho trovato un attimo per approfondire e cercare di riprodurre il problema, ho fatto un test su un database interno e fatto un trace della sessione per vedere cosa accadeva. Sono così riuscito a circoscrivere il problema e creare un banale “test case” che ho sottoposto via twitteral gruppo di sviluppo di SQLcl. Il codice è molto semplice:

create table t1(a number);
insert into t1 values (1);
insert into t1 values (2);
break on report
compute sum of uno on report
select t1a.a uno from t1 t1a join t1 t1b on (t1a.a=t1b.a);

Il problema si verifica quando si usa la direttiva “break on report” in associazione a una query di join. Per gestire questa direttiva a quanto pare la query viene riscritta e qui nasce il problema, perché dalla query riscritta spariscono le condizioni di join. Infatti uno dei miei script andava su viste di sistema, ci metteva una vita a dare il risultato che poi era anche sbagliato. La mia query veniva trasformata in un prodotto cartesiano….

Nel mio caso di test anche facendo un prodotto la query restituirebbe solo due record, quindi non sarebbe un problema.  In realtà l’unica query che vedo eseguita risulta essere:

SELECT 
sum(t1a.a) AS uno, 'sum' uno_CMPFN, COUNT(*) AS ROW_CNT, 'report' AS BRKCOLKEY FROM t1 t1a, t1 t1b;

che restituisce un solo record ma qualcosa su SQLcl sembra vada in loop.

Sono ansioso di vedere se dopo la mia segnalazione al prossimo rilascio il problema sarà corretto

Oracle Program Global Area (PGA)

Premessa

Questo post è un esperimento di importazione da un documento scritto su google docs. Ho cominciato a raccogliere un po’ di appunti per fare un po’ di ordine sull’argomento nella mia testa, poi, visto che sono riuscito a scrivere qualcosa che mi sembra accettabile ho deciso di pubblicarlo, come ho fatto molte volte in passato. Ci sono sicuramente alcuni problemi di formattazione, da tempo ho alcune difficoltà con la piattaforma wordpress ma non ho tempo ne’ voglia di approfondire e risolvere questi dettagli.

In questo articolo farò una panoramica sulla componente “Program Global Area”, riferita normalmente con la sigla PGA. La versione del database a cui faccio riferimento è la 12.1 e quindi anche i riferimenti ai manuali puntano a quella versione.

La Program Global Area in Oracle è un’area di memoria di cui riporto direttamente la definizione dal manuale “Administrators Guide” tradotta alla meglio.

Una Program Global Area (PGA) è una area di memoria che contiene dati e informazioni di controllo per un processo server. Essa è una memoria non condivisa creata dal Database Oracle quando un processo server viene avviato. L’accesso alla PGA è esclusivo per quel processo, c’è una PGA per ogni processo server. Anche in processi di background allocano la loro PGA. La PGA allocata per i processi di background e i processi server attaccati a un Database Oracle vengono chiamati “total instance PGA memory” mentre l’insieme di tutte le PGA individuali è chiamato “total instance PGA” o solo “instance PGA”

Per la verità le ultime definizioni mi sembrano un po’ ambigue ma è un dettaglio irrilevante.

Riporto poi dal manuale “Performance Tuning Guide”

Un esempio di informazioni scritte nella PGA per un processo è la “run-time area” di un cursore. Ogni volta che un cursore viene eseguito una nuova “run-time area” viene creata per quel cursore nella area di memoria PGA del processo server che sta eseguendo quel cursore.

Per query complesse una grossa porzione della area “run-time” è dedicata a “work area” allocate da operatori che fanno uso intensivo di memoria, inclusi ad esempio ORDER BY, GROUP BY, ROLLUP, HASH JOIN. Un operatore di ordinamento (sort operator) usa una “work area” (la “sort area”) per eseguire l’ordinamento in memoria di un insieme di righe. Analogamente , un operatore HASH JOIN usa la “work area” (hash area) per costruire una tabella hash dal suo input (left input).

 

Viste le definizioni generiche di PGA andiamo a vedere un po’ nel dettaglio come viene gestita la PGA.

Dimensioni delle “Work Area”

Oracle permette il controllo e la regolazione delle dimensioni delle “work area”. Generalmente “work area” più grandi possono migliorare significativamente le prestazioni di un particolare operatore al costo di un più alto consumo di memoria. Le dimensioni di “work area” possono essere classificate in :

• Optimal – quando la dimensione è sufficientemente grande da contenere i dati di ingresso e le strutture di memoria ausiliarie allocate dall’operatore SQL associato. Questa è la dimensione ideale
• One-pass – quando la dimensione della “work area” è al di sotto di quella ottimale e richiede un passaggio in più sui dati di ingresso
• Multi-pass – quando la “work area” non è sufficiente e occorrono più passaggi sui dati di ingresso

Tunable e Untunable

Oracle classifica, o almeno espone questa classificazione della PGA in sei categorie:

• SQL
• PL/SQL
• Java
• Olap
• Other
• Freeable

La parte dedicata alle work area è quella denominata “SQL”, viene anche definita “tunable” in contrapposizione alle altre che nel complesso sono definite “untunable”.

Dimensionamento della Program Global Area

Abilitando la gestione automatica della PGA (tramite il parametro PGA_AGGREGATE_TARGET con un valore maggiore di zero) Oracle dimensiona automaticamente la PGA correggendo dinamicamente la porzione di PGA dedicata alle “work area”. Se non specificato il valore di default per PGA_AGGREGATE_TARGET è il 20% delle dimensione della SGA. Il valore minimo è 10 MB. Il dimensionamento automatico gestito sulla base del parametro PGA_AGGREGATE_TARGET riguarda solo la parte definita “tunable”, quindi solo quella dedicata alle work area che in un certo senso è quella più dinamica

PGA_AGGREGATE_LIMIT

Dalla versione 12c Oracle ha introdotto il nuovo parametro PGA_AGGREGATE_LIMIT che permette di limitare l’allocazione globale di memoria per la PGA. Per default il valore di questo parametro è il valore più grande fra:

• 2 GB
• 2 * PGA_AGGREGATE_TARGET
• 3MB*PROCESSES

Può essere inferiore al 2* PGA_AGGREGATE_TARGET se tale valore supera il 90% della memoria ram totale disponibile meno la dimensione della SGA. Oracle raccomanda di non impostare PGA_AGGREATE_TARGET a un valore inferiore al suo default e per farlo l’unico modo è attraverso pfile o spfile (PGA_AGGREGATE_LIMIT). Su questa affermazione ho delle perplessità, infatti su una installazione con Oracle 12.1.0.2.0 ho fatto il seguente test:

SYSTEM@svil121 > show parameter pga
NAME TYPE VALUE
-------------------- ----------- -----
pga_aggregate_limit big integer 2856M
pga_aggregate_target big integer 2700M
SYSTEM@svil121 > alter system set pga_aggregate_limit=2900 m;
System SET modificato.
SYSTEM@svil121 > alter system set pga_aggregate_limit=2800 m;Errore con inizio alla riga : 1 nel comando -
alter system set pga_aggregate_limit=2800 m
Report error -
ORA-02097: impossibile modificare il parametro perchÚ il valore specificato non Þ valido
ORA-00093: pga_aggregate_limit deve essere compreso tra 2856M e 100000G
02097. 00000 - "parameter cannot be modified because specified value is invalid"
*Cause: Though the initialization parameter is modifiable, the modified
value is not acceptable to the parameter.
*Action: Check the DBA guide for range of acceptable values for this
parameter.
SYSTEM@svil121 > show parameter processes
NAME TYPE VALUE
------------------------- ------- -----
processes integer 1200

La RAM totale del sistema è 8100 MB e la SGA 4096 MB, quindi il valore di default dovrebbe essere almeno 3600 MB (che rientra nel 90% della memoria del sistema meno la SGA)

Sospetto possa essere un bug (di cui non ho trovato traccia sul supporto) in quanto su una installazione 18.3 con parametro processes a 1000, target a 512 MB il messaggio mi dice che il valore deve essere tra 3000 M e 100000 G, dove 3000 M è coerente con l’indicazione 3MB*PROCESSES.

Nella documentazione relativa al parametro PGA_AGGREGATE_LIMIT si dice che nel caso la dimensione della PGA superi il valore impostato dal parametro le sessioni che stanno usando la maggior quantità di “untunable memory” avranno le chiamate annullate (ORA-04036). Se la PGA sarà ancora sopra al limite tali sessioni verranno terminate.

Sul manuale Performance Tuning vengono riportate tutte le informazioni, c’è però una piccola differenza in quanto si afferma che il valore di PGA_AGGREGATE_LIMIT non supererà il 120% della dimensione della RAM disponibile meno la dimensione totale della SGA.

La nota del supporto Oracle “Limiting process size with database parameter PGA_AGGREGATE_LIMIT” (Doc id 1520324.1) riporta qualche dettaglio in più. Fra l’altro riporta che per Oracle 12.1 c’è il limite del 120% sulla dimensione della memoria fisica meno la SGA mentre per Oracle 12.2 spiega che se il parametro viene impostato a zero il suo valore sarà pari al 90% della dimensione della memoria fisica meno la dimensione della SGA.

A controllare il rispetto del parametro PGA_AGGREGATE_LIMIT ci pensa il processo CKPT ogni 3 secondi. Viene spiegato come nell’intervallo di tempo di tre secondi fra un controllo e l’altro di CKPT può essere superato il limite e se la sessione finisce la chiamata prima non ci sono conseguenze o azioni da parte di CKPT.

Controllo dell’uso e allocazione della PGA

Per controllare quanta PGA è stata allocata e come viene usata si possono usare diverse viste di sistema che possono essere interrogate via SQL, le descriverò sinteticamente una ad una.

V$PGASTAT

Questa vista mostra le statistiche globali sulla PGA, su 12.1 conto 19 righe. Viene riportato il valore di PGA_AGGREGATE_TARGET e poi, una statistica chiamata “aggregate PGA auto target”, questo è il valore derivato da PGA_AGGREGATE_TARGET che indica lo spazio PGA che oracle cerca di dedicare alle work area. Questo valore tendenzialmente è molto vicino alla differenza tra il target e la quantità di PGA “untunable” (PL/SQL ecc).

Abbiamo poi i valori di “total PGA allocated”, “total PGA inuse” e “maximum PGA allocated” che indicano, come è facile comprendere, la dimensione totale di PGA attualmente allocata, quella effettivamente in uso e quella massima allocata dall’avvio dell’istanza.

Può essere interessante il valore di “maximum PGA used for auto workareas” che indica il massimo volume di PGA consumato in una volta da work area. Vi sono poi altre statistiche che permettono di avere una indicazione dell’efficienza della PGA per poter capire se il dimensionamento è adeguato.

V$PROCESS

Se si vuole avere il dettaglio di utilizzo della PGA da parte di un processo (e quindi relativa sessione, supponendo di usare “dedicated servers”) questa vista riporta le quattro colonne:

• PGA_USED_MEMORY
• PGA_ALLOC_MEMORY
• PGA_FREEABLE_MEMORY
• PGA_MAX_MEM

Per esempio posso usare la query:

select s.sid, s.username,
pga_used_mem/1024/1024 pga_used_mem,
pga_alloc_mem/1024/1024 pga_alloc_mem,
pga_freeable_mem/1024/1024 pga_freeable_mem,
pga_max_mem/1024/1024 pga_max_mem,
row_number() over (order by pga_max_mem) rn
from
v$session s
join v$process p on (p.addr=s.paddr)
order by 6

Per ricavare la lista delle sessioni con relativo uso di PGA. In una applicazione che usa un POOL di connessioni questo dettaglio non è secondo me molto utile, occorre aggregare per avere valori medi, massimi e totali.

V$PROCESS_MEMORY

Per scendere ulteriormente nel dettaglio abbiamo a disposizione questa vista che per ciascun processo riporta il dettaglio di uso di PGA diviso nelle categorie:

• Java
• PL/SQL
• OLAP
• SQL
• Freeable
• Other

Non sempre sono tutte presenti, nel mio caso non ho mai righe relative a Java e OLAP. Se non ho capito male nella categoria SQL ricadono le work area, il resto dovrebbe essere tutto “untunable”. Riporto un esempio:

USER@prod > select category,sum(allocated)/1024/1024 a_mb,sum(used)/1024/1024 u_mb, max(max_allocated)/1024/1024 ma_mb from V$process_memory group by category;
CATEGORY A_MB U_MB MA_MB
--------------- ---------- ---------- ----------
Freeable 606,5 0
PL/SQL 64,0559082 21,6882935 5,46675873
Other 7871,91492 502,54974
SQL 14,4066696 2,82536316 1554,26163

Anche in questo caso ho fatto un’aggregazione

V$SESSTAT

La cito solo per completezza perché non mi sembra che dia informazioni che non possiamo avere già con le viste descritte in precedenza. Questa vista di sistema mostra per ogni sessione i valori per una lunga lista di statistiche, le due che ci possono interessare qui sono:

• session pga memory
• session pga memory max

La descrizione sul manuale spiega come queste statistiche abbiano senso solo sulla V$SESSTAT e non sulla V$SYSSTAT dove evidentemente non viene riportato il dato aggregato

DBA_HIST_ACTIVE_SESS_HISTORY

Premesso che questa tabella fa parte di AWR e quindi è soggetta a licenza del “tuning e diagnostics” Pack che si paga come extra sulla Enterprise Edition, riporto questa tabella non perché io la usi di frequente ma perché mi è stata indicata come fonte per individuare query che hanno richiesto più PGA. In effetti, dall’esempio che ho riportato sopra per la V$PROCESS_MEMORY si può vedere che risulta un processo che ha occupato oltre 1500 MB di work area. A meno di vedere in tempo reale le viste V$SESSION e V$PROCESS (o catturare a intervalli regolari in stile ASH il loro contenuto) non ho trovato modi per ricavare per statement/query l’utilizzo di PGA. Una alternativa è V$SQL_WORKAREA ma questa ha solo il campo LAST_MEMORY_USED, non c’è un MAX_MEMORY_USED. Per cui sembrerebbe che facendo un raggruppamento per SQL_ID, SQL_EXEC_ID (sul quale invito alla lettura di questo) si possa avere una indicazione degli statement (sql_id) che hanno richiesto maggiore PGA in un dato momento. Facendo query su snapshot limitati (se si va oltre un certo intervallo temporale il SQL_EXEC_ID potrebbe essere lo stesso ma non necessariamente l’esecuzione, ad esempio fra un giorno e l’altro…) in effetti ASH fotografa la situazione delle sessioni attive, per ciascuna sessione ricava sia la PGA_ALLOCATED che il SQL_ID dello statement in esecuzione, quindi lo statement dovrebbe essere quello che causa l’allocazione di PGA.

select sql_id,sql_exec_id,count(*),max(pga_alloc_mem) from dba_hist_active_sess_history where snap_id>=x and snap_id<=y group by sql_id,sql_exec_id;

Conclusioni

Partendo dall’esigenza di analizzare l’utilizzo di PGA in una ambiente di produzione e di individuare i “colpevoli” di elevato uso di PGA ho fatto un ripasso di tutti gli strumenti/mezzi che Oracle mette a disposizione facendo una piccola panoramica su cosa è la PGA, su come e perché viene utilizzata e infine su dove visualizzarne le informazioni e le statistiche di uso.

Riferimenti:

1. https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=20851480822242&id=1520324.1&_afrWindowMode=0&_adf.ctrl-state=rze9i5k0x_4
2. https://weidongzhou.wordpress.com/2016/02/25/pga_aggregate_target-vs-pga_aggregate_limit/
3. https://docs.oracle.com/database/121/REFRN/GUID-E364D0E5-19F2-4081-B55E-131DF09CFDB3.htm#REFRN10328
4. https://blog.tanelpoder.com/2011/10/24/what-the-heck-is-the-sql-execution-id-sql_exec_id/
5. https://oracle-base.com/articles/12c/resource-manager-per-process-pga-limits-12cr2
6. https://fritshoogland.wordpress.com/2014/12/15/oracle-database-operating-system-memory-allocation-management-for-pga/

Oracle Data Redaction

Uno degli argomenti che ho studiato per conseguire la certificazione a Oracle 12 è stato questo modulo che va precisato che, a parte per le soluzioni cloud di Oracle, richiede la licenza per l’opzione “Oracle Advanced Security” che include un sacco di funzionalità legate alla gestione della “sicurezza”.

Al solito, quanto scrivo è tratto dalla documentazione ufficiale di Oracle e da miei test.

Oracle Data Redaction è un modulo che permette di mascherare (redact) dati restituiti dalle query lanciate dalle applicazioni. Il mascheramente avviene al momento dell’esecuzione delle query (runtime) senza influire sui vincoli di integrità o sulla modalità di memorizzazione dei dati, quindi è un sistema che può essere applicato a sistemi già in produzione. Per le sue caratteristiche è una componente complementare ad altri soluzioni facendi parte del pacchetto “Oracle Database Solutions”.

Oracle Data Redaction non fa cose poi tanto complesse, maschera i dati al volo sulla base di “policy” che stabiliscono cosa mascherare, come e quando. Il cosa tipicamente è la colonna di una tabella o vista. Il come ricade nelle sei seguenti possibilità:

1. Full redaction (default, DBMS_REDACT.FULL)
2. Partial redaction (DBMS_REDACT.PARTIAL)
3. Regular Expressions (DBMS_REDACT.REGEXP)
4. Random (DBMS_REDACT.RANDOM)
5. No redaction (DBMS_REDACT.NONE)
6. Nullify (DBMS_REDACT.NULLIFY) (da 12.2)

La creazione, la configurazione e la gestione delle policy avviene tramite il pacchetto PL/SQL DBMS_REDACT. Il pacchetto contiene varie costanti e le procedure per creare, modificare, abilitare, disabilitare o rimuore le policy.

Le policy mascherano i dati quanto una espressione fornita risulta vera. L’espressione può essere un banale “1=1” che indica sempre vero oppure qualcosa che si basa su varibili di contesto (context).

Non entro nel dettaglio mi limito a riportare un esempio di utilizzo con espressioni basate su “application context”).

L’esempio si basa su questo schema: c’è un utente “amministratore” che gestisce gli “application context” e le policy di data redaction, lo chiamo sysadmin_ctx come sul manuale; c’è un utente/schema che possiede una tabella con dati “sensibili”, lo chiamo “cristian” e infine c’è un utente che deve poter accedere ai dati della tabella con l’esclusione della colonna con la parte di dato “sensibile”, questo utente si chiama “lettore”. L’utente cristian ha una tabella che ho chiamato test1 con questo tracciato:

Name Type
----------- -------------------
ID NUMBER(10)
NOME VARCHAR2(40 CHAR)
COGNOME VARCHAR2(40 CHAR)
USERNAME VARCHAR2(40 CHAR)
PASSWORD VARCHAR2(171 CHAR)
INIZIO DATE
FINE DATE

 

Un primo esempio banale di policy è basato sul “ROLE” assegnato all’utente:

EXEC DBMS_REDACT.DROP_POLICY(OBJECT_SCHEMA=>'CRISTIAN',OBJECT_NAME=>'TEST1', policy_name => 'cristian_pol_3');
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'CRISTIAN',
object_name => 'TEST1',
column_name => 'PASSWORD',
policy_name => 'cristian_pol_3',
function_type => DBMS_REDACT.FULL,
function_parameters => NULL,
policy_description => 'nasconde password',
column_description => 'users passwords',
expression => 'sys_context(''SYS_SESSION_ROLES'',''LETTORE_ROLE'')=''TRUE''');
END;
/

Il parametro “espression” specifica l’espressione che stabilisce quando la policy deve essere applicata e i dati mascherati. Il parametro function_type=> DBMS_REDACT.FULL specifica che il mascheramento deve essere completo, in questo caso per default le stringhe vengono sostituite da uno spazio vuoto.

LETTORE@svil183p1 > select sys_context('SYS_SESSION_ROLES','LETTORE_ROLE') FROM DUAL;

SYS_CONTEXT('SYS_SESSION_ROLES','LETTORE_ROLE')
------------------------------------------------------------------------------------------------------------------------
TRUE

LETTORE@svil183p1 > select id,password from cristian.test1;

ID PASSWORD
---------- -------------------------
44
1
102
21
61
81
82
101
103
100
141
142
143
320
361
242
380
480
500
520
540
640
641
120
163
451
200
561
580
600
140
280
220
319
340
400
560
620

38 rows selected.

Se si vuole applicare modalità più sofisticate si possono definire appositi “context”:

CREATE OR REPLACE PACKAGE set_lettore_ctx_pkg IS 
PROCEDURE set_lettore_id; 
END; 
/
CREATE OR REPLACE PACKAGE BODY set_lettore_ctx_pkg IS
PROCEDURE set_lettore_id 
IS 
BEGIN 
DBMS_SESSION.SET_CONTEXT('cri_ctx_test2', 'lettore_id', 1); 
END;
END;
/
CREATE CONTEXT cri_ctx_test2 USING set_lettore_ctx_pkg;

CREATE TRIGGER set_lettore_ctx_trig AFTER LOGON ON DATABASE
BEGIN
if USER='LETTORE' THEN 
sysadmin_ctx.set_lettore_ctx_pkg.set_lettore_id;
end if;
END;
/

E quindi ecco un altro esempio di policy:

EXEC DBMS_REDACT.DROP_POLICY(OBJECT_SCHEMA=>'CRISTIAN',OBJECT_NAME=>'TEST1', policy_name => 'cristian_pol_3');
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'CRISTIAN',
object_name => 'TEST1',
column_name => 'PASSWORD',
policy_name => 'cristian_pol_3',
function_type => DBMS_REDACT.REGEXP,
regexp_pattern => '.',
regexp_replace_string =>DBMS_REDACT.RE_REDACT_WITH_SINGLE_X,
regexp_position => DBMS_REDACT.RE_BEGINNING,
regexp_occurrence => DBMS_REDACT.RE_ALL,
regexp_match_parameter => DBMS_REDACT.RE_CASE_INSENSITIVE,
policy_description => 'nasconde password',
column_description => 'users passwords',
expression => 'SYS_CONTEXT(''cri_ctx_test2'',''lettore_id'') = 1');
END;
/

 

In questo caso oltre all’espressione ho modificato la modalità di mascheramento usando un caso banale di espressione regolare, qui chi ha dimestichezza con le espressioni regolari si può sbizzarrire, questo per mascherare pezzi di numeri di carte di credito, indirizzi email ecc.

LETTORE@svil183p1 > select id,password from cristian.test1;

ID PASSWORD
---------- -------------------------
44 XXXXXXX
1 XXXXXXX
102 XXXXXXX
21
61 XXXXXXX
81 XXXXXXX
82 XXXXXXX
101 XXXXXXX
103
100 XXXXXXX
141 XXXXXXX
142 XXXXXXX
143 XXXXXXX
320 XXXXXXX
361 XXXXXXX
242
380
480 XXXXXXX
500 XXXXXXX
520 XXXXXX
540 X
640 XXXXXXX
641 XXXXXXX
120
163 XXXX
451 XXXXXXX
200 XXXXXXX
561 XXXXXXX
580 XXXXXXX
600
140 XXXXXXX
280 XXXXXXX
220 XXXXXXX
319 XXXXXXX
340
400
560 XXXXXXX
620 XXX

 

 

Oracle Partitioning: 5^ parte, operazioni sulle partizioni

Una volta che si ha una tabella partizionata vi sono diverse operazioni che si possono fare.

Partition exchange

la prima operazione di modifica su tabelle di cui parlo è secondo me molto interessante. E’ utile in ambienti di datawarehouse ma è anche un’operazione che può tornare utile nel caso si voglia passare da una tabella non partizionata a una partizionata. Di fatto questa operazione permette di scambiare il segmento tra una tabella e una partizione. Questa tecnica risulta utile nei database datawarehouse per caricare massivamente blocchi di dati. Io ora faccio un esempio di passaggio da una tabella non partizionata  a una partizionata:

SQL>CREATE TABLE ticket
2 ( atickid number(10,0),
3 atickdataora timestamp(6),
4 atickfk number(10,0),
5 atickdescrizione varchar2(100 char)
6 );
Table created.

SQL>create index idx_ticket_2 on ticket (atickdataora);
Index created.

SQL>create index idx_ticket_3 on ticket (atickfk);
Index created.

SQL>begin for i in 1..10000 loop insert into ticket (atickid,atickdataora,atickfk,atickdescrizione) values (i,sysdate-520+i/100,mod(i,100),i); end loop; end;
2 /
PL/SQL procedure successfully completed.

SQL>commit;
Commit complete.

SQL>CREATE TABLE p_ticket
2 ( atickid number(10,0),
3 atickdataora timestamp(6),
4 atickfk number(10,0),
5 atickdescrizione varchar2(100 char)
6 )
7 PARTITION BY RANGE (atickdataora) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
8 SUBPARTITION BY LIST (atickfk)
9 SUBPARTITION TEMPLATE
10 ( SUBPARTITION P_1 VALUES (1)
11 , SUBPARTITION P_2 VALUES (2)
12 , SUBPARTITION P_3 VALUES (3)
13 , SUBPARTITION P_4 VALUES (4)
14 , SUBPARTITION P_5 VALUES (5)
15 , SUBPARTITION p_others VALUES (DEFAULT)
16 )
17 ( PARTITION before_2018 VALUES LESS THAN (TO_DATE('01-01-2018','dd-MM-yyyy'))
18 ( SUBPARTITION before_2018_s1 values (DEFAULT) )
19 );
Table created.

SQL>create index idx_p_ticket_2 on p_ticket (atickdataora) local;
Index created.

SQL>create index idx_p_ticket_3 on p_ticket (atickfk) local;
Index created.

SQL>ALTER TABLE p_ticket EXCHANGE SUBPARTITION BEFORE_2018_S1 WITH TABLE ticket including indexes WITH VALIDATION ;
Table altered.
SQL>@count p_ticket
COUNT(*)
----------
10000

SQL>@count ticket
COUNT(*)
----------
0

SQL> select subpartition_name,index_name,status from user_ind_SUBPARTITIONS where INDEX_name LIKE 'IDX_P_TICKET%';
SUBPARTITION_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ --------
BEFORE_2018_S1 IDX_P_TICKET_2 USABLE
BEFORE_2018_S1 IDX_P_TICKET_3 USABLE

La gestione degli indici può essere un po’ complicata, ad esempio se ho un indici univoco sulla tabella di partenza non me lo posso portare sulla tabella partizionata con l’exchange.

 

Add, Drop, Merge e Split

Riunisco in un unico capitoletto queste quattro operazioni perché penso di avere poco da dire. Parlo di partizioni o sottopartizioni nel caso la modalità sia per lista (list) o per intervallo (range), per la modalità hash le cose sono leggermente diverse.

Se devo aggiungere nuove partizioni per liste di valori o intervalli che non erano gia previsti posso facilmente fare un “ADD”. Sembra che sulle tabelle con partizionamento INTERVAL non sia possibile farlo:

SQL>alter table p_ticket add PARTITION before_2019 VALUES LESS THAN (TO_DATE('01-01-2019','dd-MM-yyyy'));
alter table p_ticket add PARTITION before_2019 VALUES LESS THAN (TO_DATE('01-01-2019','dd-MM-yyyy'))
*
ERROR at line 1:
ORA-14760: ADD PARTITION is not permitted on Interval partitioned objects

La cosa ha un senso, le partizioni vengono aggiunte in automatico. Forse si può aggirare l’ostacolo ma non vedendone il motivo per ora non me ne occupo. Da notare che non si possono aggiungere partizioni “prima”, per questo tipo di operazioni si fa lo split.

SQL > alter table range_sales add PARTITION p_1 VALUES LESS THAN (TO_DATE('01-01-2007','dd-MM-yyyy'));
alter table range_sales add PARTITION p_1 VALUES LESS THAN (TO_DATE('01-01-2007','dd-MM-yyyy'))
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

 

In questo caso comunque:

SQL > alter table range_sales add PARTITION p_1 VALUES LESS THAN (TO_DATE('01-01-2011','dd-MM-yyyy'));
alter table range_sales add PARTITION p_1 VALUES LESS THAN (TO_DATE('01-01-2011','dd-MM-yyyy'))
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
GEOCALL@scmspagna_svil > select PARTITION_NAME,HIGH_VALUE from user_tab_Partitions where table_name='RANGE_SALES';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P0 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POTHER MAXVALUE

 

Avendo definito la partizione POTHER con limite “MAXVALUE” non posso aggiungere partizioni, qui occorre usare la funzione di “SPLIT”. Lo split è l’operazione per cui si può suddividere una partizione in due partizioni, ecco un esempio:

SQL > select PARTITION_NAME,HIGH_VALUE from user_tab_Partitions where table_name='RANGE_SALES';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P0 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POTHER MAXVALUE

SQL > alter table RANGE_SALES split partition pOTHER at (TO_dATE('01-01-2011','DD-MM-YYYY')) into (Partition P4, Partition POTHER);
Table altered.

SQL > select PARTITION_NAME,HIGH_VALUE from user_tab_Partitions where table_name='RANGE_SALES';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P0 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P4 TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POTHER MAXVALUE

La prossima possibile operazione è quella di “drop”, di eliminazione di una partizione:

SQL > alter table range_sales drop partition p0;
Table altered.

SQL > select PARTITION_NAME,HIGH_VALUE from user_tab_Partitions where table_name='RANGE_SALES';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P4 TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POTHER MAXVALUE

Infine, è possibile riunire in unica partizione due partizioni:

SQL > ALTER TABLE range_sales MERGE PARTITIONS P3, P4 INTO PARTITION P3;
ALTER TABLE range_sales MERGE PARTITIONS P3, P4 INTO PARTITION P3
*
ERROR at line 1:
ORA-14275: cannot reuse lower-bound partition as resulting partition

SQL > ALTER TABLE range_sales MERGE PARTITIONS P3, P4 INTO PARTITION P4;
Table altered.

 

La documentazione afferma che le due partizioni originali vengono eliminate e ne viene creata una nuova.

Per tutte le operazioni non ho parlato di indici, cerco di riassumere in breve come avviene la loro gestione anche se confesso di non aver testato nel dettaglio tutti i casi. Per tutte le operazioni è prevista una clausola “UPDATE INDEXES” che si occupa di gestire aggiornando gli indici. Se la clausola non è specificata in generale quello che può accadere è che gli indici globali diventano invalidi (“UNUSABLE”) mentre per gli indici “locali” sono inusabili le singole partizioni coinvolte. Nel caso di ADD ho notato che ciò non accade, probabilmente perché è una situazione più facile da gestire. Nel caso di DROP non avviene se la partizione è vuota (almeno così è risultato da un piccolo test su una tabella partizionata per intervallo.

Row Movement

Tutte le operazioni di manipolazione delle partizioni non ne sono intaccate, però per default sulle tabelle non è abilitato il “row movement”, questo significa che operazioni DML che cambiano il rowid di un record non sono possibili. Un operazione di update che modifica la chiave di partizionamento di un record in modo da farlo ricadere su una partizione diversa da quella in cui risiede è uno di quei casi (per la verità non so se ne esistono altri). Riporto un esempio completo:

SQL > select PARTITION_NAME,HIGH_VALUE from user_tab_Partitions where table_name='RANGE_SALES';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3 TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POTHER MAXVALUE

SQL > select * from range_sales;
PROD_ID CUST_ID TIME_ID C PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- ------------------- - ---------- ------------- -----------
2 2 08-03-2009 00:00:00 B 2 2 2
1 1 18-04-2010 00:00:00 A 1 1 1

SQL > update range_sales set time_id=to_date('12-01-2011','dd-mm-yyyy') where prod_id=1;
update range_sales set time_id=to_date('12-01-2011','dd-mm-yyyy') where prod_id=1
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

SQL > ALTER TABLE RANGE_SALES ENABLE ROW MOVEMENT;
Table altered.

SQL > update range_sales set time_id=to_date('12-01-2011','dd-mm-yyyy') where prod_id=1;
1 row updated.
 

Vi sono poi altre operazioni di modifica delle partizioni, ad esempio TRUNC o MOVE, per i dettagli però rimando al manuale.

 

Oracle Partitioning: 4^ parte, creazione tabelle partizionate

Cominciamo a vedere un po’ i dettagli su come si definiscono e si gestiscono tabelle e indici partizionati. Una tabella nasce come partizionata alla sua creazione. In oracle 12.1 sembra sia possibile convertire una tabella non partizionata in partizionata, si tratta però di una cosa che devo ancora studiare quindi per ora non ne scrivo. Riporto solo una piccola annotazione:  Christian Antognini, nel suo libro dopo aver descritto nel dettaglio i vari “percorsi” di accesso che può utilizzare l’ottimizzatore Oracle con tabelle partizionate, spiega come nella sua esperienza l’uso del partitioning sia una cosa che deve rientrare nel progetto iniziale di una applicazione e non una cosa che si può aggiungere a posteriori sperando di risolvere dei problemi.

Rimanendo quindi sulla versione 11.2 se abbiamo una tabella esistente, popolata non partizionata e a un certo punto decidiamo di partizionarla, non lo possiamo fare in maniera diretta con un semplice “ALTER”. Che la cosa non possa essere così diretta è anche intuitivo: comunque dovremo spostare fisicamente dei dati per distribuirli su partizioni diverse e “indipendenti”. Cominciamo quindi con un esempio di creazione di una nuova tabella partizionata per “range” su un campo data:

CREATE TABLE range_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
) 
PARTITION BY RANGE (time_id) 
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')),
PARTITION p_other VALUES LESS THAN (MAXVALUE) );

 Con questo comando si crea una tabella che ha cinque partizioni, p0 conterrà i record con valori nel campo time_id inferiori (antecedenti) al 1-1-2008, p1 quelli superiori a quella data e fino al 1-1-2009. p_other conterrà tutti i record con valori superiori a 1-1-2010 o null. Anche se poi ci sono piccoli dettagli che rendono alcune operazioni più “complicate” (vedi ad esempio qui: https://connor-mcdonald.com/2017/08/01/interval-partitioning-just-got-better/). Faccio fatica a trovare un motivo per cui l’esempio precedente non debba utilizzare l’estensione “interval partitioning” che diventerebbe ad esempio:

CREATE TABLE interval_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
) 
PARTITION BY RANGE (time_id) 
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );

 In questo caso ho omesso l’ultima partizione con il limite “MAXVALUE”. Con l'”interval partitioning” Oracle prende a riferimento l’ultima partizione e al bisogno creerà nuove partizioni che contengono intervalli di date di un mese. Per questo motivo Oracle non mi lascia eliminare l’ultima partizione:

SQL> ALTER TABLE INTERVAL_SALES DROP PARTITION P0;
Table altered.
SQL > ALTER TABLE INTERVAL_SALES DROP PARTITION P3;
ALTER TABLE INTERVAL_SALES DROP PARTITION P3
*
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped

 (ed è il tema trattato nel post di Connor McDonald che ho citato sopra)

 Inseriamo un po’ di dati a caso e vediamo cosa succede:

create sequence sinterval_sales;
insert into interval_sales (prod_id,cust_id,time_id) values (sinterval_sales.nextval,1, to_date('10-11-2004','dd-mm-yyyy'));
insert into interval_sales (prod_id,cust_id,time_id) values (sinterval_sales.nextval,1, to_date('10-11-2008','dd-mm-yyyy'));
insert into interval_sales (prod_id,cust_id,time_id) values (sinterval_sales.nextval,1, to_date('10-02-2009','dd-mm-yyyy'));
insert into interval_sales (prod_id,cust_id,time_id) values (sinterval_sales.nextval,1, to_date('10-09-2009','dd-mm-yyyy'));
insert into interval_sales (prod_id,cust_id,time_id) values (sinterval_sales.nextval,1, to_date('10-09-2010','dd-mm-yyyy'));
insert into interval_sales (prod_id,cust_id,time_id) values (sinterval_sales.nextval,1, to_date('10-05-2010','dd-mm-yyyy'));
 
SQL > select partition_name, high_value,interval from user_tab_partitions where table_name='INTERVAL_SALES';
PARTITION_NAME HIGH_VALUE INT
------------------------------ -------------------------------------------------------------------------------- ---
P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO
P2 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO
P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO
SYS_P145 TO_DATE(' 2010-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES
SYS_P146 TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES

 

 Ora vediamo un esempio di partizionamento per “lista”:

 CREATE TABLE list_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
) 
PARTITION BY list (channel_id) 
( PARTITION p0 VALUES ('A'),
PARTITION p1 VALUES ('B'),
PARTITION p3 VALUES ('C','D'),
PARTITION p_other VALUES (DEFAULT) );

 E senza esitazione passiamo a un esempio di hash partitioning:

CREATE TABLE hash_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
) 
PARTITION BY hash (prod_id) 
( PARTITION p0,
PARTITION p1,
PARTITION p3,
PARTITION p4);

 Oppure:

CREATE TABLE hash2_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
) 
PARTITION BY hash (prod_id) partitions 8;

 L’hash partitioning prevede due sintassi leggermente diverse. Nel primo caso ho la possibilità si specificare in creazione proprietà diverse per ciascuna partizione, ad esempio tablespace diverse, nel secondo gli dico solo di fare 8 partizioni.

Farei un ultimo esempio di creazione introducendo il “subpartitioning”:

CREATE TABLE p_ticket
( atickid number(10,0),
atickdataora timestamp(6),
atickfk number(10,0),
atickdescrizione varchar2(100 char)
)
PARTITION BY RANGE (atickdataora) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY LIST (atickfk)
SUBPARTITION TEMPLATE
( SUBPARTITION P_1 VALUES (1)
, SUBPARTITION P_2 VALUES (2)
, SUBPARTITION P_3 VALUES (3)
, SUBPARTITION P_4 VALUES (4)
, SUBPARTITION P_5 VALUES (5)
, SUBPARTITION p_others VALUES (DEFAULT)
)
( PARTITION before_2018 VALUES LESS THAN (TO_DATE('01-01-2018','dd-MM-yyyy'))
( SUBPARTITION before_2018_s1 values (DEFAULT) )
);
INSERT INTO P_TICKET (ATICKID,ATICKDATAORA,ATICKFK,ATICKDESCRIZIONE) VALUES (1,to_date('18-07-1973','dd-mm-yyyy'),1,'a'); 
INSERT INTO P_TICKET (ATICKID,ATICKDATAORA,ATICKFK,ATICKDESCRIZIONE) VALUES (1,to_date('21-01-2019','dd-mm-yyyy'),2,'b');

 

L’esempio sopra è un po’ particolare perché ho voluto metterci dentro un po’ di tutto. Ho utilizzato un “SUBPARTITION TEMPLATE”, è una tecnica alternativa allo specificare le sottopartizioni per ogni partizione esplicitamente. Nel caso di sottopartizionamenti di tipo INTERVAL-* l’uso del template è obbligatorio in quanto indica ad Oracle come partizionare le nuove partizioni create per gli intervalli. Il template è modificabile e incide sullo schema di sottopartizionamento delle nuove partizioni create. Poi ho anche definito per la partizione di partenza uno schema di sottopartizionamento diverso. Se avessi definito più partizioni avrei potuto definire per ciascuna partizione uno schema di sottopartizionamento diverso. Per non farla troppo lunga non riporto qui i test che ho fatto ma posso dire che se non si specifica il template le nuove partizioni avranno una unica sottopartizione che prendono tutti i valori.

Oracle Partitioning: 3^ parte, modalità di partizionamento

Siccome mi trovo a lavorare su un database con versione 11gR2 parto con le caratteristiche e funzionalità disponibili in questa versione. Cercherò poi di parlare delle estensioni introdotte con le versioni successive.

Le modalità di partionamento disponibili sono tre:

• range

• list

• hash

Vi è poi la possibilità di estendere il partizionamento a un secondo livello, cioè si partizionano a loro volta le partizioni; in questo caso si parla di “composite partitioning” o “subpartitioning”. Sono possibili tutte le combinazioni; tratto in inganno da questa parte del manuale pensavo facessero eccezione le combinazioni con primo livello basato su hash ma il manuale “SQL Language Reference” non prevede questa esclusione; nel dubbio ho provato con successo a creare una tabella partizionata per hash e sottopartizionata ancora per hash (che poi sia utile è un altro discorso).

Interessante il fatto, scoperto con dei test, che posso avere “sotto-partizionamenti” diversi per partizioni diverse, farò vedere in seguito un esempio di cosa intendo. Questo per ribadire che c’è grande flessibilità. Solo per range e hash è possibile specificare una chiave di partizionamento basata su più colonne, il numero massimo di colonne su cui è possibile basare il partizionamento è 16. L’hash partitioning su più colonne è possibile dalla versione 11.2. E’ possibile anche partizionare su colonne virtuali.

Il numero massimo di partizioni e sottopartizioni possibili è 1024K-1 (1024*1024-1), come specificato nel manuale “SQL Language Reference“, assieme ad altri limiti e dettagli.

Range partitioning

Con questo schema di partizionamento i dati vengono suddivisi sulla base di intervalli prefissati sui valori dei campi scelti come chiave. Ho già accennato all’esempio classico del campo data ma può essere anche un campo numerico. Le partizioni si definiscono impostando sempre l’estremo superiore, vi è poi la possibilità di definire una partizione che contiene tutti gli altri valori usando come limite la parola chiave “MAXVALUE”. I record per cui i campi della chiave di partizionamento sono NULL vengono messi nella partizione con limite superiore MAXVALUE. Una estensione di questa cosa, che però non permette valori null è l'”INTERVAL Partitioning” di cui spiegherò meglio i dettagli più avanti.

List partitioning

Questa schema di partizionamento si basa sulla specifica esplicita di valori per i campi chiave del partizionamento per cui i record devono cadere nella specifica partizione. L’esempio riportato dal manuale parla di singoli stati per elementi da elencare. Per i valori non specificati esplicitamente si può definire una partizione per cui anziché la lista di valori si specifica la parola chiave “DEFAULT” (nella versione 12.2 è stato introdotto l'”Automatic List Partitioning”).

Hash partitioning

Questo schema di partizionamento è particolare e come tale risulta utile solo in casi specifici. In questo caso non si predetermina la partizione di destinazione di un record, la partizione viene selezionata da Oracle sulla base di una funzione di hash che viene applicata alla chiave di partizionamento. In fase di creazione della tabella partizionata quindi si specificano i campi chiave e il numero di partizioni, per una distribuzione equa dei dati tra le partizioni Kyte spiega e dimostra nel suo libro come sia necessario che il numero di partizioni sia sempre una potenza di due.

Mi sembra di poter affermare che la maggior utilità data da questo schema di partizionamento sia quella di ridurre la concorrenza nell’accesso alle strutture di gestione del segmento in fase di inserimento e aggiornamento dati. Certo abbiamo ancora partizioni più piccole su cui lavorare e “indipendenti”, che possono essere messe offline separatamente dalle altre, però i dati sono distribuiti in modo imprevedibile fra le partizioni, quindi qualunque sia la partizione offline è possibile, se non probabile, che contenga dei dati che in quel momento servono. Se si fanno interrogazioni per intervalli questo schema di partizionamento non può che avere un peggioramento in termini di prestazioni.

Estensioni

Agli schemi base di partizionamento e sottopartizionamento descritti in precedenza si sono aggiunte nel tempo delle estensioni che aumentano la flessibilità e il campo di utilizzo del partitioning riducendo le operazioni di manutenzione. Queste estensioni sono:

• Interval partitioning

• Reference partitioning

Interval partitioning

Secondo me una delle estensioni più utili introdotte nel tempo, si tratta di una estensione del “range partitioning” che permette la creazione automatica di partizioni per intervalli determinati. Facciamo l’esempio più facile e classico, un partizionamento per intervalli di data. Supponiamo di partizionare la tabella degli ordini per anno, possiamo partire con una singola partizione per l’anno corrente, quanto si inseriranno valori con date successive Oracle crea in automatico la partizione relative a quell’anno. Prima che fosse disponibile questa estensione l’unica possibilità era pre-creare in anticipo le partizioni necessarie.

Reference partitioning

Mentre l’estensione “interval partioning” semplifica molto la gestione senza aggiungere molto alle prestazioni, il reference partitioning interviene sul lato delle prestazioni, permettendo di partizionare una tabella legata tramite una chiave esterna ad un’altra già partizionata. Lo scenario è quello classico delle tabelle in relazione, dette anche master-detail o padre-figlio. Facciamo l’esempio delle tabelle ordini, testate e righe. Abbiamo partizionato la tabella delle testate in base alla data (range partition), possiamo partizionare la tabella delle righe in modalità reference partitioning in modo che i record sulla tabella delle righe degli ordini sono partizionanati in modo che i dati di una partizione della tabella padre abbiano i record figli tutti nella stessa partizione nella tabella figlia. Questo si riflette in un miglioramento nelle prestazioni nei casi in cui i dati vengano interrogati con operazioni di join.

Partizionamento degli indici

Come è possibile partizionare una tabella è possibile partizionare gli indici, seppur con qualche differenza. Non se ne parla molto, forse perché ha poco senso, però è possibile anche partizionare un indice su una tabella non partizionata.

Una prima suddivisione sulla modalità di partizionamento degli indici è quella fra indici LOCAL e indici GLOBAL. Nel primo caso gli indici sono partizionati allo stesso modo della tabella a cui si riferiscono, questo fa si che si estenda il livello di migliore gestione conseguente al partitioning e si aumenti la “disponibilità”; se spostiamo o mettiamo offline una singola partizione ne subirà le conseguenze solo la relativa partizione sugli indici. Nel caso indici partizionati GLOBAL l’indice viene partizionato con un criterio diverso dalla sua tabella, le possibilità sono limitate a range e hash, quindi non è prevista la possibilità di partizionare globalmente un indice in modalità “LIST”. Vi è poi la possibilità di definire alla creazione un indice come “GLOBAL” ma senza nessun criterio di partizionamento, in questo caso semplicemente l’indice non è partizionato. La documentazione (qui quella relativa alla versione 18c) afferma che un indice globale è composto da un unico B-tree. Nel caso di indici partizionati globalmente o non partizionati se una partizione viene eliminata o messa offline l’indice diventa inusabile. Per poter definire un indice univoco esso deve essere globale o contenere la chiave di partizionamento. Gli indici possono essere prefissati o no, se hanno come prima parte la chiave di partizionamento sono prefissati altrimenti no, gli indici globali possono essere solo prefissati, cioè le prime colonne su cui devono essere indicizzati devono essere la chiave di partizionamento. Riassumendo quindi, gli indici partizionati vengono classificati in tre tipologie:

• “Local prefixed”

• “Local nonprefixed”

• “Global prefixed”

Oracle Partitioning: 2^ parte, i vantaggi

Come ho accennato nella prima parte i vantaggi attribuiti a Oracle partitioning sono comunemente questi tre:

• Migliori prestazioni, sia delle query che delle operazioni “DML”

• Facilitazione della gestione

• Migliore “disponibilità”

Vediamoli meglio nel dettaglio.

Migliori prestazioni

si tratta senza dubbio di quello viene maggiormente considerato quando si parla di partitioning. Il guaio è che spesso lo si fa senza una analisi un minimo approfondita di come funziona il partitioning e di come può contribuire a migliorare le condizioni.

Per molti sembra che partitioning equivalga in automatico a migliori prestazioni, soprattutto nelle interrogazioni SQL, indipendentemente dal volume dei dati e dal tipo di interrogazioni fatte su questi dati. Nel suo libro, kyte dedica molto spazio a spiegare come spesso Partitioning venga percepita come una soluzione per migliorare in automatico le prestazioni e spiega come invece il miglioramento delle prestazioni sia solo una parte dei vantaggi che può dare e che li da solo in alcuni specifici ambiti. Va ricordato che Partitioning è una “opzione” del database Oracle. Una “opzione” del database Oracle significa gia parlare di Enterprise Edition, non Standard Edition. Solo  questo significa un aumento del costo delle licenze non trascurabile. Poi la seconda regola delle “opzioni” Oracle (dopo il fatto che sono disponibili solo con la Enterprise Edition) è che sono a pagamento. Da quel che ho capito da una rapida occhiata al listino Oracle passare da licenza Standard Edition a Enterprise Edition con Partitioning significa, al netto di sconti, un aumento da 3 a 4 volte del costo delle licenze. Presumo poi che ci siano svariate tipologie di accordi commerciali che possono ridurre il rapporto di costo ma su questo ne so ben poco e se ne sapessi non ne potrei certo parlare qui; già il listino viene marcato come “confidenziale”. Il sospetto che ho, alimentato dal fatto che più volte mi è stato chiesto: “perché non usiamo partitioning?…” è che qualcuno spinga per giustificare il costo delle licenze, senza avere una visione tecnica di dettaglio.

La verità è che il partitioning da molti vantaggi, molti dei quali li ho realmente capiti solo facendo diversi test dopo aver letto la documentazione approfonditamente. In molti casi le prestazioni non cambiano o possono avere anche un peggioramento. Il tutto in cambio di una gestione leggermente diversa. Devo ammettere che prima di lavorarci e fare un po’ di prove ero prevenuto e attribuivo alla gestione del partitioning un sovraccosto superiore a quello reale. Va anche detto che versione dopo versione sono state introdotte nuove funzionalità e migliorie che hanno reso questa opzione veramente potente e flessibile.

Come accennato prima il partitioning permette di suddividere una grossa tabella in tante piccole tabelline (segmenti), la tabella viene comunque vista come una unica tabella. Il criterio con qui suddividere la tabella viene fissato all’inizio e si basa sui valori che le righe della tabella hanno per una o più colonne prefissate. Il criterio può essere per intervallo (range, ad esempio intervalli di date) o per una lista di valori (se il campo prescelto ha un numero di valori distinti “limitato”). Oppure può essere “casuale” sulla base di una funzione di hashing sui valori di un campo.

Ad esempio, supponiamo di avere la classica tabella degli ordini possiamo pensare di partizionarla per intervalli di date, ad esempio con una partizione per ogni anno. In alternativa possiamo pensare di suddividere gli ordini per filiale dalla quale sono partiti, in questo caso probabilmente può andare bene il partizionamento per lista di valori (list partitioning), la lista può comprendere uno o più valori, quindi ogni partizione può contenere gli ordini di una o più filiali. Infine, possiamo suddividere la tabella in più partizioni sulla base dell’hash dell’id univoco usato come chiave primaria. La tabella degli ordini sarà vista dalle query SQL sempre come non fosse cambiato nulla, nel senso che le query sono scritte allo stesso modo; l’ottimizzatore di Oracle però è in grado di utilizzare il cosiddetto “partition pruning”, cioè al momento di prelevare i dati può escludere le partizioni non rilevanti se nei filtri delle query sono inclusi i campi sui cui valori si è partizionata la tabella. Prendendo la nostra tabella degli ordini partizionata per anno, se andiamo a cercare gli ordini relativi solo a un singolo anno l’ottimizzatore sarà in grado di andare direttamente e solo sulla partizione su cui stanno i record per cui i valori del campo data ricadono in quell’anno. Ecco, prima di andare avanti, se interrogo la tabella senza dare un filtro sulla data ma ad esempio uno solo sull’area geografica di origine dell’ordine allora il fatto di aver partizionato la tabella non migliorerà le prestazioni, anzi potrebbe peggiorarle, avendo da esaminare più segmenti. Il concetto di base che per quella che è la mia esperienza è che se la tabella che si vuole partizionare poi non viene interrogata prevalentemente con filtri sul o sui campi per cui è partizionata allora il partitioning non da nessun vantaggio in termini di prestazioni, anzi potrebbe peggiorare la situazione. Questo considerazione sembra quasi banale ma non lo è, in base alla mia esperienza in pochi ne tengono conto quanto sentono parlare per la priva volta del partitioning.

In realtà poi vi sono meccanismi complessi come le elaborazioni parallele che possono comunque beneficiare del partitioning anche in casi avversi, non mi addentro in questi casi perché è un argomento su cui sono ancora impreparato. Mi limito a ricordare che a monte di tutto occorre anche tenere in considerazione il concetto di database utilizzato per OLTP o Datawarehouse. Si tratta di due utilizzi che si pongono a due estremi contrapposti di tipologia di utilizzo di un database. Da un lato abbiamo molti utenti che fanno piccole transazioni e tante interrogazioni “puntuali”, dall’altro abbiamo pochi utenti che fanno interrogazioni massive per avere dati aggregati su cui fare analisi globali. In un OLTP non va molto bene che un singolo utente sfrutti elaborazioni parallele per prendersi tutte le risorse del sistema per soddisfare la propria richiesta, lasciando gli altri utenti ad aspettare.

Se l’applicazione è molto vicina a un OLTP e quindi le interrogazioni sono puntali è ben difficile che il partitioning possa dare grandi vantaggi in termini di prestazioni e quindi tempi di risposta dell’applicazione: le ricerche sono per lo più indicizzate quindi se le cose vengono fatte come si deve al più non abbiamo peggioramenti. Il partizionamento può essere applicato anche agli indici, essi possono essere partizionati con lo stesso criterio della tabella (LOCAL) o con un criterio diverso (GLOBAL). Oppure possono non essere partizionati per nulla.

Secondo me Thomas Kyte nel suo libro spiega e riassume molto bene le principali caratteristiche del partitioning e i casi in cui queste caratteristiche possono essere sfruttate positivamente. Non ha senso che mi metta a copiare qui quello che ha gia scritto lui. Faccio un ultimo accenno al partizionamento basato sull’hash, un caso particolare che può migliorare le prestazioni in un caso particolare, quello di numerosi inserimenti concorrenti. Facendo l’hash sulla chiave primaria e avendo partizionato la tabella in “n” partizioni, ogni inserimento consecutivo potrebbe andare su partizioni diverse, quindi segmenti diversi, questo ridurrebbe problemi di concorrenza nell’accesso alle strutture che gestisticono lo spazio del segmento stesso.

Facilitazione nella gestione

Gestire tabelle tabelle molto grandi può essere in certe condizioni piuttosto complicato. Va comunque stabilito anche cosa si intende per tabelle molto grandi. Non è un caso che il manuale Oracle dedicato al partitioning sia “Oracle database VLDB and partitioning guide“. Nell’introduzione di questo manuale si parla di database da diverse centinaia di GB o anche TB. Nella stessa introduzione poi si accenna a come in database OLTP il partitioning possa dare vantaggi in termini di gestione e “disponibilità” mentre in ambienti Datawarehouse possa dare miglioramenti in termini di prestazioni e gestione. Se abbiamo una tabella che pesa svariati GB e dobbiamo spostarla, abbiamo poco tempo a disposizione per lavorare “offline” e limitate risorse la cosa può essere problematica. Se la stessa tabella è divisa in parti più piccole possiamo lavorare sulle singole parti separatamente mantenendo le altre disponibili. Questo significa che ad esempio possiamo spostare di tablespace una singola partizione senza inibire l’accesso alla singola tabella. Se poi si usano indici partizionati localmente anche la gestione di questi sarà altrettanto più facile e comporta minor disservizio.

Migliore disponibilità

Come già scritto al capitolo “Facilitazione della Gestione” se ho necessità di spostare una tabella da una tablespace all’altra con una tabella partizionata posso spostare una partizione alla volta in modo più agevole mantenendo le altre disponibili e interrogabili senza succhiare tutte le risorse della macchina. si parla di indipendenza fra le partizioni. Per citare poi un esempio di Kyte anche il rebuild di una tabella per sistemare un po’ di “migrated rows” può essere più agevole e richiede meno spazio libero disponibile.

In questo ambito, sconfinando in quello che per me è il fantamondo dei database si può anche pensare al “restore & recovery” di singole partizioni da un backup. Operazione senz’altro relativamente facile e fattibile se le partizioni stanno su tablespace separate.

Vale la pena di ricordare che partizioni diverse di una stessa tabella possono avere strategie di compressione diverse. Anche qui aggiungo una piccola nota legata al mio piccolo mondo e quindi alla mia personale e ridotta esperienza. Nei libri e nella documentazione si spiega come si può risparmiare sullo storage dedicando disco più lenti e meno costosi a partizioni con dati più vecchi e meno usati. Si può poi usare strategie di compressione migliori riducendo ancora l’uso di risorsa spazio disco. Ora, io lavoro in un ambito relativamente piccolo ma abbiamo anche un cliente molto grande. La parte su cui lavoriamo noi è molto piccola ma già li vedo che nonostante non sembrano esserci problemi di licenze (cosa non secondaria) non ci pone neppure il problema sull’uso di storage il che mi fa da pensare abbia un costo che non preoccupa… o forse banalmente non è stata fatta una analisi dedicata.

Oracle Partitioning – Introduzione

La funzionalità Oracle Partitioning esiste da molto tempo ma fino ad oggi non ho mai avuto occasione di usarla e lavorarci e quindi di studiarne in modo approfondito i dettagli. Avendo avuto modo recentemente di fare dei test per valutarne l’utilizzo ho deciso di raccogliere un po’ di appunti e organizzarli pubblicandoli qui. L’argomento è molto più complesso e ampio di quanto si possa immaginare ad una prima occhiata alla documentazione introduttiva per cui spero e confido di riuscire a riassumere ed organizzare l’argomento in una breve serie di post di qui questo rappresenta l’introduzione.

Le fonti di studio che ho utilizzato sono primariamente i manuali Oracle (docs.oracle.com) poi vi sono libri interessanti, primo fra tutti “Expert Oracle Database Architecture” di Thomas Kyte.

La funzionalità “Partitioning” è stata introdotta da Oracle per la prima volta con la versione 8.0 del suo RDBMS. Il principio alla base di questa funzionalità è molto semplice: dividere oggetti (tabelle e indici) molto grossi in oggetti più piccoli e facilmente gestibili. L’obiettivo è da un lato semplificare la gestione dall’altro migliorare le prestazioni delle operazioni su tabelle e/o indici che possono essere molto grandi. Tutto questo viene fatto in modo trasparente per l’utente e/o l’applicazione, nel senso che ci accede ai dati non deve fare assolutamente nulla in conseguenza dell’uso di questa caratteristica perché la tabella e gli indici interessati vengono interrogati nello stesso identico modo nel caso siano partizionati o no.

Il “partitioning” (che possiamo benissimo tradurre con partizionamento) consiste nella suddivisione di una tabella ed eventualmente dei suoi indici in “pezzi” (partizioni) più piccole che possono essere gestite in modo separate perché corrispondo a oggetti fisici diversi.

In un database Oracle a una tabella di tipo heap standard corrisponde un oggetto “fisico” chiamato segmento che rappresenta la struttura di memoria in cui vengono salvati i dati della tabella. Quando una tabella viene “partizionata” essa viene logicamente suddivisa in più parti chiamate partizioni, a questo punto non c’è più un segmento associato alla tabella ma un segmento per ogni partizione.

I vantaggi comunemente attribuiti all’uso del partitioning sono solitamente:

• Migliori prestazioni, sia delle query che delle operazioni “DML”

• Facilitazione della gestione

• Migliore “disponibilità”

Cercherò di analizzare nel dettaglio ciascuno di questi vantaggi spiegandone l’origine, i limiti e il valore. Siccome però ciascuno di questi punti richiede una discussione piuttosto lunga vi dedicherò ciascuno un post separato.