I spåren av Highload++ Siberia 2019 – 8 uppgifter om Oracle

Hej!

Den 24-25 juni anordnades konferensen Highload++ Siberia 2019 i Novosibirsk. Vårt team var också där. med en presentation »Containerdatabaser Oracle (CDB/PDB) och deras praktiska användning för programutveckling«, vi kommer att lägga ut en textversion lite senare. Det var fantastiskt, tack. olegbunin för arrangemanget, samt alla som kom.

I det här inlägget vill vi dela med oss av de uppgifter som fanns på vår monter, så att ni kan testa era kunskaper i Oracle. Nedan följer 8 uppgifter, svarsalternativ och förklaringar.

Vilket är det högsta värdet av sekvensen vi kommer att se som resultat av att köra följande skript?

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
• Inget, det blir ett fel.

SvarEnligt Oracle-dokumentationen (citerad från 8.1.6):
Inom en enda SQL-sats kommer Oracle att öka sekvensen endast en gång per rad. Om en sats innehåller mer än en referens till NEXTVAL för en sekvens, ökar Oracle sekvensen en gång och returnerar samma värde för alla förekomster av NEXTVAL. Om en sats innehåller referenser till både CURRVAL och NEXTVAL, ökar Oracle sekvensen och returnerar samma värde för både CURRVAL och NEXTVAL oavsett deras ordning inom satsen.

Därför kommer det högsta värdet att motsvara antalet rader, det vill säga 5..

Hur många rader kommer att finnas i tabellen som resultat av att köra följande skript?

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

SvarEnligt Oracle-dokumentationen (citerad från 11.2):

Innan någon SQL-sats körs markerar Oracle en implicit savepoint (inte tillgänglig för dig). Om satsen misslyckas backar Oracle automatiskt tillbaka och returnerar den aktuella felkoden till SQLCODE i SQLCA. Till exempel, om en INSERT-sats orsakar ett fel genom att försöka sätta in ett dubblettvärde i ett unikt index, backas satsen tillbaka.

Anropet för lagrad procedur från klienten betraktas också och behandlas som en enda sats. Således avslutas det första anropet för den lagrade proceduren framgångsrikt och sätter in tre poster; det andra anropet för den lagrade proceduren avslutas med ett fel och backar den fjärde posten som den har lyckats sätta in; det tredje anropet avslutas med ett fel, och i tabellen finns tre poster..

Hur många rader kommer att finnas i tabellen som resultat av att köra följande skript?

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

SvarEnligt Oracle-dokumentationen (citerad från 11.2):

En kontrollbegränsning låter dig specificera ett villkor som varje rad i tabellen måste uppfylla. För att uppfylla begränsningen måste varje rad i tabellen göra villkoret antingen SANT eller okänt (på grund av null). När Oracle utvärderar ett villkor för en kontrollbegränsning för en specifik rad, refererar alla kolumnnamn i villkoret till kolumnvärdena i den raden.

Således kommer värdet null att passera kontrollen, och det anonyma blocket kommer att köras fram till försöket att införa värdet 3. Därefter kommer felhanteringsblocket att fånga undantaget, det kommer inte att bli någon tillbakagång och fyra rader kommer att förbli i tabellen med värdena 1, null, 2 och återigen null.

Vilka värdepar kommer att ta upp samma mängd utrymme i blocket?

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', 'Vanya', 10, 10, 'D', 'Vanya');

• A och X
• B och Y
• C och K
• C och Z
• K och Z
• I och J
• J och X
• Alla angivna

SvarLåt oss ge utdrag från dokumentationen (12.1.0.2) om lagring av olika datatyper i Oracle.

CHAR Datatyp
CHAR-datatypen specificerar en teckensträng med fast längd i databasens teckenkodning. Du specificerar databasens teckenkodning när du skapar din databas. Oracle säkerställer att alla värden som lagras i en CHAR-kolumn har den längd som anges av storleken i den valda längdsemantiken. Om du infogar ett värde som är kortare än kolumnens längd, då fyller Oracle upp värdet med mellanslag till kolumnens längd.

VARCHAR2 Datatyp
VARCHAR2-datatypen specificerar en teckensträng med variabel längd i databasens teckenkodning. Du specificerar databasens teckenkodning när du skapar din databas. Oracle lagrar ett teckenvärde i en VARCHAR2-kolumn exakt som du anger det, utan något mellanslagsfyllande, förutsatt att värdet inte överskrider kolumnens längd.

