ProHoster > Блог > администрация > Следвайки стъпките на Highload++ Siberia 2019 - 8 задачи на Oracle

Следвайки стъпките на Highload++ Siberia 2019 - 8 задачи на Oracle

Привет!

На 24-25 юни в Новосибирск се проведе конференцията Highload++ Siberia 2019. Нашите момчета също бяха там доклад „Оракул контейнерни бази данни (CDB/PDB) и тяхното практическо използване за разработка на софтуер“, малко по-късно ще публикуваме текстова версия. Беше страхотно, благодаря олегбунин за организацията, както и на всички дошли.

Следвайки стъпките на Highload++ Siberia 2019 - 8 задачи на Oracle
В тази публикация бихме искали да споделим с вас проблемите, които имахме на нашия щанд, за да можете да тествате знанията си за Oracle. Под разреза има 8 задачи, варианти за отговор и обяснение.

Каква е максималната стойност на последователността, която ще видим в резултат на изпълнение на следния скрипт?

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
  • Не, ще има грешка

ОтговарямСпоред документацията на Oracle (цитирано от 8.1.6):
В рамките на един SQL оператор Oracle ще увеличи последователността само веднъж на ред. Ако изразът съдържа повече от една препратка към NEXTVAL за последователност, Oracle увеличава последователността веднъж и връща същата стойност за всички срещания на NEXTVAL. Ако изразът съдържа препратки както към CURRVAL, така и към NEXTVAL, Oracle увеличава последователността и връща същата стойност както за CURRVAL, така и към NEXTVAL, независимо от техния ред в оператора.

По този начин, максималната стойност ще съответства на броя на редовете, тоест 5.

Колко реда ще има в таблицата в резултат на изпълнението на следния скрипт?

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

ОтговарямСпоред документацията на Oracle (цитирано от 11.2):

Преди да изпълни който и да е SQL израз, Oracle маркира имплицитна точка за запис (не е достъпна за вас). След това, ако изразът е неуспешен, Oracle го връща автоматично и връща приложимия код на грешка на SQLCODE в SQLCA. Например, ако оператор INSERT причини грешка, като се опитва да вмъкне дублирана стойност в уникален индекс, операторът се връща обратно.

Извикването на HP от клиента също се разглежда и обработва като единична декларация. По този начин първото повикване на HP завършва успешно, като е вмъкнало три записа; второто повикване на HP завършва с грешка и връща четвъртия запис, който успя да вмъкне; третото обаждане е неуспешно, и в таблицата има три записа.

Колко реда ще има в таблицата в резултат на изпълнението на следния скрипт?

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

ОтговарямСпоред документацията на Oracle (цитирано от 11.2):

Ограничението за проверка ви позволява да зададете условие, на което трябва да отговаря всеки ред в таблицата. За да удовлетвори ограничението, всеки ред в таблицата трябва да прави условието TRUE или unknown (поради нула). Когато Oracle оценява условие за ограничение за проверка за определен ред, всички имена на колони в условието се отнасят до стойностите на колоните в този ред.

По този начин стойността null ще премине проверката и анонимният блок ще бъде изпълнен успешно до опит за вмъкване на стойност 3. След това блокът за обработка на грешки ще изчисти изключението, няма да настъпи връщане назад и ще останат четири реда в таблицата със стойности 1, нула, 2 и отново нула.

Кои двойки стойности ще заемат еднакво място в блока?

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, 'Д', 'Вася');

  • А и Х
  • Б и У
  • С и К
  • C и Z
  • К и З
  • аз и Дж
  • J и X
  • Всички изброени

ОтговарямЕто извадки от документацията (12.1.0.2) за съхраняване на различни видове данни в Oracle.

Тип данни CHAR
Типът данни CHAR указва символен низ с фиксирана дължина в набора от знаци на базата данни. Вие определяте набора от символи на базата данни, когато създавате вашата база данни. Oracle гарантира, че всички стойности, съхранени в колона CHAR, имат дължината, определена от размера в семантиката на избраната дължина. Ако вмъкнете стойност, която е по-къса от дължината на колоната, тогава Oracle празно допълва стойността до дължината на колоната.

Тип данни VARCHAR2
Типът данни VARCHAR2 указва символен низ с променлива дължина в набора от знаци на базата данни. Вие определяте набора от символи на базата данни, когато създавате вашата база данни. Oracle съхранява символна стойност в колона VARCHAR2 точно така, както сте я посочили, без празно допълване, при условие че стойността не надвишава дължината на колоната.

