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.

Annunci

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.

LOB e temporary tablespace in Oracle

I LOB (Large OBject) sono un tipo dato introdotto nei database relazionali per estenderne l’utilizzo per gestire tipi dato non strutturato quali grandi file binari e grandi file di testo. La caratteristica principale del tipo dato LOB, come indica il nome, è quella di poter immagazzinare oggetti molto grossi, quindi si può trattare di file pdf, fotografie digitali, file eseguibili o qualunque altro tipo di file binario di piccole o grosse dimensioni; possono essere anche file di testo come file html e xml. I file binari e i file di testo vengono gestiti in modo leggermente differente, per cui ci sono due tipi di LOB: BLOB (Binary LOB) per i dati binari e CLOB (Character LOB) per i dati formato testo. Non posso e non voglio fare qui una descrizione dettagliata dei LOB nei database relazionali, dico solo che sono una estensione che permette di consolidare la gestione anche di file all’interno di un database relazionale.

In Oracle il tipo dato LOB ha un limite sulla dimensione massima piuttosto alto, come spiegato qui il limite è i (2^32-1)*<CHUNK SIZE> byte e in condizioni di default la chunk size è pari alla dimensione del blocco. Con la classica dimensione del blocco da 8 KB fa un limite di quasi 32 TB.

I LOB, vista la possibilità che siano di grosse dimensioni, devono essere gestiti attraverso delle funzioni specifiche. Esiste una interfaccia PL/SQL ed essendo un tipo dato “standard” esiste una implementazione delle funzioni di gestione anche nei driver per Java JDBC. Siccome dove lavoro si usa quest’ultima interfaccia su questa mi sono dedicato un po’ e la uso per descrivere l’argomento oggetto di questo post.

Tutto è nato dal fatto che su una installazione del cliente abbiamo verificato che le sessioni aperte sul database dall’applicazione mantengono una quota di spazio nella temporary tablespace dell’utente Oracle allocata. Dopo aver fatto un classica ricerca, prima genericamente su internet e poi sul supporto Oracle ho trovato qualche indizio ed ho cominciato a fare qualche test con lo scopo di circoscrivere le possibili cause e individuare delle soluzioni.

l riferimento per utilizzare LOB su un database Oracle in programmi java è il manuale “Oracle JDBC Developer’s Guide. Ho cercato di leggerlo ma piuttosto frettolosamente quindi non ho ancora ben chiari tutti i meccanismi. La parte che mi interessa sembra essere quella del paragrafo 14.5 dove si parla di “Temporary LOB” i quali sono fatti per dati di passaggio (transient data). Quando in java si utilizza un metodo che implementa l’interfaccia java.sql.Connection.createBlob viene creato un temporary lob. Il temporary blob è di fatto un variabile di tipo LOB che utilizza spazio allocato sulla temporary tablespace configurata per l’utente. Ho verificato che se da un programma java “prelevo” un campo blob con questo tipo di codice:

Blob blob_ris;
rs.next();
blob_ris = rs.getBlob(“contenuto”);

non viene creato un temporary lob, in questo caso si ottiene un puntatore direttamente al LOB interessato. Non viene allocato spazio sulla TEMP (interrogando la v$tempseg_usage non esce nulla). Utilizzando la chiamata ((BLOB)blob_aris).isTemporary() se ne ha la conferma. Viceversa, se si deve creare un dato di tipo LOB da zero da un programma Java è possibile utilizzare la chiamata del codice simile a questo:

Blob myblob =conn.createBlob();

Come scritto sopra si tratta di una chiamata di un metodo che implementa l’interfaccia java.sql.Connection.createBlob, che crea un temporary lob che utilizza spazio allocato sulla temporary tablespace.

Uno dei problemi che può capitare con i temporary lob è che lo spazio allocato nella temp non venga rilasciato. La nota del supporto Oracle 802897.1 spiega come in effetti se all’interno della sessione si effettua la chiamata al metodo “free” (o freeTemporary) lo spazio viene rilasciato all’interno della sessione e può essere riutilizzato all’interno della stessa sessione ma non è disponibile per le altre sessioni. Per liberarlo definitivamente occorre chiudere la sessione. La stessa nota dice che dalla versione 10.2.0.4 è stato introdotto l’evento 60025 che permette di liberare completamente lo spazio senza dover chiudere la sessione. Si deve quindi eseguire il comando:

