ProHoster > Blog > podávání > Po stopách Highload++ Siberia 2019 - 8 úkolů na Oracle

Po stopách Highload++ Siberia 2019 - 8 úkolů na Oracle

Ahoj!

Ve dnech 24. – 25. června se v Novosibirsku konala konference Highload++ Siberia 2019. Byli tam i naši kluci zpráva „Kontejnerové databáze Oracle (CDB/PDB) a jejich praktické využití pro vývoj softwaru“, textovou verzi zveřejníme o něco později. Bylo to v pohodě, díky olegbunin za organizaci, stejně jako všem, kteří přišli.

Po stopách Highload++ Siberia 2019 - 8 úkolů na Oracle
V tomto příspěvku bychom se s vámi rádi podělili o problémy, které jsme měli na našem stánku, abyste si mohli vyzkoušet své znalosti Oracle. Pod řezem je 8 problémů, možnosti odpovědí a vysvětlení.

Jaká je maximální hodnota sekvence, kterou uvidíme jako výsledek provedení následujícího skriptu?

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, dojde k chybě

odpověďPodle dokumentace Oracle (citováno z 8.1.6):
V rámci jednoho příkazu SQL bude Oracle zvyšovat sekvenci pouze jednou na řádek. Pokud příkaz obsahuje více než jeden odkaz na NEXTVAL pro sekvenci, Oracle zvýší sekvenci jednou a vrátí stejnou hodnotu pro všechny výskyty NEXTVAL. Pokud příkaz obsahuje odkazy na CURRVAL i NEXTVAL, Oracle zvýší sekvenci a vrátí stejnou hodnotu pro CURRVAL i NEXTVAL bez ohledu na jejich pořadí v příkazu.

To znamená, že maximální hodnota bude odpovídat počtu řádků, tedy 5.

Kolik řádků bude v tabulce v důsledku spuštění následujícího skriptu?

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

odpověďPodle dokumentace Oracle (citováno z 11.2):

Před provedením jakéhokoli příkazu SQL označí Oracle implicitní bod uložení (není vám k dispozici). Pokud pak příkaz selže, Oracle jej automaticky vrátí zpět a vrátí příslušný chybový kód do SQLCODE v SQLCA. Pokud například příkaz INSERT způsobí chybu tím, že se pokusí vložit duplicitní hodnotu do jedinečného indexu, bude příkaz odvolán.

Volání HP od klienta je také považováno a zpracováváno jako jediný výpis. První volání HP se tedy úspěšně dokončí po vložení tří záznamů; druhé volání HP skončí chybou a vrátí čtvrtý záznam, který se mu podařilo vložit; třetí hovor selže, a v tabulce jsou tři záznamy.

Kolik řádků bude v tabulce v důsledku spuštění následujícího skriptu?

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

odpověďPodle dokumentace Oracle (citováno z 11.2):

Kontrolní omezení vám umožňuje zadat podmínku, kterou musí splňovat každý řádek v tabulce. Aby bylo toto omezení splněno, musí každý řádek v tabulce nastavit podmínku buď na hodnotu TRUE, nebo na neznámou (kvůli nule). Když Oracle vyhodnotí podmínku kontroly pro konkrétní řádek, všechny názvy sloupců v podmínce odkazují na hodnoty sloupců v daném řádku.

Hodnota null tedy projde kontrolou a anonymní blok bude úspěšně proveden až do pokusu o vložení hodnoty 3. Poté blok zpracování chyb vymaže výjimku, nedojde k žádnému vrácení a v tabulce zůstanou čtyři řádky s hodnotami 1, null, 2 a znovu null.

Které dvojice hodnot zaberou v bloku stejné množství místa?

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 a X
  • B a Y
  • C a K
  • C a Z
  • K a Z
  • Já a J
  • J a X
  • Všechny uvedené

odpověďZde jsou výňatky z dokumentace (12.1.0.2) o ukládání různých typů dat v Oracle.

Typ dat CHAR
Datový typ CHAR určuje znakový řetězec pevné délky ve znakové sadě databáze. Znakovou sadu databáze zadáváte při vytváření databáze. Oracle zajišťuje, že všechny hodnoty uložené ve sloupci CHAR mají délku určenou velikostí ve vybrané sémantice délky. Pokud vložíte hodnotu, která je kratší než délka sloupce, Oracle tuto hodnotu doplní na délku sloupce.

Typ dat VARCHAR2
Datový typ VARCHAR2 určuje znakový řetězec proměnné délky ve znakové sadě databáze. Znakovou sadu databáze zadáváte při vytváření databáze. Oracle ukládá hodnotu znaku do sloupce VARCHAR2 přesně tak, jak ji určíte, bez jakýchkoli mezer, za předpokladu, že hodnota nepřesahuje délku sloupce.