Тип данни NUMBER
Типът данни NUMBER съхранява нула, както и положителни и отрицателни фиксирани числа с абсолютни стойности от 1.0 x 10-130 до, но без да включва 1.0 x 10126. Ако зададете аритметичен израз, чиято стойност има абсолютна стойност, по-голяма или равна на 1.0 x 10126, тогава Oracle връща грешка. Всяка стойност NUMBER изисква от 1 до 22 байта. Като се вземе това предвид, размерът на колоната в байтове за определена цифрова стойност на данните NUMBER(p), където p е точността на дадена стойност, може да се изчисли по следната формула: КРЪГ((дължина(p)+s)/2))+1 където s е равно на нула, ако числото е положително, и s е равно на 1, ако числото е отрицателно.

В допълнение, нека вземем извадка от документацията за съхраняване на стойности Null.

Нула е липсата на стойност в колона. Нулите означават липсващи, неизвестни или неприложими данни. Нулите се съхраняват в базата данни, ако попаднат между колони със стойности на данни. В тези случаи те изискват 1 байт за съхраняване на дължината на колоната (нула). Крайните нули в ред не изискват съхранение, тъй като заглавката на нов ред сигнализира, че останалите колони в предишния ред са нулеви. Например, ако последните три колони на таблица са нулеви, тогава не се съхраняват данни за тези колони.

Въз основа на тези данни ние изграждаме разсъждения. Предполагаме, че базата данни използва кодиране AL32UTF8. В това кодиране руските букви ще заемат 2 байта.

1) A и X, стойността на поле a 'Y' отнема 1 байт, стойността на поле x 'D' отнема 2 байта
2) B и Y, 'Vasya' в b стойността ще бъде подплатена с интервали до 10 знака и ще заема 14 байта, 'Vasya' в d ще заема 8 байта.
3) C и K. И двете полета имат стойност NULL, след тях има значими полета, така че те заемат 1 байт.
4) C и Z. И двете полета имат стойност NULL, но полето Z е последното в таблицата, така че не заема място (0 байта). Поле C заема 1 байт.
5) K и Z. Подобно на предишния случай. Стойността в полето K заема 1 байт, в Z – 0.
6) I и J. Според документацията и двете стойности ще заемат 2 байта. Изчисляваме дължината, като използваме формулата, взета от документацията: кръг ((1 + 0)/2) +1 = 1 + 1 = 2.
7) J и X. Стойността в полето J ще заема 2 байта, стойността в полето X ще заема 2 байта.

Общо правилните опции са: C и K, I и J, J и X.

Какъв приблизително ще бъде факторът на групиране на индекса 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);

  • Около десетки
  • Около стотици
  • Около хиляди
  • Около десетки хиляди

ОтговарямСпоред документацията на Oracle (цитирано от 12.1):

За индекс на B-дърво коефициентът на групиране на индекс измерва физическото групиране на редове по отношение на стойност на индекс.

Коефициентът на клъстериране на индекси помага на оптимизатора да реши дали сканирането на индекс или пълно сканиране на таблица е по-ефективно за определени заявки). Ниският коефициент на клъстериране показва ефективно сканиране на индекса.

Коефициент на клъстериране, който е близък до броя на блоковете в таблица, показва, че редовете са физически подредени в блоковете на таблицата чрез индексния ключ. Ако базата данни извършва пълно сканиране на таблица, тогава базата данни има тенденция да извлича редовете, тъй като те се съхраняват на диска, сортирани по индексния ключ. Коефициент на клъстериране, който е близък до броя на редовете, показва, че редовете са разпръснати на случаен принцип в блоковете на базата данни по отношение на индексния ключ. Ако базата данни извърши пълно сканиране на таблица, тогава базата данни няма да извлече редове в никакъв сортиран ред по този ключ на индекса.

В този случай данните са идеално сортирани, така че коефициентът на групиране ще бъде равен или близък до броя на заетите блокове в таблицата. За стандартен размер на блока от 8 килобайта, можете да очаквате, че около хиляда тесни числови стойности ще се поберат в един блок, така че броят на блоковете и в резултат факторът на клъстериране ще бъде около десетки.

При какви стойности на N следният скрипт ще бъде изпълнен успешно в обикновена база данни със стандартни настройки?

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

ОтговарямСпоред документацията на Oracle (цитирано от 11.2):

Ограничения на логическата база данни

Точка
Тип лимит
Гранична стойност

Индекси
Общ размер на индексираната колона
75% от размера на блока на базата данни минус някои режийни разходи

