Highload++ Siberia 2019 – 8 Oracle'i ülesannet

Tere!

24.-25. juunil toimus Novosibiris konverents Highload++ Siberia 2019. Meie inimesed olid ka seal. ettekanne „Oracle’i konteinerandmebaasid (CDB/PDB) ja nende praktiline kasutamine tarkvara arendamisel“, paneme tekstiversiooni üles natuke hiljem. Oli äge, aitäh! olegbunin korraldamise eest ja ka kõigile, kes kohale tulid.

Selles postituses tahaksime jagada teiega ülesandeid, mis olid meie stendis, et saaksite kontrollida oma teadmisi Oracle'is. Allpool — 8 ülesannet, vastusevariandid ja selgitus.

Milline on maksimaalne väärtus, mida järjestuse täitmise tulemusena näeme järgmise skripti puhul?

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
• Ei mingit, tuleb viga.

VastusOracle'i dokumentatsiooni kohaselt (tsiteeritud versioonist 8.1.6):
Ühe SQL-lause piires suurendab Oracle järjestust ainult üks kord rea kohta. Kui laus sisaldab rohkem kui ühte viidet NEXTVAL-ile järjestusele, suurendab Oracle järjestust ühe korra ja tagastab sama väärtuse kõigi NEXTVAL-i esinemiste jaoks. Kui lauses on viidatud nii CURRVAL-ile kui NEXTVAL-ile, suurendab Oracle järjestust ja tagastab sama väärtuse nii CURRVAL-ile kui NEXTVAL-ile, sõltumata nende järjekorrast lauses.

Seega, maksimaalne väärtus vastab ridade arvule, st 5..

Kui palju ridu ilmub tabelis, kui käivitatakse järgmine 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

VastusOracle'i dokumentatsiooni kohaselt (tsiteeritud 11.2):

Enne SQL-lause täitmist märgib Oracle vaikimisi säästupunkte (teie jaoks mitte kättesaadavad). Kui lause nurjub, tagastab Oracle automaatselt vea ja tagasitõmbab selle SQLCODE-s SQLCA-sse. Näiteks, kui INSERT-lausung põhjustab vea, proovides sisestada dupliitse väärtuse unikaalsesse indeksisse, tagastatakse lausung.

Protseduuri (HP) kutse kliendilt käsitletakse samuti ühe lausena. Seega lõpetab esimene HP kutse edukalt, sisestades kolm kirjet; teine HP kutse lõpetab vea tõttu ja tagastab neljanda kirje, mille ta suutis sisestada; kolmas kutse lõpetatakse veaga, ja tabelis on kokku kolm kirjet..

Kui palju ridu ilmub tabelis, kui käivitatakse järgmine 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

VastusOracle'i dokumentatsiooni kohaselt (tsiteeritud 11.2):

Kontrolli piirang võimaldab teil määrata tingimuse, millele iga rea tabelis vastata peab. Piirangu täitmiseks peab iga rea tabelis olema tingimus, mis on kas TRUE või tundmatu (kuna see on null). Kui Oracle hindab kontrolli piirangu tingimust teatud rea kohta, viitavad tingimuses olevad veeru nimed selle rea veeru väärtustele.

Seega läbib nulli väärtus kontrolli, ja anonüümne plokk täidetakse edukalt kuni katsetamiseni sisestada väärtust 3. Pärast seda katkestab viga töödeldav plokk erandi, tagasikäiku ei toimu ning tabelis jääb neli rida väärtustega 1, null, 2 ja uuesti null.

Millised väärtuste paarid võtavad samasugust ruumi plokis?

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 ja X
• B ja Y
• C ja K
• C ja Z
• K ja Z
• I ja J
• J ja X
• Kõik loetletud

VastusToome välja lõike dokumentatsioonist (12.1.0.2) erinevate andmetüüpide säilitamisest Oracle'is.

CHAR andmetüüpi
CHAR andmetüüp määrab andmebaasi karakterite komplektis fikseeritud pikkusega tegelaste stringi. Te määrate andmebaasi karakterite komplekti, kui loote oma andmebaasi. Oracle tagab, et kõik väärtused, mis salvestatakse CHAR veerus, vastavad valitud pikkussemantika määratud pikkusele. Kui sisestate väärtuse, mis on lühem kui veeru pikkus, siis Oracle tõukab väärtust veeru pikkuse ulatuses.

VARCHAR2 andmetüüp
VARCHAR2 andmetüüp määratleb muutuva pikkusega tähemärgirea andmebaasi tähemärgisüsteemis. Andmebaasi tähemärgisüsteemi määrate andmebaasi loomisel. Oracle salvestab tähemärgi väärtuse VARCHAR2 veerus täpselt nii, nagu te selle määrate, ilma igasuguste tühikuteta, tingimusel et väärtus ei ületa veeru pikkust.

