A Highload++ Siberia 2019 nyomdokain – 8 feladat az Oracle-en

Hi!

Június 24-25-én Novoszibirszkben került megrendezésre a Highload++ Siberia 2019 konferencia, amelyen a mi srácaink is ott voltak. jelentés „Az Oracle konténeradatbázisok (CDB/PDB) és gyakorlati felhasználásuk a szoftverfejlesztéshez” szöveges változatát kicsit később közöljük. Klassz volt, köszönöm olegbunin a szervezésnek, valamint mindenkinek, aki eljött.

Ebben a bejegyzésben szeretnénk megosztani veletek azokat a problémákat, amelyek a standunkon voltak, hogy próbára tegye Oracle tudását. A vágás alatt 8 feladat, válaszlehetőségek és magyarázat található.

Mekkora a maximális sorozatérték, amelyet a következő szkript végrehajtásának eredményeként látunk?

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
• Nem, hiba lesz

VálaszAz Oracle dokumentációja szerint (idézet a 8.1.6-ból):
Egyetlen SQL utasításon belül az Oracle soronként csak egyszer növeli a sorozatot. Ha egy utasítás egynél több hivatkozást tartalmaz a NEXTVAL-ra egy sorozathoz, az Oracle egyszer növeli a sorozatot, és ugyanazt az értéket adja vissza a NEXTVAL minden előfordulásakor. Ha egy utasítás a CURRVAL-ra és a NEXTVAL-ra is hivatkozásokat tartalmaz, az Oracle növeli a szekvenciát, és ugyanazt az értéket adja vissza mind a CURRVAL, mind a NEXTVAL esetében, függetlenül azok sorrendjétől az utasításban.

Így a maximális érték a sorok számának felel meg, azaz 5.

Hány sor lesz a táblázatban a következő szkript futtatásának eredményeként?

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

VálaszAz Oracle dokumentációja szerint (idézet a 11.2-ból):

Bármilyen SQL utasítás végrehajtása előtt az Oracle megjelöl egy implicit mentési pontot (az Ön számára nem elérhető). Ezután, ha az utasítás meghiúsul, az Oracle automatikusan visszagörgeti, és visszaküldi a megfelelő hibakódot az SQLCA-ban található SQLCODE-nak. Például, ha egy INSERT utasítás hibát okoz azáltal, hogy ismétlődő értéket próbál beszúrni egy egyedi indexbe, akkor az utasítás visszakerül.

A HP ügyféltől történő hívása is egyetlen nyilatkozatnak minősül és feldolgozható. Így az első HP hívás sikeresen befejeződik, három rekord beszúrása után; a második HP hívás hibával ér véget, és visszagörgeti a negyedik rekordot, amelyet sikerült beillesztenie; a harmadik hívás sikertelen, és három rekord van a táblázatban.

Hány sor lesz a táblázatban a következő szkript futtatásának eredményeként?

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

VálaszAz Oracle dokumentációja szerint (idézet a 11.2-ból):

Az ellenőrzési megkötés lehetővé teszi egy olyan feltétel megadását, amelyet a táblázat minden sorának teljesítenie kell. A megszorítás teljesítéséhez a táblázat minden sorában a feltételt IGAZ-nak vagy ismeretlennek kell tennie (a nulla miatt). Amikor az Oracle kiértékel egy ellenőrzési kényszerfeltételt egy adott sorhoz, a feltételben szereplő bármely oszlopnév az adott sorban lévő oszlopértékekre utal.

Így a null érték átmegy az ellenőrzésen, és az anonim blokk sikeresen végrehajtásra kerül a 3-as érték beszúrási kísérletéig. Ezt követően a hibakezelő blokk törli a kivételt, nem történik visszagörgetés, és négy sor marad a táblázatban 1, null, 2 és ismét null értékekkel.

Mely értékpárok foglalnak el ugyanannyi helyet a blokkban?

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 és X
• B és Y
• C és K
• C és Z
• K és Z
• Én és J
• J és X
• Minden felsorolt

