Sulle tracce di Highload++ Siberia 2019 — 8 problemi su Oracle

Ciao!

Il 24 e 25 giugno a Novosibirsk si è tenuta la conferenza Highload++ Siberia 2019. Anche il nostro team era presente. relazione «Database container Oracle (CDB/PDB) e il loro utilizzo pratico per lo sviluppo di software», pubblicheremo la versione testuale un po' più tardi. È stato fantastico, grazie. olegbunin per l'organizzazione e a tutti coloro che sono intervenuti.

In questo post vorremmo condividere con voi le domande che erano al nostro stand, in modo che possiate testare le vostre conoscenze in Oracle. Sotto — 8 domande, opzioni di risposta e spiegazione.

Qual è il valore massimo della sequenza che vedremo come risultato dell'esecuzione del seguente script?

create sequence s start with 1;
 
select s.currval, s.nextval, s.currval, s.nextval, s.currval
from dual
connect by level <= 5;

• 1
• 5
• 10
• 25
• Nessuno, ci sarà un errore.

RispostaSecondo la documentazione Oracle (citata da 8.1.6):
All'interno di una singola istruzione SQL, Oracle incrementerà la sequenza solo una volta per riga. Se un'istruzione contiene più di un riferimento a NEXTVAL per una sequenza, Oracle incrementa la sequenza una sola volta e restituisce lo stesso valore per tutte le occorrenze di NEXTVAL. Se un'istruzione contiene riferimenti sia a CURRVAL che a NEXTVAL, Oracle incrementa la sequenza e restituisce lo stesso valore sia per CURRVAL che per NEXTVAL indipendentemente dall'ordine all'interno dell'istruzione.

Pertanto, il valore massimo corrisponderà al numero di righe, ovvero 5..

Quante righe ci saranno nella tabella come risultato dell'esecuzione del seguente script?

create table t(i integer check (i < 5));
 
create procedure p(p_from integer, p_to integer) as
begin
    for i in p_from .. p_to loop
        insert into t values (i);
    end loop;
end;
/
 
exec p(1, 3);
exec p(4, 6);
exec p(7, 9);

• 0
• 3
• 4
• 5
• 6
• 9

RispostaSecondo la documentazione Oracle (citata da 11.2):

Prima di eseguire qualsiasi istruzione SQL, Oracle segna un salvataggio implicito (non disponibile per te). Poi, se l'istruzione fallisce, Oracle torna automaticamente indietro e restituisce il codice di errore applicabile a SQLCODE in SQLCA. Ad esempio, se un'istruzione INSERT causa un errore tentando di inserire un valore duplicato in un indice unico, l'istruzione viene annullata.

La chiamata alla stored procedure dal client è anch'essa considerata e trattata come un'unica istruzione. Così, la prima chiamata alla stored procedure termina con successo, inserendo tre record; la seconda chiamata termina con un errore e annulla il quarto record che era riuscita a inserire; la terza chiamata termina con un errore, e nella tabella si trovano tre record..

Quante righe ci saranno nella tabella come risultato dell'esecuzione del seguente script?

create table t(i integer, constraint i_ch check (i < 3));
 
begin
    insert into t values (1);
    insert into t values (null);
    insert into t values (2);
    insert into t values (null);
    insert into t values (3);
    insert into t values (null);
    insert into t values (4);
    insert into t values (null);
    insert into t values (5);
exception
    when others then
        dbms_output.put_line('Oops!');
end;
/

• 1
• 2
• 3
• 4
• 5
• 6
• 7

RispostaSecondo la documentazione Oracle (citata da 11.2):

Una restrizione di controllo consente di specificare una condizione che ciascuna riga nella tabella deve soddisfare. Per soddisfare la restrizione, ogni riga nella tabella deve rendere la condizione vera o sconosciuta (a causa di un null). Quando Oracle valuta una condizione di restrizione di controllo per una particolare riga, qualsiasi nome di colonna nella condizione fa riferimento ai valori delle colonne in quella riga.