NUMBER andmetüüp
NUMBER andmetüüp salvestab nulli ning positiivseid ja negatiivseid fikseeritud numbreid absoluutväärtustega vahemikus 1,0 x 10-130 kuni, kuid mitte sealhulgas, 1,0 x 10^126. Kui määrate arvutuse, mille väärtus on absoluutväärtusega sama või suurem kui 1,0 x 10^126, siis tagastab Oracle vea. Iga NUMBER väärtus vajab 1 kuni 22 baiti. Arvestades seda, saab teatud numerilise andmeväärtuse NUMBER(p) jaoks veeru suurust bittides, kus p on antud väärtuse täpsus, arvutada järgmise valemi abil: ROUND((length(p)+s)/2))+1 kus s on null, kui number on positiivne, ja s on 1, kui number on negatiivne.

Lisaks toome välja lõike dokumentatsioonist Null-väärtuste salvestamise kohta.

Null on väärtuse puudumine veerus. Nullid näitavad puuduvat, tundmatut või kohaldamatut teavet. Nullid salvestatakse andmebaasi, kui need asuvad andmeväärtustega veergude vahel. Neil juhtudel vajavad nad 1 baiti veeru pikkuse (null) salvestamiseks. Rida nulliga lõppevad nullid ei vaja salvestamist, kuna uus rea päis annab märku, et eelmistel veergudel on null. Näiteks, kui tabeli viimased kolm veergu on null, siis nende veergude jaoks andmeid ei salvestata.

Arvestades neid andmeid, järeldame. Eeldame, et andmebaasis kasutatakse kodeeringut AL32UTF8. Selles kodeeringus võtavad vene tähed 2 baiti.