alter session set events ‘60025 trace name context forever’;

L’evento si dovrebbe poter attivare a livello di sistema, però questo non l’ho provato.

La nota 1384829.1 riporta un programmino java che dimostra come funziona l’evento 60025. La premessa nella nota è che il test è stato fatto con driver jdbc 11.2.0.3 su datatabase 11.2.0.3. Io ho cominciato a giocare con il programma e fare delle prove con il driver che utilizziamo al momento maggiormente, versione 12.1 con una patch. Con questo driver il programma non funziona come descritto, cioè lo spazio nella temporary tablespace non viene liberato se non con la chiusura della connessione. Ho provato con varie versioni di driver 12.1, con java 6 e java 7 ma il risultato è sempre lo stesso, lo spazio non viene rilasciato. Poi mi sono deciso a provare con il driver 18.3; dalla 12.2 in poi il driver jdbc viene fornito solo per jdk 8, se si vuole usare versioni jdk precedenti Oracle suggerisce di usare driver precedenti. Con la versione 18.3 l’evento 60025 torna a funzionare, ovvero la chiamata lob.free() libera lo spazio definitivamente sulla temporary tablespace. Presumo quindi che ci sia qualche problema con i driver 12.1 e l’evento 60025; anche con un driver 11.2.0.3 il programma di test funziona. Ho provato a fare una ricerca più mirata sul supporto Oracle e la cosa più pertinente che ho trovato è stata la nota 2297060.1 che fa riferimento a questo problema e indica fra le versioni interessate dall 9.2 alla 12.2. Quello che viene scritto nelle conclusioni e nella sezione “solution” non mi è affatto chiara. Ho provato allora a fare delle varianti del programmino java per capirne di più. Il risultato è che a un certo punto non capivo proprio nulla perché sembrava che anche con i driver 12.1 funzionasse la liberazione dello spazio TEMP.  La verità è che effettivamente con l’evento 60025 attivo la chiamata del metodo lob.free() libera definitivamente lo spazio occupato dal temporary LOB. Il motivo per cui sembra che l’esempio non funzioni con i driver 12.1 è la riga di codice:

System.out.println(rs.getString(1));

che si trova prima dell’instanziazione della variabile/oggetto lob. Commentando via quella riga di codice l’esempio funziona anche con i driver 12.1.

Riassumento quello che ho capito. Se eseguo una query come questa:

select to_clob(‘bla’) from dual; 

con il codice:

ResultSet rs = stmt.executeQuery(SQL1_syntax);

viene gia creato un temporary lob, viene allocato dello spazio nella temporary tablespace. Anche chiudendo il ResultSet lo spazio non viene liberato; l’unico modo per liberarlo è instanziare l’oggetto, ad esempio con una semplice chiamata come questa:

my_clob = rs.getClob(1);

e poi chiamare il metodo free dell’oggetto:

my_clob.free();

Se l’evento 60025 è stato attivato lo spazio nella temporary tablespace viene liberato.

La chiamata rs.getString(1) probabilmente crea un riferimento che impedisce per motivi che ignoro che lo spazio nella temp venga liberato.

Riferimenti:

  1. How to Release the Temp LOB Space and Avoid Hitting ORA-1652 (Doc ID 802897.1)
  2. How to Release Temporary LOB Segments without Closing the JDBC Connection (Doc ID 1384829.1)

 

SQL*Plus SET FEEDBACK ON SQL_ID

Una delle nuove funzionalità introdotte su SQL*Plus nella versione 18.1 (rif.) è l’opzione “SQL_ID” della direttiva “SET FEEDBACK”. Secondo me una cosa utile e interessante,  peccato che funzioni solo se connessi a database 18c. Per un attimo ho temuto che il problema fosse il client su win, in fatti collegandomi a database con versione 11.2 o 12.1 la cosa non funzionava, poi ho provato a collegarmi a un db 18.3 e magicamente ha funzionato. Peccato, perché al momento l’unico 18c è un db di test interno.

Oracle 12cR2 Resource Manager: Performance profiles

