🥇Sulle tracce di Highload++ Siberia 2019 — 8 sfide su Oracle

Ciao!

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

In questo post desideriamo condividere con voi le attività che avevamo al nostro stand, affinché possiate mettere alla prova le vostre conoscenze su Oracle. Di seguito trovate 8 attività, le opzioni di risposta e spiegazioni.

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 di Oracle (citato da 8.1.6):
All'interno di una singola istruzione SQL, Oracle incrementa 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 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 per CURRVAL e NEXTVAL indipendentemente dal loro ordine nell'istruzione.

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

Quante righe ci saranno nella tabella a seguito 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);

RispostaSecondo la documentazione Oracle (riportata da 11.2):

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

La chiamata alla stored procedure dal client è anche considerata e trattata come un'istruzione singola. Pertanto, la prima chiamata alla stored procedure si conclude con successo, inserendo tre record; la seconda chiamata alla stored procedure termina con un errore e annulla il quarto record, che è stato inserito; la terza chiamata termina con un errore, e nella tabella ci sono tre record.

Quante righe ci saranno nella tabella a seguito 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;
/

RispostaSecondo la documentazione Oracle (riportata da 11.2):

Una restrizione di controllo consente di specificare una condizione che ogni riga nella tabella deve soddisfare. Per soddisfare la restrizione, ogni riga nella tabella deve rendere la condizione vera o sconosciuta (a causa di un valore nullo). Quando Oracle valuta una condizione di restrizione di controllo per una riga particolare, i nomi delle colonne nella condizione si riferiscono ai valori delle colonne in quella riga.

In questo modo, il valore nullo passerà il controllo e il blocco anonimo verrà eseguito correttamente fino al momento in cui si tenterà di inserire il valore 3. Dopo di ciò, il blocco di gestione degli errori interverrà per annullare l'eccezione, senza alcun rollback, e quattro righe rimarranno nella tabella con i valori 1, null, 2 e nuovamente null.

Quali coppie di valori occuperanno spazi identici 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', 'Vasya', 10, 10, 'D', 'Vasya');

A e X
B e Y
C e K
C e Z
K e Z
I e J
J e X
Tutti i suddetti

RispostaRiportiamo estratti dalla documentazione (12.1.0.2) riguardante l'archiviazione di diversi tipi di dati in Oracle.

Tipo di dati CHAR
Il tipo di dati CHAR specifica una stringa di caratteri a lunghezza fissa nel set di caratteri del database. Devi specificare il set di caratteri del database quando crei il tuo database. Oracle garantisce che tutti i valori memorizzati in una colonna CHAR abbiano la lunghezza specificata dalla dimensione nella semantica di lunghezza selezionata. Se inserisci un valore più corto della lunghezza della colonna, Oracle riempirà il valore con spazi fino alla lunghezza della colonna.

Tipo di dati VARCHAR2
Il tipo di dati 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 vuoti, a condizione che il valore non superi la lunghezza della colonna.

Tipo di dati NUMBER
Il tipo di dati NUMBER memorizza zero insieme a numeri fissi positivi e negativi con valori assoluti da 1.0 x 10-130 a, ma non incluso, 1.0 x 10^126. Se si specifica un'espressione aritmetica il cui valore ha un valore assoluto maggiore o uguale a 1.0 x 10^126, Oracle restituisce un errore. Ogni valore NUMBER richiede da 1 a 22 byte. Tenendo conto di questo, la dimensione della colonna in byte per un valore numerico particolare NUMBER(p), dove p è la precisione di un valore dato, può essere calcolata utilizzando la seguente formula: ROUND((lunghezza(p)+s)/2))+1 dove s è uguale a zero se il numero è positivo e s è uguale a 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 vengono 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 finali in una riga non richiedono spazio di memorizzazione perché un nuovo intestazione di riga segnala che le colonne rimanenti nella riga precedente sono null. Ad esempio, se le ultime tre colonne di una tabella sono null, non vengono memorizzati dati per queste colonne.

Sulla base di questi dati, costruiamo le deduzioni. Supponiamo che nel database venga 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, 'Вася' nel b verrà completato con spazi fino a 10 caratteri e occuperà 14 byte, 'Вася' nel d – occuperà 8 byte.
3) C e K. Entrambi i campi hanno valore NULL, dopo di essi ci sono campi significativi, quindi occupano ciascuno 1 byte.
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 occuperanno 2 byte. La lunghezza è calcolata secondo la formula prelevata dalla 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 opzioni corrette sono: C e K, I e J, J e X.

Quale sarà approssimativamente il clustering factor dell'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 decine
Dell'ordine di centinaia
Dell'ordine di migliaia
Dell'ordine di decine di migliaia

RispostaSecondo la documentazione Oracle (citato da 12.1):

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

Il fattore di clustering dell'indice aiuta l'ottimizzatore a decidere se è più efficiente eseguire uno scan dell'indice o uno scan completo della tabella per determinate query. Un fattore di clustering basso indica uno scan dell'indice efficiente.