NUMBER Typ dat
Datový typ NUMBER ukládá nulu a také kladná a záporná pevná čísla s absolutními hodnotami od 1.0 x 10-130 do 1.0 x 10126, ale bez zahrnutí. Pokud zadáte aritmetický výraz, jehož hodnota má absolutní hodnotu větší nebo rovnou 1.0 x 10126, pak Oracle vrátí chybu. Každá hodnota NUMBER vyžaduje 1 až 22 bajtů. S ohledem na to lze velikost sloupce v bajtech pro konkrétní číselnou datovou hodnotu NUMBER(p), kde p je přesnost dané hodnoty, vypočítat pomocí následujícího vzorce: ROUND((délka(p)+s)/2))+1 kde s se rovná nule, pokud je číslo kladné, a s se rovná 1, pokud je číslo záporné.

Kromě toho si vezměme výňatek z dokumentace o ukládání hodnot Null.

Nula je nepřítomnost hodnoty ve sloupci. Nuly označují chybějící, neznámá nebo nepoužitelná data. Null jsou uloženy v databázi, pokud spadají mezi sloupce s datovými hodnotami. V těchto případech vyžadují 1 bajt pro uložení délky sloupce (nuly). Koncové hodnoty null v řádku nevyžadují žádné úložiště, protože nové záhlaví řádku signalizuje, že zbývající sloupce v předchozím řádku jsou prázdné. Pokud jsou například poslední tři sloupce tabulky prázdné, nebudou pro tyto sloupce uložena žádná data.

Na základě těchto údajů budujeme uvažování. Předpokládáme, že databáze používá kódování AL32UTF8. V tomto kódování budou ruská písmena zabírat 2 bajty.

1) A a X, hodnota pole a 'Y' zabírá 1 bajt, hodnota pole x 'D' zabírá 2 bajty
2) B a Y, 'Vasya' v b hodnota bude doplněna mezerami o délce až 10 znaků a bude trvat 14 bajtů, 'Vasya' v d bude trvat 8 bajtů.
3) C a K. Obě pole mají hodnotu NULL, za nimi jsou významná pole, takže zabírají 1 byte.
4) C a Z. Obě pole mají hodnotu NULL, ale pole Z je poslední v tabulce, takže nezabírá místo (0 bajtů). Pole C zabírá 1 bajt.
5) K a Z. Podobně jako v předchozím případě. Hodnota v poli K zabírá 1 bajt, v Z – 0.
6) I a J. Podle dokumentace budou obě hodnoty trvat 2 bajty. Délku vypočítáme pomocí vzorce převzatého z dokumentace: round( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J a X. Hodnota v poli J bude trvat 2 bajty, hodnota v poli X bude trvat 2 bajty.

Celkem jsou správné možnosti: C a K, I a J, J a X.

Jaký přibližně bude shlukovací faktor indexu 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);

  • Asi desítky
  • Asi stovky
  • O tisících
  • Asi desetitisíce

odpověďPodle dokumentace Oracle (citováno z 12.1):

U indexu B-stromu měří faktor shlukování indexu fyzické seskupení řádků ve vztahu k hodnotě indexu.

Faktor klastrování indexu pomáhá optimalizátoru rozhodnout, zda je pro určité dotazy efektivnější prohledávání indexu nebo úplné prohledávání tabulky). Nízký faktor shlukování označuje efektivní prohledávání indexů.

Faktor shlukování, který se blíží počtu bloků v tabulce, označuje, že řádky jsou fyzicky seřazeny v blocích tabulky podle indexového klíče. Pokud databáze provádí úplné skenování tabulky, má databáze tendenci načítat řádky tak, jak jsou uloženy na disku seřazené podle indexového klíče. Faktor shlukování, který se blíží počtu řádků, označuje, že řádky jsou náhodně rozptýleny po blocích databáze ve vztahu k indexovému klíči. Pokud databáze provede úplné prohledávání tabulky, pak databáze nenačte řádky v žádném seřazeném pořadí podle tohoto indexového klíče.

V tomto případě jsou data ideálně setříděna, takže shlukovací faktor bude roven nebo blízko počtu obsazených bloků v tabulce. Pro standardní velikost bloku 8 kilobajtů můžete očekávat, že do jednoho bloku se vejde asi tisíc úzkých číselných hodnot, takže počet bloků a v důsledku toho bude shlukovací faktor asi desítky.

Při jakých hodnotách N bude následující skript úspěšně proveden v běžné databázi se standardním nastavením?

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

odpověďPodle dokumentace Oracle (citováno z 11.2):

Logické limity databáze

Položka
Typ limitu
Limitní hodnota

Indexy
Celková velikost indexovaného sloupce
75 % velikosti bloku databáze mínus nějaká režie

