{"id":36171,"date":"2019-10-31T22:09:58","date_gmt":"2019-10-31T19:09:58","guid":{"rendered":"https:\/\/prohoster.info\/blog\/po-sledam-highload-siberia-2019-8-zadach-po-oracle\/"},"modified":"2019-10-31T22:09:58","modified_gmt":"2019-10-31T19:09:58","slug":"po-sledam-highload-siberia-2019-8-zadach-po-oracle","status":"publish","type":"post","link":"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/po-sledam-highload-siberia-2019-8-zadach-po-oracle","title":{"rendered":"Pe urmele Highload++ Siberia 2019 \u2014 8 sarcini pe Oracle","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>Bun\u0103!<\/p>\n<p>\u00cen perioada 24-25 iunie, conferin\u021ba Highload++ Siberia 2019 s-a desf\u0103\u0219urat \u00een Novosibirsk. Echipa noastr\u0103 a fost \u0219i ea prezent\u0103. <noindex><a rel=\"nofollow\" href=\"https:\/\/www.highload.ru\/siberia\/2019\/abstracts\/5211\">cu o prezentare<\/a><\/noindex> \u00abBaze de date containerizate Oracle (CDB\/PDB) \u0219i utilizarea lor practic\u0103 \u00een dezvoltarea software-ului\u00bb, vom publica versiunea textului ceva mai t\u00e2rziu. A fost grozav, mul\u021bumim. <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/users\/olegbunin\/\" class=\"user_link\">olegbunin<\/a><\/noindex> pentru organizare, dar \u0219i tuturor celor care au venit. <\/p>\n<p><img decoding=\"async\" alt=\"Pe urmele Highload++ Siberia 2019 \u2014 8 sarcini pe Oracle\" src=\"\/wp-content\/uploads\/2019\/07\/27f4707d4bb5968eaaa5c4748ff467cd.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n\u00cen aceast\u0103 postare, am dori s\u0103 v\u0103 \u00eemp\u0103rt\u0103\u0219im sarcinile care au fost la standul nostru, pentru a putea s\u0103 v\u0103 verifica\u021bi cuno\u0219tin\u021bele \u00een Oracle. Sub tag \u2014 8 sarcini, variante de r\u0103spunsuri \u0219i explica\u021bii.<br \/>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h3>Care este valoarea maxim\u0103 a secven\u021bei pe care o vom vedea \u00een urma execu\u021biei urm\u0103torului script?<\/h3>\n<p><\/p>\n<pre><code class=\"sql\">create sequence s start with 1;\n \nselect s.currval, s.nextval, s.currval, s.nextval, s.currval\nfrom dual\nconnect by level &lt;= 5;\n<\/code><\/pre>\n<p><\/p>\n<ul>\n<li>1<\/li>\n<li>5<\/li>\n<li>10<\/li>\n<li>25<\/li>\n<li>Nicio valoare, va ap\u0103rea o eroare.<\/li>\n<\/ul>\n<p>\n<b class=\"spoiler_title\">R\u0103spuns<\/b>Conform documenta\u021biei Oracle (citat din 8.1.6):<br \/>\n\u00cen cadrul unei singure instruc\u021biuni SQL, Oracle va incrementa secven\u021ba doar o dat\u0103 per r\u00e2nd. Dac\u0103 o instruc\u021biune con\u021bine mai multe referin\u021be la NEXTVAL pentru o secven\u021b\u0103, Oracle incrementeaz\u0103 secven\u021ba o dat\u0103 \u0219i returneaz\u0103 aceea\u0219i valoare pentru toate apari\u021biile lui NEXTVAL. Dac\u0103 o instruc\u021biune con\u021bine referin\u021be at\u00e2t la CURRVAL, c\u00e2t \u0219i la NEXTVAL, Oracle incrementeaz\u0103 secven\u021ba \u0219i returneaz\u0103 aceea\u0219i valoare pentru at\u00e2t CURRVAL, c\u00e2t \u0219i NEXTVAL, indiferent de ordinea lor \u00een cadrul instruc\u021biunii.<\/p>\n<p>Astfel, <b>valoarea maxim\u0103 va corespunde num\u0103rului de r\u00e2nduri, adic\u0103 5.<\/b>.<\/p>\n<h3>C\u00e2te r\u00e2nduri vor exista \u00een tabel ca urmare a execu\u021biei urm\u0103torului script?<\/h3>\n<p><\/p>\n<pre><code class=\"sql\">create table t(i integer check (i &lt; 5));\n \ncreate procedure p(p_from integer, p_to integer) as\nbegin\n    for i in p_from .. p_to loop\n        insert into t values (i);\n    end loop;\nend;\n\/\n \nexec p(1, 3);\nexec p(4, 6);\nexec p(7, 9);<\/code><\/pre>\n<p><\/p>\n<ul>\n<li>0<\/li>\n<li>3<\/li>\n<li>4<\/li>\n<li>5<\/li>\n<li>6<\/li>\n<li>9<\/li>\n<\/ul>\n<p>\n<b class=\"spoiler_title\">R\u0103spuns<\/b>Conform documenta\u021biei Oracle (citat din 11.2):<\/p>\n<p>\u00cenainte de a executa orice instruc\u021biune SQL, Oracle marcheaz\u0103 un punct de salvare implicit (neavizat pentru tine). Apoi, dac\u0103 instruc\u021biunea e\u0219ueaz\u0103, Oracle efectueaz\u0103 automat rollback \u0219i returneaz\u0103 codul de eroare aplicabil \u00een SQLCODE \u00een SQLCA. De exemplu, dac\u0103 o instruc\u021biune INSERT cauzeaz\u0103 o eroare prin \u00eencercarea de a insera o valoare duplicat\u0103 \u00eentr-un index unic, instruc\u021biunea este anulat\u0103.<\/p>\n<p>Apelul procedurii stocate din client este, de asemenea, considerat \u0219i procesat ca o instruc\u021biune unic\u0103. Astfel, primul apel al procedurii se finalizeaz\u0103 cu succes, inser\u00e2nd trei \u00eenregistr\u0103ri; al doilea apel al procedurii se finalizeaz\u0103 cu o eroare \u0219i anuleaz\u0103 a patra \u00eenregistrare pe care a reu\u0219it s\u0103 o insereze; al treilea apel se finalizeaz\u0103 cu o eroare, <b>iar \u00een tabel r\u0103m\u00e2n trei \u00eenregistr\u0103ri.<\/b>.<\/p>\n<h3>C\u00e2te r\u00e2nduri vor exista \u00een tabel ca urmare a execu\u021biei urm\u0103torului script?<\/h3>\n<p><\/p>\n<pre><code class=\"sql\">create table t(i integer, constraint i_ch check (i &lt; 3));\n \nbegin\n    insert into t values (1);\n    insert into t values (null);\n    insert into t values (2);\n    insert into t values (null);\n    insert into t values (3);\n    insert into t values (null);\n    insert into t values (4);\n    insert into t values (null);\n    insert into t values (5);\nexception\n    when others then\n        dbms_output.put_line(&#039;Ups!&#039;);\nend;\n\/<\/code><\/pre>\n<p><\/p>\n<ul>\n<li>1<\/li>\n<li>2<\/li>\n<li>3<\/li>\n<li>4<\/li>\n<li>5<\/li>\n<li>6<\/li>\n<li>7<\/li>\n<\/ul>\n<p>\n<b class=\"spoiler_title\">R\u0103spuns<\/b>Conform documenta\u021biei Oracle (citat din 11.2):<\/p>\n<p>O constr\u00e2ngere de verificare v\u0103 permite s\u0103 specifica\u021bi o condi\u021bie pe care fiecare r\u00e2nd din tabel trebuie s\u0103 o satisfac\u0103. Pentru a \u00eendeplini constr\u00e2ngerea, fiecare r\u00e2nd din tabel trebuie s\u0103 fac\u0103 ca condi\u021bia s\u0103 fie fie ADEV\u0102RAT\u0102, fie necunoscut\u0103 (din cauza unui null). C\u00e2nd Oracle evalueaz\u0103 o condi\u021bie de constr\u00e2ngere de verificare pentru un anumit r\u00e2nd, orice nume de coloan\u0103 din condi\u021bie se refer\u0103 la valorile coloanei din acel r\u00e2nd.<\/p>\n<p>Astfel, valoarea null va trece verificarea, iar blocul anonim va fi executat cu succes p\u00e2n\u0103 la \u00eencercarea de a insera valoarea 3. Dup\u0103 aceasta, blocul de gestionare a erorilor va anula excep\u021bia, nu va exista o revocare \u0219i <b>\u00een tabel vor r\u0103m\u00e2ne patru r\u00e2nduri<\/b> cu valorile 1, null, 2 \u0219i din nou null.<\/p>\n<h3>Ce perechi de valori vor ocupa volume de spa\u021biu identice \u00een bloc?<\/h3>\n<p><\/p>\n<pre><code class=\"sql\">create table t (\n    a char(1 char),\n    b char(10 char),\n    c char(100 char),\n    i number(4),\n    j number(14),\n    k number(24),\n    x varchar2(1 char),\n    y varchar2(10 char),\n    z varchar2(100 char));\n \ninsert into t (a, b, i, j, x, y)\n    values ('Y', 'Vasile', 10, 10, 'D', 'Vasile');\n<\/code><\/pre>\n<p><\/p>\n<ul>\n<li>A \u0219i X<\/li>\n<li>B \u0219i Y<\/li>\n<li>C \u0219i K<\/li>\n<li>C \u0219i Z<\/li>\n<li>K \u0219i Z<\/li>\n<li>I \u0219i J<\/li>\n<li>J \u0219i X<\/li>\n<li>Toate enumerate<\/li>\n<\/ul>\n<p>\n<b class=\"spoiler_title\">R\u0103spuns<\/b>S\u0103 prezent\u0103m extrase din documenta\u021bia (12.1.0.2) privind stocarea diferitelor tipuri de date \u00een Oracle.<\/p>\n<p><b>Tip de date CHAR<\/b><br \/>\nTipul de date CHAR specific\u0103 un \u0219ir de caractere de lungime fix\u0103 \u00een setul de caractere al bazei de date. Specifica\u021bi setul de caractere al bazei de date atunci c\u00e2nd crea\u021bi baza de date. Oracle se asigur\u0103 c\u0103 toate valorile stocate \u00eentr-o coloan\u0103 CHAR au lungimea specificat\u0103 de dimensiune \u00een semantica lungimii selectate. Dac\u0103 insera\u021bi o valoare care este mai scurt\u0103 dec\u00e2t lungimea coloanei, atunci Oracle completeaz\u0103 valoarea cu caractere goale p\u00e2n\u0103 la lungimea coloanei.<\/p>\n<p><b>Tip de date VARCHAR2<\/b><br \/>\nTipul de date VARCHAR2 specific\u0103 un \u0219ir de caractere de lungime variabil\u0103 \u00een setul de caractere al bazei de date. Specifica\u021bi setul de caractere al bazei de date atunci c\u00e2nd crea\u021bi baza de date. Oracle stocheaz\u0103 o valoare de caracter \u00eentr-o coloan\u0103 VARCHAR2 exact a\u0219a cum o specifica\u021bi, f\u0103r\u0103 niciun\u0103 completare cu caractere goale, at\u00e2ta timp c\u00e2t valoarea nu dep\u0103\u0219e\u0219te lungimea coloanei.<\/p>\n<p><b>Tip de date NUMBER<\/b><br \/>\nTipul de date NUMBER stocheaz\u0103 zero, precum \u0219i numere fixe pozitive \u0219i negative cu valori absolute de la 1.0 x 10-130 la dar f\u0103r\u0103 a include 1.0 x 10126. Dac\u0103 specifica\u021bi o expresie aritmetic\u0103 a c\u0103rei valoare are o valoare absolut\u0103 mai mare sau egal\u0103 cu 1.0 x 10126, atunci Oracle returneaz\u0103 o eroare. Fiecare valoare NUMBER necesit\u0103 de la 1 la 22 de bytes. \u021ain\u00e2nd cont de acest lucru, dimensiunea coloanei \u00een bytes pentru o valoare numeric\u0103 particular\u0103 NUMBER(p), unde p este precizia unei valori date, poate fi calculat\u0103 folosind urm\u0103toarea formul\u0103: <i>ROUND((length(p)+s)\\\/2))+1<\/i> unde s este egal cu zero dac\u0103 num\u0103rul este pozitiv, iar s este egal cu 1 dac\u0103 num\u0103rul este negativ.<\/p>\n<p>\u00cen plus, s\u0103 lu\u0103m un extras din documenta\u021bia despre stocarea valorilor Null.<\/p>\n<p>Un null este absen\u021ba unei valori \u00eentr-o coloan\u0103. Nullurile indic\u0103 date lips\u0103, necunoscute sau neaplicabile. Nullurile sunt stocate \u00een baza de date dac\u0103 se afl\u0103 \u00eentre coloane cu valori de date. \u00cen aceste cazuri, ele necesit\u0103 1 byte pentru a stoca lungimea coloanei (zero). Nullurile de la sf\u00e2r\u0219itul unui r\u00e2nd nu necesit\u0103 stocare deoarece un nou antet de r\u00e2nd semnaleaz\u0103 c\u0103 coloanele r\u0103mase din r\u00e2ndul anterior sunt null. De exemplu, dac\u0103 ultimele trei coloane ale unui tabel sunt null, atunci nu se stocheaz\u0103 date pentru aceste coloane.<\/p>\n<p>Pe baza acestor date, formul\u0103m ra\u021bionamente. Consider\u0103m c\u0103 \u00een Baza de Date se folose\u0219te codificarea AL32UTF8. \u00cen aceast\u0103 codificare, literele ruse\u0219ti vor ocupa 2 bytes.<\/p>\n<p>1) A \u0219i X, valoarea c\u00e2mpului a &#8216;Y&#8217; ocup\u0103 1 byte, valoarea c\u00e2mpului x \u2018\u0414\u2019 \u2013 2 bytes<br \/>\n2) B \u0219i Y, \u2018\u0412\u0430\u0441\u044f\u2019 \u00een b va fi completat cu spa\u021bii p\u00e2n\u0103 la 10 caractere \u0219i va ocupa 14 octe\u021bi, \u2018\u0412\u0430\u0441\u044f\u2019 \u00een d \u2013 va ocupa 8 octe\u021bi.<br \/>\n3) C \u0219i K. Ambele c\u00e2mpuri au valoarea NULL, dup\u0103 ele exist\u0103 c\u00e2mpuri semnificative, deci ocup\u0103 c\u00e2te 1 octet.<br \/>\n4) C \u0219i Z. Ambele c\u00e2mpuri au valoarea NULL, dar c\u00e2mpul Z \u2013 este ultimul din tabel, deci nu ocup\u0103 spa\u021biu (0 octe\u021bi). C c\u00e2mpul ocup\u0103 1 octet.<br \/>\n5) K \u0219i Z. Similar cazului anterior. Valoarea din c\u00e2mpul K ocup\u0103 1 octet, \u00een Z \u2013 0.<br \/>\n6) I \u0219i J. Conform documenta\u021biei, ambele valori vor ocupa c\u00e2te 2 octe\u021bi. Lungimea se calculeaz\u0103 conform formula luat\u0103 din documenta\u021bie: round( (1 + 0) \/ 2) + 1 = 1 + 1 = 2.<br \/>\n7) J \u0219i X. Valoarea din c\u00e2mpul J va ocupa 2 octe\u021bi, valoarea din c\u00e2mpul X va ocupa 2 octe\u021bi.<\/p>\n<p><b>\u00cen total, variantele corecte sunt: C \u0219i K, I \u0219i J, J \u0219i X.<\/b><\/p>\n<p><\/p>\n<h3>Care va fi aproximativ clustering factor al indicelui T_I?<\/h3>\n<p><\/p>\n<pre><code class=\"sql\">create table t (i integer);\n \ninsert into t select rownum from dual connect by level &lt;= 10000;\n \ncreate index t_i on t(i);\n<\/code><\/pre>\n<p><\/p>\n<ul>\n<li>Ordinea zecilor<\/li>\n<li>Ordinea sutelor<\/li>\n<li>Ordinea miilor<\/li>\n<li>Ordinea zecilor de mii<\/li>\n<\/ul>\n<p>\n<b class=\"spoiler_title\">R\u0103spuns<\/b>Conform documenta\u021biei Oracle (citat\u0103 din 12.1):<\/p>\n<p>Pentru un indice B-tree, clustering factor-ul indicelui m\u0103soar\u0103 gruparea fizic\u0103 a r\u00e2ndurilor \u00een rela\u021bie cu o valoare de index.<\/p>\n<p>Clustering factor-ul indicelui ajut\u0103 optimizatorul s\u0103 decid\u0103 dac\u0103 un scan pe index sau un scan complet al tabelului este mai eficient pentru anumite interog\u0103ri). Un clustering factor sc\u0103zut indic\u0103 un scan eficient al indexului.<\/p>\n<p>Un clustering factor care este apropiat de num\u0103rul de blocuri dintr-un tabel indic\u0103 faptul c\u0103 r\u00e2ndurile sunt ordonate fizic \u00een blocurile tabelului dup\u0103 cheia indexului. Dac\u0103 baza de date efectueaz\u0103 un scan complet al tabelului, atunci tendin\u021ba bazei de date este de a recupera r\u00e2ndurile a\u0219a cum sunt stocate pe disc sortate dup\u0103 cheia indexului. Un clustering factor care este apropiat de num\u0103rul de r\u00e2nduri indic\u0103 faptul c\u0103 r\u00e2ndurile sunt dispersate aleatoriu \u00een blocurile bazei de date \u00een rela\u021bie cu cheia indexului. Dac\u0103 baza de date efectueaz\u0103 un scan complet al tabelului, atunci baza de date nu ar recupera r\u00e2nduri \u00een niciun ordin sortat dup\u0103 aceast\u0103 cheie de index.<\/p>\n<p>\u00cen acest caz, datele sunt perfect sortate, a\u0219a c\u0103 clustering factor-ul va fi egal sau aproape de num\u0103rul de blocuri ocupate \u00een tabel. Pentru dimensiunea standard a blocului de 8 kilobyte, putem anticipa c\u0103 \u00eentr-un bloc vor \u00eenc\u0103pea aproximativ o mie de valori de tip number sub\u021biri, deci num\u0103rul de blocuri, \u0219i prin urmare clustering factor-ul va fi <b>ordinea zecilor<\/b>.<\/p>\n<h3>Pentru ce valori N urm\u0103torul script va fi executat cu succes \u00eentr-o baz\u0103 de date obi\u0219nuit\u0103 cu set\u0103ri standard?<\/h3>\n<p><\/p>\n<pre><code class=\"sql\">create table t (\n    a varchar2(N char),\n    b varchar2(N char),\n    c varchar2(N char),\n    d varchar2(N char));\n \ncreate index t_i on t (a, b, c, d);\n<\/code><\/pre>\n<p><\/p>\n<ul>\n<li>100<\/li>\n<li>200<\/li>\n<li>400<\/li>\n<li>800<\/li>\n<li>1600<\/li>\n<li>3200<\/li>\n<li>6400<\/li>\n<\/ul>\n<p>\n<b class=\"spoiler_title\">R\u0103spuns<\/b>Conform documenta\u021biei Oracle (citat din 11.2):<\/p>\n<p>Limitele logice ale bazei de date<\/p>\n<p><strong>Element<\/strong><br \/>\n<strong>Tipul de limit\u0103<\/strong><br \/>\n<strong>Valoarea limit\u0103<\/strong><\/p>\n<p>Indici<br \/>\nDimensiunea total\u0103 a coloanei indexate<br \/>\n75% din dimensiunea blocului bazei de date minus unele costuri<\/p>\n<p>\nPrin urmare, dimensiunea total\u0103 a coloanelor indexate nu trebuie s\u0103 dep\u0103\u0219easc\u0103 6KB. Totul depinde de encodarea bazei de date aleas\u0103. \u00cen encodarea AL32UTF8, un caracter poate ocupa maxim 4 bytes, astfel, \u00een 6 kilobi\u021bi, \u00een cel mai prost caz pot \u00eenc\u0103pea aproximativ 1500 de caractere. De aceea, Oracle va interzice crearea indexului c\u00e2nd N = 400 (c\u00e2nd lungimea cheii \u00een cel mai prost caz va fi 1600 de caractere * 4 bytes + lungimea rowid), \u00een timp ce <b>c\u00e2nd N = 200 (\u0219i mai pu\u021bin)<\/b> crearea indexului va func\u021biona f\u0103r\u0103 probleme.<\/p>\n<h3>Operatorul INSERT cu hint APPEND este destinat \u00eenc\u0103rc\u0103rii datelor \u00een modul direct. Ce se \u00eent\u00e2mpl\u0103 dac\u0103 este aplicat pe o mas\u0103 care are un trigger?<\/h3>\n<p><\/p>\n<ul>\n<li>Datele vor fi \u00eenc\u0103rcate \u00een modul direct, triggerul va func\u021biona a\u0219a cum trebuie<\/li>\n<li>Datele vor fi \u00eenc\u0103rcate \u00een modul direct, dar triggerul nu va fi executat<\/li>\n<li>Datele vor fi \u00eenc\u0103rcate \u00een modul conventional, triggerul va func\u021biona a\u0219a cum trebuie<\/li>\n<li>Datele vor fi \u00eenc\u0103rcate \u00een modul conventional, dar triggerul nu va fi executat<\/li>\n<li>Datele nu vor fi \u00eenc\u0103rcate, va fi generat\u0103 o eroare<\/li>\n<\/ul>\n<p>\n<b class=\"spoiler_title\">R\u0103spuns<\/b>\u00cen principiu, aceasta este o \u00eentrebare mai mult de logic\u0103. Pentru a g\u0103si r\u0103spunsul corect, a\u0219 propune urm\u0103torul model de ra\u021bionament:<\/p>\n<ol>\n<li>Inser\u021bia \u00een modul direct se realizeaz\u0103 prin crearea direct\u0103 a unui bloc de date, ocolind motorul SQL, ceea ce asigur\u0103 o vitez\u0103 mare. Prin urmare, asigurarea execut\u0103rii triggerului este foarte dificil\u0103, dac\u0103 nu imposibil\u0103, \u0219i nu are sens, deoarece oricum va \u00eencetini drastic inser\u021bia.<\/li>\n<li>Nefunc\u021bionarea triggerului va duce la faptul c\u0103, av\u00e2nd acelea\u0219i date \u00een mas\u0103, starea bazei de date \u00een ansamblu (a celorlalte mese) va depinde de modul \u00een care aceste date au fost inserate. Aceasta va distruge evident integritatea datelor \u0219i nu poate fi aplicat\u0103 ca solu\u021bie \u00een produc\u021bie.<\/li>\n<li>Imposibilitatea de a executa opera\u021biunea solicitat\u0103 este, \u00een general, interpretat\u0103 ca o eroare. Dar aici trebuie s\u0103 ne amintim c\u0103 APPEND este un hint, iar logica general\u0103 a hinturilor este c\u0103 acestea sunt luate \u00een considerare dac\u0103 este posibil; dac\u0103 nu, operatorul este executat f\u0103r\u0103 a \u021bine cont de hint.<\/li>\n<\/ol>\n<p>\nPrin urmare, r\u0103spunsul a\u0219teptat este <b>datele vor fi \u00eenc\u0103rcate \u00een modul obi\u0219nuit (SQL), triggerul va func\u021biona.<\/b><\/p>\n<p>Conform documenta\u021biei Oracle (citat din 8.04):<\/p>\n<p>\u00cenc\u0103lcarea restric\u021biilor va determina ca instruc\u021biunea s\u0103 fie executat\u0103 \u00een mod serial, folosind calea de inser\u021bie conven\u021bional\u0103, f\u0103r\u0103 avertiz\u0103ri sau mesaje de eroare. O excep\u021bie este restric\u021bia privind instruc\u021biunile care acceseaz\u0103 aceea\u0219i mas\u0103 de mai multe ori \u00eentr-o tranzac\u021bie, ceea ce poate cauza mesaje de eroare.<br \/>\nDe exemplu, dac\u0103 exist\u0103 declan\u0219atoare sau integritate referen\u021bial\u0103 \u00een tabel, atunci sugestia APPEND va fi ignorat\u0103 atunci c\u00e2nd \u00eencerci s\u0103 folose\u0219ti INSERT cu \u00eenc\u0103rcare direct\u0103 (seriat sau paralel), precum \u0219i sugestia sau clauza PARALLEL, dac\u0103 exist\u0103.<\/p>\n<h3>Ce se va \u00eent\u00e2mpla la executarea urm\u0103torului script?<\/h3>\n<p><\/p>\n<pre><code class=\"sql\">create table t(i integer not null primary key, j integer references t);\n \ncreate trigger t_a_i after insert on t for each row\ndeclare\n    pragma autonomous_transaction;\nbegin\n    insert into t values (:new.i + 1, :new.i);\n    commit;\nend;\n\/\n \ninsert into t values (1, null);\n<\/code><\/pre>\n<p><\/p>\n<ul>\n<li>Executare reu\u0219it\u0103<\/li>\n<li>E\u0219ec din cauza unei erori de sintax\u0103<\/li>\n<li>Eroare legat\u0103 de inexisten\u021ba unei tranzac\u021bii autonome<\/li>\n<li>Eroare legat\u0103 de dep\u0103\u0219irea nivelului maxim de apeluri \u00eencruci\u0219ate<\/li>\n<li>Eroare legat\u0103 de \u00eenc\u0103lcarea cheii externe<\/li>\n<li>Eroare legat\u0103 de blocaje<\/li>\n<\/ul>\n<p>\n<b class=\"spoiler_title\">R\u0103spuns<\/b>Tabelul \u0219i declan\u0219atorul sunt create corect \u0219i aceast\u0103 opera\u021biune nu ar trebui s\u0103 cauzeze probleme. Tranzac\u021biile autonome \u00een declan\u0219atoare sunt, de asemenea, permise, altfel ar fi imposibil, de exemplu, s\u0103 se fac\u0103 logarea.<\/p>\n<p>Dup\u0103 inserarea primei linii, executarea reu\u0219it\u0103 a declan\u0219atorului ar conduce la inserarea celei de-a doua linii, ceea ce ar declan\u0219a din nou declan\u0219atorul, ar insera a treia linie \u0219i a\u0219a mai departe, p\u00e2n\u0103 c\u00e2nd instruc\u021biunea s-ar opri din cauza dep\u0103\u0219irii nivelului maxim de apeluri \u00eencruci\u0219ate. Totu\u0219i, exist\u0103 un alt aspect delicat. \u00cen momentul execut\u0103rii declan\u0219atorului pentru prima \u00eenregistrare inserat\u0103, commit-ul nu a fost \u00eenc\u0103 efectuat. Prin urmare, declan\u0219atorul, care func\u021bioneaz\u0103 \u00eentr-o tranzac\u021bie autonom\u0103, \u00eencearc\u0103 s\u0103 insereze \u00een tabel o linie care se refer\u0103 prin cheie extern\u0103 la o \u00eenregistrare care nu a fost \u00eenc\u0103 confirmat\u0103. Acest lucru duce la a\u0219teptare (tranzac\u021bia autonom\u0103 a\u0219teapt\u0103 commit-ul principal pentru a determina dac\u0103 datele pot fi inserate) \u0219i, \u00een acela\u0219i timp, tranzac\u021bia principal\u0103 a\u0219teapt\u0103 commit-ul autonom pentru a continua dup\u0103 declan\u0219ator. <b>Apas\u0103 un deadlock \u0219i, ca urmare, tranzac\u021bia autonom\u0103 este anulat\u0103 din cauza blocajelor<\/b>.<\/p>\n<p class=\"for_users_only_msg\">Numai utilizatorii \u00eenregistra\u021bi pot participa la sondaj. <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/auth\/login\/\">Conecta\u021bi-v\u0103<\/a><\/noindex>, v\u0103 rug\u0103m.<\/p>\n<h2 class=\"default-block__polling-title\">A fost greu?<\/h2>\n<ul class=\"content-list content-list_polling\">\n<li class=\"content-list__item content-list__item_polling\">\n<p>                    Ca doi degete, am rezolvat totul corect din prima.<\/p>\n<\/li>\n<li class=\"content-list__item content-list__item_polling\">\n<p>                    Nu foarte, am gre\u0219it la c\u00e2teva \u00eentreb\u0103ri.<\/p>\n<\/li>\n<li class=\"content-list__item content-list__item_polling\">\n<p>                    Am rezolvat corect jum\u0103tate.<\/p>\n<\/li>\n<li class=\"content-list__item content-list__item_polling\">\n<p>                    Am ghicit r\u0103spunsul de dou\u0103 ori!<\/p>\n<\/li>\n<li class=\"content-list__item content-list__item_polling\">\n<p>                    Voi scrie \u00een comentarii<\/p>\n<\/li>\n<\/ul>\n<p>    14 utilizatori au votat. 10 utilizatori s-au ab\u021binut.<br \/>\n<br \/>Sursa: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/sportmaster_lab\/blog\/459680\/\">habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u041f\u0440\u0438\u0432\u0435\u0442! 24-25 \u0438\u044e\u043d\u044f \u0432 \u041d\u043e\u0432\u043e\u0441\u0438\u0431\u0438\u0440\u0441\u043a\u0435 \u043f\u0440\u043e\u0448\u043b\u0430 \u043a\u043e\u043d\u0444\u0435\u0440\u0435\u043d\u0446\u0438\u044f Highload++ Siberia 2019. \u041d\u0430\u0448\u0438 \u0440\u0435\u0431\u044f\u0442\u0430 \u0442\u043e\u0436\u0435 \u0442\u0430\u043c \u0431\u044b\u043b\u0438 \u0434\u043e\u043a\u043b\u0430\u0434\u043e\u043c \u00ab\u041a\u043e\u043d\u0442\u0435\u0439\u043d\u0435\u0440\u043d\u044b\u0435 \u0431\u0430\u0437\u044b Oracle (CDB\/PDB) \u0438 \u0438\u0445 \u043f\u0440\u0430\u043a\u0442\u0438\u0447\u0435\u0441\u043a\u043e\u0435 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435 \u0434\u043b\u044f \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u043a\u0438 \u041f\u041e\u00bb, \u043c\u044b \u0432\u044b\u043b\u043e\u0436\u0438\u043c \u0442\u0435\u043a\u0441\u0442\u043e\u0432\u0443\u044e \u0432\u0435\u0440\u0441\u0438\u044e \u043d\u0435\u043c\u043d\u043e\u0433\u043e \u043f\u043e\u0437\u0436\u0435. \u0411\u044b\u043b\u043e \u043a\u0440\u0443\u0442\u043e, \u0441\u043f\u0430\u0441\u0438\u0431\u043e olegbunin \u0437\u0430 \u043e\u0440\u0433\u0430\u043d\u0438\u0437\u0430\u0446\u0438\u044e, \u0430 \u0442\u0430\u043a\u0436\u0435 \u0432\u0441\u0435\u043c, \u043a\u0442\u043e \u043f\u0440\u0438\u0448\u0451\u043b. \u0412 \u044d\u0442\u043e\u043c \u043f\u043e\u0441\u0442\u0435 \u043c\u044b \u0445\u043e\u0442\u0435\u043b\u0438 \u0431\u044b \u043f\u043e\u0434\u0435\u043b\u0438\u0442\u044c\u0441\u044f \u0441 \u0432\u0430\u043c\u0438 \u0437\u0430\u0434\u0430\u0447\u0430\u043c\u0438, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":27053,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-36171","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.2.1 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u041f\u0440\u0438\u0432\u0435\u0442! 24-25 \u0438\u044e\u043d\u044f \u0432 \u041d\u043e\u0432\u043e\u0441\u0438\u0431\u0438\u0440\u0441\u043a\u0435 \u043f\u0440\u043e\u0448\u043b\u0430 \u043a\u043e\u043d\u0444\u0435\u0440\u0435\u043d\u0446\u0438\u044f Highload++ Siberia 2019.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/po-sledam-highload-siberia-2019-8-zadach-po-oracle\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.2.1\" \/>\n\t\t<meta property=\"og:locale\" content=\"ro_RO\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u041f\u043e \u0441\u043b\u0435\u0434\u0430\u043c Highload++ Siberia 2019 \u2014 8 \u0437\u0430\u0434\u0430\u0447 \u043f\u043e Oracle | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u041f\u0440\u0438\u0432\u0435\u0442! 24-25 \u0438\u044e\u043d\u044f \u0432 \u041d\u043e\u0432\u043e\u0441\u0438\u0431\u0438\u0440\u0441\u043a\u0435 \u043f\u0440\u043e\u0448\u043b\u0430 \u043a\u043e\u043d\u0444\u0435\u0440\u0435\u043d\u0446\u0438\u044f Highload++ Siberia 2019.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/po-sledam-highload-siberia-2019-8-zadach-po-oracle\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2019-10-31T19:09:58+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2019-10-31T19:09:58+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47Pe urmele Highload++ Siberia 2019 \u2014 8 sarcini pe Oracle | ProHoster","description":"Salut! \u00centre 24-25 iunie, la Novosibirsk a avut loc conferin\u021ba Highload++ Siberia 2019.","canonical_url":"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/po-sledam-highload-siberia-2019-8-zadach-po-oracle","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"ro_RO","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u041f\u043e \u0441\u043b\u0435\u0434\u0430\u043c Highload++ Siberia 2019 \u2014 8 \u0437\u0430\u0434\u0430\u0447 \u043f\u043e Oracle | ProHoster","og:description":"\u041f\u0440\u0438\u0432\u0435\u0442! 24-25 \u0438\u044e\u043d\u044f \u0432 \u041d\u043e\u0432\u043e\u0441\u0438\u0431\u0438\u0440\u0441\u043a\u0435 \u043f\u0440\u043e\u0448\u043b\u0430 \u043a\u043e\u043d\u0444\u0435\u0440\u0435\u043d\u0446\u0438\u044f Highload++ Siberia 2019.","og:url":"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/po-sledam-highload-siberia-2019-8-zadach-po-oracle","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-10-31T19:09:58+00:00","article:modified_time":"2019-10-31T19:09:58+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"36171","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-22 02:18:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-03-01 01:49:43","updated":"2026-01-22 02:18:19","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts\/36171","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/comments?post=36171"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts\/36171\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/media\/27053"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/media?parent=36171"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/categories?post=36171"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/tags?post=36171"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}