In questo post in cui ho passato in rassegna velocemente le nuove caratteristiche dell’archiettettura multitenant in Oracle 12cR2 ho citato anche i Performance Profile. Oggi, testatando l’implementazione di un CDB resource plan mi sono imbattutto in quella che sembra l’unica parte della documentazione che parla di questa carattaristica. E’ quasi inutile che io riporti qui un esempio quanto oltre a quello che si trova sul manuale c’è una descrizione ancora più completa sul solito sito di Tim Hall, la si trova qui. Io posso aggiungere che ho provato a fare un piano misto, definendo cioè direttive sia per PDB che per profilo. Sul manuale dice:

…Each PDB that uses a profile adopts the CDB resource plan for that profile…

io lo interpetro nel senso che se si definisce un piano come ho fatto io, per cui per un PDB c’è sia una direttiva che riferisce esplicitamente quel PDB che una direttiva per il profilo attivato su quel PDB allora ha precedenza la direttiva sul profilo. Cerco di spiegarmi meglio, riportando la situazione che ho creato:

PLAN PLUGGABLE_DATABASE PROFILE SHARES UTIL PARALLEL
------------------------------ ------------------------- ------------------------- ---------- ---------- ----------
TEST122_PLAN ORA$AUTOTASK 1 85 85
TEST122_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 50 50
TEST122_PLAN SVIL122P1 1 70 70
TEST122_PLAN TEST122P1 3 100 100
TEST122_PLAN BRONZE 1 20 20
TEST122_PLAN GOLD 3 100 100
TEST122_PLAN SILVER 2 40 40

ora, se sul mio PDB svil122p1 ho attivato il profilo “GOLD” anziché avere a disposizione una sola share e un limite del 70% su CPU e processi paralleli, come definito dalla direttiva esplicita, il resource manager darà al PDB svil122p1 3 share  e nessuna limitazione su CPU e processi paralleli. Certamente non è questo il modo in cui Oracle intende in cui vengano utilizzati i performance profile. Sembra che siano fatti per non non dovere definire una direttiva per ogni PDB, anche quando i PDB possono essere molti; da ricordare che su Exadata con la release due si possono avere oltre quattromila PDB. A conferma di questo c’è il fatto che il parametro DB_PERFORMANCE_PROFILE sui PDB è statico, cioè per attivare un dato profilo occorre chiudere e riaprire il PDB, il che non è comodo a mio avviso. Altrimenti io avrei visto i performance profile un modo per cambiare dinamicamente il “profilo” o il ruolo di un PDB per il resource manager, nell’eventualità ad esempio di voler assegnare temporaneamente più risorse a un solo PDB all’interno del CDB.

Oracle 12cR2, primi test con gli Application Container

Dopo aver letto un po’ di documentazione sull’argomento “application container” in Oracle 12cR2 sono passato a dei “test sul campo”. Ho recuperato una installazione fatta qualche mese fa e ho cominciato a fare un po’ di prove. Va premesso che l’argomento è abbastanza complesso, nel senso che le cose che si possono fare sono tante, io mi limito a riportare delle semplici prove che credo siano utili per cominciare a “entrare” nell’argomento.

L’ambiente di partenza era una installazione 12.2.0.1, siccome c’è stato un cambio di macchina per la precisione ho spostato il database da una macchina ad un’altra. Il database era stato gia creato come CDB (se non ho capito male non c’è modo di passare da non-CDB a CDB se non creando un nuovo database, qui si parla di qualcosa di simile). In quel CDB avevo gia creato un PDB che poi era stato usato per un breve test di una funzionalità.

Tramite la query:

select * from database_properties where property_name=’LOCAL_UNDO_ENABLED’;

ho verificato che il database era gia in modalità “LOCAL UNDO”, infatti è il default.

A questo punto ho creato il mio primo application container:

12:45:46 SQL> CREATE PLUGGABLE DATABASE test122ac AS APPLICATION CONTAINER ADMIN USER psystem IDENTIFIED BY manager ROLES = (DBA)
FILE_NAME_CONVERT = (‘/u01/app/oracle/oradata/test122/pdbseed/’, ‘/u01/app/oracle/oradata/test122/test122ac/’);12:45:54 2

Pluggable database created.

Elapsed: 00:00:16.18