Un fattore di clustering vicino al numero di blocchi in una tabella indica che le righe sono fisicamente ordinate nei blocchi della tabella in base alla chiave dell'indice. Se il database esegue uno scan completo della tabella, tende a recuperare le righe come sono memorizzate su disco, ordinate dalla chiave dell'indice. Un fattore di clustering vicino al numero di righe indica che le righe sono sparse casualmente tra i blocchi del database in relazione alla chiave dell'indice. Se il database esegue uno scan completo della tabella, non recupererebbe le righe in nessun ordine ordinato per questa chiave dell'indice.

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

Quali valori di N consentiranno l'esecuzione del seguente script 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 (riportata 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 sovraccarico

Pertanto, la dimensione totale delle colonne indicizzate non deve 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 caso peggiore, ci stanno circa 1500 caratteri. Di conseguenza, Oracle impedirà la creazione dell'indice quando N = 400 (quando la lunghezza della chiave nel caso peggiore sarà 1600 caratteri * 4 byte + lunghezza del rowid), mentre quando N = 200 (e meno) la creazione dell'indice funzionerà senza problemi.

L'operatore INSERT con hint APPEND è progettato per caricare dati in modalità diretta. Cosa succede se viene applicato a una tabella con un trigger attivo?

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

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

L'inserimento in modalità diretta avviene tramite la generazione diretta di un blocco di dati, bypassando il motore SQL, il che garantisce un'alta velocità. Pertanto, è molto difficile garantire l'esecuzione di un trigger, se non impossibile, e non ha senso, poiché rallenterebbe drasticamente l'inserimento.
L'eventuale mancato funzionamento del trigger porterà a una situazione in cui, con dati identici nella tabella, lo stato complessivo del database (di altre tabelle) dipenderà da quale modalità è stata utilizzata per l'inserimento di questi dati. Questo distruggerà evidentemente l'integrità dei dati e non può essere applicato come soluzione in produzione.
L'impossibilità di eseguire l'operazione richiesta viene generalmente interpretata come un errore. Ma qui è opportuno ricordare che APPEND è un hint, e la logica generale degli hint consiste nel fatto che vengono presi in considerazione se possibile; in caso contrario, l'operatore viene eseguito senza considerare l'hint.

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

Secondo la documentazione Oracle (citata dalla versione 8.04):

Le violazioni delle restrizioni comporteranno l'esecuzione della dichiarazione in modo seriale, utilizzando il percorso di inserimento convenzionale, senza avvisi o messaggi di errore. Un'eccezione è rappresentata dalla restrizione su dichiarazioni che accedono alla stessa tabella più di una volta in una transazione, il che può provocare messaggi di errore.
Ad esempio, se sono presenti trigger o integrità referenziale sulla tabella, allora l'indicazione APPEND verrà ignorata quando si tenta di utilizzare INSERT a caricamento diretto (seriale o parallelo), così come l'indicazione o clausola PARALLEL, se presente.

Cosa succede quando esegui il 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
Fallimento 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 della chiave esterna
Errore relativo ai lock

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

Dopo l'inserimento della prima riga, l'attivazione del trigger avrebbe comportato l'inserimento della seconda riga, il che avrebbe riattivato il trigger, inserito la terza riga e così via, fino a quando lo statement non sarebbe fallito a causa del superamento del numero massimo di chiamate annidate. Tuttavia, c'è un altro aspetto sottile. Al momento dell'esecuzione del trigger per la prima riga inserita, il commit non è ancora stato eseguito. Pertanto, il trigger, che opera in una transazione autonoma, tenta di inserire una riga nella tabella che fa riferimento a un record non ancora impegnato da un vincolo esterno. Questo provoca un'attesa (la transazione autonoma aspetta il commit principale per capire se i dati possono essere inseriti) e contemporaneamente la transazione principale aspetta il commit della transazione autonoma per continuare a operare dopo il trigger. Si verifica un deadlock e, di conseguenza, la transazione autonoma viene interrotta a causa di blocchi..

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

È stato difficile?

Come bere un bicchier d'acqua, ho risolto tutto subito.
Non molto, ho sbagliato un paio di domande.
Ho risolto metà correttamente.
Ho indovinato due volte la risposta!
Scriverò nei commenti.

Hanno votato 14 utenti. 10 utenti si sono astenuti.

Fonte: habr.com

Sulle orme di Highload++ Siberia 2019 — 8 compiti su Oracle

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

Quante righe ci saranno nella tabella a seguito dell'esecuzione del seguente script?

Quante righe ci saranno nella tabella a seguito dell'esecuzione del seguente script?

Quali coppie di valori occuperanno spazi identici nel blocco?

Quale sarà approssimativamente il clustering factor dell'indice T_I?

Quali valori di N consentiranno l'esecuzione del seguente script in un normale database con impostazioni standard?

L'operatore INSERT con hint APPEND è progettato per caricare dati in modalità diretta. Cosa succede se viene applicato a una tabella con un trigger attivo?

Cosa succede quando esegui il seguente script?

È stato difficile?

Yuri Gagarin