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.

Annunci

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.

Istogrammi su tipo dato *CHAR

Premessa importante: il caso a cui mi riferisco è su Oracle 11.2.0.4, pur essendo una versione ormai piuttosto obsoleta è una versione su cui abbiamo diverse installazioni.

Seconda premessa meno importante: da un po’ di tempo non riesco a riportare nei post il codice formattato in maniera decente, oggi ho provato a cambiare tema, visto che il precedente non è più supportato da questa piattaforma. Il codice continua a essere formattato in modo mediocre, credo che sia un limite del piano gratuito.

Finite le premesse andiamo al dunque. Per la prima volta mi è capitato un caso in cui un istogramma, nelle statistiche oracle dei dati di una tabella, faceva compiere all’ottimizzatore Oracle una scelta non buona. In prima istanza ho attribuito la causa del non utilizzo di un indice su un campo i cui valori sono praticamente univoci al fatto che il tipo dato del campo fosse NVARCHAR2. In realtà quello è una concausa ma non è il motivo principale. Di fatto, osservando l’esito dell’interrogazione della vista USER_TAB_HISTOGRAMS, ho notato una cosa particolare grazie al campo ENDPOINT_ACTUAL_VALUE. Cioè mi sono reso conto, facendo poi una ulteriore verifica sui dati della tabella, che c’erano una serie di valori per il campo di tipo NVARCHAR2 uguali per la prima parte della stringa che poi compariva come valore del campo ENDPOIN_ACTUAL_VALUE di un singolo bucket dell’istogramma.  In realtà a quel bucket corrispondeva un terzo dei record della tabella. Questo in pratica implicava che per le interrogazioni per quell’intervallo di valori, anche se su un valore specifico, che filtrava un unico record, l’ottimizzatore comunque sceglieva di non utilizzare l’indice sul campo. Prima di riportare i riferimenti ad articoli che spiegano bene e nel dettaglio cosa succede voglio riportare un caso di test che sono riuscito a preparare:

CRIS@svil112 > create table test_nvarchar2(id number, bnv nvarchar2(150));

Table created.

CRIS@svil112 > begin
2 for i in 10001..20000 loop
3 insert into test_nvarchar2 (id,bnv) values (i,'TXTOOOOOOOOOOOO'||to_char(i));
4 insert into test_nvarchar2 (id,bnv) values (i,to_char(i));
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

CRIS@svil112 > @GATHER_TABLE_STATS TEST_NVARCHAR2

PL/SQL procedure successfully completed.

Avendo ora un tabella di prova facciamo un paio di interrogazioni:

CRIS@svil112 > select * from test_nvarchar2 where bnv='TXTOOOOOOOOOOOO11234';

ID BNV
---------- ----------------------------------------
11234 TXTOOOOOOOOOOOO11234

CRIS@svil112 > @PLAN

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 7j6w8gg693f0k, child number 0
-------------------------------------
select * from test_nvarchar2 where bnv='TXTOOOOOOOOOOOO11234'

Plan hash value: 1043612967

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 170 (100)| |
|* 1 | TABLE ACCESS FULL| TEST_NVARCHAR2 | 9867 | 298K| 170 (0)| 00:00:03 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("BNV"=U'TXTOOOOOOOOOOOO11234')


18 rows selected.

CRIS@svil112 > select * from test_nvarchar2 where bnv='11234';

ID BNV
---------- ----------------------------------------
11234 11234

SCRIS@svil112 > @PLAN

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 38faau52d83fb, child number 0
-------------------------------------
select * from test_nvarchar2 where bnv='11234'

Plan hash value: 1818196464

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_NVARCHAR2 | 1 | 31 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_NVARCHAR2_IDX_1 | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("BNV"=U'11234')

Come si può vedere per due interrogazioni “puntuali” usa due piani di esecuzione diversi, uno con l’utilizzo dell’indice e l’altro no, anche se entrambe le query estraggono un record solo, perchè di fatto i valori del campo BNV sono univoci, anche se non certificati da un indice univoco. L’indizio sulla causa dell’anomalo comportamento lo abbiamo gia sui dati forniti con il piano e sta nelle stime: nel primo caso l’ottimizzatore si aspetta di trovare 9867 record, nel secondo uno solo. Andiamo quindi a vedere nel dettaglio le statistiche su cui si basa l’ottimizzatore, in particolare l’istogramma sui dati della colonna BNV:

CRIS@svil112  > select histogram,num_distinct,num_buckets from user_Tab_col_statistics where column_name='BNV';

HISTOGRAM       NUM_DISTINCT NUM_BUCKETS
--------------- ------------ -----------
HEIGHT BALANCED        19954          75

CRIS@svil112  > sELECT ENDPOINT_NUMBER,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE FROM USER_TAB_HISTOGRAMS WHERE COLUMN_NAME='BNV';

ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
--------------- --------------------------------------------- --------------------------------
0 993852926086080000000000000000000 1 0 0 0 1
1 993852926095525000000000000000000 1 0 2 6 7
2 993852926109692000000000000000000 1 0 5 3 4
3 993852926123859000000000000000000 1 0 8 0 1
4 993853235571091000000000000000000 1 1 0 6 8
5 993853235585258000000000000000000 1 1 3 3 5
6 993853235599424000000000000000000 1 1 6 0 2
7 993853235608869000000000000000000 1 1 8 6 9
8 993853545060823000000000000000000 1 2 1 3 6
9 993853545074989000000000000000000 1 2 4 0 3
10 993853545084435000000000000000000 1 2 6 7 0
11 993853545098602000000000000000000 1 2 9 3 7
12 993853854550555000000000000000000 1 3 2 0 4
13 993853854560000000000000000000000 1 3 4 7 1
14 993853854574167000000000000000000 1 3 7 3 8
15 993854164026120000000000000000000 1 4 0 0 5
16 993854164035565000000000000000000 1 4 2 7 2
17 993854164049732000000000000000000 1 4 5 3 9
18 993854164063899000000000000000000 1 4 8 0 6
19 993854473511130000000000000000000 1 5 0 7 3
20 993854473525297000000000000000000 1 5 3 4 0
21 993854473539464000000000000000000 1 5 6 0 7
22 993854473548909000000000000000000 1 5 8 7 4
23 993854783000862000000000000000000 1 6 1 4 1
24 993854783015029000000000000000000 1 6 4 0 8
25 993854783024474000000000000000000 1 6 6 7 5
26 993854783038641000000000000000000 1 6 9 4 2
27 993855092490594000000000000000000 1 7 2 0 9
28 993855092500039000000000000000000 1 7 4 7 6
29 993855092514206000000000000000000 1 7 7 4 3
30 993855401966159000000000000000000 1 8 0 1 0
31 993855401975604000000000000000000 1 8 2 7 7
32 993855401989771000000000000000000 1 8 5 4 4
33 993855402003938000000000000000000 1 8 8 1 1
34 993855711451169000000000000000000 1 9 0 7 8
35 993855711465336000000000000000000 1 9 3 4 5
36 993855711479503000000000000000000 1 9 6 1 2
37 993855711488948000000000000000000 1 9 8 7 9
74 1703749641784290000000000000000000 T X T O O O O O O O O O O O O 1
75 1703749641784290000000000000000000 T X T O O O O O O O O O O O O 2

40 rows selected.

Per una spiegazione dettagliata sugli istogrammi posso rimandare ai libri e al blog di Jonathan Lewis, mi limito a dire che di fatto quello che succede, come ben spiegato da Thomas Kyte in questo post, è che per costruire l’istogramma vengono presi solo i primi 32 byte della stringa  per cui vengono presi i valori ‘TXTOOOOOOOOOOOO1’ e ‘TXTOOOOOOOOOOOO2’. Al valore vengono assegnati 37 bucket, che sono quasi metà dei totali, infatti i record per cui la prima parte della stringa contenuta nel campo BNV è ‘TXTOOOOOOOOOOOO1’  sono effettivamente 9999, quasi metà. Il problema è che per lui così quei valori sono tutti uguali. Da notare come 20000/75*37=9866.667 che arrotondato fa proprio 9867, il numero di righe stimato nel primo piano di esecuzione.

