ProHoster > blog > amministrazione > Seguendo le orme di Highload++ Siberia 2019 - 8 attività su Oracle

Seguendo le orme di Highload++ Siberia 2019 - 8 attività su Oracle

Hi!

Il 24 e 25 giugno si è tenuta a Novosibirsk la conferenza Highload++ Siberia 2019. Erano presenti anche i nostri ragazzi rapporto “I database contenitore Oracle (CDB/PDB) e il loro utilizzo pratico per lo sviluppo di software”, pubblicheremo una versione testuale poco più tardi. È stato bello, grazie olegbunin per l'organizzazione, così come per tutti coloro che sono venuti.

Seguendo le orme di Highload++ Siberia 2019 - 8 attività su Oracle
In questo post vorremmo condividere con te i problemi che abbiamo avuto al nostro stand in modo che tu possa mettere alla prova le tue conoscenze su Oracle. Sotto il taglio ci sono 8 problemi, opzioni di risposta e spiegazione.

Qual è il valore di sequenza massimo 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
  • No, si verificherà 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 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 dal loro ordine all'interno dell'istruzione.

Così, la il valore massimo corrisponderà al numero di righe, ovvero 5.

Quante righe saranno presenti 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 contrassegna un punto di salvataggio implicito (non disponibile per l'utente). Quindi, se l'istruzione fallisce, Oracle ne esegue il rollback automaticamente e restituisce il codice di errore applicabile a SQLCODE nell'SQLCA. Ad esempio, se un'istruzione INSERT provoca un errore tentando di inserire un valore duplicato in un indice univoco, viene eseguito il rollback dell'istruzione.

Anche la chiamata ad HP dal cliente viene considerata ed elaborata come un'unica dichiarazione. Pertanto, la prima chiamata HP viene completata con successo, dopo aver inserito tre record; la seconda chiamata HP termina con un errore e ripristina il quarto record che è riuscita a inserire; la terza chiamata fallisce, e nella tabella sono presenti tre record.

Quante righe saranno presenti 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):

Un vincolo check consente di specificare una condizione che ogni riga della tabella deve soddisfare. Per soddisfare il vincolo, ogni riga nella tabella deve rendere la condizione VERA o sconosciuta (a causa di un valore nullo). Quando Oracle valuta una condizione di vincolo di controllo per una riga particolare, tutti i nomi di colonna nella condizione si riferiscono ai valori di colonna in quella riga.

Pertanto, il valore null supererà il controllo e il blocco anonimo verrà eseguito con successo fino al tentativo di inserire il valore 3. Successivamente, il blocco di gestione degli errori cancellerà l'eccezione, non si verificherà alcun rollback e nella tabella rimarranno quattro righe con valori 1, null, 2 e ancora null.

Quali coppie di valori occuperanno la stessa quantità di spazio 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
  • Io e J
  • J e X
  • Tutti elencati

rispostaEcco alcuni estratti dalla documentazione (12.1.0.2) sulla memorizzazione di vari tipi di dati in Oracle.

CHAR Tipo di dati
Il tipo di dati 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 archiviati in una colonna CHAR abbiano la lunghezza specificata da size nella semantica della lunghezza selezionata. Se inserisci un valore inferiore alla lunghezza della colonna, Oracle inserisce il valore nella 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 specificato, senza spazi vuoti, a condizione che il valore non superi la lunghezza della colonna.

NUMERO Tipo di dati
Il tipo di dati NUMBER memorizza zero nonché numeri fissi positivi e negativi con valori assoluti compresi tra 1.0 x 10-130 e 1.0 x 10126 escluso. 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. Ciascun valore NUMERO richiede da 1 a 22 byte. Tenendo conto di ciò, la dimensione della colonna in byte per un particolare valore di dati numerici NUMBER(p), dove p è la precisione di un determinato valore, 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 sulla memorizzazione dei valori Null.

Un null è l'assenza di un valore in una colonna. I valori Null indicano dati mancanti, sconosciuti o inapplicabili. I valori null vengono archiviati nel database se rientrano tra colonne con valori di dati. In questi casi, richiedono 1 byte per memorizzare la lunghezza della colonna (zero). I valori null finali in una riga non richiedono archiviazione perché una nuova intestazione di riga segnala che le colonne rimanenti nella riga precedente sono null. Ad esempio, se le ultime tre colonne di una tabella sono nulle, per queste colonne non verrà archiviato alcun dato.

Sulla base di questi dati costruiamo il ragionamento. Supponiamo che il database utilizzi 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 'D' occupa 2 byte
2) B e Y, 'Vasya' in b il valore verrà riempito con spazi fino a 10 caratteri e occuperà 14 byte, 'Vasya' in d richiederà 8 byte.
3) C e K. Entrambi i campi hanno il valore NULL, dopo di loro ci sono campi significativi, quindi occupano 1 byte.
4) C e Z. Entrambi i campi hanno valore NULL, ma il campo Z è l'ultimo della tabella, quindi non occupa spazio (0 byte). Il campo C occupa 1 byte.
5) K e Z. Simile al caso precedente. Il valore nel campo K occupa 1 byte, in Z – 0.
6) I e J. Secondo la documentazione, entrambi i valori richiederanno 2 byte. Calcoliamo la lunghezza utilizzando la formula presa 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 fattore di clustering 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);

  • Circa decine
  • Circa centinaia
  • Circa migliaia
  • Circa decine di migliaia

