Po stopinjah Highload++ Siberia 2019 - 8 nalog na Oracle

Lep pozdrav!

24. in 25. junija je v Novosibirsku potekala konferenca Highload++ Siberia 2019. Tam so bili tudi naši fantje poročilo “Oracle kontejnerske baze podatkov (CDB/PDB) in njihova praktična uporaba za razvoj programske opreme”, bomo besedilno različico objavili malo kasneje. Bilo je kul, hvala olegbunin za organizacijo, pa tudi vsem, ki ste prišli.

V tem prispevku bi radi z vami delili težave, ki smo jih imeli na naši stojnici, da bi lahko preizkusili svoje znanje Oracle. Pod rezom je 8 nalog, možnosti odgovora in razlaga.

Kakšna je največja vrednost zaporedja, ki jo bomo videli kot rezultat izvajanja naslednjega skripta?

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
• Ne, prišlo bo do napake

OdgovoriteGlede na dokumentacijo Oracle (citirano iz 8.1.6):
Znotraj posameznega stavka SQL bo Oracle povečal zaporedje samo enkrat na vrstico. Če stavek vsebuje več kot en sklic na NEXTVAL za zaporedje, Oracle enkrat poveča zaporedje in vrne isto vrednost za vse pojavitve NEXTVAL. Če stavek vsebuje sklice na CURRVAL in NEXTVAL, Oracle poveča zaporedje in vrne isto vrednost za CURRVAL in NEXTVAL ne glede na njun vrstni red v stavku.

Tako največja vrednost bo ustrezala številu vrstic, to je 5.

Koliko vrstic bo v tabeli zaradi izvajanja naslednjega skripta?

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

OdgovoriteGlede na dokumentacijo Oracle (citirano iz 11.2):

Pred izvedbo katerega koli stavka SQL Oracle označi implicitno točko shranjevanja (vam ni na voljo). Če stavek nato ne uspe, ga Oracle samodejno povrne in vrne veljavno kodo napake v SQLCODE v SQLCA. Na primer, če stavek INSERT povzroči napako, ker poskuša vstaviti podvojeno vrednost v enolični indeks, se stavek povrne nazaj.

Klicanje HP-ja s strani odjemalca se prav tako upošteva in obdela kot en sam stavek. Tako se prvi HP-jev klic uspešno zaključi z vstavitvijo treh zapisov; drugi klic HP se konča z napako in vrne četrti zapis, ki ga je uspelo vstaviti; tretji klic ne uspe, in v tabeli so trije zapisi.

Koliko vrstic bo v tabeli zaradi izvajanja naslednjega skripta?

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

OdgovoriteGlede na dokumentacijo Oracle (citirano iz 11.2):

Omejitev preverjanja vam omogoča, da določite pogoj, ki ga mora izpolnjevati vsaka vrstica v tabeli. Za izpolnitev omejitve mora biti za vsako vrstico v tabeli pogoj TRUE ali neznan (zaradi ničelne vrednosti). Ko Oracle ovrednoti pogoj kontrolne omejitve za določeno vrstico, se vsa imena stolpcev v pogoju nanašajo na vrednosti stolpcev v tej vrstici.

Tako bo vrednost null prestala preverjanje in anonimni blok se bo uspešno izvajal do poskusa vstavitve vrednosti 3. Po tem bo blok za obravnavo napak počistil izjemo, ne bo prišlo do povrnitve nazaj in v tabeli bodo ostale štiri vrstice z vrednostmi 1, null, 2 in spet null.

Kateri pari vrednosti bodo zavzeli enako količino prostora v bloku?

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 in X
• B in Y
• C in K
• C in Z
• K in Z
• jaz in J
• J in X
• Vsi navedeni

OdgovoriteTu so izvlečki iz dokumentacije (12.1.0.2) o shranjevanju različnih vrst podatkov v Oracle.

Vrsta podatkov CHAR
Podatkovni tip CHAR podaja znakovni niz s fiksno dolžino v naboru znakov baze podatkov. Nabor znakov baze podatkov določite, ko ustvarite bazo podatkov. Oracle zagotavlja, da imajo vse vrednosti, shranjene v stolpcu CHAR, dolžino, določeno z velikostjo v semantiki izbrane dolžine. Če vstavite vrednost, ki je krajša od dolžine stolpca, potem Oracle prazno vstavi vrednost v dolžino stolpca.

Vrsta podatkov VARCHAR2
Podatkovni tip VARCHAR2 podaja znakovni niz spremenljive dolžine v naboru znakov baze podatkov. Nabor znakov baze podatkov določite, ko ustvarite bazo podatkov. Oracle shrani vrednost znaka v stolpec VARCHAR2 točno tako, kot jo določite, brez kakršnega koli praznega oblazinjenja, pod pogojem, da vrednost ne presega dolžine stolpca.