VálaszÍme kivonatok a dokumentációból (12.1.0.2) a különféle típusú adatok Oracle-ben való tárolásáról.

CHAR adattípus
A CHAR adattípus egy rögzített hosszúságú karakterláncot határoz meg az adatbázis karakterkészletében. Az adatbázis karakterkészletét az adatbázis létrehozásakor adja meg. Az Oracle biztosítja, hogy a CHAR oszlopban tárolt összes érték a kiválasztott hosszúságú szemantikában a méret szerint meghatározott hosszúságú legyen. Ha az oszlophossznál rövidebb értéket szúr be, az Oracle az értéket az oszlop hosszához igazítja.

VARCHAR2 adattípus
A VARCHAR2 adattípus változó hosszúságú karakterláncot ad meg az adatbázis karakterkészletében. Az adatbázis karakterkészletét az adatbázis létrehozásakor adja meg. Az Oracle pontosan úgy tárolja a karakterértéket egy VARCHAR2 oszlopban, ahogyan Ön megadta, mindenféle üres kitöltés nélkül, feltéve, hogy az érték nem haladja meg az oszlop hosszát.

NUMBER adattípus
A SZÁM adattípus nulla, valamint pozitív és negatív fix számokat tárol 1.0 x 10-130 és 1.0 x 10126 közötti abszolút értékekkel. Ha olyan számtani kifejezést ad meg, amelynek abszolút értéke nagyobb vagy egyenlő 1.0 x 10126, akkor az Oracle hibát ad vissza. Minden NUMBER értékhez 1–22 bájt szükséges. Ezt figyelembe véve egy adott NUMBER(p) numerikus adatérték oszlopmérete bájtban, ahol p egy adott érték pontossága, a következő képlettel számítható ki: KEREK((hossz(p)+s)/2))+1 ahol s egyenlő nullával, ha a szám pozitív, és s egyenlő 1-gyel, ha a szám negatív.

Ezen kívül vegyünk egy részletet a Null értékek tárolásáról szóló dokumentációból.

A null az érték hiánya egy oszlopban. A nullák hiányzó, ismeretlen vagy nem alkalmazható adatokra utalnak. A nullákat a rendszer az adatbázisban tárolja, ha az adatértékeket tartalmazó oszlopok közé esik. Ezekben az esetekben 1 bájt szükséges az oszlop hosszának (nulla) tárolásához. A sor végén lévő nullák nem igényelnek tárhelyet, mert az új sorfejléc azt jelzi, hogy az előző sor többi oszlopa nulla. Például, ha egy tábla utolsó három oszlopa nulla, akkor ezekhez az oszlopokhoz nem kerül tárolásra adat.

Ezen adatok alapján építjük fel az érvelést. Feltételezzük, hogy az adatbázis AL32UTF8 kódolást használ. Ebben a kódolásban az orosz betűk 2 bájtot foglalnak el.