rispostaSecondo la documentazione Oracle (citata 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 una scansione dell'indice o una scansione completa della tabella è più efficiente per determinate query). Un fattore di clustering basso indica una scansione 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 una scansione completa della tabella, il database tende a recuperare le righe così come sono archiviate sul disco ordinate in base alla chiave dell'indice. Un fattore di clustering vicino al numero di righe indica che le righe sono sparse in modo casuale nei blocchi del database in relazione alla chiave dell'indice. Se il database esegue una scansione completa della tabella, il database non recupererà le righe in alcun ordine in base a questa chiave di indice.

In questo caso, i dati sono ordinati idealmente, quindi il fattore di clustering sarà uguale o vicino al numero di blocchi occupati nella tabella. Per una dimensione di blocco standard di 8 kilobyte, puoi aspettarti che circa un migliaio di valori numerici ristretti rientrino in un blocco, quindi il numero di blocchi e, di conseguenza, il fattore di clustering sarà circa decine.

A 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 del database logico

Articolo
Tipo di limite
Valore limite

Indici
Dimensione totale della colonna indicizzata
Il 75% della dimensione del blocco del database meno un po' di sovraccarico

Pertanto, la dimensione totale delle colonne indicizzate non deve superare i 6 KB. Ciò che accade dopo dipende dalla codifica di base selezionata. Per la codifica AL32UTF8, un carattere può occupare un massimo di 4 byte, quindi nel peggiore dei casi, circa 6 caratteri rientrano in 1500 kilobyte. Pertanto, Oracle non consentirà la creazione dell'indice a N = 400 (quando la lunghezza della chiave nel caso peggiore è 1600 caratteri * 4 byte + lunghezza rowid), mentre a N = 200 (o meno) la creazione dell'indice funzionerà senza problemi.

L'operatore INSERT con l'hint APPEND è progettato per caricare i dati in modalità diretta. Cosa succede se viene applicato al tavolo su cui si blocca il trigger?

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

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

  1. L'inserimento in modalità diretta viene eseguito mediante la formazione diretta di un blocco dati, bypassando il motore SQL, che garantisce un'elevata velocità. Pertanto, garantire l'esecuzione del trigger è molto difficile, se non impossibile, e non ha senso, poiché rallenterebbe comunque radicalmente l'inserimento.
  2. La mancata esecuzione del trigger porterà al fatto che, se i dati nella tabella sono gli stessi, lo stato del database nel suo insieme (altre tabelle) dipenderà dalla modalità in cui questi dati sono stati inseriti. Ciò ovviamente distruggerà l'integrità dei dati e non può essere applicato come soluzione in produzione.
  3. L'impossibilità di eseguire l'operazione richiesta viene generalmente considerata un errore. Ma qui dovremmo ricordare che APPEND è un suggerimento, e la logica generale dei suggerimenti è che vengono presi in considerazione se possibile, ma in caso contrario l'operatore viene eseguito senza tenere conto del suggerimento.

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

Secondo la documentazione Oracle (citata da 8.04):

Le violazioni delle restrizioni causeranno l'esecuzione dell'istruzione in serie, utilizzando il percorso di inserimento convenzionale, senza avvisi o messaggi di errore. Un'eccezione è la restrizione sulle istruzioni che accedono alla stessa tabella più di una volta in una transazione, che può causare messaggi di errore.
Ad esempio, se nella tabella sono presenti trigger o integrità referenziale, l'hint APPEND verrà ignorato quando si tenta di utilizzare INSERT a caricamento diretto (seriale o parallelo), così come l'hint o la clausola PARALLEL, se presente.

Cosa accadrà quando verrà eseguito 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
  • Errore dovuto a un errore di sintassi
  • Errore: la transazione autonoma non è valida
  • Errore relativo al superamento del massimo annidamento delle chiamate
  • Errore di violazione della chiave esterna
  • Errore relativo ai blocchi

rispostaLa tabella e il trigger vengono creati abbastanza correttamente e questa operazione non dovrebbe causare problemi. Sono consentite anche transazioni autonome in un trigger, altrimenti ad es. la registrazione non sarebbe possibile.

Dopo aver inserito la prima riga, un'attivazione riuscita del trigger causerebbe l'inserimento della seconda riga, provocando l'attivazione di nuovo del trigger, l'inserimento di una terza riga e così via fino a quando l'istruzione non ha avuto esito negativo a causa del superamento della nidificazione massima di chiamate. Tuttavia entra in gioco un altro punto delicato. Nel momento in cui viene eseguito il trigger, il commit non è ancora stato completato per il primo record inserito. Pertanto, un trigger in esecuzione in una transazione autonoma tenta di inserire nella tabella una riga che fa riferimento a una chiave esterna a un record di cui non è stato ancora eseguito il commit. Ciò si traduce in un'attesa (la transazione autonoma attende che la transazione principale si impegni per vedere se può inserire dati) e allo stesso tempo la transazione principale attende che la transazione autonoma continui a funzionare dopo il trigger. Si verifica un deadlock e, di conseguenza, la transazione autonoma viene annullata per motivi legati ai lock.

Solo gli utenti registrati possono partecipare al sondaggio. AccediPer favore.

È stato difficile?

  • Come due dita, ho deciso immediatamente tutto correttamente.

  • Non proprio, ho sbagliato su un paio di domande.

  • Ne ho risolto metà correttamente.

  • Ho indovinato la risposta due volte!

  • Scriverò nei commenti

14 utenti hanno votato. 10 utenti si sono astenuti.

Fonte: habr.com

Aggiungi un commento