21 dicembre 2008

Analytics - n.4: ranking

Stavolta ci occupiamo delle funzioni RANK e DENSE_RANK.

Consideriamo ancora l'esempio dei maratoneti.
All'arrivo possiamo fare la classifica generale, ordinando per tempo di arrivo e assegnando a ogni corridore un numero progressivo che parte da 1, ad esempio con ROWNUM. Prendo un sottoinsieme della mia tabella di esempio per semplicità:
SQL> select ri, at, rownum rn from
2 (select runner_id ri, arrival_time at
3 from marathon
4 where arrival_time between to_date('2008-06-19 13:01:00', 'YYYY-MM-DD HH24:MI:SS')
5 and to_date('2008-06-19 13:03:00', 'YYYY-MM-DD HH24:MI:SS') order by arrival_time)
6 ;

RI AT RN
---------- ------------------- ----------
2015 2008-06-19 13:01:04 1
2293 2008-06-19 13:01:05 2
3895 2008-06-19 13:01:06 3
4931 2008-06-19 13:01:07 4
4810 2008-06-19 13:01:10 5
2747 2008-06-19 13:01:17 6
125 2008-06-19 13:01:19 7
1664 2008-06-19 13:01:20 8
632 2008-06-19 13:01:21 9
638 2008-06-19 13:01:23 10
2207 2008-06-19 13:01:31 11
4816 2008-06-19 13:01:32 12
3550 2008-06-19 13:01:33 13
3632 2008-06-19 13:01:33 14
309 2008-06-19 13:01:34 15
2254 2008-06-19 13:01:36 16
1671 2008-06-19 13:01:43 17
2068 2008-06-19 13:01:46 18
3167 2008-06-19 13:02:02 19
4269 2008-06-19 13:02:02 20
4962 2008-06-19 13:02:02 21
3921 2008-06-19 13:02:02 22
2298 2008-06-19 13:02:29 23
4933 2008-06-19 13:02:30 24
2093 2008-06-19 13:02:34 25
1528 2008-06-19 13:02:36 26
1779 2008-06-19 13:02:40 27
4313 2008-06-19 13:02:40 28
2547 2008-06-19 13:02:40 29
4482 2008-06-19 13:02:43 30
3667 2008-06-19 13:02:44 31
1117 2008-06-19 13:02:51 32
4393 2008-06-19 13:02:53 33
591 2008-06-19 13:02:55 34

34 rows selected.

Ma che cosa succede se due corridori hanno lo stesso tempo di arrivo (es. 13:02:02)? Bisogna assegnargli la posizione a parimerito. La funzione DENSE_RANK ci viene in aiuto (colonna DR):
SQL> select ri, at, rownum rn, dense_rank() over (order by at) dr from
2 (select runner_id ri, arrival_time at
3 from marathon
4 where arrival_time between to_date('2008-06-19 13:01:00', 'YYYY-MM-DD HH24:MI:SS')
5 and to_date('2008-06-19 13:03:00', 'YYYY-MM-DD HH24:MI:SS') order by arrival_time)
6 ;

RI AT RN DR
---------- ------------------- ---------- ----------
2015 2008-06-19 13:01:04 1 1
2293 2008-06-19 13:01:05 2 2
3895 2008-06-19 13:01:06 3 3
4931 2008-06-19 13:01:07 4 4
4810 2008-06-19 13:01:10 5 5
2747 2008-06-19 13:01:17 6 6
125 2008-06-19 13:01:19 7 7
1664 2008-06-19 13:01:20 8 8
632 2008-06-19 13:01:21 9 9
638 2008-06-19 13:01:23 10 10
2207 2008-06-19 13:01:31 11 11
4816 2008-06-19 13:01:32 12 12
3550 2008-06-19 13:01:33 13 13
3632 2008-06-19 13:01:33 14 13
309 2008-06-19 13:01:34 15 14
2254 2008-06-19 13:01:36 16 15
1671 2008-06-19 13:01:43 17 16
2068 2008-06-19 13:01:46 18 17
3167 2008-06-19 13:02:02 19 18
4269 2008-06-19 13:02:02 20 18
4962 2008-06-19 13:02:02 21 18
3921 2008-06-19 13:02:02 22 18
2298 2008-06-19 13:02:29 23 19
4933 2008-06-19 13:02:30 24 20
2093 2008-06-19 13:02:34 25 21
1528 2008-06-19 13:02:36 26 22
1779 2008-06-19 13:02:40 27 23
4313 2008-06-19 13:02:40 28 23
2547 2008-06-19 13:02:40 29 23
4482 2008-06-19 13:02:43 30 24
3667 2008-06-19 13:02:44 31 25
1117 2008-06-19 13:02:51 32 26
4393 2008-06-19 13:02:53 33 27
591 2008-06-19 13:02:55 34 28

34 rows selected.

Diverso sarebbe il caso in cui volessimo considerare l'ordine di arrivo, fatti salvi i corridori a parimerito: infatti quelli a parimerito sarebbero tutti nella stessa posizione, mentre il successivo avrebbe comunque la posizione assoluta (tipo ROWNUM) conservata (colonna R):
SQL> select ri, at, rownum rn, dense_rank() over (order by at) dr,
2 rank() over (order by at) r from
3 (select runner_id ri, arrival_time at
4 from marathon
5 where arrival_time between to_date('2008-06-19 13:01:00', 'YYYY-MM-DD HH24:MI:SS')
6 and to_date('2008-06-19 13:03:00', 'YYYY-MM-DD HH24:MI:SS') order by arrival_time)
7 ;

RI AT RN DR R
---------- ------------------- ---------- ---------- ----------
2015 2008-06-19 13:01:04 1 1 1
2293 2008-06-19 13:01:05 2 2 2
3895 2008-06-19 13:01:06 3 3 3
4931 2008-06-19 13:01:07 4 4 4
4810 2008-06-19 13:01:10 5 5 5
2747 2008-06-19 13:01:17 6 6 6
125 2008-06-19 13:01:19 7 7 7
1664 2008-06-19 13:01:20 8 8 8
632 2008-06-19 13:01:21 9 9 9
638 2008-06-19 13:01:23 10 10 10
2207 2008-06-19 13:01:31 11 11 11
4816 2008-06-19 13:01:32 12 12 12
3550 2008-06-19 13:01:33 13 13 13
3632 2008-06-19 13:01:33 14 13 13
309 2008-06-19 13:01:34 15 14 15
2254 2008-06-19 13:01:36 16 15 16
1671 2008-06-19 13:01:43 17 16 17
2068 2008-06-19 13:01:46 18 17 18
3167 2008-06-19 13:02:02 19 18 19
4269 2008-06-19 13:02:02 20 18 19
4962 2008-06-19 13:02:02 21 18 19
3921 2008-06-19 13:02:02 22 18 19
2298 2008-06-19 13:02:29 23 19 23
4933 2008-06-19 13:02:30 24 20 24
2093 2008-06-19 13:02:34 25 21 25
1528 2008-06-19 13:02:36 26 22 26
1779 2008-06-19 13:02:40 27 23 27
4313 2008-06-19 13:02:40 28 23 27
2547 2008-06-19 13:02:40 29 23 27
4482 2008-06-19 13:02:43 30 24 30
3667 2008-06-19 13:02:44 31 25 31
1117 2008-06-19 13:02:51 32 26 32
4393 2008-06-19 13:02:53 33 27 33
591 2008-06-19 13:02:55 34 28 34

34 rows selected.

Un'altra funzione è ROW_NUMBER, che semplicemente assegna un valore crescente a partire da 1 alle righe della partizione, nulla più.
In pratica potremmo sostituire la subquery più interna che fa l'ordinamento direttamente con ROW_NUMBER, senza utilizzare ROWNUM:
SQL> select runner_id ri, arrival_time at, row_number() over (order by arrival_time) rn
2 from marathon
3 where arrival_time between to_date('2008-06-19 13:01:00', 'YYYY-MM-DD HH24:MI:SS')
4 and to_date('2008-06-19 13:03:00', 'YYYY-MM-DD HH24:MI:SS') order by arrival_time;

RI AT RN
---------- ------------------- ----------
2015 2008-06-19 13:01:04 1
2293 2008-06-19 13:01:05 2
3895 2008-06-19 13:01:06 3
4931 2008-06-19 13:01:07 4
4810 2008-06-19 13:01:10 5
2747 2008-06-19 13:01:17 6
125 2008-06-19 13:01:19 7
1664 2008-06-19 13:01:20 8
632 2008-06-19 13:01:21 9
638 2008-06-19 13:01:23 10
2207 2008-06-19 13:01:31 11
4816 2008-06-19 13:01:32 12
3550 2008-06-19 13:01:33 13
3632 2008-06-19 13:01:33 14
309 2008-06-19 13:01:34 15
2254 2008-06-19 13:01:36 16
1671 2008-06-19 13:01:43 17
2068 2008-06-19 13:01:46 18
3167 2008-06-19 13:02:02 19
4269 2008-06-19 13:02:02 20
4962 2008-06-19 13:02:02 21
3921 2008-06-19 13:02:02 22
2298 2008-06-19 13:02:29 23
4933 2008-06-19 13:02:30 24
2093 2008-06-19 13:02:34 25
1528 2008-06-19 13:02:36 26
1779 2008-06-19 13:02:40 27
4313 2008-06-19 13:02:40 28
2547 2008-06-19 13:02:40 29
4482 2008-06-19 13:02:43 30
3667 2008-06-19 13:02:44 31
1117 2008-06-19 13:02:51 32
4393 2008-06-19 13:02:53 33
591 2008-06-19 13:02:55 34