Celková velikost indexovaných sloupců by tedy neměla přesáhnout 6 kB. Co se stane dále, závisí na zvoleném základním kódování. U kódování AL32UTF8 může jeden znak zabírat maximálně 4 bajty, takže v nejhorším případě se do 6 kilobajtů vejde asi 1500 znaků. Proto Oracle zakáže vytvoření indexu při N = 400 (když je délka klíče v nejhorším případě 1600 znaků * 4 bajty + délka rowid), zatímco při N = 200 (nebo méně) vytvoření indexu bude fungovat bez problémů.

Operátor INSERT s nápovědou APPEND je určen k načítání dat v přímém režimu. Co se stane, když se aplikuje na stůl, na kterém spoušť visí?

  • Data se načtou v přímém režimu, spoušť bude fungovat podle očekávání
  • Data budou načtena v přímém režimu, ale spoušť se neprovede
  • Data se načtou v konvenčním režimu, spoušť bude fungovat podle očekávání
  • Data budou načtena v konvenčním režimu, ale spoušť nebude provedena
  • Data nebudou načtena, bude zaznamenána chyba

odpověďV zásadě je to spíše otázka logiky. Pro nalezení správné odpovědi bych navrhl následující model uvažování:

  1. Vkládání v přímém režimu se provádí přímou tvorbou datového bloku, obchází SQL engine, což zajišťuje vysokou rychlost. Zajištění provedení spouště je tedy velmi obtížné, ne-li nemožné, a nemá to smysl, protože to stejně radikálně zpomalí zasunutí.
  2. Neprovedení triggeru povede k tomu, že pokud jsou data v tabulce stejná, bude stav databáze jako celku (ostatních tabulek) záviset na režimu, ve kterém byla tato data vložena. To samozřejmě zničí integritu dat a nelze to použít jako řešení ve výrobě.
  3. Neschopnost provést požadovanou operaci je obecně považována za chybu. Zde bychom si ale měli pamatovat, že APPEND je nápověda a obecná logika nápověd je taková, že pokud je to možné, berou se v úvahu, ale pokud ne, operátor se provede bez zohlednění nápovědy.

Takže očekávaná odpověď je data se načtou v normálním (SQL) režimu, spoušť se spustí.

Podle dokumentace Oracle (citováno z 8.04):

Porušení omezení způsobí, že se příkaz spustí sériově pomocí konvenční cesty pro vkládání bez varování nebo chybových zpráv. Výjimkou je omezení příkazů přistupujících ke stejné tabulce více než jednou v transakci, což může způsobit chybová hlášení.
Pokud jsou například v tabulce přítomny spouštěče nebo referenční integrita, bude nápověda APPEND ignorována, když se pokusíte použít INSERT s přímým načtením (sériové nebo paralelní), stejně jako nápověda nebo klauzule PARALLEL, pokud existují.

Co se stane, když se spustí následující 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);

  • Úspěšné provedení
  • Selhání kvůli chybě syntaxe
  • Chyba: Autonomní transakce není platná
  • Chyba související s překročením maximálního počtu vnoření hovorů
  • Chyba porušení cizího klíče
  • Chyba související se zámky

odpověďTabulka a spoušť jsou vytvořeny zcela správně a tato operace by neměla vést k problémům. Jsou povoleny i autonomní transakce ve spouštěči, jinak by například nebylo možné protokolování.

Po vložení prvního řádku by úspěšné spuštění triggeru způsobilo vložení druhého řádku, což by způsobilo opětovné spuštění triggeru, vložení třetího řádku a tak dále, dokud příkaz selhal kvůli překročení maximálního vnoření volání. Do hry však vstupuje další jemný bod. V době, kdy je spouštěč spuštěn, nebylo potvrzení pro první vložený záznam ještě dokončeno. Spouštěč spuštěný v autonomní transakci se proto pokusí vložit do tabulky řádek, který odkazuje na cizí klíč na záznam, který ještě nebyl potvrzen. To má za následek čekání (autonomní transakce čeká na potvrzení hlavní transakce, aby zjistila, zda může vložit data) a zároveň hlavní transakce čeká, až bude autonomní transakce po spuštění pokračovat v práci. Dojde k uváznutí a v důsledku toho je autonomní transakce zrušena z důvodů souvisejících se zámky.

Průzkumu se mohou zúčastnit pouze registrovaní uživatelé. Přihlásit se, prosím.

Bylo to těžké?

  • Jako dva prsty jsem hned rozhodl vše správně.

  • Vlastně ne, v několika otázkách jsem se mýlil.

  • Polovinu jsem vyřešil správně.

  • Odpověď jsem hádal dvakrát!

  • Napíšu do komentářů

Hlasovalo 14 uživatelů. 10 uživatelů se zdrželo hlasování.

Zdroj: www.habr.com

Přidat komentář