select name,open_mode,application_root,local_undo from v$pdbs;
12:51:48 SQL> select name,open_mode,application_root,local_undo from v$pdbs;

NAME OPEN_MODE APP LOCAL_UNDO
——————– ———- — ———-
PDB$SEED READ ONLY NO 1
TEST122P1 READ WRITE NO 1
TEST122AC MOUNTED YES 1

 

Bene, a questo punto volevo portare dentro l’application container il PDB esistente (test122p1). Quindi i passi fatti sono brevemente:

  • avvio application container test122ac
  • arresto PDB test122p1
  • unplug PDB test122p1
  • drop PDB test122p1
  • plug PDB test122p1 in test122ac

Ecco, fin qui non ho avuto problemi, sono passato alla creazione di un nuovo PDB sotto lo stesso application container test122ac:

SQL> CREATE PLUGGABLE DATABASE svil122p1 ADMIN USER psystem IDENTIFIED BY manager ROLES = (DBA)
FILE_NAME_CONVERT = (‘/u01/app/oracle/oradata/test122/pdbseed/’, ‘/u01/app/oracle/oradata/test122/svil122p1/’); 2

Pluggable database created.

A questo punto sono andato a dare un’occhiata all’alert.log e vi ho trovato questo messaggio:

TEST122P1(5):Database Characterset for TEST122P1 is AL32UTF8
2018-08-29T16:06:53.458517+02:00
TEST122P1(5):Opatch validation is skipped for PDB TEST122P1 (con_id=0)
TEST122P1(5):***************************************************************
TEST122P1(5):WARNING: Pluggable Database TEST122P1 with pdb id – 5 is
TEST122P1(5): altered with errors or warnings. Please look into
TEST122P1(5): PDB_PLUG_IN_VIOLATIONS view for more details.
TEST122P1(5):***************************************************************

In effetti interrogando la vista PDB_PLUG_IN_VIOLATIONS ho trovato questo messaggio:

Non-Application PDB plugged in as an Application PDB, requires pdb_to_apppdb.sql be run

Come utente SYS ho eseguito lo script pdb_to_apppdb.sql, sulla vista PDB_PLUG_IN_VIOLATIONS la colonna STATUS è passata da “PENDING” a “RESOLVED” (quindi il record rimane li) e il problema è rientrato.

Ora è arrivato il momento di testare la creazione di una applicazione. Qui la cosa secondo me si fa complessa perché occorre tenere conto di un po’ di vincoli, legati al fatto che un’applicazione che viene definita sull’application container poi verrà in qualche modo “replicata” o installata sui vari PDB. Questo implica che occorre fare attenzione che il “contesto” si replicabile. Un primo esempio, con la spiegazione di un limite di cui tenere conto viene ben descritto in questo post, come l’autore spiega nel post di aggiornamento infatti  se la definizione dell’applicazione prevede la creazione di tablespace allora si è vincolati a usare la struttura OMF, come ha fatto Tim Hall nel suo eccellente articolo. Ora, io sono partito con una prova molto più semplice, in cui nell’applicazione creavo solo una tabella, solo che l’ho fatto con l’utenza PSYSTEM creata assieme all’application container. Ecco qui ho incontrato il primo problema, quando sono andato su TEST122p1 ed ho cercato di fare la sincronizzazione. Infatti anche utilizzando l’utenza SYS (prima avevo provato sempre con PSYSTEM) ottenevo sempre l’errore di privilegi insufficenti:

alter pluggable database application appcri1 sync
*
ERROR at line 1:
ORA-01031: insufficient privileges

Non ci sono arrivato subito, è nel tentativo di risolvere quel problema che sono incappato prima nel problema oggetto del mio post precedente e poi nel post di Anju Garg che ho citato prima. Per farla breve il problema era che il role PDB_DBA su TEST122p1 non aveva il role DBA. In mezzo avevo provato disperatamente a dare anche SYSDBA a PSYSTEM e comunque non funzionava. In realtà questo prova che nel definire un’applicazione occorre usare un approccio come quello esemplificato da Tim Hall, quindi creando anche tablespace (per cui però occorre usare OMF) e utenti/schemi.

Ho quindi testato la funzionalità di disinstallazione di una applicazione da un application container, ho lanciato questi comandi:

alter pluggable database application appcri2 begin uninstall;
drop table common_table_appcri2;
alter pluggable database application appcri2 end uninstall;

Ritengo interessante riportare un estratto dell’alert.log da cui si vede ciò che i comandi sopra hanno scatenato, ovvero la creazione un clone dell’application root che permette di mantenere la versione dell’applicazione com’era prima della disintallazione:

TEST122AC(4):alter pluggable database application appcri2 begin uninstall
CREATE PLUGGABLE DATABASE “F1450013744_4_1” AS APPLICATION CONTAINER from “TEST122AC” CREATE_FILE_DEST=’/u01/app/oracle/oradata/test122/test122ac/’
2018-08-30T12:47:51.000009+02:00
TEST122AC(4): APEX_050000.WWV_FLOW_ADVISOR_CHECKS (CHECK_STATEMENT) – CLOB populated
2018-08-30T12:48:05.018535+02:00
F1450013744_4_1(6):Endian type of dictionary set to little
****************************************************************
Pluggable Database F1450013744_4_1 with pdb id – 6 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x00000000000000e0
****************************************************************
F1450013744_4_1(6):Media Recovery Start
2018-08-30T12:48:05.263003+02:00
F1450013744_4_1(6):Serial Media Recovery started
2018-08-30T12:48:05.323789+02:00
F1450013744_4_1(6):Recovery of Online Redo Log: Thread 1 Group 3 Seq 144 Reading mem 0
F1450013744_4_1(6): Mem# 0: /u01/app/oracle/oradata/test122/redo03.log
2018-08-30T12:48:05.361129+02:00
F1450013744_4_1(6):Incomplete Recovery applied until change 6934636 time 08/30/2018 12:48:04
2018-08-30T12:48:05.368956+02:00
F1450013744_4_1(6):Media Recovery Complete (test122)
F1450013744_4_1(6):Autotune of undo retention is turned on.
2018-08-30T12:48:06.332381+02:00
F1450013744_4_1(6):[20290] Successfully onlined Undo Tablespace 2.
2018-08-30T12:48:06.370038+02:00
F1450013744_4_1(6):Undo initialization finished serial:0 start:3099160625 end:3099160912 diff:287 ms (0.3 seconds)
F1450013744_4_1(6):Database Characterset for F1450013744_4_1 is AL32UTF8
F1450013744_4_1(6):JIT: pid 20290 requesting stop
Completed: CREATE PLUGGABLE DATABASE “F1450013744_4_1” AS APPLICATION CONTAINER from “TEST122AC” CREATE_FILE_DEST=’/u01/app/oracle/oradata/test122/test122ac/’
ALTER PLUGGABLE DATABASE “F1450013744_4_1” OPEN
F1450013744_4_1(6):Autotune of undo retention is turned on.
F1450013744_4_1(6):Endian type of dictionary set to little
2018-08-30T12:48:07.583580+02:00
F1450013744_4_1(6):[20290] Successfully onlined Undo Tablespace 2.
F1450013744_4_1(6):Undo initialization finished serial:0 start:3099161622 end:3099162160 diff:538 ms (0.5 seconds)
F1450013744_4_1(6):Deleting old file#16 from file$
F1450013744_4_1(6):Deleting old file#17 from file$
F1450013744_4_1(6):Deleting old file#18 from file$
F1450013744_4_1(6):Adding new file#30 to file$(old file#16)
F1450013744_4_1(6):Adding new file#31 to file$(old file#17)
F1450013744_4_1(6):Adding new file#32 to file$(old file#18)
****************************************************************
Post plug operations are now complete.
Pluggable database F1450013744_4_1 with pdb id – 6 is now marked as NEW.
****************************************************************
F1450013744_4_1(6):Database Characterset for F1450013744_4_1 is AL32UTF8
F1450013744_4_1(6):Opatch validation is skipped for PDB F1450013744_4_1 (con_id=0)
2018-08-30T12:48:13.067787+02:00
F1450013744_4_1(6):Opening pdb with no Resource Manager plan active
Pluggable database F1450013744_4_1 opened read write
Completed: ALTER PLUGGABLE DATABASE “F1450013744_4_1” OPEN
ALTER PLUGGABLE DATABASE “F1450013744_4_1” CLOSE
F1450013744_4_1(6):JIT: pid 20290 requesting stop
2018-08-30T12:48:14.643037+02:00
Pluggable database F1450013744_4_1 closed
Completed: ALTER PLUGGABLE DATABASE “F1450013744_4_1” CLOSE
ALTER PLUGGABLE DATABASE “F1450013744_4_1” OPEN READ ONLY INSTANCES=ALL
F1450013744_4_1(6):Autotune of undo retention is turned on.
F1450013744_4_1(6):Endian type of dictionary set to little
F1450013744_4_1(6):Undo initialization finished serial:0 start:3099169330 end:3099169330 diff:0 ms (0.0 seconds)
F1450013744_4_1(6):Database Characterset for F1450013744_4_1 is AL32UTF8
F1450013744_4_1(6):Opatch validation is skipped for PDB F1450013744_4_1 (con_id=0)
F1450013744_4_1(6):Opening pdb with no Resource Manager plan active
Pluggable database F1450013744_4_1 opened read only
Completed: ALTER PLUGGABLE DATABASE “F1450013744_4_1” OPEN READ ONLY INSTANCES=ALL
TEST122AC(4):Completed: alter pluggable database application appcri2 begin uninstall
2018-08-30T12:49:02.396116+02:00
TEST122AC(4):alter pluggable database application appcri2 end uninstall
TEST122AC(4):Completed: alter pluggable database application appcri2 end uninstall