In un caso come questo, dove i valori sono distribuiti in modo uniforme, estremamente uniforme, essendo di fatto univoci, l’istogramma in realtà è di scarsa utililità. A questo punto ho trovato comodo questo post del blog del gruppo dell’ottimizzatore del database di Oracle in quanto spiega come usare la procedura DBMS_STAT.GATHER_TABLE_STATS e in particolar modo il parametro METHOD_OPT che regola la generazione degli istogrammi.

CRIS@svil112 > select * from test_nvarchar2 where bnv='TXTOOOOOOOOOOOO11234';

ID BNV
---------- ----------------------------------------
11234 TXTOOOOOOOOOOOO11234

CRIS@svil112 > @plan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7j6w8gg693f0k, child number 0
-------------------------------------
select * from test_nvarchar2 where bnv='TXTOOOOOOOOOOOO11234'

Plan hash value: 1818196464

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_NVARCHAR2 | 1 | 31 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_NVARCHAR2_IDX_1 | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("BNV"=U'TXTOOOOOOOOOOOO11234')

CRIS@svil112> select histogram,num_distinct,num_buckets from user_Tab_col_statistics where column_name='BNV';

HISTOGRAM       NUM_DISTINCT NUM_BUCKETS
--------------- ------------ -----------
NONE                   19954           1

Come si vede, eliminando l’istogramma l’ottimizzatore si baserà solo sul numero di valori distinti e si comporta in modo corretto. In questo post viene poi spiegato come eventualmente salvare questa impostazione di non ricalcolo dell’istogramma.

Va notato come a peggiorare la situazione sia stato in effetti il fatto che il tipo campo è NVARCHAR2, il che significa che nel mio caso usa il charset AL16UTF16 che codifica con 2 byte ogni carattere:

 

CRIS@svil112 > select length(bnv),lengthb(bnv) from test_nvarchar2 where bnv='TXTOOOOOOOOOOOO11234';

LENGTH(BNV) LENGTHB(BNV)
----------- ------------
20           40

e come ha spiegato bene Kyte nel calcolo dell’istogramma vengono presi i primi 32 byte. Jonathan Lewis da più dettagli in una serie di post, anche se si riferisce ai “Frequency Histograms”, per cui dovrò indagare ulteriormente. Pare che con la versione 12c i 32 byte siano diventati 64 e poi ci sono nuove tipologie di istogrammi volte forse a risolvere anche questo problemi. Anche su questo devo approfondire.

Nel caso che riporto la soluzione è relativamente facile in quanto i dati sono distribuiti uniformemente, non fosse così la soluzione adottata non andrebbe bene. Fra le cose da approfondire e capire mi rimarrebbe anche il criterio con cui viene valorizzato il campo ENDPOINT_ACTUAL_VALUE della USER_TAB_HISTOGRAMS, Lewis ne da una spiegazione ed io ho fatto qualche test ma comunque non sono riuscito a capirlo bene.

Oracle Resource Manager con CDB e PDB

Con l’introduzione dell’architettura “multitenant” in Oracle 12 si sono stati degli aggiornamenti e delle estensioni al funzionamento di Oracle Resource Manager che è lo strumento per ripartire in base a determinate esigenze le risorse disponibili su un server.  Confesso di non aver mai potuto testare e vedere in azione veramente il resource manager, credo di avere fatto solo delle piccole prove per gestire sessioni inattive e bloccanti ma non sono andato oltre. Oltre dieci anni fa (!) ho scritto un mio riassunto di cui ho parlato in questo post.  Dopo tanto tempo ho l’ambizione di fare un post riassuntivo delle novità del resource manager nell’ambito di Container database e Pluggable database.

Con l’architettura multitenant si è aggiunto un livello ulteriore su cui gestire le risorse in un database Oracle. Per cui in Oracle 12 su un database CDB il resource manager lavora a due livelli:

  • a livello CDB, in cui smista le risorse fra i PDB
  • a livello di PDB che come nella versione precedente smista le risorse fra gli utenti del singolo PDB.

