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/

SET AUTOTRACE

Si vede che sono un po’ fuori allenamento e dimentico le cose, quelle che imparai anni fa con tanto impegno e che registrai su questo blog proprio per evitare che ciò accadesse. E’ il caso di quanto scrissi in questo post,  l’unica scusante è che si tratta di un post di 12 anni fa; non posso ammetterla come scusante ma non posso neppure accettare di dimenticare cose che mi servono ancora nel mio lavoro.

Nei giorni scorsi stavo analizzando una query sottopostami da un collega, per mia sventura ho pensato di analizzare il piano di esecuzione usando l’opzione “set autotrace” di sqlplus, per fare più velocemente. Poi in realtà ho provato un passaggio a SQLcl che sembra un prodotto decisamente maturo e in grado di superare molti limiti di SQL*Plus anche se ho trovato qualche problemino con alcuni vecchi script. Premetto che il db su cui stavo facendo i test è un Oracle 12.1. A un certo punto noto una anomalia, il piano di esecuzione rimaneva lo stesso ma le statistiche, in particolare i “consistent gets” diminuivano molto dopo la prima esecuzione. A quel punto sono tornato sui miei passi ed ho utilizzato esplicitamente la cara vecchia “dbms_xplan.display_cursor” e mi sono accorto che in effetti la query aveva la particolarità di ottenere, grazie alle funzionalità di “adaptive query optimization”, un piano diverso alla seconda esecuzione.  Chiaramente il fatto di utilizzare un “explain plan” per calcolare il piano di esecuzione tagliava fuori questa casistica confondendomi. Per la verità quando mi sono accorto dell’inghippo stavo utilizzando SQLcl e quindi ho fatto una breve ricerca, imbattendomi in questo “twitt“; a questo punto ho fatto un test, anche se su un database su cui non c’erano gli stessi dati e la particolarità del cambio dati non si ripresenta. Ho abilitato il trace della sessione, abilitato il set autotrace, ho eseguito la query, fermato il trace e analizzato il trace. Dentro il trace ci ho trovato la solita “explain plan for” per cui ho chiesto informazioni commentando il twitt. Anche con l’ultima versione, 19.2 di SQLcl io registro lo stesso comportamento, quindi continuerò ad utilizzare la cara vecchia “dbms_xplan.display_cursor”

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.

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

Ottimizzatore delle query in Oracle 12c

Con questo post ho l’ambizione di riassumere le principali caratteristiche dell'”ottimizzatore” di query di Oracle. L’impresa sta nel far stare in un unico post tutte le principali informazioni, cosa che non so se sarò in grado di fare vista l’abbondanza di elementi che il soggetto ha in questa versione di Oracle. Premetto che la fonte principale delle informazioni è il manuale Oracle “Database SQL Tuning Guide” versione 12.1. Trovo che la manualistica Oracle sia molto ben fatta, molto ampia è approfondita quanto basta. Siccome studiando per la certificazione ho avuto qualche difficoltà a mettere insieme e in ordine nella mia memoria tutti gli argomenti trattati in questo capitolo ho sentito la necessità di scrivere questo post che seguirà il tracciato del manuale. Se dovesse diventare troppo lungo lo spezzerò in più post.

Cominciamo con un veloce ripasso dei concetti di base, a partire dalle definizioni, tenendo conto che tradurre tutto è difficile quindi per semplicità, a differenza di quello che ho fatto nel titolo, manterrò la nomenclatura originale in inglese.

Il “query optimizer”, chiamato più in breve semplicemente “optimizer” (o ottimizzatore) è quella parte del software Oracle che si occupa di determinare il modo più efficente di recuperare i dati richiesti tramite una query SQL; il risultato dell’elaborazione dell’ottimizzatore è il “piano di esecuzione”. Sappiamo che l’SQL è un linguaggio dichiarativo, quindi l’ottimizzatore può permettersi di scegliere abbastanza liberamente come recuperare e preparare i dati richiesti tramite SQL. In Oracle l’ottimizzatore è chiamato anche “cost-based optimizer” (CBO) perché le sue elaborazioni girano attorno al concetto di costo. Ogni elemento di accesso ed elaborazione dei dati da recuperare ha un costo che può essere tempo di CPU, tempo di accesso ai dischi e tempo di comunicazione. La somma dei costi di tutti i singoli passi di un piano di esecuzione da il costo del piano di esecuzione che sarà l’elemento di valutazione del piano stesso. Alla base di tutto ci sono una serie di statistiche, sui dati e sul sistema che l’ottimizzatore usa per stimare il costo finale di un piano.