In verità non mi sembra sia possibile ripristinare una applicazione disintallata… però il clone serve a gestire il fatto che ci siano PDB collegati all’application container su cui non è stata fatta la sincronizzazione e quindi l’applicazione rimane installata.

Come avevo scritto nel mio post di introduzione agli “application container” ho testato la possibilità di creare più applicazioni su uno stesso application container; è possibile quindi, seppur non so quanto abbia senso, creare più applicazioni su uno stesso application container.

Dopo il primo test di base ho quindi disinstallato entrambe le applicazioni che avevo creato trovandomi con due cloni della application root. Ho quindi fatto un nuovo test cercando di creare un’applicazione “completa”, quindi sulla base dell’esempio di Tim Hall creando tablespace e schema al cui interno poi creare gli oggetti. Mi sono trovato un po’ in difficoltà a capire i privilegi necessari, negli esempi, anche  sui manuali si utlizza l’utenza SYS, però mi sembra un po’ eccessivo dover utilizzare una utenza superprivilegiata per installare delle applicazioni. Ho deciso di rimandare l’approfondimento sul tema privilegi ad  un’altra occasione e sono andato avanti con l’utenza SYS per creare una nuova applicazione con queste istruzioni:

alter session set db_create_file_dest=’/u01/app/oracle/oradata/test122/test122ac’;
alter pluggable database application appcri3 begin install ‘1.0’;
create tablespace apptbs1 datafile size 1 M autoextend on maxsize 2 g;
create user app1user identified by app1user default tablespace apptbs1 quota unlimited on apptbs1 container=ALL;
grant create session, create table to app1user;
create table app1user.apptable1 sharing=extended data (a number,b varchar2(100 char));
insert into app1user.apptable1 (a,b) values (1,’a’);
commit;
alter pluggable database application appcri3 end install;

Ho fatto una scelta un po’ bizzarra, ho usato OMF ma ho impostato il parametro DB_CREATE_FILE_DEST a livello di sessione, questo comporta occorrerà fare attenzione in fase di sincronizzazione dell’applicazione sui PDB, infatti se non si setta prima della sincronizzazione opportunamente il parametro accade questo:

SYS@//svil122p1 AS SYSDBA> alter pluggable database application appcri3 sync;
alter pluggable database application appcri3 sync
*
ERROR at line 1:
ORA-02236: invalid file name

Se invece si imposta prima il parametro DB_CREATE_FILE_DEST tutto fila liscio; mi sembra chiaro che se si vuole gestire in modo efficiente è utile impostare il parametro a livello di system root, quindi usare OMF per la gestione degli application container e dei PDB.

Ho fatto poi un banale test di aggiornamento dell’applicazione con questi comandi:

alter pluggable database application appcri3 begin upgrade ‘1.0’ to ‘1.1’;
create table app1user.apptable2 sharing=metadata (a number,b varchar2(100 char));
insert into app1user.apptable2 (a,b) values (2,’b’);
commit;
alter pluggable database application appcri3 end upgrade;