По този начин общият размер на индексираните колони не трябва да надвишава 6Kb. Какво ще се случи след това зависи от избраното базово кодиране. За кодиране AL32UTF8 един знак може да заема максимум 4 байта, така че в най-лошия случай 6 килобайта ще се поберат в около 1500 знака. Следователно Oracle ще забрани създаването на индекс при N = 400 (когато дължината на ключа в най-лошия случай е 1600 знака * 4 байта + дължина на rowid), докато при N = 200 (или по-малко) създаването на индекса ще работи без проблеми.

Операторът INSERT с подсказката APPEND е предназначен за зареждане на данни в директен режим. Какво се случва, ако се приложи върху масата, на която виси тригерът?

  • Данните ще бъдат заредени в директен режим, тригерът ще работи според очакванията
  • Данните ще бъдат заредени в директен режим, но тригерът няма да бъде изпълнен
  • Данните ще бъдат заредени в конвенционален режим, тригерът ще работи според очакванията
  • Данните ще бъдат заредени в конвенционален режим, но тригерът няма да бъде изпълнен
  • Данните няма да бъдат заредени, ще бъде записана грешка

ОтговарямПо принцип това е по-скоро въпрос на логика. За да намеря правилния отговор, бих предложил следния модел на разсъждение:

  1. Вмъкването в директен режим се извършва чрез директно формиране на блок от данни, заобикаляйки SQL машината, което осигурява висока скорост. По този начин осигуряването на изпълнението на спусъка е много трудно, ако не и невъзможно, и няма смисъл от това, тъй като все още радикално ще забави вмъкването.
  2. Неуспешното изпълнение на тригера ще доведе до факта, че ако данните в таблицата са еднакви, състоянието на базата данни като цяло (други таблици) ще зависи от режима, в който тези данни са били въведени. Това очевидно ще унищожи целостта на данните и не може да се приложи като решение в производството.
  3. Невъзможността за извършване на исканата операция обикновено се третира като грешка. Но тук трябва да запомним, че APPEND е подсказка и общата логика на подсказките е, че те се вземат предвид, ако е възможно, но ако не, операторът се изпълнява, без да се вземе предвид подсказката.

Така че очакваният отговор е данните ще бъдат заредени в нормален (SQL) режим, тригерът ще се задейства.

Според документацията на Oracle (цитирано от 8.04):

Нарушенията на ограниченията ще доведат до серийно изпълнение на оператора, като се използва конвенционалния път за вмъкване, без предупреждения или съобщения за грешка. Изключение е ограничението за оператори, които имат достъп до една и съща таблица повече от веднъж в транзакция, което може да причини съобщения за грешка.
Например, ако в таблицата присъстват тригери или референтна цялост, тогава подсказката APPEND ще бъде игнорирана, когато се опитате да използвате INSERT с директно зареждане (сериен или паралелен), както и подсказката или клаузата PARALLEL, ако има такава.

Какво ще се случи, когато се изпълни следният скрипт?

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

  • Успешно завършване
  • Неуспех поради синтактична грешка
  • Грешка: Автономната транзакция не е валидна
  • Грешка, свързана с превишаване на максималното влагане на повиквания
  • Грешка при нарушаване на външния ключ
  • Грешка, свързана с ключалки

ОтговарямТаблицата и тригерът са създадени съвсем правилно и тази операция не би трябвало да доведе до проблеми. Автономните транзакции в тригер също са разрешени, в противен случай регистрирането не би било възможно, например.

След вмъкване на първия ред успешното задействане на тригера ще доведе до вмъкване на втория ред, което ще доведе до повторно задействане на тригера, вмъкване на трети ред и така нататък, докато операторът не успее поради превишаване на максималното влагане на повиквания. Налице е обаче още един тънък момент. По времето, когато тригерът се изпълнява, ангажирането все още не е завършено за първия вмъкнат запис. Следователно тригер, изпълняващ се в автономна транзакция, се опитва да вмъкне в таблицата ред, който препраща чужд ключ към запис, който все още не е ангажиран. Това води до изчакване (автономната транзакция изчаква основната транзакция да се ангажира, за да види дали може да вмъкне данни) и в същото време основната транзакция чака автономната транзакция да продължи да работи след тригера. Възниква блокиране и в резултат на това автономната транзакция се анулира поради причини, свързани със заключвания.

В анкетата могат да участват само регистрирани потребители. Впиши се, Моля те.

Беше ли трудно?

  • Като два пръста веднага реших всичко правилно.

  • Не наистина, сбърках с няколко въпроса.

  • Реших половината правилно.

  • Познах отговора два пъти!

  • Ще пиша в коментарите

14 потребители гласуваха. 10 потребители се въздържаха.

Източник: www.habr.com

Добавяне на нов коментар