NUMBER Datatyp
NUMBER-datatypen lagrar noll samt positiva och negativa fasta tal med absoluta värden från 1.0 x 10-130 till men inte inklusive 1.0 x 10126. Om du anger ett aritmetiskt uttryck vars värde har ett absolut värde större än eller lika med 1.0 x 10126, då returnerar Oracle ett fel. Varje NUMBER-värde kräver från 1 till 22 byte. Med detta i åtanke kan kolumnens storlek i byte för ett specifikt numeriskt datavärde NUMBER(p), där p är precisionen för ett givet värde, beräknas med följande formel: ROUND((length(p)+s)/2))+1 där s är lika med noll om talet är positivt, och s är lika med 1 om talet är negativt.

Dessutom ska vi ta en utdrag ur dokumentationen om lagring av Null-värden.

Ett null-värde är avsaknaden av ett värde i en kolumn. Null-värden indikerar saknad, okänt eller ovidkommande data. Null-värden lagras i databasen om de ligger mellan kolumner med datavärden. I dessa fall kräver de 1 byte för att lagra längden på kolumnen (noll). Efterföljande null-värden i en rad kräver ingen lagring eftersom en ny radhuvud signalerar att de återstående kolumnerna i den föregående raden är null. Till exempel, om de tre sista kolumnerna i en tabell är null, lagras ingen data för dessa kolumner.

Baserat på dessa data bygger vi resonemang. Vi antar att databasen använder kodningen AL32UTF8. I denna kodning kommer ryska bokstäver att ta upp 2 byte.

1) A och X, värdet av fältet a 'Y' tar 1 byte, värdet av fältet x 'D' – 2 byte
2) B och Y, 'Vasya' i b kommer att fyllas ut med mellanslag upp till 10 tecken och ta 14 byte, 'Vasya' i d – kommer att ta 8 byte.
3) C och K. Båda fälten har värdet NULL, efter dem finns betydelsefulla fält, så de tar upp 1 byte vardera.
4) C och Z. Båda fälten har värdet NULL, men fältet Z är det sista i tabellen, så det tar ingen plats (0 byte). Fält C tar upp 1 byte.
5) K och Z. På samma sätt som föregående fall. Värdet i fält K tar upp 1 byte, i Z – 0.
6) I och J. Enligt dokumentationen kommer båda värdena att ta upp 2 byte. Längden beräknas med formeln från dokumentationen: round((1 + 0) / 2) + 1 = 1 + 1 = 2.
7) J och X. Värdet i fält J tar upp 2 byte, värdet i fält X tar upp 2 byte.

Sammanlagt, korrekta alternativ: C och K, I och J, J och X.

Vad kommer clustering-faktorn för indexet T_I att vara ungefär?

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

• Cirka tiotal
• Cirka hundratal
• Cirka tusental
• Cirka tiotusental

SvarEnligt Oracle-dokumentationen (citeras från 12.1):

För ett B-träd index mäter indexets clustering-faktor den fysiska grupperingen av rader i förhållande till ett indexvärde.

Indexets clustering-faktor hjälper optimeraren att avgöra om en indexscanning eller en full tabellscanning är mer effektiv för vissa frågor. En låg clustering-faktor indikerar en effektiv indexscanning.

En clustering-faktor som ligger nära antalet block i en tabell indikerar att raderna är fysiskt ordnade i tabellblocken efter indexnyckeln. Om databasen gör en full tabellscanning tenderar databasen att hämta raderna i den ordning de lagras på disk sorterade efter indexnyckeln. En clustering-faktor som ligger nära antalet rader indikerar att raderna är slumpmässigt spridda över databaskluster i förhållande till indexnyckeln. Om databasen gör en full tabellscanning hämtar databasen inte rader i någon sorterad ordning efter denna indexnyckel.

I detta fall är data perfekt sorterade, så clustering-faktorn kommer att vara lika med eller nära antalet upptagna block i tabellen. För en standard blockstorlek på 8 kilobyte kan man förvänta sig att ett block rymmer cirka tusen smala nummervärden, så antalet block, och därmed clustering-faktorn, kommer att vara cirka tiotal.

Vid vilka värden av N kommer följande skript att köras framgångsrikt i en vanlig databas med standardinställningar?

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

SvarEnligt Oracle-dokumentationen (citerad från 11.2):

Logiska datab gränser

Objekt
Typ av begränsning
Begränsningsvärde

Index
Total storlek av indexerat kolumn
75% av databasklusterstorleken minus viss overhead