Faccio riferimento a questa sezione del manuale Oracle.

CDB Resource Plans

un CDB resource plan alloca le risorse ai PDB in base al suo insieme di direttive (resource plan directives).

C’è una relazione padre-figlio tra un CDB resource plan e le sue direttive. Ogni direttiva fa riferimento a un PDB e non ci possono essere due direttive nel piano correntemente attivo che fanno riferimento allo stesso PDB.

Le direttive controllano l’allocazione delle seguenti risorse ai PDB:

  • CPU
  • Parallel execution server.

Le direttive di un CDB resource plan sono molto semplici, hanno tre parametri:

  • SHARE: rappresenta la fetta di CPU e di parallel server riservata al PDB
  • UTILIZATION_LIMIT: è un limite superiore alla percentuale di CPU utilizzabile dal PDB
  • PARALLEL_SERVER_LIMIT: è un limite superiore percentuale (sul valore impostato tramite il parametro PARALLEL_SERVERS_TARGET)

Esiste una direttiva di default per i PDB per cui non è stata definita una direttiva e assegna uno share e un limite del 100% sia per UTILIZATION_LIMIT e PARALLEL_SERVER_LIMIT.   Questa direttiva può essere modifica con la procedura dedicata del package DBMS_RESOURCE_MANAGER UPDATE_CDB_DEFAULT_DIRECTIVE.

C’è poi un’altra direttiva predefinita, chiamata AUTOTASK che si applica ai task di gestione automatica (automatic maintenance tasks) che girano nel root duranto le finestre di manutenzione. Questa direttiva può essere modificata con la procedura UPDATE_CDB_AUTOTASK_DIRECTIVE

Una cosa curiosa, scritta sul manuale, è che se un PDB viene scollegato “unplugged” la direttiva che lo riferisce viene mantenuta e nel caso il PDB venga ricollegato viene riutilizzata. Qui ho una lacuna da colmare perché mi sembra di ricordare dai miei test che dopo l’unplug l’unica cosa che si può fare con un PDB e la rimozione, dopodiché si riesce a ricollegare; mi sembra strano che venga mantenuto un riferimento a un PDB rimosso… spero di poter fare una prova al riguardo.

PDB Resource Plans

A livello di PDB si definiscono dei piani in maniera analoga a quanto si faceva prima su database non-CDB, ci sono però dei limiti:

  • un PDB resource plan non può avere sottopiani
  • un PDB resource plan può avere un massimo di otto consumer group
  • un PDB resource plan non può avere politiche di schedulazione multi-livello (multiple-level scheduling policy)

Se si collega come PDB un database non-CDB che ha dei resource plan che non rispettano i vincoli sopra Oracle modificherà i piani per farli rientrare nei vincoli.

P.S. 18/06/2018

sono riuscito a fare una prova riguardo al definire un CDB plan con delle direttive che fanno riferimento a un PDB. Ho fatto successivamente l’unplug e poi il drop del PDB ed effettivamente la direttiva che riferisce il pluggable database rimosso rimane.

Real-Time Database Operation Monitoring

Con la versione 11.1 Oracle ha introdotto un nuovo strumento di diagnosi e analisi delle prestazioni chiamato “Real-Time SQL Monitoring”. Si tratta di uno strumento che permette di controllare o monitorare in tempo reale l’esecuzione di query o statement SQL che durano a lungo e consumano risorse in modo significativo. Oracle in determinate condizioni, quando in una sessione gira una query che attiva l’esecuzione in parallelo o consuma più di 5 secondo di tempo CPU o I/O, comincia a registrare le principali statistiche di esecuzione dello statement quali tempo CPU, letture su disco, letture da buffer ecc e le espone tramite due viste chiamate V$SQL_MONITOR e V$SQL_PLAN_MONITOR.  Le statistiche vengono aggiornate ogni secondo e successivamente mantenute per almeno 5 minuti (qui dice così, qui dice un minuto), dopodiché se l’area di memoria in SGA dove risiedono queste informazioni è in esaurimento le statische possono venir cancellate. Si tratta di una evoluzione significativa rispetto alle analisi che si potevano e si possono ancora fare sulle classiche viste V$SQL e V$SQL_PLAN perché ad esempio le statistiche esposte sulle nuove viste sono legate alla singola esecuzione e non cumulative come nelle tradizionali V$SQL e V$SQL_PLAN. Vi è poi la possibilità, tramite procedure del package DBMS_SQLTUNE, di   avere dei report dettagliati e completi sull’esecuzione di una singola query: troviamo piano di esecuzione, valori delle bind variables. Ci sono  statistiche come il numero di righe attese e quello reale che prima era difficile avere. Naturalmente queste informazioni sono accessibili in modo comodo dalle console grafiche Cloud Control e, per 12c,  EM Express.