34 rows selected.

02 dicembre 2008

Indovinello con COUNT: soluzione

L'indovinello e la relativa soluzione derivano dal libro di Jonathan Lewis "Cost-based Oracle", di cui ho già parlato; libro peraltro a volte molto difficile, quasi sempre molto pesante, che però a volte riserva soprese e piccole grandi illuminazioni.
Ho riscritto tutto in un modo che mi sembra più chiaro.

Cominciamo a definire un vettore contenente tutte le età possibili da 1 a 36 anni:

select
rownum age
from
all_objects
where
rownum <= 36;

Cominciamo a porre la prima condizione: il prodotto delle età è 36.
Imponiamo anche, per comodità, la condizione che le età siano crescenti (age1 <= age2 <= age3), per evitare risultati simmetrici:
with age_list as
(
select
rownum age
from
all_objects
where
rownum <= 36
)
select
age1.age a1,
age2.age a2,
age3.age a3
from
age_list age1,
age_list age2,
age_list age3
where
age1.age <= age2.age
and age2.age <= age3.age
and age1.age * age2.age * age3.age = (select max(age) from age_list);

Ora abbiamo tutte le età possibili:
 A1    A2       A3
---------- ---------- ----------
1 1 36
2 2 9
1 2 18
3 3 4
2 3 6
1 3 12
1 4 9
1 6 6

Ora sommiamo le età:
with age_list as
(
select
rownum age
from
all_objects
where
rownum <= 36
),
product_check as
(
select
age1.age a1,
age2.age a2,
age3.age a3
from
age_list age1,
age_list age2,
age_list age3
where
age1.age <= age2.age
and age2.age <= age3.age
and age1.age * age2.age * age3.age = (select max(age) from age_list)
)
select
a1,
a2,
a3,
a1+a2+a3 age_sum
from product_check;

A1 A2 A3 AGE_SUM
---------- ---------- ---------- ----------
1 1 36 38
2 2 9 13
1 2 18 21
3 3 4 10
2 3 6 11
1 3 12 16
1 4 9 14
1 6 6 13

Ora: il secondo DBA è ancora dubbioso anche sapendo la somma delle età, ottenuta contando le persone presenti nella stanza, quindi ci troviamo in un caso di ambiguità (somme uguali), che troviamo mediante la nostra funzione analitica :-)
with age_list as
(
select
rownum age
from
all_objects
where
rownum <= 36
),
product_check as
(
select
age1.age a1,
age2.age a2,
age3.age a3
from
age_list age1,
age_list age2,
age_list age3
where
age1.age <= age2.age
and age2.age <= age3.age
and age1.age * age2.age * age3.age = (select max(age) from age_list)
),
sum_check as
(
select
a1,
a2,
a3,
a1+a2+a3 age_sum
from
product_check
)
select
a1,
a2,
a3,
count(*) over (partition by age_sum) c
from
sum_check;

A1 A2 A3 C
---------- ---------- ---------- ----------
3 3 4 1
2 3 6 1
2 2 9 2
1 6 6 2
1 4 9 1
1 3 12 1
1 2 18 1
1 1 36 1

I due casi sono 2, 2, 9 e 1, 6, 6, dove il conteggio delle somme uguali è maggiore di 1.
L'ultimo indizio ci informa che esiste una figlia maggiore (il criceto serve a confondere un po' le idee), il che ci permette di escludere che il caso 1, 6, 6 e di affermare quindi che le età sono 2, 2 e 9 anni.

30 novembre 2008

Analytics - n.3: un indovinello con COUNT

Anche le funzioni classiche come COUNT, SUM, AVG hanno la loro controparte analitica.

Oggi utilizziamo Oracle per risolvere un indovinello, utilizzando la versione analitica delle tradizionali funzioni di gruppo. Chi lo dice che tutte le spiegazioni devono per forza essere noiose? :-)

Due DBA si incontrano all'Oracle Openworld. Il primo viene a sapere che il secondo ha 3 figlie, quindi gli chiede la loro età. Il secondo, in vena di enigmi, dice:
"Il prodotto delle età delle mie figlie è 36."
L'altro ragiona un attimo, ma chiede più informazioni, perché il solo prodotto delle età non gli basta.
"La somma delle età delle mie figlie è uguale al numero di persone che stanno in questa stanza"
L'altro conta le persone, ci pensa, ma i dati non gli bastano ancora, e chiede maggiori indizi.
"La più grande delle mie figlie ha un criceto maculato".
L'altro raggiunge un prompt SQL e, dopo pochi minuti, ha la soluzione!

Come avrà fatto?

Domani pubblicherò la soluzione.
Mi raccomando non utilizzate Google, altrimenti non è leale (sempre che si riesca a trovare con Google).
Pubblicate le vostre soluzioni nei commenti, a condizione che utilizziate SQL e le funzioni analitiche.

23 novembre 2008

Solaris sostituisce Linux a casa

Sono passato a Solaris per il mio megaserver casalingo. Complice la scarsa affidabilità del paio di dischi originali, sostituiti con una coppia da 320 GB nuova, mi sono trovato a provare la nuova splendida versione di Solaris (10/08) su x86/64.

Con la nuova versione di Solaris è possibile avere il disco di root in mirror software, attraverso un pool ZFS. Non solo: il programma di installazione installa il boot-loader grub anche sul secondo disco, rendendo praticamente indifferente il disco da cui viene fatto il boot.
ZFS infatti si basa su metadati registrati sui dischi; da ciò ne deriva che, anche facendo il boot dal secondo disco, ci si ritrova esattamente come se si fosse fatto il boot dal primo.
Non ho ancora provato ma, quasi sicuramente, anche l'ordine dei dischi rispetto al BIOS e la loro posizione sul bus SATA sia del tutto indifferente.

Con ZFS lo spazio dei dischi si sfrutta molto meglio, non essendoci spazi vuoti tra un filesystem e l'altro, quindi mi sento un po' meno "costretto" dallo spazio disponibile.
ZFS inoltre ottimizza le letture da disco, utilizzando entrambi i dischi in parallelo come i migliori controller RAID, ottenendo quasi un raddoppio delle prestazioni. Anche in scrittura la velocità non manca, almeno da quanto ho potuto constatare. Pensavo infatti che la doppia scrittura fosse molto penalizzante, ma mi sono ricreduto; evidentemente ZFS fa la seconda scrittura in modo asincrono.

Pochi giorni fa è uscito l'ultimo patchset 10g per Solaris x86/64, che ora fa bella mostra di sé nel nuovo server. Purtroppo per 11g si prevedono tempi più lunghi, sempre che non decidano (chissà) di trascurare Solaris su Intel.

VirtualBox, il software di virtualizzazione open-source della Sun, funziona perfettamente, e per ora sto provando a creare nuove macchine virtuali Linux, per avere installazioni RAC di prova.

ZFS esporta nativamente target iSCSI tramite volumi ZFS, differenti dai filesystem ZFS, quindi penso che i nuovi RAC virtuali avranno il loro storage condiviso via iSCSI. Non so invece se sono utilizzabili con RAC i volumi vboxfs condivisi, per i soliti problemi di sincronia dell'accesso ai dischi.

Ho completato anche una installazione di Oracle BI Publisher (per una demo) in modalità manuale, ovvero facendo uso del servlet engine di Sun, invece del nativo OC4J, non disponibile per la mia coppia CPU/OS.
Ho utilizzato infatti Sun Java System Web Server 7.0, molto interessante. Ovviamente l'installazione del web server è stata facilissima, come la sua gestione (se si conosce ciò che si sta facendo). In questi aspetti, il software enterprise è enormemente più piacevole da usare del software open-source.

Ho fatto i primi passi anche con i Solaris Containers, che sono una specie di macchine virtuali uguali al sistema operativo ospite. Di questo parleremo più avanti.

19 novembre 2008

Oracle e SQL Server a confronto

Avendo avuto a che fare con un test di migrazione da Microsoft SQL Server a Oracle, mi si è aperto un mondo nuovo con l'utilizzo di SQL Developer Migration Workbench.

Nella documentazione HTML è presente un'interessante comparativa tra SQL Server e Oracle, molto istruttiva da qualsiasi punto di vista, anche per riassumere le caratteristiche principali dei due database.
In particolare vengono sintetizzate le differenze per quanto riguarda i concetti di locking e di concorrenza.

04 novembre 2008

I servizi RAC anche su istanza singola

Una delle particolarità di Oracle RAC è la possibilità di definire i servizi, ovvero una sorta di modalità di connessione via rete al db avente caratteristiche definibili dall'utente.
Se, per esempio, abbiamo un RAC da 3 nodi e volessimo che una nostra applicazione si collegasse solo ai nodi 2 e 3 del cluster, definiremmo un servizio che viene attivato solo sui nodi 2 e 3, in modo che le connessioni dirette al cluster vengano rimandate dai listener solo ai nodi su cui è attivo il servizio.
Ci sono molti altri parametri di configurazione di un servizio, tra cui le istanze verso le quali viene rediretto il servizio nel caso di indisponibilità dei nodi attivi, timeout vari e quant'altro.