Pertanto, il valore null passerà il controllo e il blocco anonimo sarà eseguito con successo fino al tentativo di inserire il valore 3. Dopo di ciò, il blocco di gestione degli errori spegnerà l'eccezione, non ci sarà alcun rollback e nella tabella rimarranno quattro righe con i valori 1, null, 2 e di nuovo null.

Quali coppie di valori occuperanno spazi uguali nel blocco?

create table t (
    a char(1 char),
    b char(10 char),
    c char(100 char),
    i number(4),
    j number(14),
    k number(24),
    x varchar2(1 char),
    y varchar2(10 char),
    z varchar2(100 char));
 
insert into t (a, b, i, j, x, y)
    values ('Y', 'Вася', 10, 10, 'Д', 'Вася');

• A e X
• B e Y
• C e K
• C e Z
• K e Z
• I e J
• J e X
• Tutte le coppie menzionate

RispostaPresentiamo estratti dalla documentazione (12.1.0.2) sullo stoccaggio di vari tipi di dati in Oracle.

Tipo di dato CHAR
Il tipo di dato CHAR specifica una stringa di caratteri a lunghezza fissa nel set di caratteri del database. Si specifica il set di caratteri del database quando si crea il database. Oracle garantisce che tutti i valori memorizzati in una colonna CHAR abbiano la lunghezza specificata dalla dimensione nelle semantiche di lunghezza selezionate. Se si inserisce un valore più corto della lunghezza della colonna, Oracle riempie il valore con spazi bianchi fino alla lunghezza della colonna.

Tipo di dato VARCHAR2
Il tipo di dato VARCHAR2 specifica una stringa di caratteri a lunghezza variabile nel set di caratteri del database. Si specifica il set di caratteri del database quando si crea il database. Oracle memorizza un valore di carattere in una colonna VARCHAR2 esattamente come lo si specifica, senza alcun riempimento con spazi bianchi, a condizione che il valore non superi la lunghezza della colonna.

Tipo di dato NUMBER
Il tipo di dato NUMBER memorizza zero e numeri fissi positivi e negativi con valori assoluti compresi tra 1.0 x 10-130 e fino a non includere 1.0 x 10126. Se si specifica un'espressione aritmetica il cui valore ha un valore assoluto maggiore o uguale a 1.0 x 10126, Oracle restituisce un errore. Ogni valore NUMBER richiede da 1 a 22 byte. Tenendo conto di ciò, la dimensione della colonna in byte per un particolare valore numerico di tipo NUMBER(p), dove p è la precisione di un dato valore, può essere calcolata utilizzando la seguente formula: ROUND((length(p)+s)\/2))+1 dove s è zero se il numero è positivo e s è 1 se il numero è negativo.

Inoltre, prendiamo un estratto dalla documentazione riguardante la memorizzazione dei valori null.

Un null è l'assenza di un valore in una colonna. I null indicano dati mancanti, sconosciuti o non applicabili. I null sono memorizzati nel database se si trovano tra colonne con valori di dati. In questi casi, richiedono 1 byte per memorizzare la lunghezza della colonna (zero). I null terminali in una riga non richiedono alcuno spazio di archiviazione perché un nuovo intestazione della riga segnala che le colonne rimanenti nella riga precedente sono null. Ad esempio, se le ultime tre colonne di una tabella sono null, allora nessun dato è memorizzato per queste colonne.

Basandoci su questi dati, costruiamo ragionamenti. Consideriamo che nel DB sia utilizzata la codifica AL32UTF8. In questa codifica le lettere russe occuperanno 2 byte.

1) A e X, il valore del campo a ‘Y’ occupa 1 byte, il valore del campo x ‘Д’ – 2 byte
2) B e Y, ‘Vasja’ in b verrà completato con spazi fino a 10 caratteri e occuperà 14 byte, ‘Vasja’ in d – occuperà 8 byte.
3) C e K. Entrambi i campi hanno valore NULL, dopo di essi ci sono campi significativi, quindi occupano 1 byte ciascuno.
4) C e Z. Entrambi i campi hanno valore NULL, ma il campo Z è l'ultimo nella tabella, quindi non occupa spazio (0 byte). Il campo C occupa 1 byte.
5) K e Z. Analogamente al caso precedente. Il valore nel campo K occupa 1 byte, in Z – 0.
6) I e J. Secondo la documentazione, entrambi i valori occupano 2 byte. La lunghezza si calcola secondo la formula fornita nella documentazione: round( (1 + 0) / 2) +1 = 1 + 1 = 2.
7) J e X. Il valore nel campo J occuperà 2 byte, il valore nel campo X occuperà 2 byte.