Componenti dell’ottimizzatore

Il manuale ci dice che l’ottimizzatore consiste di tre componenti:

  1. Query transformer
  2. Estimator
  3. Plan generator

Query transformer

questo componente cerca di riscrivere un blocco SQL in uno semanticamente identico ma gestibile in modo più efficente, l’esempio banale fatto sul manuale è la trasformazione di una query con un “OR” in una query con una “UNION ALL”

Estimator

direi che questo è il compente più importante, quello che calcola il costo di un piano per un blocco SQL. Gli elementi di misura che usa l'”estimator” sono:

  1. Selectivity (selettività), percentuale dei dati estratti, questo dato non viene visualizzato nei piani di esecuzione.
  2. Cardinality (cardinalità), volume dati estratto
  3. Cost (costo), gli elementi base sono CPU, I/O e memoria. È la parte più complessa, tiene conto delle risorse utilizzate, nel numero stimato di righe restituite (cardinalità), della dimensione iniziale dell’insieme dei dati, della distribuzione dei dati e delle strutture di accesso.

 

Il costo non è necessariamente legato al tempo finale di esecuzione.

Plan generator

questo componente si occupa di generare possibili piani di esecuzione, confrontarne il costo e scegliere quello con il costo minore.

Sembra chiaro che le tre componenti in fase di generazione di un piano di esecuzione interagiscono continuamente e non lavorano semplicemente per stadi successivi.

Alla base di tutto vi sono delle statistiche di sistema, ad esempio: a certe operazioni che richiedono elaborazione di CPU verrà assegnato un costo in termini di cicli di CPU, poi il sistema in qualche modo terrà conto del costo di un ciclo di CPU. Poi ci sarà un costo per l’I/O, ad esempio il sistema stimerà che recuperare un blocco da disco costa tot e anche qui il costo potrebbe dipendere dalle prestazioni del sistema di I/O, mi vien da dire  che il tempo medio potrebbe essere 1 msec o 10 msec e fra i due c’è molta differenza. Ci sono poi operazioni come l'”hash join” che necessitano sia di CPU che di memoria per fare degli ordinamenti.

Le statistiche di sistema insomma sono dati “fissi” legati essenzialmente alle potenza computazionale della macchina su cui gira il database. Vi sono poi altre statistiche più complesse e molto importanti che sono quelle sui dati, ad esempio il numero di record in una tabella, la distribuzione dei valori in un campo di una tabella ecc. Mi sento di poter semplificare molto dicendo che le statistiche sui dati vengono moltiplicate per dei coefficenti fissi che sono dati dal tipo di operazione e le statistiche di sistema. Quindi potremmo avere due sistemi con lo stesso insieme di dati ma con caratteristiche computazionali molto diverse, supponiamo uno con tanta CPU e Memoria ma un I/O “lento” e uno opposto con poca CPU e memoria ma I/O molto veloce e nei due casi una stessa query SQL potrebbe dare origine a due piani molto diversi sui due sistemi. Ovviamente vale anche la situazione inversa (più comune), sistemi con caratteristiche computazionali uguali ma insiemi dati diversi. L’esempio più facile da capire è una select su una tabella voluminosa, se il filtro fa si che venga richiesta una percentuale molto piccola dei dati della tabella tipicamente il piano di esecuzione più efficente consiste nell’utilizzare un indice (dando per pressupposto che l’indice esista), viceversa se il filtro fa si che ad essere richiesti siano quasi tutti i dati della tabella allora è più efficente scandire direttamente tutta la tabella piuttosto che perdere tempo palleggiando tra indice e tabella.

Fin qui abbiamo fatto un ripasso generale su concetti di base che valgono anche in vecchie versioni Oracle, ora vediamo un po’ di cose nuove.