Con la versione 12.1 il concetto di Real-Time SQL Monitoring è stato esteso ed è diventato “Real-Time Database operation monitoring”. E’ possibile dalla versione 12c avere le stesse informazioni ma non più solo a livello di singolo statement SQL o blocco PL/SQL ma anche per un blocco di istruzioni delimitato temporalmente all’interno di una sessione che è definito appunto Database Operation. Ad esempio è possibile così monitorare complesse procedure ETL o altri tipo di procedure batch per tenere traccia dell’esecuzione ed individuare problemi di prestazioni o controllarne il consumo di risorse. Con oracle 12c è stato introdotto un nuovo package PL/SQL chiamato DBMS_SQL_MONITOR che fornisce le procedure “BEGIN_OPERATION” e “END_OPERATION” che servono appunto a delimitare le istruzioni che compongono la “database operation” che si vuole monitorare e analizzare. Oltre a quelle due procedure nel package si ritrovano le procedure “REPORT_SQL_MONITOR” e “REPORT_SQL_MONITOR_LIST” che si trovano ancora nel package DBMS_SQLTUNE, non so quale sia la differenza.

Posso solo segnalare una mancanza nel report, che da un lato da indicazioni in tempo reale anche sul punto in cui si trova l’esecuzione, dall’altro però non include le “predicate information” che si hanno ad esempio con la funzione “dbms_xplan.display_cursor”. Questo è un peccato perché costringe a fare una analisi con più strumenti per avere tutte le informazioni che possono essere utili ad analizzare le prestazioni di una query.

“Real-Time Database operation monitoring” è una “feature” del pacchetto “Oracle Database Tuning Pack” (per il quale quindi serve apposita licenza).

 

Ottimizzatore Oracle 12cR1 note varie

Gli ultimi due post che ho scritto erano dedicati alle nuove funzionalità introdotte nell’ottimizzatore delle query in oracle 12c (12.1). Ho riassunto in due post relativamente brevi una serie di  funzionalità abbastanza consistente e su quegli argomenti si potrebbe veramente scrivere molto. In realtà è già stato scritto molto, il problema è leggere tutto e riordinare tutte le informazioni. Questo post prende spunto principalmente da una letta che ho dato a un paio di post sul blog di Christian Antognini sull’argomento “Adaptive query optimization”, in particolare questo, questo  e questo.  Volendo ce ne sono anche altri, basta aver tempo di leggerli con attenzione. Io riassumerei, anche leggendo la nota del supporto Oracle “Recommendations for Adaptive Features in Oracle Database 12c Release 1 (Adaptive Features, Adaptive Statistics and 12c SQL Performance) (Doc ID 2312911.1)” , che qualche problemino su Oracle 12.1 lo si può avere con queste nuove caratteristiche e usarle e tenerle sotto controllo con tutti i parametri e le variabili coinvolte non mi sembra affatto facile. In ogni caso per riuscirci è proprio il caso di leggere bene i post di Antognini  e le note del supporto Oracle che indica, seguendo anche gli ulteriori rimandi che si trovano. Forse con la versione 12.2 le cose vanno meglio.