È abbastanza interessante però che si possano definire i servizi anche per un database non RAC, composto da una sola istanza (esistono comunque anche i RAC single-instance).
I servizi, nel caso istanza singola, si possono creare e gestire con DBMS_SERVICE e le sue procedure CREATE_SERVICE e START_SERVICE.
CREATE_SERVICE prende come parametri il nome del servizio visto dal lato db e il nome visto dal lato listener, ovvero il famoso SERVICE_NAME all'interno dei connect descriptor dei client.

Ovviamente il connection load-balancing, e i vari metodi di failover non hanno senso, ma è possibile trarre qualche vantaggio da questa architettura.
Direi che i vantaggi possono essere un paio:
  1. Se creo un servizio e gli utenti di un certo gruppo/applicazione si collegano a quel servizio, posso fermarlo per manutenzione quando mi pare e impedirgli di connettersi, mentre il db rimane aperto e gli altri utenti continuano a lavorare senza problemi.
  2. Tramite la procedura DISCONNECT_SESSION posso disconnettere tutte e sole le sessioni collegate ad un determinato servizio con un solo comando, e a volte può essere veramente utile.

18 ottobre 2008

Backup "istantaneo" con Oracle e ZFS

Continuiamo la miniserie di post su ZFS, l'ottimo filesystem di Solaris e OpenSolaris.

La proprietà di poter fare lo snapshot di un filesystem consente di utilizzare il backup a caldo del database via ALTER DATABASE BEGIN BACKUP con Oracle 10g.
In Oracle 10g, infatti, è possibile mettere tutto il db in backup mode con un solo comando, invece di farlo singolarmente per ogni tablespace.

Ecco il log di un backup effettuato a caldo con uno snapshot su una macchina Solaris 10:
Connected to:
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

SQL> set timing on
SQL> alter database begin backup;

Database altered.

Elapsed: 00:00:00.62

SQL> host sudo zfs destroy u01/oradata@`date +%A`
cannot open 'u01/oradata@Thursday': dataset does not exist

SQL> host sudo zfs snapshot u01/oradata@`date +%A`

SQL> alter database end backup;

Database altered.

Elapsed: 00:00:00.44
SQL>

Il tutto è durato pochi secondi, e comunque la durata dello snapshot è quasi indipendente dalle dimensioni del database.
Lo snapshot è ora pronto su disco da copiare su un altro server. Questo è il passo che richiederà più tempo, ma il backup è praticamente già fatto.

$ zfs list
NAME USED AVAIL REFER MOUNTPOINT
u01 17.2G 49.7G 80.6M /u01
u01/oradata 17.1G 49.7G 17.1G /u01/oradata
u01/oradata@Thursday 526K - 17.1G -
u02 16.9G 79.6G 14.3G /u02
u02/oradata 2.59G 79.6G 2.59G /u02/oradata

Per il trasferimento dello snapshot su nastro o su altri dischi o server si possono usare i comandi tradizionali, oppure "rigenerare" lo snapshot direttamente su un altro filesystem ZFS di un altro server via ssh con zfs send.
Ma vi lascio scoprire questa magica e gustosa feature da soli per esercizio :-)

Come è possibile tutto ciò?
Come si può vedere lo snapshot, dopo qualche secondo dalla creazione, occupa circa 500 KB, e le sue dimensioni continuano a crescere.
ZFS alloca lo spazio su disco in "record" (analoghi ai blocchi dei filesystem tradizionali), grandi di default 128 KB.
In pratica ZFS fa in modo di tenere validi su disco i record appartenenti al filesystem al momento dello snapshot, occupando nuovo spazio per i blocchi che vengono modificati o aggiunti al filesystem originario. Il nuovo spazio allocato, man mano che passa il tempo, compare nella lista come occupazione dello snapshot. Ricorda il funzionamento degli snapshot LVM. Geniale.

08 ottobre 2008

RAC su macchine virtuali: ma ha senso?

Leggo volentieri da Techblast che Oracle ha certificato RAC su macchine virtuali Oracle VM.

Avere un cluster su macchine virtuali va un po' contro l'idea di alta affidabilità insita nel concetto di cluster.

La parte interessante nel documento Oracle è ovviamente l'architettura. E le chicche non mancano.

In sintesi l'architettura è spiegata dall'immagine seguente:
È evidente come la virtualizzazione venga utilizzata per ospitare più nodi virtuali (di cluster diversi) su un solo server fisico, ma il meccanismo di alta affidabilità viene conservato poichè ogni nodo di uno stesso database cluster risiede su un server fisico diverso.

Lo storage condiviso di RAC viene gestito in domain0:
Il multipathing viene utilizzato solamente sul percorso fisico dei dati da e verso i dischi, non su quello virtuale da domain0 ai domini virtuali.

Analogamente, le interfacce di rete sono in bond solo sulle porte fisiche, mentre i bridge virtuali di xenbr0 e xenbr1, sempre in domain0, sono rispettivamente la rete pubblica e la rete privata del RAC.
Essendo le istanze del RAC su nodi fisici differenti, anche per la comunicazione su rete privata si passa da rete fisica; ciò non è necessario invece nel caso di nodi sullo stesso server, che comunicherebbero direttamente attraverso il bridge 1; ma questa non è assolutamente una configurazione ragionevole per un ambiente HA.

Date le premesse del documento Oracle, sotto quest'ottica la soluzione RAC su macchine virtuali diventa tutt'altro che insensata in vari ambienti, soprattutto per consolidare server e sfruttare al massimo la potenza di elaborazione dei server attuali, facendo economia di scala.

25 settembre 2008

Analytics - n.2: LEAD e LAG

LEAD e LAG sono funzioni analitiche che permettono di accedere al valore di un campo di un record del recordset risultato della nostra query, specificando l'offset dal record in cui la funzione viene valutata.
Nel caso di un offset che va fuori dal recordset, è possibile specificare un valore di default ritornato dalla funzione.

Il punto essenziale, come per gran parte delle funzioni analitiche, è l'ordinamento del risultato: il valore dell'n-esima riga prima o dopo la riga corrente è dato proprio dell'"order by" specificato nel partizionamento del resultset.

Prendiamo sempre l'esempio dei maratoneti che ho usato in un post precedente.
Mettiamo che io voglia conoscere la differenza di tempo in arrivo tra un corridore e il successivo, un po' come viene fatto alla fine di gare a tempo.
Posso usare la funzione LAG con parametro 1 e computata ordinando la tabella per arrival_time:
 SELECT runner_id,