In totale, le combinazioni corrette sono: C e K, I e J, J e X.

Qual è approssimativamente il clustering factor per l'indice T_I?

create table t (i integer);
 
insert into t select rownum from dual connect by level <= 10000;
 
create index t_i on t(i);

• Dell'ordine di alcune decine
• Dell'ordine di alcune centinaia
• Dell'ordine di alcune migliaia
• Dell'ordine di alcune decine di migliaia

RispostaSecondo la documentazione Oracle (citata dalla versione 12.1):

Per un indice B-tree, il clustering factor dell'indice misura il raggruppamento fisico delle righe rispetto a un valore di indice.

Il clustering factor dell'indice aiuta l'ottimizzatore a decidere se è più efficiente un'analisi dell'indice o una scansione dell'intera tabella per determinate query. Un fattore di clustering basso indica una scansione efficiente dell'indice.

Un fattore di clustering vicino al numero di blocchi in una tabella indica che le righe sono fisicamente ordinate nei blocchi della tabella per chiave di indice. Se il database esegue una scansione dell'intera tabella, allora tende a recuperare le righe così come sono memorizzate su disco ordinate per chiave di indice. Un fattore di clustering vicino al numero di righe indica che le righe sono sparse casualmente nei blocchi del database rispetto alla chiave di indice. Se il database esegue una scansione dell'intera tabella, non recupererebbe righe in alcun ordine ordinato da questa chiave di indice.

In questo caso i dati sono perfettamente ordinati, quindi il clustering factor sarà uguale o vicino al numero di blocchi occupati nella tabella. Per la dimensione standard del blocco di 8 kilobyte, ci si può aspettare che in un blocco si possano inserire circa mille valori numerici stretti, quindi il numero di blocchi, e di conseguenza il clustering factor sarà dell'ordine di alcune decine.

Per quali valori di N il seguente script verrà eseguito con successo in un normale database con impostazioni standard?

create table t (
    a varchar2(N char),
    b varchar2(N char),
    c varchar2(N char),
    d varchar2(N char));
 
create index t_i on t (a, b, c, d);

• 100
• 200
• 400
• 800
• 1600
• 3200
• 6400

RispostaSecondo la documentazione Oracle (citata da 11.2):

Limiti logici del database

Voce
Tipo di limite
Valore limite

Indici
Dimensione totale della colonna indicizzata
75% della dimensione del blocco del database meno un certo overhead

Pertanto, la dimensione totale delle colonne indicizzate non dovrebbe superare i 6 KB. Il resto dipende dalla codifica scelta per il database. Per la codifica AL32UTF8, un carattere può occupare al massimo 4 byte; quindi, in 6 kilobyte, nel peggiore dei casi, possono stare circa 1500 caratteri. Perciò Oracle vieterebbe la creazione dell'indice quando N = 400 (quando la lunghezza della chiave nel peggiore dei casi sarà di 1600 caratteri * 4 byte + la lunghezza del rowid), mentre quando N = 200 (e meno) la creazione dell'indice funzionerà senza problemi.

L'operatore INSERT con l'hint APPEND è destinato al caricamento dei dati in modalità diretta. Cosa succederà se verrà applicato a una tabella con un trigger associato?

• I dati saranno caricati in modalità diretta, il trigger verrà attivato come dovrebbe
• I dati saranno caricati in modalità diretta, ma il trigger non verrà eseguito
• I dati saranno caricati in modalità convenzionale, il trigger verrà attivato come dovrebbe
• I dati saranno caricati in modalità convenzionale, ma il trigger non verrà eseguito
• I dati non saranno caricati, verrà registrato un errore