Per quanto mi riguarda ho voluto provare su un database di sviluppo interno a mettere in opera le SQL Plan Baselines mettendo il parametro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES a TRUE; dopo alcuni giorni però ho avuto alcune segnalazioni da sviluppatori che accedendo al database con SQL Developer avevano problemi di lentezza. Non ho avuto modo di fare una analisi seria, ho solo identificato la causa in una query lanciata da SQLDeveloper che recupera le informazioni sulle colonne delle tabelle, a quanto pare la query  rivista da oracle rimaneva per qualche motivo bloccata. Per risolvere velocemente il prolema ho dovuto reimpostare il parametro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES a FALSE e siccome non risolveva il problema ho impostato anche OPTIMIZER_USE_SQL_PLAN_BASELINES a FALSE, perché immagino che avendo comunque creato una serie di SQL Plan Baselines l’ottimizzatore continuasse ad usarle, anche non ne creava più di nuove. Essendo la query su viste di sistema (in particolare la ALL_OBJECTS per il cui il solo count usa un piano con 129 righe) non sono riuscito a capirci molto, sono però rimasto un po’ deluso da una funzionalità che dovrebbe avere lo scopo di impedire peggioramenti di prestazioni.

SQL Plan Management e SQL Plan Baselines

Con la versione 11.1 Oracle ha sostituito il meccanismo chiamato “Stored Outlines” con uno nuovo chiamato “SQL Plan Baselines”. L’obiettivo è rimasto lo stesso, quello di prevenire il peggioramento delle prestazioni dovuto a cambiamenti di sistema, di versione dell’ottimizzatore o altro che possa far cambiare il piano di esecuzione scelto per una query SQL. Evidentemente le stored outlines erano limitate rispetto alle nuove “SQL Plan Baselines” tanto appunto da richiedere una cosa molto più sofisticata che quindi ha preso un nuovo nome. Cercherò di riportare una sintesi di quanto riporta il manuale “Oracle 12.1 Database SQL Tuning Guide”, capitolo 23: Managing SQL Plan Baselines” Posso dire che a suo tempo ho studiato le Stored Outlines ma non sono mai riuscito ad utilizzarle perché non si adattavano alle esigenze che avevo. Le SQL Plan Baselines le ho esplorate da poco e non potrò per ora portare esperienze pratiche, mi limiterò quindi a quanto trovo sul manuale.

SQL Plan Baseline

Una SQL plan baseline (SPB) è un insieme di piani accettati che l’ottimizzatore è autorizzato ad utilizzare per uno statement SQL. Tipicamente i piani vengono introdotti in una SQL plan baseline dopo che è stato verificato che non portano a maggior uso di risorse con conseguente peggioramento delle prestazioni. Questo sistema si affianca al meccanismo dei “SQL Profile”, generati dal SQL Tuning Advisor che però sono un meccanismo reattivo, mentre l’SPB vuole essere un meccanismo proattivo: ha lo scopo di prevenire situazioni di peggioramento di prestazioni. Questo nel quadro di un aggiornamento di versione Oracle, di cambiamento di parametri o di cambiamento dell’hardware sottostante. All’interno di una SQL plan baseline ogni piano è specificato usando un insieme di “outline hints” che lo definiscono completamente. Questo si contrappone a SQL Profiles che piuttosto specificano solo degli hint che cercano di correggere errate stime sui costi del piano e quindi non forzano l’uso di un preciso piano.

SQL Plan Capture

La gestione dei piani di esecuzione tramite le SQL Plan baseline avviene in diversi passi, il primo di questi è la cattura e il salvataggio di tutte le informazioni sui piani di esecuzione di un insieme di statement SQL.  Le informazioni vengono salvate nella “SQL Management Base” (SMB). La cattura dei piani può avvenire in modo automatico tramite il settaggio del parametro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES a “true” (per default è “false”). Quando il parametro è a true il database crea una SQL plan baseline per ogni statement SQL “ripetibile” eseguito sul database stesso.

La cattura manuale avviene tramite procedure del package PL/SQL DBMS_SPM (DBMS_SPM.LOAD_PLANS_FROM_%) e consiste nel caricamento o da un “SQL tuning set (STS)” o dalla “share SQL area” o da una “tabella di staging” o da una “Stored Outline”. Non posso non riportare una immagine dal manuale che mi sembra molto chiara e completa:

Un piano caricato manualmente viene salvato come “accettato”