Vrsta podatkov NUMBER
Podatkovni tip NUMBER shranjuje nič ter pozitivna in negativna fiksna števila z absolutnimi vrednostmi od 1.0 x 10-130 do 1.0 x 10126, vendar ne vključuje. Če podate aritmetični izraz, katerega vrednost ima absolutno vrednost večjo ali enako 1.0 x 10126, potem Oracle vrne napako. Vsaka vrednost NUMBER zahteva od 1 do 22 bajtov. Ob upoštevanju tega je mogoče velikost stolpca v bajtih za določeno vrednost številskega podatka ŠTEVILO(p), kjer je p natančnost dane vrednosti, izračunati z naslednjo formulo: OKROG((dolžina(p)+s)/2))+1 kjer je s enak nič, če je število pozitivno, s pa 1, če je število negativno.

Poleg tega vzemimo izvleček iz dokumentacije o shranjevanju vrednosti Null.

Nič je odsotnost vrednosti v stolpcu. Ničelne vrednosti pomenijo manjkajoče, neznane ali neuporabne podatke. Ničelne vrednosti so shranjene v bazi podatkov, če padejo med stolpce s podatkovnimi vrednostmi. V teh primerih potrebujejo 1 bajt za shranjevanje dolžine stolpca (nič). Končne ničle v vrstici ne zahtevajo shranjevanja, ker nova glava vrstice signalizira, da so preostali stolpci v prejšnji vrstici ničelni. Na primer, če so zadnji trije stolpci tabele ničelni, potem za te stolpce niso shranjeni nobeni podatki.

Na podlagi teh podatkov gradimo sklepanje. Predvidevamo, da zbirka podatkov uporablja kodiranje AL32UTF8. V tem kodiranju bodo ruske črke zasedle 2 bajta.

1) A in X, vrednost polja a 'Y' ima 1 bajt, vrednost polja x 'D' pa 2 bajta
2) B in Y, 'Vasya' v b bo vrednost obložena s presledki do 10 znakov in bo zajela 14 bajtov, 'Vasya' v d bo zavzela 8 bajtov.
3) C in K. Obe polji imata vrednost NULL, za njima so pomembna polja, torej zasedata 1 bajt.
4) C in Z. Obe polji imata vrednost NULL, vendar je polje Z zadnje v tabeli, zato ne zaseda prostora (0 bajtov). Polje C zavzema 1 bajt.
5) K in Z. Podobno kot v prejšnjem primeru. Vrednost v polju K zavzema 1 bajt, v Z – 0.
6) I in J. V skladu z dokumentacijo bosta obe vrednosti vzeli 2 bajta. Dolžino izračunamo po formuli, vzeti iz dokumentacije: krog( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J in X. Vrednost v polju J bo imela 2 bajta, vrednost v polju X pa 2 bajta.

Skupaj so pravilne možnosti: C in K, I in J, J in X.

Kakšen bo približno faktor združevanja indeksa 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);

• O desetinah
• Približno na stotine
• Približno na tisoče
• Približno deset tisoč

OdgovoriteGlede na dokumentacijo Oracle (citirano iz 12.1):

Za indeks B-drevesa faktor združevanja indeksa meri fizično združevanje vrstic v skupine glede na vrednost indeksa.

Faktor združevanja indeksov pomaga optimizatorju pri odločitvi, ali je za določene poizvedbe učinkovitejši pregled indeksa ali pregled celotne tabele). Nizek faktor združevanja v gruče kaže na učinkovito pregledovanje indeksa.

Faktor združevanja v gruče, ki je blizu številu blokov v tabeli, kaže, da so vrstice v blokih tabele fizično razvrščene po ključu indeksa. Če baza podatkov izvede pregled celotne tabele, potem baza podatkov teži k pridobivanju vrstic, ko so shranjene na disku, razvrščene po ključu indeksa. Faktor združevanja v gruče, ki je blizu števila vrstic, kaže, da so vrstice naključno razpršene po blokih baze podatkov glede na ključ indeksa. Če zbirka podatkov izvede pregled celotne tabele, potem baza podatkov ne bo pridobila vrstic v nobenem razvrščenem vrstnem redu po tem ključu indeksa.

V tem primeru so podatki idealno razvrščeni, tako da bo faktor združevanja enak ali blizu števila zasedenih blokov v tabeli. Za standardno velikost bloka 8 kilobajtov lahko pričakujete, da se bo približno tisoč ozkih številskih vrednosti prilegalo enemu bloku, tako da bo število blokov in posledično faktor združevanja v gruče približno desetine.

Pri katerih vrednostih N bo naslednji skript uspešno izveden v običajni bazi podatkov s standardnimi nastavitvami?

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

OdgovoriteGlede na dokumentacijo Oracle (citirano iz 11.2):

Omejitve logične baze podatkov

Postavka
Vrsta limita
Mejna vrednost

Kazala
Skupna velikost indeksiranega stolpca
75 % velikosti bloka baze podatkov minus nekaj režijskih stroškov