RispostaIn linea di principio, questa è una questione più logica. Per trovare la risposta corretta, suggerirei il seguente modello di ragionamento:

1. L'inserimento in modalità diretta avviene tramite la formazione diretta del blocco dati, bypassando il motore SQL, il che garantisce un'elevata velocità. Pertanto, garantire l'esecuzione del trigger è piuttosto difficile, se non impossibile, e non ha senso, poiché rallenterebbe drasticamente l'inserimento.
2. Il non eseguire il trigger porterà al fatto che, con dati identici nella tabella, lo stato complessivo del database (di altre tabelle) dipenderà da quale modalità siano stati inseriti questi dati. Questo ovviamente distruggerà l'integrità dei dati e non può essere applicato come soluzione in produzione.
3. L'impossibilità di eseguire l'operazione richiesta, in linea generale, viene interpretata come un errore. Ma qui bisogna ricordare che APPEND è un hint, e la logica generale degli hint consiste nel fatto che vengono presi in considerazione se possibile; altrimenti, l'operatore viene eseguito senza considerare l'hint.

Pertanto, la risposta attesa è i dati saranno caricati in modalità normale (SQL), il trigger verrà attivato.

Secondo la documentazione Oracle (citato da 8.04):

Le violazioni delle restrizioni causaranno l'esecuzione della dichiarazione in modo seriale, utilizzando il percorso di inserimento convenzionale, senza avvisi o messaggi di errore. Un'eccezione è la restrizione sulle dichiarazioni che accedono alla stessa tabella più di una volta in una transazione, che può causare messaggi di errore.
Ad esempio, se ci sono vincoli o integrità referenziale presenti nella tabella, il suggerimento APPEND verrà ignorato quando provi a utilizzare INSERT con carico diretto (seriale o parallelo), così come il suggerimento o la clausola PARALLEL, se presenti.

Cosa succederà durante l'esecuzione del seguente script?

create table t(i integer not null primary key, j integer references t);
 
create trigger t_a_i after insert on t for each row
declare
    pragma autonomous_transaction;
begin
    insert into t values (:new.i + 1, :new.i);
    commit;
end;
/
 
insert into t values (1, null);

• Esecuzione riuscita
• Errore a causa di un errore di sintassi
• Errore relativo all'invalidità della transazione autonoma
• Errore relativo al superamento della massima profondità di chiamate
• Errore relativo alla violazione del vincolo di chiave esterna
• Errore relativo ai blocchi

RispostaLa tabella e il trigger sono creati correttamente e questa operazione non dovrebbe causare problemi. Le transazioni autonome nel trigger sono anch'esse abilitate, altrimenti non sarebbe possibile, ad esempio, il logging.

Dopo l'inserimento della prima riga, l'attivazione del trigger porterebbe all'inserimento della seconda riga, il che attiverebbe nuovamente il trigger, inserendo la terza riga e così via fino a quando la dichiarazione non fallirebbe a causa del superamento della massima profondità di chiamate. Tuttavia, si presenta un altro aspetto sottile. Al momento dell'esecuzione del trigger per il primo record inserito, il commit non è ancora stato eseguito. Pertanto, il trigger, funzionando in una transazione autonoma, tenta di inserire nella tabella una riga che fa riferimento a un record non ancora impegnato. Questo porta a un'attesa (la transazione autonoma attende il commit principale per capire se i dati possono essere inseriti) e contemporaneamente la transazione principale attende il commit della transazione autonoma per continuare dopo il trigger. Si verifica un deadlock e di conseguenza la transazione autonoma viene annullata a causa dei blocchi.

Solo gli utenti registrati possono partecipare al sondaggio. Accedi, per favore.

Era difficile?

• Come bere un bicchier d'acqua, ho risolto tutto correttamente.

• Non molto, ho sbagliato un paio di domande.

• Ho risolto metà correttamente.

• Ho indovinato la risposta due volte!

• Scriverò nei commenti

Hanno votato 14 utenti. Si sono astenuti 10 utenti.

Fonte: habr.com