SQL Plan Selection

Come si vede dalla figura sopra nella SQL Management Base viene salvato anche la storia di un piano di esecuzione, cioè vengono registrati i cambi di piano

Quando il database esegue un “hard parse” di uno statement SQL l’ottimizzatore genera un piano ottimale per costo. Per default poi l’ottimizzatore cerca un piano che corrisponda nella SQL plan baseline di quello statement. Se non trova una SQL plan baseline per quello statement usa il piano gia generato (crea la SPB e aggiunge il piano come “accepted”), se la trova e il piano generato è gia nella baseline allora lo usa, se non c’è lo aggiunge alla “plan history” marcato come “unaccepted”. In quest’ultimo caso, se esistono piani fissati viene usato quello con il costo minore, se non esistono piani fissati viene usato il piano nella baseline con il costo minore, se non esistono piani riproducibili nella baseline (ad esempio perché nel frattempo è stato rimosso un indice) allora l’ottimizzatore usa il nuovo piano generato. Un piano fissato è un piano accettato e marcato manualmente come preferito.

SQL Plan Evolution

L’evoluzione di un piano è il procedimento che consiste nel verificare che i piani non accettati abbiano pretazioni almeno pari a quelli gia accettati (SQL Plan verification) e in tal caso nella loro aggiunta ai piani accettati della baseline. È possibile configurare il SQL Plan management affinché vengano eseguite tutte le possibili combinazioni dei due passi, cioè si può fare la verifica del piano senza l’aggiunta alla baseline e si può fare l’aggiunta senza fare la verifica. Il package PL/SQL DBMS_SPM fornisce le seguenti procedure:

  • SET_EVOLVE_TASK_PARAMETER
  • CREATE_EVOLVE_TASK
  • EXECUTE_EVOLVE_TASK
  • REPORT_EVOLVE_TASK
  • IMPLEMENT_EVOLVE_TASK
  • ACCEPT_SQL_PLAN_BASELINE

Queste procedure servono per avviare manualmente il processo di evoluzione. Oracle comunque raccomanda di lasciare al task automatico SYS_AUTO_SPM_EVOLVE_TASK (introdotto in Oracle 12c) il compito di fare il lavoro di verifica e accettazione durante la finestra di manutenzione pianificata (normalmente ogni notte)

SQL Management Base (SMB)

Si tratta della struttura logica dove vengono salvate le informazioni relative alle SQL plan baseline, fisicamente stanno nella tablespace SYSAUX. Le componenti sono quattro:

  • SQL statement log
  • SQL plan history (che include le sql plan baseline)
  • SQL profiles
  • SQL patches

SQL statement log

Durante il “parse” dello statement oracle calcola una “firma” (SQL signature)  normalizzando rispetto a maiuscole e minuscole e a spazi il testo, quando avviene la cattura automatica viene cercata la firma all’interno del “SQL statement log” (SQLLOG$), se non c’è l’aggiunge se c’è lo statement si può definire un “repeated SQL statement”

SQL Plan History

si tratta dell’insieme di piani di esecuzione catturati,  si quelli accettati, quindi nelle SQL plan baseline che quelli non accettati. Dalla versione 12c viene salvato il piano intero per tutti i piani, in questo modo non deve essere ricompilato lo statement per rigenerare il piano. I piani possono essere abilitati (enabled), che è il default o no. Nel caso non siano abilitati non vengono utilizzati anche se accettati.

La SQL Plan history può essere analizzata tramite la vista DBA_SQL_PLAN_BASELINES, tramite la procedure DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE è possibile vedere il dettaglio di una SQL plan baseline.

 

Oltre al parametro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES citato prima c’è un’altro parametro OPTIMIZER_USER_SQL_PLAN_BASELINES che per default è impostato a true e fa si che comunque per gli statement che hanno gia SQL plan baseline esistenti vengano aggiunti nuovi piani come non accettati. Se viene impostato a false viene disabilitato completamente l’uso delle baseline.

Sul manuale si trovano ulteriori dettagli ed esempi sulla gestione che non sto qui a riportare per non appesantire troppo il post