Mi sono trovato quindi con tre cloni della application root. A questo punto mi sono chiesto se è possibile contenere la prolificazione di questi cloni. In effetti un metodo c’è ed è l’impostazione del livello di compatibilità. Lanciando questo comando:

alter pluggable database application appcri3 set compatibility version ‘1.1’;

Sull’alert.log ho trovato questo:

TEST122AC(4):alter pluggable database application appcri3 set compatibility version ‘1.1’
ALTER PLUGGABLE DATABASE “F1450013744_23_1” CLOSE
2018-08-31T14:43:06.003621+02:00
F1450013744_23_1(8):JIT: pid 20697 requesting stop
Pluggable database F1450013744_23_1 closed
Completed: ALTER PLUGGABLE DATABASE “F1450013744_23_1” CLOSE
DROP PLUGGABLE DATABASE “F1450013744_23_1” INCLUDING DATAFILES
TEST122AC(4):Deleted Oracle managed file /u01/app/oracle/oradata/test122/test122ac/TEST122/74A7AB8E0E337014E0533B00640AEA9C/datafile/o1_mf_apptbs1_frhtnv92_.dbf
TEST122AC(4):Deleted Oracle managed file /u01/app/oracle/oradata/test122/test122ac/TEST122/74A7AB8E0E337014E0533B00640AEA9C/datafile/o1_mf_temp_frhtnv92_.dbf
TEST122AC(4):Deleted Oracle managed file /u01/app/oracle/oradata/test122/test122ac/TEST122/74A7AB8E0E337014E0533B00640AEA9C/datafile/o1_mf_undotbs1_frhtnv92_.dbf
TEST122AC(4):Deleted Oracle managed file /u01/app/oracle/oradata/test122/test122ac/TEST122/74A7AB8E0E337014E0533B00640AEA9C/datafile/o1_mf_sysaux_frhtnv91_.dbf
TEST122AC(4):Deleted Oracle managed file /u01/app/oracle/oradata/test122/test122ac/TEST122/74A7AB8E0E337014E0533B00640AEA9C/datafile/o1_mf_system_frhtnv90_.dbf
Completed: DROP PLUGGABLE DATABASE “F1450013744_23_1” INCLUDING DATAFILES
TEST122AC(4):Completed: alter pluggable database application appcri3 set compatibility version ‘1.1’

E in effetti il clone è stato rimosso:

SYS@//test122ac AS SYSDBA> select con_id,name,APPLICATION_ROOT_CON_ID,APPLICATION_ROOT_Clone from v$containers;

CON_ID NAME APPLICATION_ROOT_CON_ID APP
———- —————— ———————– —
1 CDB$ROOT NO
2 PDB$SEED NO
3 SVIL122P1 4 NO
4 TEST122AC NO
5 TEST122P1 4 NO
6 F1450013744_4_1 4 YES
7 F1450013744_3_1 4 YES

Esiste anche l’opzione di impostare il livello di compatibilità al valore “corrente”:

alter pluggable database application appcri1 set compatibility version current;

In questo modo anche i cloni creati in seguiti alla disinstallazione delle applicazioni sono stati rimossi, cosa che mi è piaciuta. Nel dizionario dati, come scritto sulla documentazione rimangono comunque le informazioni sulle applicazioni:

SYS@//test122ac AS SYSDBA> select * from DBA_APP_PDB_STATUS;

CON_UID APP_NAME APP_ID APP_VERSION APP_STATUS
———- ——————– ———- —————————— ———–
4143301262 APP$7491A9CF30E3674F 2 1.0 NORMAL
E0533B00640A0B7F

1030522367 APP$7491A9CF30E3674F 2 1.0 NORMAL
E0533B00640A0B7F

4143301262 APPCRI3 23 1.1 NORMAL
1030522367 APPCRI3 23 1.1 NORMAL
4143301262 APPCRI1 3 1.0 UNINSTALLED
1030522367 APPCRI1 3 1.0 UNINSTALLED
1030522367 APPCRI2 4 1.0 UNINSTALLED
4143301262 APPCRI2 4 1.0 UNINSTALLED

8 rows selected.