Således får den totala storleken på de indexerade kolumnerna inte överstiga 6 KB. Vad som händer därfter beror på den valda databaskodningen. För kodningen AL32UTF8 kan en symbol ta upp till 4 byte, vilket innebär att cirka 1500 symboler kan få plats i 6 kilobyte i det värsta fallet. Därför kommer Oracle att förbjuda skapandet av ett index vid N = 400 (när nyckellängden i värsta fall kommer att vara 1600 symboler * 4 byte + längden av rowid), medan vid N = 200 (och mindre) kan skapandet av indexet utföras utan problem.

INSERT-operatören med APPEND-hint är avsedd för att ladda data i direktläge. Vad händer om den tillämpas på en tabell som har en trigger?

• Data kommer att laddas i direktläge, triggern aktiveras som den ska
• Data kommer att laddas i direktläge, men triggern kommer inte att utföras
• Data kommer att laddas i konventionellt läge, triggern aktiveras som den ska
• Data kommer att laddas i konventionellt läge, men triggern kommer inte att utföras
• Data kommer inte att laddas, ett fel kommer att registreras

SvarRent tekniskt är detta fråga om logik. För att hitta rätt svar skulle jag föreslå följande resonemangsmodell:

1. Infogning i direktläge utförs genom att direkt bilda datablokkar, utan att gå genom SQL-motorn, vilket möjliggör hög hastighet. Därför är det mycket svårt att säkerställa att triggern utförs, om det ens är möjligt, och det finns ingen mening med det, eftersom det ändå kraftigt skulle bromsa infogningen.
2. Att triggern inte utförs kommer att leda till att tillståndet hos databasen som helhet (andra tabeller) kommer att bero på vilket läge dessa data har infogats. Detta skulle uppenbart underminera dataintegriteten och kan inte tillämpas som en lösning i produktion.
3. Oförmågan att utföra den begärda operationen betraktas generellt som ett fel. Men här bör vi komma ihåg att APPEND är en hint, och den allmänna logiken med hints är att de beaktas om det är möjligt; om inte, så utförs operatören utan att ta hänsyn till hint.

Således är det förväntade svaret - data kommer att laddas i normalt (SQL) läge, triggern aktiveras.

Enligt Oracle-dokumentationen (citerat från 8.04):

Överträdelser av restriktionerna kommer att leda till att satsen körs sekventiellt, med den konventionella insättningsvägen, utan varningar eller felmeddelanden. Ett undantag är restriktionen mot satser som kommer åt samma tabell mer än en gång i en transaktion, vilket kan orsaka felmeddelanden.
Till exempel, om det finns triggar eller referentiell integritet på tabellen, kommer APPEND-hintet att ignoreras när du försöker använda direktladdning INSERT (seriel eller parallell), liksom PARALLEL-hintet eller klausulen, om det finns något.

Vad händer när följande skript körs?

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);

• Framgångsrik körning
• Misslyckande på grund av syntaxfel
• Fel relaterat till otillåten autonom transaktion
• Fel relaterat till överskridande av maximal anropsnivå
• Fel relaterat till en brytning av en utländsk nyckel
• Fel relaterat till låsningar

SvarTabellen och triggarna skapas helt korrekt och denna operation bör inte orsaka problem. Autonoma transaktioner i triggar är också tillåtna, annars skulle det vara omöjligt, till exempel loggning.

Efter att den första raden infogats skulle den framgångsrika triggen leda till en andra infogning, vilket skulle orsaka att triggen kördes igen, infoga en tredje rad och så vidare tills kommandot misslyckade på grund av överskridande av maximal anropsnivå. Emellertid inträffar en annan subtil punkt. Vid körning av triggen för den första infogade posten har commit ännu inte utförts. Därför försöker triggen, som arbetar i en autonom transaktion, att infoga en rad i tabellen som refererar till en ännu icke-committad post. Detta leder till väntan (den autonoma transaktionen väntar på commit av huvudet för att förstå om data kan infogas) och samtidigt väntar den huvudsakliga transaktionen på commit av den autonoma för att fortsätta arbetet efter triggen. Det uppstår en deadlock och som en följd av detta avbryts den autonoma transaktionen på grund av låsningar.

Endast registrerade användare kan delta i omröstningen. Logga in, tack.

Var det svårt?

• Som två fingrar, löste allt rätt med en gång.

• Inte särskilt, jag gjorde fel på ett par frågor.

• Jag löste hälften rätt.

• Jag gissade svaret två gånger!

• Jag skriver i kommentarerna

14 användare röstade. 10 användare avstod.

Källa: habr.com