arrival_time ,
extract(minute FROM
(arrival_time - lag(arrival_time, 1, arrival_time) over (order by arrival_time)) DAY TO second)
||''''
|| extract(second FROM
(arrival_time - lag(arrival_time, 1, arrival_time) over (order by arrival_time)) DAY TO second)
||'"' time_diff
FROM marathon
WHERE rownum < 10
ORDER BY arrival_time;

Nell'esempio prendo solo 10 righe per avere un po' di intervallo tra i tempi di arrivo e il risultato è il seguente:
 RUNNER_ID ARRIVAL_TIME        TIME_DIFF
---------- ------------------- ----------------------------------------------------------------------------------
4038 2008-06-19 12:32:09 0'0"
3144 2008-06-19 12:44:10 12'1"
660 2008-06-19 12:51:40 7'30"
4450 2008-06-19 12:54:54 3'14"
2441 2008-06-19 12:58:28 3'34"
591 2008-06-19 13:02:55 4'27"
3825 2008-06-19 13:05:03 2'8"
3169 2008-06-19 13:11:21 6'18"
4036 2008-06-19 13:11:39 0'18"

La funzionalità di LEAD è analoga a LAG, solo che va nel verso opposto, ovvero considera i record precedenti a quello in cui viene valutata.

21 settembre 2008

Oracle DBA Italia

Tempo fa sul blog di Cristian Cudizio si parlava di qualche iniziativa per riunire tutti i DBA Oracle italiani in una specie di network.
Ho provato a pensare a qualche soluzione, e l'unica che mi è venuta in mente è utilizzare Facebook.
Ho deciso quindi di fare una prova: in pieno spirito Web 2.0, ho creato un gruppo Facebook per tutti i DBA Oracle in Italia.

Invito tutti gli interessati ad iscriversi, prima di tutto a Facebook, se non fossero già iscritti.

Per ora il gruppo è pubblico, quindi tutti possono iscriversi e utilizzare tutte le funzioni messe a disposizione da Facebook. Anche il contenuto delle pagine create dai membri rimane pubblico.

Le funzioni sono le seguenti:
  • Discussioni
  • Bacheca
  • Foto
  • Video
  • Link utili
  • Eventi

Ritengo che siano più che sufficienti per iniziare a interagire, scambiarsi informazioni che altrove (es. sui blog dei partecipanti) non trovano collocazione e, perché no, organizzare futuri incontri ed eventi.
Non dimentichiamo che anche le aziende potrebbero essere molto interessate a noi!

Forza quindi! Io per oggi ho contribuito con il logo. Sfogate la vostra voglia di comunicare!
Attendo commenti, contributi, modifiche, critiche, segnali di vita...

25 agosto 2008

Dischi, burro e marmellata

Sono quantomai sorpreso dell'apprendere, sull'ultimo numero dell'ottimo Oracle Magazine, che Oracle ha avviato pochi mesi fa un progetto open source per un nuovo potente filesystem.

La grande sorpresa consiste nel fatto che tale BTRFS dove, viene precisato, btr si pronuncia butter, ricalca fedelmente l'interessantissimo ZFS di Sun, disponibile già da tempo nella famiglia di sistemi operativi Solaris.

Avevo intenzione di fare un post solo su ZFS per le sue rivoluzionarie caratteristiche, ovviamente dal punto di vista Oracle, anche per il fatto che ci sto lavorando abbastanza intensamente e ne ho potuto apprezzare le ottime funzionalità.

A questo punto cominciamo da ZFS. La migliore definizione che mi viene in mente è che ZFS è un filesystem e volume manager integrati, con possibilità di snapshot stile LVM e possibilità di replica anche incrementale.

Un'importantissima caratteristica di ZFS è che è un filesystem copy-on-write (COW): ciò significa che tutte le modifiche ai dati vengono scritte su disco in nuove allocazioni, lasciando integra su disco la precedente versione dei dati. Ciò è da tenere ben presente per un DBA poichè, col passare del tempo, i dati tendono a venire sparpagliati sul disco, aumentando quindi il tempo d'accesso specie per le letture di dati in sequenza; si pensi ad esempio all'utilità del db_file_multiblock_read_count di Oracle, anche se è ormai quasi obsoleto.

ZFS è anche un volume manager, ovvero può prendere in gestione più dischi e comporre zpool più grandi, in cui gestisce automaticamente lo striping; oppure può garantire livelli di tolleranza ai guasti come il mirroring e il RAIDZ, una modalità che somiglia molto al RAID5 o addirittura RAID6 con due dischi di "parità".

Il COW diventa meno importante man mano che il numero dei dischi a disposizione aumenta, poiché lo striping riduce naturalmente il raggruppamento dei dati in blocchi contigui su disco.

ZFS è un filesystem transazionale, ovvero le modifiche ai dati o avvengono in blocco o non avvengono; ciò, unito al COW, consente di fare a meno del journal, caratteristica di punta di filesystem avanzati come ReiserFS o ext3.

Con ZFS si possono anche fare snapshot consistenti istantanei dei filesystem, direttamente accessibili sul filesystem stesso, quindi copiabili e salvabili. Ciò rende possibile un hot-backup con il comando 10g ALTER DATABASE BEGIN BACKUP, come potrebbe avvenire con storage molto più costosi.

Da un punto di vista amministrativo ZFS è troppo semplice per essere vero: consente in pochi e semplicissimi comandi di sintetizzare numerose operazioni di amministrazione del sistema. E non solo, offre un supporto nativo a iSCSI e NFS senza toccare alcun file di configurazione.

Torniamo a BTRFS: le caratteristiche sul sito Wiki sembrano proprio ricalcare quelle di ZFS, che è anch'esso open-source. Ma allora perché non integrarlo nel kernel Linux?
Il problema sta nella licenza, che non è GPL (e sembra che Sun abbia fatto apposta).

La mia sensazione però è che alla fine non se ne faccia un granché, visto che ZFS è già in produzione avanzata sotto Solaris, mentre BTRFS è all'inizio dello sviluppo.

Tornerò sul tema ZFS per dirvi degli interessanti documenti in rete sui database e dei risultati delle mie prove.

14 agosto 2008

Oracle 11g su OpenSuSE 11

Visto che uno dei due dischi del mio megaserver di casa ha deciso, bontà sua, di rimettersi a funzionare, sono riuscito a fare vari esperimenti di cui vi parlerò, tra cui la prova di installazione di Oracle 11g su OpenSuSE 11.

L'installazione è andata molto bene e senza alcun errore, quindi la piattaforma preferenziale per le mie prossime installazioni di prova 11g sarà sicuramente OpenSuSE 11.

L'unica variazione nella configurazione, rispetto a quella di default, è l'aggiunta delle componenti di sviluppo C/C++ durante l'installazione, e un package libstdc++-devel che ho aggiunto io per precauzione.

Il vantaggio maggiore di utilizzare una distribuzione Linux non supportata da Oracle è di avere un sistema operativo completamente aggiornato, e in modo gratuito. Per noi che smanettiamo spesso con questo genere di software sarebbe bello che tutto ciò che dobbiamo installare sia liberamente utilizzabile per fini di ricerca.

12 luglio 2008

ASM a linea di comando

Ogni tanto arriva qualche e-mail dai lettori di questo blog. La maggior parte delle domande riguarda ASM, il volume manager/filesystem di Oracle dedicato all'immagazzinamento dei dati del suo motore RDBMS.

Forse non tutti sanno che ASM è accessibile anche da linea di comando con asmcmd.

asmcmd è sostanzialmente un prompt dei comandi che si collega all'istanza ASM attiva sulla $ORACLE_HOME impostata nell'ambiente, con il relativo $ORACLE_SID impostato a +ASM o +ASM<n> nel caso di RAC.

Eseguiamo asmcmd:
oracle@host:~> asmcmd -p
ASMCMD [+] >

Il flag -p permette di mostrare il path tra parentesi quadre.
I comandi disponibili sono: cd, du, find, help, ls, lsct, lsdg, mkalias, mkdir, pwd, rm, rmalias.
Per ogni comando si può avere l'help con help <comando>.
All'avvio troviamo l'elenco dei diskgroup montati dall'istanza ASM e in seguito i database montati in notazione OMF:
ASMCMD [+] > ls
DG1/
ASMCMD [+] > ls
ASMCMD [+] > cd DG1
ASMCMD [+DG1] > ls
DBTEST/
ASMCMD [+DG1] >

All'interno delle directory DB troveremo le sottodirectory in notazione OMF come su filesystem ordinario:
ASMCMD [+DG1/DBTEST] > ls
ARCHIVELOG/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileDBTEST.ora
ASMCMD [+DG1/DBTEST] >

Il comando du mostra l'utilizzo del diskgroup:
ASMCMD [+DG1/DBTEST] > du .  
Used_MB Mirror_used_MB
24529 24529

Il comando lsdg mostra tutti i diskgroup:
ASMCMD [+] > lsdg
State Type Rebal Unbal
Sector Block AU Total_MB
Free_MB Req_mir_free_MB Usable_file_MB
Offline_disks Name
MOUNTED EXTERN N N
512 4096 1048576 347430
322841 0 322841
0 DG1/


Contrariamente a un filesystem normale, esistono i comandi per creare e rimuovere alias mkalias e rmalias.
Gli alias in ASM sono file finti nel filesystem che puntano a file normali che risiedono sempre in ASM; possono essere visti come link simbolici. Ricordiamo infatti che ogni file in un volume ASM è un file OMF, ovvero è un file del database Oracle (controlfile, datafile, redo, e così via) con un nome scelto dal RDBMS, ma non un file ordinario. Per ogni file del db, quindi, esiste uno e un solo file OMF con il nome scelto da Oracle. Per avere file con nomi più leggibili o facili da ricordare si usano gli alias, a cui si può fare riferimento con i comandi DDL da prompt SQL al posto dei file OMF. Gli alias possono risiedere ovunque in ASM.

03 luglio 2008

Vantaggi del sampling dinamico

Ho recentemente letto un curioso aricolo di Tom Kyte su Oracle Magazine sul dynamic sampling e i suoi vantaggi rispetto alle statistiche usate dal cost-based optimizer (CBO) in alcuni casi particolari.

La prima parte dell'articolo parla di V$SQL ed è molto interessante. Qui mi occupo della seconda parte, ovvero della cardinalità nel piano di esecuzione a seconda che venga usato il CBO o il sampling dinamico.

Nell'articolo si fa l'esempio di una tabella in cui due colonne (X e Y) hanno 12 valori distinti distribuiti uniformemente tra le righe, per cui una query del tipo
SELECT * FROM T WHERE X=5;

restituisce 1/12 delle righe, mentre
SELECT * FROM T WHERE X=5 AND Y=6;

restituisce 1/144 delle righe (1/12 * 1/12).
O almeno, questa è la selettività che si ottiene dalle statistiche sulla tabella.

Ma che cosa accade se X=Y per ogni riga? Una query come
SELECT * FROM T WHERE X=5 AND Y=6;

ritornerebbe 0 righe, quindi la stima del CBO sarebbe errata (introducendo eventuali piani di esecuzione sballati).

Utilizzando il sampling dinamico:
select /*+ dynamic_sampling(t 3) */ * from t

si ottiene invece il costo corretto, molto più basso, e quindi anche il piano corretto.

Il sampling dinamico è regolabile a vari livelli, espressi dal numero che segue il nome della tabella nell'hint all'optimizer. Il valore minimo del sampling è 32 blocchi con n=1; per i livelli da 1 a 9 vale la formula 32*2^(l-1) blocchi dove l è il livello di sampling. Nel nostro caso di n=3 si ha un sampling di 4*32=128 blocchi, per cui quasi certamente l'optimizer ha letto l'intera tabella. Per questo motivo il costo finale è molto preciso. Se il livello è 0 non si ha sampling, se è 10 il sampling viene fatto su tutti i blocchi della tabella (quindi può risultare mooolto lento).

Il dynamic sampling è quindi una possibilità in più utilizzabile nei casi in cui il CBO ci fornisce piani di esecuzione subottimali.

OCA 10g solo con esame SQL

Dal 28 novembre 2008 verrà richiesto un esame SQL per ottenere la certificazione OCA 10g, contrariamente a quanto è successo fin'ora, da quando fu introdotta la certificazione di base 10g.

Vedo con piacere che il buon vecchio 1Z0-007 (SQL 9i) torna in auge.

23 giugno 2008

Analytics - n.1: NTILE

Con questo post inauguro la serie "Analytics", ovvero come utilizzare le potenti funzioni analitiche di Oracle 10g.

NTILE è una funzione che permette di ottenere il numero corrente di partizione dei dati, basandosi su un criterio di partizione determinato nella query.

Facciamo subito un esempio: ho una tabella che contiene i tempi di arrivo di una maratona con tanti partecipanti, diciamo 5000. Di questi, 1000 sono arrivati al traguardo, mentre gli altri si sono persi per strada.
La tabella contiene un ID univoco del corridore e il suo tempo di arrivo.

Supponiamo che io voglia dividere l'insieme degli "arrivati" in n gruppi omogenei, cioè aventi lo stesso numero di componenti.
Gli ID, anche se generati con una sequenza, sono distribuiti casualmente, dato che solo 1/5 dei corridori è arrivato alla fine; lo stesso vale per i tempi di arrivo.

La funzione NTILE ci permette di dividere il nostro set di dati in partizioni uguali, assegnando ad ogni riga il numero di partizione in cui si trova. Il criterio per trovarsi in una partizione o in un'altra è il tempo di arrivo in ordine crescente.

Dividiamoli ad esempio in 10 gruppi:
SQL> select * from marathon;

RUNNER_ID ARRIVAL_TIME
---------- -------------------
93 2008-06-19 13:11:39
3144 2008-06-19 12:44:10
4450 2008-06-19 12:54:54
453 2008-06-19 12:52:58
808 2008-06-19 12:48:00
4036 2008-06-19 13:11:39
4048 2008-06-19 13:06:48
660 2008-06-19 12:51:40
4091 2008-06-19 13:12:38
591 2008-06-19 13:02:55
[...]

Con la query seguente otteniamo la divisione in gruppi:
SQL> select runner_id, arrival_time, ntile(10) over (order by arrival_time) nt from marathon;

Come si può vedere, ci sono esattamente 100 corridori in ogni gruppo:
SQL> select nt, count(*) from
(select runner_id, arrival_time,
ntile(10) over (order by arrival_time) nt from marathon)
group by nt order by nt;

NT COUNT(*)
---------- ----------
1 100
2 100
3 100
4 100
5 100
6 100
7 100
8 100
9 100
10 100

10 rows selected.

Possiamo anche calcolare l'ntile su una partizione dei dati originari: immaginiamo per esempio che ai corridori siano state assegnate delle pettorine colorate di rosso se il loro id era tra 1 e 1000, verde se tra 1001 e 2000, blu se maggiore di 2000, divisi quindi per categoria.
Una query come la seguente divide in dieci sottogruppi ogni categoria, basandosi sempre sul tempo di arrivo:
SQL> select runner_id, arrival_time, color,
ntile(10) over (partition by color order by arrival_time) from marathon;

02 giugno 2008

ROWSCN, una soluzione ai lost update

Per le applicazioni web-based l'aggiornamento di una serie di record nel database presenta problemi diversi da quelli delle applicazioni tradizionali client-server, che di solito utilizzano il metodo "SELECT ... FOR UPDATE".

Facciamo un esempio: due utenti di una webapp caricano una form di attributi di una stessa persona (identificata solamente da una chiave primaria) e li modificano nello stesso intervallo di tempo. Trascurando l'opportunità della cosa, l'ultima che avrà cliccato su "aggiorna" scriverà i dati della form nel database, sovrascrivendo eventuali modifiche fatte dal primo utente.

Ci sono un paio di metodi per accorgersi questa situazione: fare la "SELECT FOR UPDATE" appena prima di modificare il record inserendo una condizione WHERE che contiene tutti i dati originali, oppure usare una colonna timestamp per sapere la data di ultima modifica.
Non ci interessa invece che cosa fa l'applicazione una volta accortasi della cosa: ciò dipende dalla business logic, ovvero dal tipo di applicazione.

In Oracle 10g è presente una nuova pseudo-colonna: ORA_ROWSCN. Essa contiene l'SCN relativo all'ultima operazione DML effettuata sul blocco che contiene la riga selezionata. In questo modo è possibile sapere se il blocco è stato modificato dall'ultima lettura effettuata; ma come si fa ad utilizzare a livello di riga? La dipendenza a livello di riga viene abilitata all'atto di creazione di una tabella con la clausola ROWDEPENDENCIES.

Facciamo un esempio:
SQL> select CLAIM_NUM, CLAIM_AMT, CLAIM_BAL, ROWID, ORA_ROWSCN
from claim;

CLAIM_NUM CLAIM_AMT CLAIM_BAL ROWID ORA_ROWSCN
---------- ---------- ---------- ------------------ ----------
1 100 0 AAAdsYAAEAAAAD0AAA 1070257362
2 200 99 AAAdsYAAEAAAAD0AAB 1070257362
3 300 0 AAAdsYAAEAAAAD0AAC 1070257362
4 400 350 AAAdsYAAEAAAAD0AAD 1070257362
5 500 0 AAAdsYAAEAAAAD0AAE 1070257362
6 600 295 AAAdsYAAEAAAAD0AAF 1070257362
7 700 700 AAAdsYAAEAAAAD0AAG 1070257362
8 800 800 AAAdsYAAEAAAAD0AAH 1070257362

8 rows selected.

SQL> update claim set CLAIM_BAL = 1 where claim_num = 1;

1 row updated.

SQL> select CLAIM_NUM, CLAIM_AMT, CLAIM_BAL, ROWID, ORA_ROWSCN
from claim;

CLAIM_NUM CLAIM_AMT CLAIM_BAL ROWID ORA_ROWSCN
---------- ---------- ---------- ------------------ ----------
1 100 1 AAAdsYAAEAAAAD0AAA 1070257362
2 200 99 AAAdsYAAEAAAAD0AAB 1070257362
3 300 0 AAAdsYAAEAAAAD0AAC 1070257362
4 400 350 AAAdsYAAEAAAAD0AAD 1070257362
5 500 0 AAAdsYAAEAAAAD0AAE 1070257362
6 600 295 AAAdsYAAEAAAAD0AAF 1070257362
7 700 700 AAAdsYAAEAAAAD0AAG 1070257362
8 800 800 AAAdsYAAEAAAAD0AAH 1070257362

8 rows selected.

SQL> commit;

Commit complete.

SQL> select CLAIM_NUM, CLAIM_AMT, CLAIM_BAL, ROWID, ORA_ROWSCN
from claim;

CLAIM_NUM CLAIM_AMT CLAIM_BAL ROWID ORA_ROWSCN
---------- ---------- ---------- ------------------ ----------
1 100 1 AAAdsYAAEAAAAD0AAA 1201770241
2 200 99 AAAdsYAAEAAAAD0AAB 1201770241
3 300 0 AAAdsYAAEAAAAD0AAC 1201770241
4 400 350 AAAdsYAAEAAAAD0AAD 1201770241
5 500 0 AAAdsYAAEAAAAD0AAE 1201770241
6 600 295 AAAdsYAAEAAAAD0AAF 1201770241
7 700 700 AAAdsYAAEAAAAD0AAG 1201770241
8 800 800 AAAdsYAAEAAAAD0AAH 1201770241

8 rows selected.

SQL> create table claim_rowdep ROWDEPENDENCIES
as select * from claim;

Table created.

SQL> select CLAIM_NUM, CLAIM_AMT, CLAIM_BAL, ROWID, ORA_ROWSCN
from claim_rowdep;

CLAIM_NUM CLAIM_AMT CLAIM_BAL ROWID ORA_ROWSCN
---------- ---------- ---------- ------------------ ----------
1 100 1 AAAlQ9AAEAAAAIEAAA 1201772166
2 200 99 AAAlQ9AAEAAAAIEAAB 1201772166
3 300 0 AAAlQ9AAEAAAAIEAAC 1201772166
4 400 350 AAAlQ9AAEAAAAIEAAD 1201772166
5 500 0 AAAlQ9AAEAAAAIEAAE 1201772166
6 600 295 AAAlQ9AAEAAAAIEAAF 1201772166
7 700 700 AAAlQ9AAEAAAAIEAAG 1201772166
8 800 800 AAAlQ9AAEAAAAIEAAH 1201772166

8 rows selected.

SQL> update claim_rowdep set claim_bal = 0 where claim_num = 1;

1 row updated.

SQL> select CLAIM_NUM, CLAIM_AMT, CLAIM_BAL, ROWID, ORA_ROWSCN
from claim_rowdep;

CLAIM_NUM CLAIM_AMT CLAIM_BAL ROWID ORA_ROWSCN
---------- ---------- ---------- ------------------ ----------
1 100 0 AAAlQ9AAEAAAAIEAAA
2 200 99 AAAlQ9AAEAAAAIEAAB 1201772166
3 300 0 AAAlQ9AAEAAAAIEAAC 1201772166
4 400 350 AAAlQ9AAEAAAAIEAAD 1201772166
5 500 0 AAAlQ9AAEAAAAIEAAE 1201772166
6 600 295 AAAlQ9AAEAAAAIEAAF 1201772166
7 700 700 AAAlQ9AAEAAAAIEAAG 1201772166
8 800 800 AAAlQ9AAEAAAAIEAAH 1201772166

8 rows selected.

SQL> commit;

Commit complete.

SQL> select CLAIM_NUM, CLAIM_AMT, CLAIM_BAL, ROWID, ORA_ROWSCN
from claim_rowdep;

CLAIM_NUM CLAIM_AMT CLAIM_BAL ROWID ORA_ROWSCN
---------- ---------- ---------- ------------------ ----------
1 100 0 AAAlQ9AAEAAAAIEAAA 1201772868
2 200 99 AAAlQ9AAEAAAAIEAAB 1201772166
3 300 0 AAAlQ9AAEAAAAIEAAC 1201772166
4 400 350 AAAlQ9AAEAAAAIEAAD 1201772166
5 500 0 AAAlQ9AAEAAAAIEAAE 1201772166
6 600 295 AAAlQ9AAEAAAAIEAAF 1201772166
7 700 700 AAAlQ9AAEAAAAIEAAG 1201772166
8 800 800 AAAlQ9AAEAAAAIEAAH 1201772166

8 rows selected.


Come si vede dal ROWID i dati stanno tutti nello stesso blocco, mentre il ROWSCN cambia per tutto il blocco nella tabella claim e solo per la riga interessata dall'update nella tabella claim_rowdep.

Non è possibile aggiungere ROWDEPENDENCIES alla tabella già creata, bisogna farlo alla creazione: la clausola infatti comporta che ogni riga aumenti le proprie dimensioni di 6 bytes.

26 maggio 2008

Tornato da USA

Sono tornato domenica da una trasferta di lavoro in USA, per conoscere i miei nuovi colleghi d'oltreoceano e fare un po' di team-building con il mio omologo americano dell'azienda che abbiamo comprato.

Probabilmente per lavoro avrò a che fare, prima o poi, anche col mondo SQL Server, di cui ho visto qualche utilizzo. Per ora non so che cosa dire, il mondo dei wizard mi è sconosciuto, ma ammetto che la comodità di impostare ad esempio un backup solo con il mouse e con un'ottima interfaccia grafica non è trascurabile. Manca solo SQL Server per Linux :-)

Una giornata l'ho spesa a spiegare al mio collega USA come funziona RMAN, con una dimostrazione pratica di recovery. I suoi occhi brillavano, così come quando gli ho mostrato Data Pump con l'esportazione del "suo" database in 10 minuti, quando con export normale ci metteva 1 ora e mezza (ma non ho visto lo script che usava).

Mentre ero via è arrivato intanto il kit Oracle del certificato OCP 10g, con la tesserina e il diploma. Sono soddisfazioni, anche se preferirei avere anche una maglietta e un set di adesivi per la moto :-)

Oracle con una mail strana mi invita all'OpenWorld 2008 a S. Francisco. Ho qualche mese di tempo per convincere il mio capo della "necessità" della mia visita a S. Francisco, chissà forse in concomitanza con un altro viaggio in USA, ma si sa noi personaggi dell'IT non viaggiamo molto come i commerciali. Anzi praticamente non viaggiamo mai.

Una novità sulle certificazioni Oracle: Oracle Certification E-Magazine.
Le novità sono molteplici:
  • L'OCA 11g viene conseguita con 2 esami e non più uno come 10g
  • L'OCP 11g può essere conseguito come upgrade da 10g OCP ma anche da 9i OCP
  • C'è una nuova certificazione per il database: SQL certified expert

04 maggio 2008

Cost-based Oracle cap. 2: le statistiche di sistema

Sto leggendo il libro "Cost-based Oracle" di Jonathan Lewis.
Pensavo di dedicare un post ad ogni capitolo, ma non riesco a riportare tutte le cose interessanti che leggo; più realisticamente proverò a commentare i passi più importanti.

Oggi vi volevo parlare delle statistiche di sistema Oracle 10g. Non intendo le statistiche sugli oggetti di SYS e compari, e nemmeno le statistiche sulle fixed tables, ma su un gruppo di misure che l'optimizer può utilizzare per il suo lavoro.
Tra queste sono comprese la velocità della CPU, il tempo di lettura a singoli blocchi, il tempo di lettura multiblocco.
I valori ricavati dalle misure vengono inseriti nella tabella SYS.AUX_STATS$, che tra l'altro non ha sinonimi.

La tabella SYS.AUX_STATS$ si presenta più o meno così, dopo la creazione di un database:
SQL> select SNAME, PNAME, PVAL1 from sys.aux_stats$ where SNAME = 'SYSSTATS_MAIN';

SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
SYSSTATS_MAIN CPUSPEEDNW 1116.77078
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR

9 rows selected.

Si può notare come i valori di *READTIM e MAXTHR, tra i più interessanti, non siano valorizzati; inoltre i valori di IOSEEKTIM e IOTFRSPEED sono sempre uguali, anche per altri database non RAC.

Questa tabella viene popolata dalla procedura DBMS_STATS.GATHER_SYSTEM_STATS(). Può lavorare in due gathering_mode: NOWORKLOAD e a intervalli.
Nel primo caso la procedura misura le performance di I/O del sistema, determinando i valori di cpuspeednw, ioseektim, iotfrspeed.
Nel secondo caso si può far partire la misura con START e fermarla con STOP, scegliendo così un periodo di carico di lavoro tipico, altrimenti è possibile specificare INTERVAL con l'intervallo, misurato in minuti, trascorso il quale la misura viene terminata.

Alla fine delle misure l'aspetto potrebbe essere più o meno questo:
SQL> select SNAME, PNAME, PVAL1 from sys.aux_stats$ where SNAME = 'SYSSTATS_MAIN';

SNAME PNAME PVAL1
------------------------------ ------------------------------ ----------
SYSSTATS_MAIN CPUSPEEDNW 1606.781
SYSSTATS_MAIN IOSEEKTIM 7.469
SYSSTATS_MAIN IOTFRSPEED 52290.723
SYSSTATS_MAIN SREADTIM 5.912
SYSSTATS_MAIN MREADTIM 2.651
SYSSTATS_MAIN CPUSPEED 1614
SYSSTATS_MAIN MBRC 10
SYSSTATS_MAIN MAXTHR 105472
SYSSTATS_MAIN SLAVETHR

9 rows selected.


A che cosa serve tutto ciò? Le statistiche di sistema vengono utilizzate dal cost-based optimizer per determinare il miglior piano di esecuzione basandosi anche sul costo della CPU e della modalità di I/O (signolo blocco o multiblocco).

Manca ancora il costo di lettura dalla cache, ovvero la stima del costo basata sulla probabilità che un determinato blocco si ritrovi già in cache; ciò dipende soprattutto dalle statistiche derivabili da V$SEGSTAT, che misura l'attività relativa ai segmenti (dati e indici scritti su disco). Dovrò fare delle prove su 11g per vedere se ciò è stato implementato.

16 aprile 2008

Ruoli delle istanze nei cluster RAC

Continuiamo la serie dei post sul RAC a due nodi.

Stavolta mi è capitato di provare il parametro ACTIVE_INSTANCE_COUNT.

Con il RAC a due nodi è possibile specificare l'esistenza di una istanza primaria e di un'istanza secondaria.
Impostando il parametro ACTIVE_INSTANCE_COUNT a 1, solo nell'spfile, si impone che, al riavvio successivo del database, la prima istanza che monta il database diventi l'istanza primaria, mentre l'altra diventi ovviamente la secondaria.
Si può controllare lo stato delle istanze dalla colonna INSTANCE_ROLE di GV$INSTANCE, che assumerà i valori PRIMARY_INSTANCE e SECONDARY_INSTANCE.

Il risultato è che tutte le connessioni verranno dirette verso l'istanza primaria, mentre la seconda continuerà a rifiutare connessioni finché la prima istanza rimarrà attiva.

Anche i servizi RAC vengono avviati sull'istanza primaria, come se la secondaria fosse spenta.

Qual è il vantaggio? Non si trova molto sui manuali o su internet, comunque da questo articolo si deduce che il failover delle connessioni può essere più veloce.
Ho fatto una prova, ma personalmente non lo userei mai.

12 aprile 2008

1Z0-040: missione compiuta

Ieri ho aggiornato la mia certificazione Oracle OCP, passando l'esame 1Z0-040 e diventando OCP 10g.

L'esame non è dei più semplici per un paio di motivi: è meno popolare degli altri esami e deve concentrarsi sugli argomenti che costituiscono le novità della nuova release rispetto alla vecchia.
Il primo motivo ha come conseguenza che è più difficile reperire materiale di studio; il secondo che generalmente le domande riguardano aspetti minori del funzionamento del database, nonché poco interessanti e a volte addirittura noiosi.
Le domande a volte sono mal formulate, prestandosi a diverse interpretazioni, e a volte sembra che chi le ha formulate non aveva ben chiaro il concetto.
Come conseguenza di tutto ciò, mi sono trovato davanti a molte domande inaspettate ma, preso il toro per le corna, ragionando e sfruttando bene tutto il tempo a disposizione, ne sono uscito vincitore.

Per quanto riguarda il materiale di studio, mi sono affidato per la prima volta ad un testo Sybex che, sebbene più preciso dei testi Oracle Press per gli esami, latita dal punto di vista del software per la preparazione.
Il CD-ROM allegato al libro contiene una versione PC e una versione palmare delle esercitazioni. La prima è semplicemente in Flash, quindi fruibile con tutti i dispositivi dove ci sia un browser che supporti Flash, mentre sulla confezione del CD c'è scritto "per Windows". Misteri.

I testi Oracle Press, invece, avevano un software apposito per Windows, che comprensibilmente permetteva di fare di più rispetto a un'applicazione flash, sfruttando il fatto che poteva salvare su disco i risultati delle esercitazioni e indicare chiaramente quali fossero gli argomenti su cui insistere con lo studio, nonché mischiare le domande e le risposte per evitare che si memorizzassero meccanicamente.

La versione per palmare della Sybex è ancora più limitata di quella per PC, visto che si tratta solo di una serie di "schede" da compilare o a cui rispondere mentalmente, corredate da un tasto per far comparire la soluzione. Diciamo che si adattano al contesto in cui di solito viene usato il palmare, ovvero "on the road"; sono comunque un valido aiuto.

Ora pausa di qualche mese per finire i libri in coda, e poi chissà, ci sono molti esami interessanti da sostenere!

27 marzo 2008

OpenSuSE 10.3 x86_64: gli rpm per Oracle 10g

Tra tutte le installazioni Oracle 10g che ho fatto, escludendo Red Hat 4, mi sembra che la migliore distribuzione a cui appoggiarsi sia OpenSuSE 10.3.
L'unico problema che si potrebbe avere è nella versione a 32 bit, che probabilmente richiede la ricompilazione del kernel. Con le macchine attuali x86_64 il problema non si pone.

Riporto qui la lista degli rpm da installare oltre a quelli di default da DVD OpenSuSE 10.3 a 64 bit:

gcc-32bit-4.2-24.x86_64.rpm
gcc42-32bit-4.2.1_20070724-17.x86_64.rpm
glibc-32bit-2.6.1-18.x86_64.rpm
glibc-devel-32bit-2.6.1-18.x86_64.rpm
libgcc42-32bit-4.2.1_20070724-17.x86_64.rpm
libgomp42-32bit-4.2.1_20070724-17.x86_64.rpm
libmudflap42-32bit-4.2.1_20070724-17.x86_64.rpm


Nell'installazione grafica normale, alla selezione dei pacchetti da DVD, selezionare anche il Development e C/C++ development.
I pacchetti dell'elenco di sopra sono reperibili solo da repository web, quindi o si aggiunge l'URL del repository al SuSE online update, oppure si scaricano manualmente e si installano da linea di comando.
Vedere il sito SuSE per i mirror.

19 marzo 2008

RAC cluster interconnect: twisted!

Una delle "limitazioni" più famose delle implementazioni Oracle RAC a due nodi è l'impossibilità, secondo la documentazione, di utilizzare un'interconnect formata da un solo cavo diretto tra un server e l'altro.
Le FAQ di RAC riportano:
NO. CROSS OVER CABLES ARE NOT SUPPORTED.
The requirement is to use a switch:
Detailed Reasons:
1) cross-cabling limits the expansion of RAC to two nodes
2) cross-cabling is unstable:
a) Some NIC cards do not work properly with it. They are not able to negotiate the DTE/DCE clocking, and will thus not function. These NICS were made cheaper by assuming that the switch was going to have the clock. Unfortunately there is no way to know which NICs do not have that clock.
b) Media sense behaviour on various OS's (most notably Windows) will bring a NIC down when a cable is disconnected.
Either of these issues can lead to cluster instability and lead to ORA-29740 errors (node evictions).

Due to the benefits and stability provided by a switch, and their afforability ($200 for a simple 16 port GigE switch), and the expense and time related to dealing with issues when one does not exist, this is the only supported configuration.
Please see certify.us.oracle.com as well.

Vorrei però precisare quanto segue:
  1. Per gli switch moderni a 1 Gb i cavi "cross" sono equivalenti a quelli normali; a me risulta che siano equivalenti anche a 100 Mb (verificato sul mio router di casa).
  2. L'instabilità non è giustificata, a meno che si pensi ai SO Microsoft e ad eventuali versioni Linux che spengono la scheda di rete in caso di inattività prolungata o assenza di segnale all'altro capo in fase di avvio.
  3. Per il funzionamento effettivo del cross-link, basta provare e vedere se funziona
Aggiungiamo che le schede di rete recenti (es. e1000) hanno tutte le migliori caratteristiche desiderabili, e soprattutto possono essere configurate in bonding.

Gli scenari da confrontare sarebbero due:
  1. I 2 nodi collegati a uno switch
  2. I due nodi collegati tra loro
Gli oggetti che si possono rompere, nel caso del "sistema interconnect", sono:
  1. lo switch
  2. i due cavi di rete
  3. le quattro schede di rete
I cavi di rete di per sè sono molto poco propensi a rompersi, visto che strutturalmente sono molto semplici; eventualmente potrebbero essere soggetti a sollecitazioni meccaniche esterne. Sono due, quindi devono rompersi entrambi nello stesso periodo per dare problemi.
Le schede di rete hanno più o meno la stessa probabilità di rompersi di qualsiasi altro elemento attivo. Sono due per server, quindi la ridondanza abbassa di molto la probabilità di disservizio.
Lo switch è unico. Per avere una certa affidabilità e velocità bisogna acquistare qualcosa di più di "$200 for a simple 16 port GigE switch"; qualcosa di molto costoso rispetto ai server. Sta di fatto che è un elemento unico; se si vogliono due switch i costi raddoppiano.

Veniamo ora agli scenari problematici:
  1. Shutdown di una delle due istanze, più o meno volontario
  2. Reset o shutdown software di un server
  3. Spegnimento togliendo la corrente
Le istanze RAC comunicano via interconnect e voting disk. Un reset o uno spegnimento di un nodo sarebbe facilmente risolto dal voting-disk, in teoria. Tra le configurazioni con e senza switch di mezzo c'è una differenza: la mancanza di alimentazione a uno dei capi dell'interconnect, ad esempio per spegnimento di un server, provoca l'errore di link down.
Consideriamo solo il caso senza switch: gli scenari peggiori quindi sono l'interruzione totale e improvvisa dell'alimentazione per un nodo, e il distacco di entrambi i cavi di rete.
La prima possibilità è realistica, e provoca il link down. La seconda possibilità è, consentitemi, praticamente irrealizzabile.

Pensiamoci un attimo: la differenza col caso switch è che in mezzo c'è un altro componente che potenzialmente può dare problemi. La cosa migliore è quindi che lo switch non sia presente! Con un solo switch in mezzo, in caso di spegnimento dello switch stesso per qualsiasi motivo, si realizzerebbe la condizione per cui i nodi non si sentono più via interconnect, hanno un link down, e le istanze sono entrambe nello stesso stato: è la situazione di gran lunga peggiore: quella del distacco dei due cavi di rete dell'interconnect. La configurazione con i cavi diretti è quindi, a mio modo di vedere, addirittura migliore di quella consigliata da Oracle senza appello.

Rimane da provare l'interruzione improvvisa dell'alimentazione per un nodo.
L'ho provata.
In questo caso le istanze non sono nello stesso stato: una non c'è più.
Ecco ciò che succede all'altra istanza:
Tue Mar 11 15:01:42 2008
ospid 1179: network interface with IP address 192.168.1.107 no longer running (check cable)
Tue Mar 11 15:01:52 2008
Reconfiguration started (old inc 2, new inc 4)
List of nodes:
0
Global Resource Directory frozen
* dead instance detected - domain 0 invalid = TRUE
...

Il link down viene rilevato. Tutto prosegue regolarmente. In caso di dubbio Clusterware avrebbe riavviato la macchina. Quindi tutto bene.
Gli indirizzi IP VIP vengono correttamente riconfigurati.

Al ripristino del nodo in failure si ottiene:
Tue Mar 11 15:12:42 2008
ospid 1179: network interface with IP address 192.168.1.107 is now running
Tue Mar 11 15:15:12 2008
Reconfiguration started (old inc 4, new inc 6)
List of nodes:
0 1
Global Resource Directory frozen
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
...

Ottimo: link up e riconfigurazione del cluster. Tutto regolare.

In sintesi, per un sistema RAC a due nodi non espandibile, a quanto sembra, è molto meglio avere un'interconnect a cavi diretti piuttosto che avere di mezzo un apparato di rete.

05 marzo 2008

Restore in ambiente RAC

Per lavoro ho dovuto spostare un database RAC di test da un volume ASM ad un altro. Tralasciando i motivi, ho deciso di effettuare un restore completo della parte dati ASM a partire da un backup RMAN con catalogo.
Noto alcune cose interessanti:
  • Nonostate il catalogo, bisogna sempre impostare il DBID prima di tutto
  • È necessario ricreare la directory base sotto +<DISKGROUP>
  • Se proprio non si ha il pfile creato a mano dall'spfile, si può usare lo startup force nomount da RMAN, per avere un minimo server process in modo da accedere ad ASM e fare il restore dell'spfile.
  • Il restore dall'autobackup è molto comodo: si recupera sia il controlfile che l'spfile (separatamente)
  • Il restore dell'spfile va fatto specificando il filename di destinazione nel diskgroup condiviso (TO '...')
Ho notato anche due difetti di Enterprise Manager db control una volta che il database RAC è tornato a funzionare: per prima cosa la configurazione interattiva con emca non funziona, almeno nel mio caso di ASM home separata. Bisogna per forza usare la linea di comando di emca ricavandola da uno degli script di creazione del database fatti da dbca (consiglio vivamente di far creare sempre gli script di creazione del database da dbca).
Secondo difetto: nelle pagine EM su ASM, lo spazio utilizzato dal database viene visto come "internal" (tipo archivelog) e non come spazio allocato da un database. Questo aspetto è tradizionalmente difettoso, almeno in RAC: i nomi dei database che utilizzano un dato diskgroup sono a volte solamente quelli di una delle due istanze; in questo caso, nemmeno quello.

25 febbraio 2008

Oracle 10.2.0.4.0/x86 is out

Un veloce post perché stamattina ho trovato il patchlevel 3 di 10gR2 su Metalink, solo per x86, e lo sto scaricando.
A presto per eventuali commenti.

Aggiornamento (10:33): a quanto pare è stato fatto il backport di Real Application Testing da 11g a 10g.

Aggiornamento (11:24): Enterprise Manager Database Control ora è di default via HTTPS!

Aggiornamento (10-3): risolto anche il problema della tabella ROUT troppo grande nel catalogo di RMAN.

27 gennaio 2008

11g Real Application Testing spiegato

Segnalo un interessante articolo di Arup Nanda sull'ultimo Oracle Magazine, a proposito di Real Application Testing e i suoi meccanismi.
Molto più chiaro delle decine di documenti marketing letti fin'ora, l'articolo di Nanda mette alla luce le procedure necessarie per replicare il carico reale su un server di test, facendo capire quali sono gli oggetti fisici in gioco, quindi in ultima analisi come funziona Real Application Testing.

18 gennaio 2008

Bind peeking e piani di esecuzione: DTY

In questi giorni uno sviluppatore mi ha sottoposto una query abbastanza semplice, che però veniva eseguita troppo lentamente dalla nuova versione della nostra webapp.
Ho subito cercato di replicare il comportamento della query via sqlplus, anche con le variabili di bind, e ho visto che effettivamente la query era lenta.
Dopo avere controllato rifatto le statistiche (macchina di test), tutto è tornato normale e la query era velocissima. Da un rapido esame dell'autotrace era evidente che il piano soffriva di statistiche sballate, ma ciò era nelle attese sul database di test, in cui le tabelle vengono caricate e scaricate continuamente.

La situazione per il thin client Java invece non migliorava, anzi a tratti peggiorava.
Ho fatto attivare il trace dalla sessione Java, ma simpaticamente proprio il piano di esecuzione che mi interessava non ha fatto a tempo a finire nel file di trace; pazienza, tanto già sapevo che era sballato e che un piano corretto esisteva (quello scelto via sqlplus e visto con autotrace), l'importante era capire perché. I dati di consistent gets/query, nel trace, erano completamente diversi. La query via Java muoveva 1000 volte la quantità di dati mossa da sqlplus, con le stesse 807 righe risultanti.

Mi sono ricordato di un post di Cristian Cudizio sul peeking, nel quale veniva citato sia il metodo di Alberto Dell'Era per estrarre le variabili di bind usando la feature non documentata dbms_xplan.display_cursor(null, null, 'ADVANCED'), sia un post di Jonathan Lewis in cui ho rapidamente identificato la stessa mia situazione.

Subito ho tentato di vedere le variabili di bind con il metodo di Alberto, ma serve almeno l'accesso alla V$SESSION, e non avevo molta intenzione di indagare, visto che, a mio modo di vedere, avrebbe dovuto funzionare con gli stessi privilegi necessari per l'autotrace.
Ho utilizzato invece il trace 10g via DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id IN BINARY_INTEGER DEFAULT NULL, serial_num IN BINARY_INTEGER DEFAULT NULL, waits IN BOOLEAN DEFAULT TRUE, binds IN BOOLEAN DEFAULT FALSE), applicandola solamente alle sessioni del pool di connessioni esclusivo dello sviluppatore in questione.
Per ogni sessione del pool ho avviato il trace con
DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => sid, binds => true). Non serve infatti il serial#.

È risultato che sqlplus eseguiva la query velocemente perché io correttamente impostavo il tipo delle variabili di bind a char(5), mentre il thin client Java finiva per impostare il tipo a varchar2.

Dove sono disponibili queste informazioni? Nel file di trace era presente la sezione delle variabili di bind, e per tutte era indicato, oltre il valore, una serie di dati tra cui
oacdty=01
che contraddistingue il tipo varchar (DTY = data type), mentre il tipo di sqlplus era char/nchar (96).

Vi risparmio il piano di esecuzione sbagliato, quasi comico.
Ho potuto vagliare comunque, in seguito, la bontà del SQL tuning advisor di Enterprise Manager, che con una comprehensive analysis mi ha fornito sia il piano sbagliato che quello corretto, proponendomi però di applicare un SQL profile alla query, invece che risolvere correttamente il problema.

16 gennaio 2008

Lentezze in RMAN

Oracle 10g ha problemi residui di lentezza di RMAN, ad esempio durante la sincronizzazione del catalogo. Metalink riporta i workaround principali a nella nota 463227.1.
Un ottimo punto di partenza è l'analisi delle performance da parte di Enterprise Manager sul database del catalogo. Si possono identificare problemi di indici già dall'esame sui picchi di attività.

Le cose principali da controllare sono:
  1. il numero dei record nella tabella ROUT e nella RSR

  2. la presenza di indici su ROUT e RSR

Devono essere presenti gli indici rout_i_db on rout(db_key) e rout_i_rsr on rout(rsr_key).

Anche sulla tabella RSR può essere utile l'indice rsr_i_stamp on rsr(rsr_sstamp, rsr_srecid).

Se collegate al repository di RMAN una versione aggiornata di Oracle, potete sempre fare un'UPGRADE CATALOG per assicurarvi che le modifiche indicate vengano automaticamente incluse, se necessario.

09 gennaio 2008

La saga di DBCA e ASM

Dopo decine di installazioni Oracle questo strano problema di DBCA che non riconosce l'istanza ASM presente e funzionante sul server forse è giunto alla sua soluzione.

Il problema è questo: durante la creazione di un database senza la creazione contestuale dell'istanza ASM (specie RAC, nel mio caso), oppure (più probabile) con ASM già configurata in un'altra ORACLE_HOME, DBCA visualizza il seguente errore:
DBCA could not startup the ASM instance configured on this node. To proceed with ASM diskgroup management you need the ASM instance to be up and running. Do you want to recreate the ASM instance on this node?

Oracle, nella nota Metalink 467756.1, afferma che il problema riguarda la versione 10.2.0.3.0; io confermo, ma accade a volte anche con la 10.2.0.1.0. La soluzione però non è valida, almeno per RAC: la nota dice di aggiungere l'istanza ASM a /etc/oratab, ma nel mio caso la entry apposita già c'era e su tutti e due i nodi del cluster.
Qualsiasi tipo di combinazione e configurazione delle variabili d'ambiente non aveva alcun effetto.

La soluzione (tenetevi forte) sembra proprio la seguente:
eseguire DBCA in questo modo:
#>cd $ORACLE_HOME/bin
#>./dbca

e non tramite path!

D'oh!

07 gennaio 2008

Homepage di EM lenta

In questi giorni sono incappato in una strana lentezza della homepage di Enterprise Manager (database control). Tutte le altre pagine venivano visualizzate con la solita velocità (abbastanza lente), ma quella principale ci metteva fino a mezzo minuto (!).

Ebbene, è risultato che lo statement incriminato era una semplice select da una view di sistema, precisamente V$RMAN_BACKUP_JOB_DETAILS, che ci metteva fino a 30 secondi per restituire una risultato; per inciso, la view conteneva una dozzina di righe.

Bene.

Andando a vedere il testo della view, si scopre che si basa su un'altra view, V$RMAN_BACKUP_SUBOBJ_DETAILS, la quale si basa su un'altra view, V$RMAN_STATUS, la quale si basa finalmente su 3 fixed table.

Da una ricerca sui forum Oracle, ho intuito che la lentezza è dovuta alla presenza delle statistiche sulle 3 fixed table.

È bastato quindi eseguire:
dbms_stats.delete_table_stats('SYS', 'X$KRBMRST')
dbms_stats.delete_table_stats('SYS', 'X$KSFQP')
dbms_stats.delete_table_stats('SYS', 'X$KCCRSR')

per riportare la situazione ad una velocità normale (a dire il vero piuttosto veloce).

Per essere sicuri che non vengano prese in futuro le statistiche su quelle fixed table, si può usare
dbms_stats.lock_table_stats('SYS', 'X$KRBMRST')
e così via.

Un caso in cui le statistiche è meglio non averle.

Nota Metalink: 420200.1