Automatic tuning optimizer

L’ottimizzatore può lavorare in due modalità

  • “normal optimization”, quella con cui normalmente genera i piani di esecuzione
  • “SQL Tuning Advisor optimization”, una modalità avanzata in cui l’ottimizzatore fa una analisi più accurata di uno statement SQL, il risultato però non è il piano di esecuzione ma una serie di suggerimenti con la loro motivazione (rationale) e la stima dei benefici

In realtà questa cosa forse esiste gia dalla versione Oracle 10g….

Adaptive Query Optimization

Questa funzionalità abilita l’ottimizzatore a fare delle correzioni al volo sul piano di esecuzione e a rilevare ulteriori informazioni che possono aiutare alle successive esecuzioni. L'”ottimizzazione adattiva” è utile quando le statistiche esistenti non permettono di generare un piano ottimale.

Gli elementi che rientrano nella “Adaptive query optimization” sono diversi:

  • Adaptive plans
    • Join methods
    • Parallel distribution methods
    • Bitmap index pruning
  • Adaptive Statistics
    • Dynamic Statistics
    • Automatic reoptimization
      • statistics feedback
      • performance feedback
    • SQL Plan Directives

Mi permetto di riportare un collegamento alla figura del manuale che rende meglio l’idea:

Adaptive Query Plans

Con questo nome di definisce la caratteristica per cui l’ottimizzatore è in grado di cambiare parte del piano di esecuzione anche dopo l’inizio dell’esecuzione del piano. Un adaptive plan (piano adattivo?) è un piano che contiene più sottopiani precalcolati e comprende un “optimizer statistics collector” (raccoglitore di statistiche). Durante l’esecuzione l’ottimizzatore tramite il “collector” inizia a raccogliere informazioni sull’esecuzione e se si accorge che le stime iniziali erano sbagliate può decidere di cambiare il “default plan” (quello scelto in partenza) con un altro piano, cambiandone un pezzo, quello che può cambiare è un metodo di join (hash piuttosto che nested loop). A questo punto il collettore di statistiche si ferma e il nuovo piano diventa quello definitivo.

Affinché questa funzionalità sia operativa devono essere impostati i seguenti parametri:

  • OPTIMIZER_FEATURES_ENABLE=12.1.0.1
  • OPTIMIZER_ADAPTIVE_REPORTING_ONLY=FALSE

Entrambi hanno quei valori per default.

Il parametro “FORMAT=>’ADAPTIVE'” della funzione DBMS_XPLAN.DISPLAY_CURSOR permette di visualizzare informazioni sugli “adaptive query plan”

Sul manuale vengono riportati degli esempi per ciascuno dei tre casi possibili, metodo di join, metodo di distribuzione parallela (al riguardo credo che l’argomento meriti un approfondimento, per ora mi limito a riportare il link al manuale) e “bitmap index pruning”.

Adaptive statistics

Quando i predicati nelle query sono molto complessi le statistiche di base sui dati da sole non garantiscono stime precise, per questo c’è il supporto delle “adaptive statistics” (statistiche adattive) che sono di tre tipi, Dynamic statistics, Automatic reoptimization e SQL Plan Directives

Dynamic Statistics

durante la compilazione di una query SQL l’ottimizzatore valuta se le statistiche disponibili sono sufficienti, altrimenti per aumentare i dati a disposizione usa le statistiche dinamiche che sono una estensione di quello che nelle versioni precedenti di Oracle veniva chiamato “dynamic sampling”. Queste statistiche possono venir usate per “table scans”, “index access”, join e GROUP BY.

Le statistiche dinamiche sono attivate in funzione del valore del parametro OPTIMIZER_DYNAMIC_SAMPLING che per default ha valore 2  che indica che il campionamento viene fatto solo se una delle tabelle coinvolte non ha statistiche. Il parametro accetta valori da 0 (dynamic statistics disabilitate completamente) a 11 dynamic statistics completamente automatiche

Automatic Reoptimization