Tako skupna velikost indeksiranih stolpcev ne sme presegati 6Kb. Kaj se zgodi potem, je odvisno od izbranega osnovnega kodiranja. Za kodiranje AL32UTF8 lahko en znak zasede največ 4 bajte, tako da bo v najslabšem primeru približno 6 znakov ustrezalo 1500 kilobajtom. Zato bo Oracle onemogočil ustvarjanje indeksa pri N = 400 (ko je dolžina ključa v najslabšem primeru 1600 znakov * 4 bajte + dolžina rowid), medtem ko pri N = 200 (ali manj) ustvarjanje indeksa bo delovalo brez težav.

Operator INSERT z namigom APPEND je zasnovan za nalaganje podatkov v neposrednem načinu. Kaj se zgodi, če ga nanesemo na mizo, na kateri visi sprožilec?

• Podatki bodo naloženi v neposrednem načinu, sprožilec bo deloval po pričakovanjih
• Podatki bodo naloženi v neposrednem načinu, vendar se sprožilec ne bo izvedel
• Podatki bodo naloženi v običajnem načinu, sprožilec bo deloval kot mora
• Podatki bodo naloženi v običajnem načinu, vendar se sprožilec ne bo izvedel
• Podatki se ne bodo naložili, zabeležena bo napaka

OdgovoriteV bistvu je to bolj vprašanje logike. Da bi našli pravilen odgovor, bi predlagal naslednji model razmišljanja:

1. Vstavljanje v neposrednem načinu poteka z neposrednim oblikovanjem podatkovnega bloka, mimo SQL motorja, kar zagotavlja visoko hitrost. Tako je zagotoviti izvedbo sprožilca zelo težko, če ne nemogoče, pa tudi nima smisla, saj bo še vedno radikalno upočasnilo vstavljanje.
2. Če sprožilca ne izvedete, bo stanje baze podatkov kot celote (drugih tabel) odvisno od načina, v katerem so bili ti podatki vstavljeni, če so podatki v tabeli enaki. To bo očitno uničilo celovitost podatkov in ga ni mogoče uporabiti kot rešitev v proizvodnji.
3. Nezmožnost izvedbe zahtevane operacije se na splošno obravnava kot napaka. Tukaj pa ne smemo pozabiti, da je APPEND namig in splošna logika namigov je, da se upoštevajo, če je mogoče, če pa ne, se operator izvede brez upoštevanja namiga.

Pričakovan odgovor je torej bodo podatki naloženi v običajnem (SQL) načinu, sprožilec se bo sprožil.

Glede na dokumentacijo Oracle (citirano iz 8.04):

Kršitve omejitev bodo povzročile serijsko izvajanje stavka z uporabo običajne poti vstavljanja, brez opozoril ali sporočil o napakah. Izjema je omejitev stavkov, ki v transakciji večkrat dostopajo do iste tabele, kar lahko povzroči sporočila o napakah.
Na primer, če so v tabeli prisotni sprožilci ali referenčna celovitost, bo namig APPEND prezrt, ko poskusite uporabiti INSERT z neposrednim nalaganjem (serijski ali vzporedni), kot tudi namig ali člen PARALLEL, če obstaja.

Kaj se bo zgodilo, ko se izvede naslednji skript?

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

• Uspešen zaključek
• Napaka zaradi sintaksne napake
• Napaka: avtonomna transakcija ni veljavna
• Napaka, povezana s prekoračitvijo največjega ugnezdenja klicev
• Napaka kršitve tujega ključa
• Napaka v zvezi s ključavnicami

OdgovoriteTabela in sprožilec sta ustvarjena povsem pravilno in ta operacija ne bi smela povzročiti težav. Dovoljene so tudi avtonomne transakcije v sprožilcu, sicer beleženje na primer ne bi bilo mogoče.

Po vstavitvi prve vrstice bi uspešno sprožitev sprožilca povzročila vstavitev druge vrstice, zaradi česar bi se sprožilec znova sprožil, vstavil tretjo vrstico in tako naprej, dokler izjava ne bi uspela zaradi prekoračitve največjega ugnezdenja klicev. Vendar pride v poštev še ena subtilna točka. V času, ko se sprožilec izvede, potrditev še ni bila dokončana za prvi vstavljeni zapis. Zato sprožilec, ki se izvaja v avtonomni transakciji, poskuša v tabelo vstaviti vrstico, ki se sklicuje na tuji ključ do zapisa, ki še ni bil potrjen. Posledica tega je čakanje (avtonomna transakcija čaka, da se glavna transakcija zaveže, da vidi, ali lahko vstavi podatke), hkrati pa glavna transakcija čaka, da avtonomna transakcija nadaljuje z delom po sprožilcu. Pride do zastoja in posledično je avtonomna transakcija preklicana zaradi razlogov, povezanih z zaklepanji.

V anketi lahko sodelujejo samo registrirani uporabniki. Prijaviti se, prosim.

Je bilo težko?

• Kot dva prsta sem se takoj odločil vse pravilno.

• Ne res, zmotil sem se pri par vprašanjih.

• Polovico sem pravilno rešil.

• Dvakrat sem uganil odgovor!

• Napisal bom v komentarjih

Glasovalo je 14 uporabnikov. 10 uporabnikov se je vzdržalo.

Vir: www.habr.com