1) A és X, az a mező értéke 'Y' 1 bájtot vesz fel, az x mező értéke 'D' 2 bájtot
2) B és Y, „Vasya” a b-ben az érték legfeljebb 10 karakteres szóközökkel lesz kitöltve, és 14 bájtot vesz igénybe, a „Vasya” a d-ben pedig 8 bájtot.
3) C és K. Mindkét mező értéke NULL, utánuk jelentős mezők vannak, tehát 1 bájtot foglalnak el.
4) C és Z. Mindkét mező értéke NULL, de a Z mező az utolsó a táblázatban, így nem foglal helyet (0 bájt). A C mező 1 bájtot foglal el.
5) K és Z. Hasonló az előző esethez. A K mezőben lévő érték 1 bájtot foglal el, Z-ben 0.
6) I és J. A dokumentáció szerint mindkét érték 2 bájtot vesz igénybe. A hosszt a dokumentációból vett képlettel számítjuk ki: kerek( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J és X. A J mező értéke 2 bájt, az X mező értéke 2 bájt lesz.

Összességében a helyes opciók: C és K, I és J, J és X.

Körülbelül mekkora lesz a T_I index klaszterezési tényezője?

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

• Tíz körül
• Körülbelül több száz
• Körülbelül több ezer
• Körülbelül több tízezer

VálaszAz Oracle dokumentációja szerint (idézet a 12.1-ból):

B-fa index esetén az index klaszterezési tényező a sorok fizikai csoportosítását méri egy indexértékhez viszonyítva.

Az indexfürtözési tényező segít az optimalizálónak eldönteni, hogy bizonyos lekérdezések esetén az indexvizsgálat vagy a teljes táblavizsgálat hatékonyabb-e. Az alacsony klaszterezési tényező hatékony indexvizsgálatot jelez.

A táblában lévő blokkok számához közel álló klaszterezési tényező azt jelzi, hogy a sorok fizikailag az indexkulcs által vannak rendezve a táblázatblokkban. Ha az adatbázis teljes táblaellenőrzést hajt végre, akkor az adatbázis hajlamos lekérni a sorokat, amint azokat a lemezen tárolják, az indexkulcs szerint rendezve. A sorok számához közel álló klaszterezési tényező azt jelzi, hogy a sorok véletlenszerűen vannak szétszórva az adatbázis-blokkok között az indexkulcshoz képest. Ha az adatbázis teljes táblavizsgálatot végez, akkor az adatbázis nem kéri le a sorokat semmilyen rendezett sorrendben ezen indexkulcs szerint.

Ebben az esetben az adatok ideális sorrendben vannak rendezve, így a klaszterezési tényező megegyezik a táblázatban szereplő foglalt blokkok számával vagy ahhoz közel. Egy szabványos, 8 kilobyte-os blokkméretnél arra számíthatunk, hogy egy blokkba körülbelül ezer szűk számérték fog beleférni, így a blokkok száma, és ennek eredményeként a klaszterezési tényező körülbelül tíz.

Milyen N értékeinél fog sikeresen végrehajtani a következő szkriptet egy normál adatbázisban, szabványos beállításokkal?

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

VálaszAz Oracle dokumentációja szerint (idézet a 11.2-ból):

Logikai adatbázis korlátok

Tétel
Limit típusa
Határérték

Indexek
Az indexelt oszlop teljes mérete
Az adatbázis blokk méretének 75%-a mínusz némi rezsi

Így az indexelt oszlopok teljes mérete nem haladhatja meg a 6 Kb-ot. A következő lépés a kiválasztott alapkódolástól függ. AL32UTF8 kódolásnál egy karakter maximum 4 bájtot foglalhat el, így a legrosszabb esetben körülbelül 6 karakter fér bele 1500 kilobájtba. Ezért az Oracle nem fogja engedélyezni az index létrehozását N = 400 értéknél (amikor a legrosszabb kulcshossz 1600 karakter * 4 bájt + sorhosszúság), míg N = 200 (vagy kevesebb) esetén az index létrehozása problémamentesen fog működni.

Az INSERT operátor az APPEND tippel az adatok közvetlen módban történő betöltésére szolgál. Mi történik, ha arra az asztalra alkalmazzák, amelyen a kioldó lóg?

• Az adatok direkt módban töltődnek be, a trigger a várt módon fog működni
• Az adatok közvetlen módban töltődnek be, de a trigger nem kerül végrehajtásra
• Az adatok hagyományos módban töltődnek be, a trigger úgy fog működni, ahogy kell
• Az adatok hagyományos módban töltődnek be, de a trigger nem kerül végrehajtásra
• Az adatok nem töltődnek be, hiba kerül rögzítésre

VálaszEz alapvetően inkább logikai kérdés. A helyes válasz megtalálásához a következő érvelési modellt javaslom:

1. A közvetlen módban történő beillesztés egy adatblokk közvetlen kialakításával történik, az SQL motor megkerülésével, ami nagy sebességet biztosít. Így a trigger végrehajtásának biztosítása nagyon nehéz, ha nem lehetetlen, és ennek semmi értelme, hiszen így is radikálisan lelassítja a beillesztést.
2. A trigger végrehajtásának elmulasztása azt a tényt eredményezi, hogy ha a táblázatban szereplő adatok megegyeznek, az adatbázis egészének állapota (más táblák) attól függ, hogy milyen módban kerültek beillesztésre az adatok. Ez nyilvánvalóan tönkreteszi az adatok integritását, és nem alkalmazható megoldásként a termelésben.
3. A kért művelet végrehajtásának képtelenségét általában hibaként kezelik. De itt nem szabad elfelejtenünk, hogy az APPEND egy tipp, és a tippek általános logikája az, hogy lehetőség szerint figyelembe veszik őket, de ha nem, akkor az operátor végrehajtásra kerül anélkül, hogy figyelembe venné a tippet.

A várt válasz tehát az az adatok normál (SQL) módban töltődnek be, a trigger elindul.

Az Oracle dokumentációja szerint (idézet a 8.04-ból):

A korlátozások megsértése esetén az utasítás sorozatosan, a hagyományos beillesztési útvonalon, figyelmeztetések és hibaüzenetek nélkül fut le. Kivételt képez az a korlátozás, hogy az utasítások egy tranzakció során többször is hozzáférjenek ugyanahhoz a táblához, ami hibaüzeneteket okozhat.
Például, ha triggerek vagy hivatkozási integritás jelen vannak a táblán, akkor a rendszer figyelmen kívül hagyja az APPEND tippet, amikor megpróbálja használni a közvetlen terhelésű INSERT (soros vagy párhuzamos), valamint a PARALLEL tippet vagy záradékot, ha van ilyen.

Mi fog történni, ha a következő szkript fut?

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

• Sikeres végrehajtás
• Hiba szintaktikai hiba miatt
• Hiba: Az autonóm tranzakció nem érvényes
• A maximális hívásbeágyazás túllépésével kapcsolatos hiba
• Idegen kulcs megsértése hiba
• Zárakkal kapcsolatos hiba

VálaszA táblázat és a trigger teljesen helyesen van létrehozva, és ez a művelet nem vezethet problémákhoz. Egy triggerben az autonóm tranzakciók is megengedettek, különben például nem lenne lehetséges a naplózás.

Az első sor beszúrása után a sikeres trigger aktiválása a második sor beszúrását okozza, aminek következtében az eseményindító ismét aktiválódik, majd beszúr egy harmadik sort, és így tovább, amíg az utasítás meghiúsult a hívások maximális egymásba ágyazása miatt. Azonban egy másik finom szempont is szóba kerül. A trigger végrehajtásakor a véglegesítés még nem fejeződött be az első beillesztett rekordnál. Ezért egy autonóm tranzakcióban futó trigger megpróbál beszúrni a táblába egy olyan sort, amely egy idegen kulcsra hivatkozik egy még nem véglegesített rekordra. Ez várakozást eredményez (az autonóm tranzakció megvárja a fő tranzakció véglegesítését, hogy megnézze, be tud-e illeszteni adatokat), ugyanakkor a fő tranzakció arra vár, hogy az autonóm tranzakció tovább működjön a trigger után. Patthelyzet következik be, és ennek eredményeként az autonóm tranzakció a zárolásokkal kapcsolatos okok miatt törlődik.

A felmérésben csak regisztrált felhasználók vehetnek részt. Bejelentkezés, kérem.

Nehéz volt?

• Mint két ujjam, azonnal helyesen döntöttem el mindent.

• Nem igazán, tévedtem pár kérdésben.

• A felét helyesen megoldottam.

• Kétszer is sejtettem a választ!

• Megírom a kommentekben

14 felhasználó szavazott. 10 felhasználó tartózkodott.

Forrás: will.com