1) A ja X, väli a väärtus ‘Y’ võtab 1 bait, väli x väärtus ‘Д’ – 2 baiti
2) B ja Y, ‘Вася’ b väärtus täiendatakse tühikutega 10 sümbolini ja võtab 14 baiti, ‘Вася’ d – võtab 8 baiti.
3) C ja K. Mõlemad väljad on NULL väärtusega, pärast neid on tähenduslikud väljad, seega võtavad nad 1 baidi.
4) C ja Z. Mõlemad väljad on NULL väärtusega, kuid väli Z on tabelis viimane, seega ei võta see ruumi (0 baiti). Väli C võtab 1 baidi.
5) K ja Z. Sarnane eelnevaga. Väli K väärtus võtab 1 baidi, Z – 0.
6) I ja J. Dokumentatsiooni kohaselt võtavad mõlemad väärtused 2 baiti. Pikkuse arvutame dokumentatsioonist saadud valemi järgi: round( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J ja X. Väli J väärtus võtab 2 baiti, väli X väärtus võtab 2 baiti.

Kokkuvõttes on õiged variandid: C ja K, I ja J, J ja X.

Mis on umbkaudselt T_I indeksi clustering factor?

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

• Umbkaudu kümneid
• Umbkaudu sadu
• Umbkaudu tuhandeid
• Umbkaudu kümneid tuhandeid

VastusDokumentatsiooni kohaselt Oracle'ist (tsiteeritud 12.1):

B-puu indeksi puhul mõõdab indeksi clustering factor ridade füüsilist rühmitust seoses indeksi väärtusega.

Indeksiklastri faktor aitab optimeerijal otsustada, kas teatud päringute jaoks on efektiivsem indeksi skaneerimine või täielik tabeli skaneerimine. Madal klassifitseerimisfaktor näitab efektiivset indeksi skaneerimist.

Klasterfaktor, mis on lähedane tabeli plokkide arvule, näitab, et read on füüsiliselt tabeli plokkides indeksi võtme järgi järjekorras. Kui andmebaas teostab täisklastri skaneerimise, siis tavaliselt hankib andmebaas read nii, nagu need on salvestatud kettale, järjestatud indeksi võtme järgi. Klasterfaktor, mis on lähedane ridade arvule, tähendab, et read on andmebaasi plokkides indeksi võtme suhtes juhuslikult hajutatud. Kui andmebaas teostab täisklastri skaneerimise, ei hanki andmebaas ridu selle indeksi võtme järgi mingis sorteeritud järjekorras.

Antud juhul on andmed ideaalselt järjestatud, seega klassifitseerimisfaktor on või on lähedal tabeli hõivatud plokkide arvule. Tüüpilise ploki suuruse korral, mis on 8 kilobaiti, võib oodata, et ühte plokki mahub umbes tuhat kitsast number väärtust, seega plokkide arv ja seega klassifitseerimisfaktor on kümneid.

Milliste väärtuste N korral täidab järgmine skript edukalt tavalisest andmebaasist, millel on vaikeseaded?

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

VastusOracle'i dokumentatsiooni kohaselt (tsiteeritud 11.2):

Loogilised andmebaasi piirid

Esemed
Piiri tüüp
Piiri väärtus

Indeksid
Indekseeritud veeru kogumaht
75% andmebaasi ploki suurusest miinus mingi ülemäärane kulu

Seega ei tohi indekseeritavate veergude kogumahu suurus ületada 6 KB. Edasi oleneb valitud andmebaasi kodeeringust. Kodeeringu AL32UTF8 puhul võib üks sümbol võtta maksimaalselt 4 baiti, seega mahub 6 kilobaidi halvemal juhul umbes 1500 sümbolit. Seetõttu keelab Oracle indeksi loomise, kui N = 400 (kui võtme pikkus halvemal juhul on 1600 sümbolit * 4 baiti + rowid pikkus), samas kui N = 200 (ja vähem) indeksi loomine sujub probleemideta.

INSERT operaator koos APPEND vihjega on mõeldud andmete laadimiseks otse-režiimis. Mis juhtub, kui seda rakendada tabelile, millel on triger?

• Andmed laaditakse otse-režiimis, triger töötab nagu peab
• Andmed laaditakse otse-režiimis, kuid triger ei tööta
• Andmed laaditakse tavarežiimis, triger töötab nagu peab
• Andmed laaditakse tavarežiimis, kuid triger ei tööta
• Andmeid ei laadita, tekib viga

VastusPõhimõtteliselt on see küsimus rohkem loogika kohta. Õige vastuse leidmiseks soovitaksin järgmise mõtlemismudeli:

1. Andmete sisestamine otse režiimis toimub andmeploki otsese genereerimise teel, möödaminnes SQL-mootorist, mis tagab kõrge kiirus. Seetõttu on triki täitmine väga keeruline, kui üldse võimalik, ja sellel ei ole mõtet, kuna see aeglustab ikka veel sisestamist dramaatiliselt.
2. T‟rigri täitmata jätmine tähendab, et andmete sarnase sisestamise puhul sõltub kogu andmebaasi seisund (sealhulgas teised tabelid) olukorrast, millises režiimis need andmed sisestati. See rikub ilmselgelt andmete terviklikkust ja ei saa olla lahendus tootmises.
3. Taotletud operatsiooni teostamise võimatus tähistatakse üldiselt veana. Kuid siin tuleks meelde tuletada, et APPEND on vihje, ja vihjete üldine loogika on, et neid arvestatakse, kui see on võimalik; kui ei, siis teostatakse operaator ilma vihjet arvestamata.

Seetõttu on oodatav vastus – andmed laaditakse tavapärases (SQL) režiimis, trikk aktiveeritakse.

Oracle'i dokumentatsiooni kohaselt (tsiteeritud 8.04):

Piirangute rikkumised toovad kaasa lause järjestikuse täitmise konventsionaalsel sisestusteel, ilma hoiatusete või veateadeteta. Erandiks on piirangud, mis kehtivad lause puhul, mis pääseb samu tabeleid rohkem kui üks kord tehingus, mis võib põhjustada veateateid.
Näiteks, kui tabelil on olemas käivitajad või viidete terviklikkus, siis APPEND vihje eiratakse, kui proovite kasutada otsest laadimist INSERT (järjestikuline või paralleelne), samuti PARALLEL vihje või klausel, kui see on olemas.

Mis juhtub järgmise skripti täitmisel?

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

• Edukalt täidetud
• Viga sünaktakiga
• Viga, mis on seotud autonoomse tehingu keeldumisega
• Viga, mis on seotud maksimaalse kutse sügavause ületamisega
• Viga, mis on seotud välisvõtme rikkumisega
• Viga, mis on seotud lukustustega

VastusTabel ja käiviti luuakse täiesti korrektselt ja see toiming ei peaks probleeme tekitama. Käivitites on autonoomsed tehingud samuti lubatud, muidu oleks näiteks logimise tegemine võimatu.

Esimese rea sisestamise järel aktiveerib automaatne sündmus teise rea sisestamise, mistõttu aktiveerub taas sündmus, sisestades kolmanda rea ja nii edasi, kuni statement ebaõnnestub maksimaalse sisestuste sügavuse ületamise tõttu. Siiski, on veel üks peene nüanss. Esimese sisestatud kirje töötluse hetkel ei ole commit toimunud. Seega, autonoomses tehingus töötav sündmus püüab sisestada tabelisse rida, mis viitab välisvõtmega veel commiteerimata kirjele. See põhjustab ooteolukorra (autonoomne tehing ootab põhitehingu commit'i, et mõista, kas andmeid saab sisestada) ja samal ajal ootab põhitehing autonoomse commit'i lõpetamist, et jätkata tööd pärast sündmust. Tekkib deadlock ja selle tagajärjel – autonoomne tehing katkestatakse seoses lukustustega.

Ainult registreeritud kasutajad saavad küsitluses osaleda. Logige sisse, palun.

Kas see oli raske?

• Kaks sõrme, lahendasin kohe kõik õigesti.

• Ei olnud eriti, eksisin paaris küsimuses.

• Lahendasin poole õigesti.

• Kaks korda arvasin vastuse õigesti!

• Kirjutan kommentaaridesse

Hääletas 14 kasutajat. 10 kasutajat jäi erapooletuks.

Allikas: habr.com