Con l’adaptive query plan abbiamo visto può solo cambiare per un piano il tipo di join o il metodo di distribuzione parallela o applicare il bitmap index pruning. Per casi più complessi, ad esempio per cambiare l’ordine dei join può intervenire la automatic reoptimization, la quale però può solo stabilire un nuovo piano di esecuzione al termine della prima esecuzione della query in modo da utilizzarlo alle successive richieste. Non è in grado di cambiare un piano in esecuzione. Questo processo può continuare anche alle successive esecuzioni con l’obbiettivo di usare gli ulteriori dati raccolti per ottimizzare. La ri-ottimizzazione avviene sulla base di due tipologie di dati: “statistics feedback” e “performance feedback”

Statistics feedback

si tratta di quello che in precedenza si chiamava “cardinality feedback”. Se durante l’esecuzione di una query SQL l’ottimizzatore verifica una delle seguenti situazioni:

  • tabelle senza statistiche
  • predicati multipli congiuntivi o disgiuntivi  (traduzione mia, spero corretta)
  • predicati contenenti operatori complessi

allora attiva il monitoraggio delle statistiche di feedback. Se al termine dell’esecuzione c’è una differenza sostanziale fra dati rilevati e stime allora l’ottimizzatore salva le informazioni ad uso futuro sotto forma di SQL plan directive. In verità nel manuale dice che le salva e anche crea una SQL plan directive. Dopo la prima esecuzione il monitoraggio delle statistiche di feedback viene disabilitato.

Performance feedback

In questo caso vengono raccolte informazioni per stabilire un grado di parallelismo ottimale. Per attivare questa funzionalità occorre settare il parametro:

  • PARALLEL_DEGREE_POLICY=ADAPTIVE

Il principio rimane lo stesso, viene confrontato il grado di parallelismo calcolato a priori con quello calcolato alla fine dell’esecuzione sulla base dei dati raccolti (ad esempio tempo di CPU).

Sul manuale una nota dice che anche si parametro PARALLEL_DEGREE_POLICY non è settato a ADAPTIVE le “statistics feedback” possono influenzare il grado di parallelismo scelto per una query.

SQL Plan Directives

Si tratta di informazioni aggiuntive che l’ottimizzatore usa per generare  un piano ottimale. Si tratta di informazioni legate a “query expressions”, non all’intera query, questo permette di utilizzarle anche per query non uguali (seppur simili).

Durante la compilazione l’ottimizzatore verifica se mancano “statistiche estese” o istogrammi e ne registra la mancanza. Alle successive chiamate di raccolta delle statistiche tramite il package DBMS_STATS verranno calcolati eventuali istogrammi mancanti e statistiche estese.

Le SQL Plan Directives (SPD) vengono salvate nella SHARED POOL e ogni 15 minuti in modo permanente sulla tablespace SYSAUX . Se per 53 settimane non vengono usate vengono eliminate automaticamete. Possono essere gestite con il package PL/SQL DBMS_SPD, sul manuale riporta un esempio d’uso dei seguenti due metodi:

  • FLUSH_SQL_PLAN_DIRECTIVE (forza la scrittura su disco)
  • DROP_SQL_PLAN_DIRECTIVE

Vi sono poi dei metodi per esportare e importare le SPD

Le viste per vedere le SPD sono:

  • DBA_SQL_PLAN_DIRECTIVES
  • DBA_SQL_PLAN_DIR_OBJECTS

Extended Statistics

Le statistiche estese sono statistiche su gruppi di colonne o statistiche su espressioni. Le normali statistiche sulle singole colonne possono non bastare se i dati di due colonne  hanno una correlazione “imprevedibile”. Tramite il package DBMS_STAT e precisamente i seguenti metodi:

  • SEED_COL_USAGE
  • REPORT_COL_USAGE
  • CREATE_EXTENDED_STATS

É possibile, in maniera semiautomatica creare estensioni basandosi un test di carico. Una volta creata l'”estensione” le statistiche verranno raccolte anche sull’estensione e utilizzate dall’ottimizzatore.

È possibile visualizzare le estensioni esistenti tramite la vista USER_STAT_EXTENSIONS

 

Con questo penso di potermi fermare qui. La lunghezza del post mi pare accettabile e il livello di approfondimento è medio, spero di riuscire a trascrivere ancora qualcosa per approfondire ed estendere la tematica.