Warning: Declaration of Suffusion_MM_Walker::start_el(&$output, $item, $depth, $args) should be compatible with Walker_Nav_Menu::start_el(&$output, $item, $depth = 0, $args = Array, $id = 0) in /DISK2/WWW/plsql.cz/www/wp-content/themes/suffusion/library/suffusion-walkers.php on line 0
Oct 242014
 

Kolega si kdys postěžoval, že blog moc často neaktualizuji a má pravdu ;) Nicméně dneska si ten post doslova vynutil, když mi potrápil s jedním selectem. Prvně bych chtěl všem připomenout, že to v Oracle funguje (alespon v zapatchované 11.2g) tak, že pokud Vám select vrací něco, co vypadá jako nesmysl – máte to špatně vy, ne Oracle. V mé první práci mě tohle myšlení naučil kolega, který nebyl ochoten přemýšlet o bugu Oracle dříve než za několik hodin doumání nad selectem. A já jsem tohle posunul ještě dál – tak 14 dní. Pokud jste daným selectem neztrávili alespoň 14 dní, pak není racionální přemýšlet nad tím, že je to bug Oracle. Za svůj život jsem potkal přesně 3 selecty, které vrátily špatný výsledek a byl to bug (hashjoin u subpartitions , left join na dual a to poslední si nepamauji – všechno již dávno fixnuto Oraclem). Zatímco já jsem na svůj select koukal asitak přesně bžilionkrát s tím, že “to není možné” a vždycky jsem končil přiznáním vlastní chyby a chutí ukousknout si vlastní ucho, když jsem zjistil, že to celou dobu přehlížím..

Jedinou vyjímkou, kdy bych přemýšlel nad chybou Oracle jsou ještě chvíle, kdy jste si hráli s datovým slovníkem (updaty pod sysem apod.) či je to chyba, kterou by mohlo vyvolat poškození datového bloku či indexu/objektu na mediu. Ale to je jen tak mé doporučení.

A nyní čím mi potrápil kolega (vypadalo nechtěně ačkoliv v průběhu zjišťování, v čem je zakopaná ponorka jsem ho podezíral se spousty věcí včetně VPD policy). Nejprve když mi popisoval svůj problém mluvil o constrainech a o tom, že mu “nefunguje cizí klíč”, což neznělo moc zajímavě, upřímně ;) Do chvíle, než mi ukázal výsledky těchto dvou selectů:

Jiný výsledek

Všimněte, že výsledek je pokaždý jiný (počet řádků – sloupce jsou vždy z jiné tabulky) ačkoliv jediné co jsem změnil jsou sloupce v projection – join a predikát je naprosto stejný. Takže kde, že je ta zakopaná ponorka? Většinou v takovýhle případech zkouším exekuční plány – minimálně pro svou představu a oživení nápadů:

(explain plan)

Tohle píšu doma, takže to nemá naprosto stejný exekuční plán jako to mělo u nás v práci (speciálně to dělám na 12.1c tudíš tam mám navíc krok COLLECTORU a mám naprosto jiné tabulky), nicméně jako v práci to není ničím zajímavým, zkusme to pro b.*:

Tohle již vypadá zajímavěji  – mám tam join na dvou tabulkách, ale exekuční plán jde jen do jedné tabulky. Krásná síla Oracle, který zjistí, že join na tabulku A vůbec přístup do ní není třeba. Důvodem je FK constrain. Ten zaručuje, že join by zafungoval všude, zároveň na a.id je primární klíč, který zamezuje “pronásobení řádků”.  Takže nakonec po optimaizaci zbyde jen čtení z tabulky na predikát dsk=1 a join Oracle ztransformoval na  b.album_id is not null.

Nicméně to je “co je v exekučním plánu” a úplně to nenasvěčuje, proč dva selecty vrací jiné výsledky. Takže jsem si řekl, co to udělá, když Oracle donutím si tu tabulku vzít a fyzicky je na sebe najoinovat – zkusil jsem hinty na full, use_hash. – a odnáším si z toho ponaučení: tohle se ohintovat nedá, jakmile to Oracle vykydlí hned na pravidlech, neni žádná šance na to šahat hintem. Což je věc nad kterou jsem nikdy nepřemýšlel – většinou člověk hintuje, to co drhne v exekučním plánu, nikoliv takhle od pohledu do selectu. Každopádně nejde. Co mi ale napadlo až doma je vypnout tuhle featuru kydlení pomocí hintu OPT_PARAM. Což má nevýhodu v tom, že já vlastně ani nevím, co přesně vypnout – je to nějaký ze bžilonu zkrytých parametrů. Takže jsem nahodil brutálnější hint OPTIMALIZER_FEATURE_ENABLE, který umožní zadat pouze verzi ;) A tak se stačí jen modlit, aby to nebylo hned od první verze, ale nebylo:

Fajn zanfungovalo – hned na poprvé ;) Taky jsem teda zbaběle střelil nízkou verzi Oracle. Nicméně případně hrubou silou by šlo zjistit od kdy to začne zase vracet dva řádky a pak případně udělat rozdíl hidden parametrů a tipnout si, co přesně kde je třeba vypnout. Ale to bylo jen tak pro zajímavost.

Každopádně to visí a padá na constrainu, mrknu jsem co kolega psal ve scriptech a s úsměvěm jsem na tváři objevil slovo “novalidate”. Řikám si ha, má tam bordel v datech nedodržuje FK/špinavá data a přidal constrain novalidate, takže zvalidovat:

ALTER TABLE track MODIFY CONSTRAINT fk_track ENABLE VALIDATE;
table TRACK altered

Po té, co mi tenhle příkaz nevyfuckoval, mi to začalo postupně kazit pátek :-( Každopádně po delší odmlce jsme přišli, na to, že je problém s attributem RELY na constrainu. A to je můj druhý nový poznatek pro dnešek – pokud je na constrainu RELY, tak vás následné VALIDATE nevyhodí a normálně proběhne a co horší, proběhne pak i zpětné NORELY, jinými slovy, udělá to tahle kombinace:

ALTER TABLE track MODIFY CONSTRAINT fk_track RELY;
ALTER TABLE track MODIFY CONSTRAINT fk_track ENABLE VALIDATE;
ALTER TABLE track MODIFY CONSTRAINT fk_track NORELY;

Pekelné ;) Takže je třebas to zpravit:

ALTER TABLE track MODIFY CONSTRAINT fk_track ENABLE NOVALIDATE;
ALTER TABLE track MODIFY CONSTRAINT fk_track ENABLE VALIDATE;

SQL Error: ORA-02298: není možno zkontrolovat platnost (AZOR6.FK_TRACK) - nebyly nalezeny nadřízené klíče
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
*Cause: an alter table validating constraint failed because the table has
child records.
*Action: Obvious

A od téhle chvíle již to není vůbec zajímavé, v  tabulce je nějaký bordel, který je třebas promazat nebo se rozloučit sconstrainem. Takže asi tak, hébičkám zdar a pěkný víkend.

 Posted by at 21:35
Oct 072014
 

Dnešní tip na hint, který dokáže trošku podpořit odhad cardinality estimátorem. Nevýhodou tohoto hintu je, že je nedokumentovaný, takže na produkci ne-e(ačkoliv vypadá bezpečně..). Což je velká škoda, protože jinak je to neuvěřitelně sexy hint.

Použití: Hint slouží k narovnání odhadu estimátoru (podobně jako nedokumentovaný /*+ cardinality */). Můžeme určit nejen cardinalitu u tabulky, ale dokonce i u joinu. A co více, existuje dokonce možnost odkazovat se násobky na to, co spočitá estimátor. A snad to nejlepší nakonec – lze zadat MINum a MAXimum. Prostě vymazlenej hint.

Syntaxe (s rezervou – z netu, není to dokumentované):

/*+ OPT_ESTIMATE( [query block] operation_type identifier adjustment ) */

Možné hodnoty :

operation_type = table | index_fliter | index_scan | index_skip_scan | join..

identfier = tab_alias@qrblk | tab_alias@qrblk index_name | JOIN(tab1_alias@qrblk tab2_alias@qrblk) s tím, že, query blok je nepovinný.

ajdusment  = rows -počet řádků, které očekáme | scale_rows - multiplikátor pro estimátor | min - minimální počet řádků, které očekáváme | max  maxmální počet řádků, které očekáváme

A nyní nějaké ty příklady, nejprve založme tabulku:

CREATE TABLE tbl_test AS SELECT ROWNUM AS sloupec FROM dual CONNECT BY LEVEL<784561;

A spočtěme statistiky (na 12C nemusím – ale pro lidi, co mají ještě 11.2 či hůře..)

BEGIN dbms_stats.gather_table_stats(null, 'tbl_test', method_opt => 'FOR ALL COLUMNS SIZE 1'); END;

Tabulka má přesně 784561 řádků. Takže odhad pro select count(*) from tbl_test je přesně 784561. Číslo, takhle hnusné, jsem vybral schválně ;), takže zkusme odhad pro select count(*) from tbl_test where ora_hash(id,5)=1. Zde se Oracle pekelně splete – defalutní selectivita na “=” pro filtrování pomocí funkce, tzn. where f(n)=x|const je 1%, tedy jeho odhad je 7846. Ve skutečnosti při náhodných datech ora_hash(id,5)=1 selectuje 1/6 tabulky (16,6%), protože máme rovnoměrně rozhozené hodnoty <0,5> a filtrujeme na =1.

SELECT count(*) FROM tbl_test WHERE ora_hash(sloupec,5)=1;

(predikát ora_hash(sloupec,5)=1)

Celkový vrácený počet řádků: 130521 (16,6%)
Odhad estimátoru: 7846 (1%)

A udělejme to ještě jednou, ještě jednou přidejme filtrování funkcí ať je Oracle hodně vedle:

SELECT count(*) FROM tbl_test WHERE ora_hash(sloupec,5)=1 AND ora_hash(sloupec,5)+1=2;

Přidání predikátu ora_hash(id,5)+1=2 (nemohu znovu ora_hash(id,5)=1, doublování predikátů Oracle pozná, tak jednoduše se ošálit nenechá), pro Oracle znamená znovu selectivitu 1%. Takže celkový odhad estimátoru je 0.01*0.01* num_rows=78 +/- zaokrouhlení. Z logického hlediska je to po odečtení 1 na obou stranách naprosto stejný predikát jako ora_hash(id,5)=1. Tedy stále selectujeme stále 1/6 tabulky (16,6%), podmínku jsme pouze zdvojili.

(preditkát ora_hash(sloupec,5)=1 v and s tím samým predikátem)

Celkový vrácený počet řádků: 130521 (16,6%)
Odhad estimátoru: 78 (0.0001%)

Takže spočítejme kolikrát více řádků opravdu vracíme o proti estimátoru – select ((1/6)/0.0001) from dual – 1666.6 násobek. A nyní k našemu vytouženému hintu:

SELECT /*+ OPT_ESTIMATE(TABLE a SCALE_ROWS=1667) */ count(*) FROM tbl_test2 a WHERE ora_hash(id,5)=1 AND ora_hash(id,5)+1=2;

(s predikátama a hintem opravený odhadem estimátoru)

Nyní je odhad 130786 proti skutečnému 130521 (jsme o 0,2% vedle, not bad). Výhoda tohoto hintu o proti /*+ cardinality */ je, že je to datově citlivé – přes odhad estimátoru.

Takže po té, co vím, že pro predikát “ora_hash(id,5)=1 and ora_hash(id,5)+1=2″ je potřeba magické číslo 1667 můžu to udělat s jinou tabulkou znovu:

CREATE TABLE tbl_test2 AS SELECT 'ABCD_'||ROWNUM as sloupec FROM dual CONNECT BY LEVEL<=100000;
BEGIN dbms_stats.gather_table_stats(null, 'tbl_test2', method_opt => 'FOR ALL COLUMNS SIZE 1'); END;

SELECT /*+ OPT_ESTIMATE(TABLE a SCALE_ROWS=1667) */ count(*) FROM tbl_test2 a WHERE ora_hash(id,5)=1 AND ora_hash(id,5)+1=2;

(odhad s hintem na jiné tabulce se stejným predikátem)

Celkový vrácený počet řádků: 16716 (cca (1/6)*100000)
Odhad estimátoru s hintem:   16670 (0.01*0.01*100000*1667)
Odhad estimátoru bez hintu: 10 (0.01*0.01*100000)

Myslím, hezký příklad na úpravu odhadu estimátoru. Hint má navíc i takové možnosti jako MIN a MAX. Speciálně to MIN je zajímavé, vzhledem k tomu, že většinou dochází k podhodnceni počtu řádků, které Oracle odhadne o proti skutečnosti. Myslím, že se asi každému z nás stává, že Oracle odhadne 1 (ve skutečnosti spočítá 0, ale až na menší vyjímky vždy raději počitá s tím, že se vrátí alepsoň jeden řádek – a to i za nesmyslných podmínek, aby to mohl propasovat do dalšího stupně exekučního plánu) a nakonec je tam řádků asi tak přesně bžlilion.. takže timto hintem se mu dá vnutit, že prostě musí počítat s tím, že tam minimálně xxx řádků mít může ;) Prostě sexy hint.

A ještě nějaký krátký příklad na join:

SELECT /*+ OPT_ESTIMATE(JOIN, (a, b), ROWS=15) */ * FROM dual a join dual b on (a.dummy=b.dummy);

(oprava cardinality joinu hintem)

To by bylo vše k tomuto pozoruhodnému, leč nedokumentovanému, hintu.

 Posted by at 21:59
Oct 042014
 

Včera (a dneska) mi v práci potrápil jeden select. Respektive jeho zobrazení v V$SQL_PLAN_MONITOR – spustil jsem select a ta mrška běžela dlouho, takže jsem se začal starat, co právě dělá a především za jako dlouho asi doběhne a jestli vůbec.  Bylo tam pod sebou pár hash joinů – úplně optimálně to nevypadalo, ale nic drastického také ne. Takže jsem si vpohodě čekal až to doběhne – přesně do chvíle, než Oracle začal full scannovat tabulku, říkejme jí třebas ACC_SOURCE, která měla 972 partitions. Dokud se ve sloupci STARTS objevovala čísla 1..972 bylo to fajn, jakmile Oracle začal ukazovat 1008 a pokračoval dál, bylo to jasné WTF (whats the fuck = to mi poser kozy). Inu, cílem tohodle článků je podívat se na hodnoty ve sloupci STATS v pohledu V$SQL_PLAN_MONITOR s tím, že to rozdělím do třech dílů:

  • Nepartitiované tabulky (tenhle díl)
  • Pro partitiované tabulky (a tam bude odpověď proč tam může být více startů než kolik je partitions)
  • Pro paralelní selecty

Takže tolik na úvod a nyní pojďme na nějaké to zkoušení:

create table tbl_just_one_part_100rows as select rownum as id,mod(rownum,2) id_mod_2 from dual connect by level<101;

Tabulka se dvěma sloupci (id a id_mod_2), 100 řádky a bez partitioningu. Takže jíz zkusme proscannovat:

select * from tbl_just_one_part_100rows;

Zjistím si sql_id (ckqzr8t38gug7) a podívám se do v$sql_plan_monitor do sloucpce starts, kolik uvidím? Neuvidím nic, protože jsem nesplnil žádnou s podmínek, aby oracle online monitoroval sql – neběželo to déle než 5s, nebylo to paralelní a ani jsem tam neměl hint. Takže přidávám hint:

select /*+ MONITOR */ * from tbl_just_one_part_100rows

A nyní už je to lepší (nyní sql_id 36ztar2wkfvww):

select status,plan_line_id as id,plan_operation,plan_object_owner as owner
,plan_object_name,starts,output_rows as o_rows from v$sql_plan_monitor
where sql_id='36ztar2wkfvww'

 (pozn. řádky 1 a 2 jsou jedno spuštění a řádky 3 a 4 jsou druhé)

Vcelku jednoduché, ne? Jedno čtení = hodnota ve sloupci STARTS = 1. Trošku matoucí je možná output rows, které řiká 50 ačkoliv má tabulka 100 řádků – jednoduché vysvětlení, SQL Developer prvně fetchnul 50 řádků a mě výsledek nezajímal, takže jsem nefetchoval dalších 50.  Zůstaňme u této tabulky a zkusme nějaký join:

select /*+ MONITOR */ * from tbl_just_one_part_100rows a join tbl_just_one_part_100rows b on (a.id=b.id)

Kolik bude startů? Tentokrát to ovšem již závisí na exekučním plánu. Takže jaký bude plán? Inu, joinuju na sebe dvakrát stejnou tabulku a u obou jejich instancí je to bez predikátu. Takže pro instanci tabulky A i instanci B odhadne Oracle stejnou cardinalidu. Ve skutečnosti to zkouším na Oracle 12C, které při CTASu už počitá statistiky. Takže vcelku přesně “ví”, že je to 100 řádků na 100 řádků:

Takže po spuštění:

select /*+ MONITOR */ * from tbl_just_one_part_100rows a join tbl_just_one_part_100rows b on (a.id=b.id);

První test joinu

Je vidět, že to každou instanci tabulky (A i B) vezme jednou a počet STARTů je tedy jedna, dle očekávání trhu. Za zmínku možná stojí ještě output_rows, které je 100 přestože jsem nefetchnul všechno v SQL Developeru. Důvodem je, že Oracle nemůže vracet průběžně výsledek pro operaci hashjoin (všechno nebo nic).  Pustil bych se dál do zkoušení, ale raději založme jiné tabulky, ať je to lépe vidět:

create table tbl_A_100 as select rownum as ID,mod(rownum,2) ID_MOD_2 from dual connect by level<101;
create table tbl_B_200 as select rownum as ID,mod(rownum,2) ID_MOD_2 from dual connect by level<201;

Dobře, dobře a nyní join a vynuceně neasted loopu:

select /*+ MONITOR use_nl(a b)*/ a.id from tbl_A_100 a join tbl_B_200 b on (a.id=b.id);

(neasted loopa a počet starts)

U tabulky tbl_b je 100 startů u tabulky tbl_a je 1 start. Což je logické – tabulku tbl_a si slízne oracle jednou a pak dle definice nested loopy si pro každý řádek líže tbl_b. Takže je pojďme přehodit:

select /*+ MONITOR use_nl(b a) leading(b a)*/ a.id from tbl_A_100 a join tbl_B_200 b on (a.id=b.id);

(počet startu neasted loopy)

Pokud v nested loopě obrátíme pořadí tak je to přesně opačně – slízne si to jednou tabulku tbl_b_200 a pak to iteruje pro každý řádek tabulku tbl_a_100 tentokrát však 200x.  No dobře, pokračujme dále. Merge join:

select /*+ MONITOR use_merge(a b)*/ * from tbl_A_100 a join tbl_B_200 b on (a.id=b.id);

(merge join

Merge join je již lehce složitější na čtení ale i tak je vidět, že obě tabulky se čtou pouze jednou, a počet startů u sortu je pouze u jedné (v mém případě TBL_B_200) a je datově závislý na datech z druhé tabulky.

Tohle samozřejmě nejsou všechny případy a zdaleka nemám v plánu zkoušet je všechny, ale pro ukázku jak s takovým sloupcem zacházet to myslím stačí ;)

A nyní to, co mělo být na začátku – proč se tomu věnuji. Ačkoliv hint byl již v úvodním odstavci. Pole STARTS (a nejen tohle pole) sleduji poměrně často pokud ladím nějaké dlouhoběžící sql. Taková ta jedna z nejrychlejších cest jak najít úzké hrdlo. Typickým příkladem je, když tohle pole obsahuje nějaké velké číslo (bžilion a více). Vcelku se člověk může vsadit, že Oracle dělá místo hashjoin nested loopu. Další krása tohodle pole je ta, že dokáže odhadnout jak dlouho daná operace bude trvat:

  • Během operace NESTED join poznáte kolik % již je hotovo (pokud je znám očekávaný počet startů – známe z dat, z předchozího stupně exekučního plánu – output rows v V$SQL_PLAN_MONITOR atd.)
  • Během operace HASH JOIN (neparallelně a bez partitions) je počet startů 1, takže tam koukáme na počet datových bloků kolik ještě zbývá (highmark watermark či z předchozího exekučního stupně – V$SQL_LONGOPS apod.)
  • Během full scannů (index i table) je opět počet startů 1 nebo počet sub(partitions) tedy alespoň se dá odhadnout kolik z kolika partitions již to vzalo (s lehkou rezervou, ne vždy je to přesný počet partitions a vlastně proto vzniknul i tenhle příspěvek na blogu, respektive další díl)

Zkrátka je to hodně sexy sloupec. Kdo neznal může si klidně vyselectit where starts>100000 a má dobrý tip na sql, které nejspíše nebudou optimální. Nutno podotknout, že v tomhle view nezůstávají data moc dlouho.

 Posted by at 00:14
Apr 032014
 

Všichni známe velkou trojku – SQL Developer, TOAD a PL/SQL Developer. Osobně mám z nich nejraději právě PL/SQL Developera – více užitečný než SQL Developer a né tak připlácaný jako TOAD. Dnes jen krátká informace – vyšla jeho nová major verze s číslem 10. V rychlosti co je to obsahuje:

  • Jednodušší možnost přepínání edicí. Tedy místo ALTER SESSION již stačí klikat.
  • To samé platí pro Workspace – možnost výběru pod kterým Workspace chce developer pracovat. Tedy místo DBMS_WM.GoToWorkspace opět klikání.
  • Pokud napíšete jméno objektu delší než 30 znaků tak se podrhne červeně.
  • Párování závorek v editoru ať je kurzor před či za závorkou.
  • Export dat přímo do clipboardu (a krásně i verzi v jaké to chceme – txt/csv..).
  • Podpora chainů pro DBMS_SCHEDULER.
  • a něco hodně dalšího..

Celý list nových features lze vyčíst zde: http://www.allroundautomations.com/bodyplsqldev100.html

Myslím, že dobrý ;) Workspace/Edice asi moc programátorů nepouživá, ale spousta novinek se zajisté hodí. V listu jsem spoustu věcí nezmínil, takže je třeba si ti projet co je užitečné a co patří kočičkám. Enjoy!

 Posted by at 21:51
Mar 232014
 

Tento blog je online od února 2011 a je na čase udělat nějaké to čištění. Od uvedeného data se mi podařilo ve wordpressu úspěšně rozepsat a nezveřejnit několik postů. A co je možné bych rád zpětně pročistil, dopsal či bez návratu odstranil. Důvody nedopsání jsou různé od časových až po takové, kde jsem zjistil, že to tak jednoduché nebude – napříkad mi tu straší článek o tom jak založit tabulku. Moje představa byla založení tabulky pomocí insertů do datového slovníku pod SYSem, modifikace datových bloků, dopočítání kontrolních součtů atd. A musím říct, že jsem nebyl úspěšný – tabulku se mi daří téměř správně založit, funguje describe, select a vše vypadá korektně, ale insert se prostě nedaří (internal error oracle). A od té doby jsem se k tomu nedostal. Rozepsaných a slíbených postů, které jsem nezveřejnil bych se rád postupně zbavil.

Před dvěma lety jsem měl  plánu napsat post o hintu IGNORE_ROW_ON_DUPKEY_INDEX, který patří mezi ty specifické, které mění chování a nevěnují se optimalizaci. Oracle ho uvádí společně tyto hinty: CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX a RETRY_ON_ROW_CHANGE. A když už jsem v tom, rovnou je zkusme všechny ;. A začněme hned tím prvním CHANGE_DUPKEY_ERROR_INDEX.

CHANGE_DUPKEY_ERROR_INDEX – Umí změnit typ chyby v případě, že dojde k porušení unikátnosti. Konkrétně z ORA-001 udělá ORA-38911. K čemu je to dobré? Například pokud jsou nad tabulkou dva různé unikátní sloupce a jednoduššímu zjištění ve kterém ze zmíněných došlo k porušení unikátnosti. Hint lze použít v INSERT a UPDATE statementu. Takže příklad:

create table tbl_unique_hint_test as
select rownum as first_unique,
rownum as second_unique
from dual connect by level<6;

Synaxe je následující:

Tedy takto:

insert /*+CHANGE_DUPKEY_ERROR_INDEX(tbl_unique_hint_test(first_unique)) */ into tbl_unique_hint_test values (1,1);
SQL Error: ORA-38913: Index zadaný v tipu k indexu není platný

Vyhučelo na chybě. I když jde zadat pro zjištění unikátnosti místo indexu sloupce, tak není možné zadat sloupce na kterých není unikátní index/constraint. Dokumentace píše, že je to nutné toto omezení mín, ale nepíše co se stane když není – odpověď je tedy ORA-38913. Takže založení unikátního indexu nad sloupcem first_unique:

create unique index inx_unique_first on tbl_unique_hint_test(first_unique);

A opět test hintu:

create unique index inx_unique_first on tbl_unique_hint_test(first_unique);
unique index INX_UNIQUE_FIRST created.
insert /*+CHANGE_DUPKEY_ERROR_INDEX(tbl_unique_hint_test(first_unique)) */ into tbl_unique_hint_test values (1,1);
SQL Error: ORA-38911: nesplněna podmínka jedinečnosti (APEX_WORKOUT.INX_UNIQUE_FIRST)

Fajn, číslo chyby je jiné. Ale daleko zajímavější je to takto:

create unique index inx_unique_second on tbl_unique_hint_test(second_unique);
unique index INX_UNIQUE_SECOND created.
insert /*+CHANGE_DUPKEY_ERROR_INDEX(tbl_unique_hint_test(second_unique)) */ into tbl_unique_hint_test values (1,1);
SQL Error: ORA-38911: nesplněna podmínka jedinečnosti (APEX_WORKOUT.INX_UNIQUE_SECOND)

Nyní když jsou nad tabulkou unikátní dva indexy má nová chyba ORA-38911 přednost ORA-001. Tedy místo toho, aby příkaz vyhuče na duplikaci nad sloupcem FIRST_UNIQUE, protože hodnota 1 již tam je, vyhučí na ORA-38911 pro sloupec SECOND_UNIQUE.

 Posted by at 00:05
Jul 142013
 
Již nějaký ten týden je venku dvanáctka s dokumentací. Když pominu celý koncept “pluggable database” a všechny změny, které se toho týkají, tak je tu spousta nových fíčur, které budeme milovat (i nenávidět), ale jejich chování a implementace není nějak technicky zajímavá. Ovšem pak jsou tu větší fíčury, které stojí za podstatně více pozornost a minimálně tři již mám vybrány “hybridní histogramy“, “adaptivní exekuční plány” a “automatická akceptace akceptovatelného exekučního plánu v sql plan managementu nalezeného autotunnig taskem“. A zajisté najdu další . Na některé bych se rád podíval blíže a pochopitelně sem o nich napsal nějaký ten post – a jako první jsem zkoumat hybridní histogramy ;)

Nicméně článek se postupně rozrůstá a tak jsem se rozhodl vyhodit ven do jiného postu jednu věc, která mi na Oracle 11gr2 vadila a která bohužel nadále zůstává. Jednoho času mi trápil sloupec se zkosenými daty – říkejme mu třeba MDS_LAST_UPD – ne, protože bych si to vymyslel, ale protože se tak opravdu jmenoval. MDS_LAST_UPD měl jednu velmi nehezkou dvojvlastnost – zkosená data a zároveň velice často aktualizovaná a ještě navíc byl pekelně důležitý. Což bohužel s neaktuálními statistikami vedlo na vcelku špatně odhadovanou cardinalitu, pokud histogram nebyl aktuální (desítky hodin max.). Výpočet/odhad počtu řádků, které má predikát vrátit je bohužel pro hodnoty, které jsou mimo interval histogramu tzn. <LOW_VALUE nebo >HIGH_VALUE stejný. A to bohužel nezávisle na tom, jak distribuce dat vypadá v histogramu.

Na obrázku (ano, obrázku – graf to není ;) to pak vypadá nějak takto. Červená křivka jsou hodnoty v histogramu:
Hodnoty mimo LOW a HIGH histogramu

Hodnoty X1 a X2 - mimo LOW a HIGH histogramu

Oracle pak pro WHERE ID=X1 odhadne stejný počet řádků jako pro WHERE ID=X2. Protože X1 je od LOW_VALUE hodnoty v histogramu stejně daleko – 50 – jako X2 od hodnoty HIGH_VALUE. Ale pouhým pohledem na histogram si od oka asi tipnem, že pro X1 by bylo lepšejší odhadovat větší cardinalitu než pro X2. Neřešme o kolik – ale určitě by se pro to hodil přesnější výpočet
než:

Cardinalita= num_rows /num_distinct * (1 - (Distance - num_distinct) / (high_value - low_value))

Vzorec jsem splašil, kdys na netu – je to pro operátor “=” a hodnoty mimo histogram. Přesný vzorce to, imho, úplně není – nikdy není odhadováno méně než 1 řádek a chtělo by to asi ještě obalit nějakým max(1,ten výraz). Ale je vidět, že pokles je lineární, žádné normální rozdělení, jak by si asi člověk tipnul a hodnotě rozhoduje nejen spread mezi hodnotou a LOW_VALUE případně HIGH_VALUE, ale i samotný spread mezi LOW_VALUE a HIGH_VALUE. Kdo chce počítat přesně, je k tomu kniha Cost-Based Oracle Fundamentals od Jonathana Lewise - bohužel, jak píše na začátku, jen co to vyzkoumá a vydá knihu – Oracle už má novou verzi databáze, což je bohužel fakt – nejaktuálnější verze knihy sotva pokrývá “desítku”.

Nicméně zpět k odhadování cardinality pro hodnoty mimo histogram a nějakou zkoušku (na verzi 11gR2):

CREATE TABLE TEST_11G_HISTOGRAM AS
SELECT
CASE
WHEN ROWNUM<300000 THEN MOD(ROWNUM,21)
ELSE MOD(ROWNUM,32001)
END AS ID
FROM DUAL CONNECT BY LEVEL<=475000;

Tabulka s 475 000 řádky. Hodnoty <0-20> jsou v tabulce cca 15005x a hodnoty <21-32000> pouze 5x, na obrázku nějak takto:
Distribuce dat v jedniném sloupci ID

Distribuce dat v jedniném sloupci ID

Spočet histogramu:
begin
dbms_stats.gather_table_stats(USER,'TEST_11G_HISTOGRAM',method_opt=>'FOR ALL COLUMNS SIZE 254');
end;
Histogram

Histogram

Odhadovaná cardinalita a predikáty:
Predikát Odhadovaná cardinalita Skutečná cardinalita
where ID=0 1309 1504
where ID=-1 5 0
where ID=-10 5 0
where ID=-5000 4 0
where ID=-15000 3 0
where ID=32000 5 5
where ID=32000+1 5 0
where ID=32000+10 5 0
where ID=32000+5000 4 0
where ID=32000+15000 3 0

Je vidět, že odhad cardinality pro zkosená data pro hodnoty mimo histogram Oracle řeší nezávisle na tom, co má v histogramu - pro zkosená data odhaduje stejnou cardinalitu (modře/vlevo) pod LOW_VALUE a stejným způsobem i pro hodnoty nad HIGH_VALUE (červeně/vpravo) nezávisle na distribuci dat – smutné, smutné a ještě jednou smutné. Tenhle příklad je dost umělý, reálně však nuly nebudou mimo interval histogramu a tipneme si, že cardinalita pro hodnoty pod LOW_VALUE by měla být prostě odhadována většejší.

Trošku tam mate ten přechod na záporná čísla, ale to je jen příkladem – obecně jsou tam prostě čísla, či datum – jako tomu bylo v mém nenáviděném sloupci MDS_LST_UPD.
A nyní hrubou silou, kdy dochází ke změně odhady cardinality (tedy klesá to 5,4,3,2,1):

DECLARE
P INT:=-1;
A INT:=-1;
BEGIN
FOR I IN 1..50000 LOOP
EXECUTE IMMEDIATE 'EXPLAIN PLAN FOR SELECT * FROM TEST_11G_HISTOGRAM WHERE ID=-'||i;
SELECT MAX(CARDINALITY) INTO A FROM PLAN_TABLE WHERE
OPERATION='TABLE ACCESS' AND OBJECT_NAME='TEST_11G_HISTOGRAM' AND FILTER_PREDICATES='"ID"=(-'||to_char(I)||')';
IF P<>A THEN
dbms_output.put_line('Změna v -'||I||', CARDINALITY='||A);
P:=A;
END IF;
END LOOP;
END;
Asi nepotřebuje komentář – jede predikáty od -1 do -50000, nechává pro ně vygenerovat (EXPLAIN PLAN FOR..) exekuční plán, fetchne cardinalitu (SELECT CARDINALITY..) a pokud je rozdílná než minule, vyhodí to na output. Výsledek je pak něco takového:
Změna v -1, CARDINALITY=5
Změna v -3776, CARDINALITY=4
Změna v -10048, CARDINALITY=3
Změna v -16320, CARDINALITY=2
Změna v -22591, CARDINALITY=1
Fajn, to byly hodnoty pod LOW_VALUE, a nyní po menší úpravě skriptu ještě jednou pro hodnoty nad HIGH_VALUE:
Změna v 32001, CARDINALITY=5
Změna v 35774, CARDINALITY=4
Změna v 42046, CARDINALITY=3
Změna v 48318, CARDINALITY=2
Změna v 54589, CARDINALITY=1
Obě strany intervalu tedy padají, naprosto stejně. Například pro změnu na odhad cardinality 3 je to hodnota -10048, tzn vzdálenost 10048 vlevo od LOW_VALUE a hodnota 42046, tzn vzdálenost 42046-32000=10046 vpravo od HIGH_VALUE. Rozdíl 10048 vs 10046 je prostě zaokrouhlení – v histogramech i v odhadech.
Pokled odhadu cardinality mimo histogram

Pokled odhadu cardinality mimo histogram

Nelíbí, nelíbí a nelíbí..
 Posted by at 12:36
Jul 122013
 
Nedávno jsem byl svědkem diskuze dvou kolegů, kde řeč byla o tom jestli by šlo udělat to, že jednou by v selectu byla analytická klauzule projection, tzn select count(*) over (partition by..) a jednou by byl select spouštěn bez ní. Existovaly dva názory – jeden že to nejde bez toho aniž bychom měli dva podobné selecty v proceduře a druhý obecný, že to lze. Na meetingu jsem sprostě mlčel neb jsem na to neměl úplně přesný názor, neztotožňoval jsem se ani s větou, že to lze. Ale ani s větou, že bychom na to potřebovali dva selecty, když nebudeme uvažovat podmínečnou kompilaci. Nemyslel jsem si, že bychom potřebovali dva různé selecty – jeden s analytickou klauzulí a jeden bez. Spíše než dva selecty jsem si říkal, že budou nutné dva výstupy řádkového generování, což nezbytně nutně neznamená dva různé selecty. Mířeno na otázku jestli, nedokážeme napsat schizofrenní sql select, kde můžeme optimalizátor řídit a buď mu analytickou klauzuli povolit vykydlit nebo naopak mu tuhle optimalizaci nedovolit? Konkrétně jsem myslel na něco takového:

select
a,
b,
case when plsql_promena=1 then 0 else C end
from (select
a,
b,
count(*) over (partition by C) as C
from TBL_TEST)

Myšlenka byla taková, že pokud z PL/SQL enginu propasuji proměnnou (a tedy v sql enginu to bude už jako konstanta) plsql_promena s hodnotou 1, pak zcela jistě Oracle nemusí provádět sort, neb může vykydlit “count(*) over (partition by d) as C”, protože to k výsledku nepotřebuje. Naopak pro plsql_promena<>1 to prostě spočítat musí – ten výsledek chceme. Nicméně takhle to nefunguje, Oracle optimalizátor to takhle nevykydlí.
Napadlo mi to, protože Oracle má tuhle úžasnou optimalizaci:

select
a,
b
from (select
a,
b,
c from TBL_TEST)

Oracle nebude číst sloupec C – sice je požadován v subselectu, ale ve výsledku se na něj vůbec neodkazujeme, není tedy potřeba.
Dá se na to udělat krásný test – viz starší příspěvek na blogu o covering indexech. Tedy ten test.

Založení tabulky:
create table tbl_test
as
select rownum as a,
rownum as b,
rownum as c
from dual connect by level<=100

Constrainy jsou potřeba (nebo je nutné to vypsat v selectu IS NOT NULL):
ALTER TABLE TBL_TEST MODIFY (A NUMBER CONSTRAINT cons_a NOT NULL);
ALTER TABLE TBL_TEST MODIFY (B NUMBER CONSTRAINT cons_b NOT NULL);
ALTER TABLE TBL_TEST MODIFY (C NUMBER CONSTRAINT cons_c NOT NULL);

A nyní index, ale pouze na sloupcích A a B:
CREATE INDEX inx_tbl_test_ab ON TBL_TEST(a,b);

A nyní exekuční plán pro select všech tří sloupců:
Exekuční plán pro SELECT A,B,C

Exekuční plán pro SELECT A,B,C

Sloupec C má jen v tabulce, musí do ní. Exekuční plán pro select sloupců a,b:
select sloupců A a B

Exekuční plán pro SELECT A,B

V tomhle případě zafungovar covering index a Oracle čte pouze index ve kterém má oba sloupce – a i b. A constrainy zaručují, že tam není žádná hodnota NULL, jinak by to nebylo možné, protože v b-tree indexu NULLy nejsou. A ted exekuční plán pro select uvedený dříve – v subselectu chci sloupce A, B, C ale v hlavním selectu se odkazuji jen na sloupce A a B:
Exekuční plán pro select a,b from (select a,b,c from tbl_test)

Exekuční plán pro select a,b from (select a,b,c from tbl_test)

Hébičkozní, ne? Optimalizátor rozpoznal, že C pak vůbec nepotřebujeme a proto fyzicky jde jen do indexu ve kterém může najít pouze A a B sloupec, C tam vůbec není. Dokonce funguje i když se na to navěsí funkce:
Exekuční plán pro subselect s funkcí

Exekuční plán pro subselect s funkcí

Oracle dokonce vykydlí v optimalizaci i spouštění funkce, když její výsledek v hlavním selectu vůbec nepoužiji. Tím to však končí, jakmile se na něj začne odkazovat bude to reálně spuštěno – a to bohužel dokonce i když logicky ten výsledek nemůže být použit a je to odvozeno od konstant, tedy:
Exekuční plán pro select a,b,case..

Exekuční plán pro select a,b,case..

A nezafunguje na to bohužel ani hint DYNAMIC_SAMPLING, který by měl umožnit optimalizátoru vypočítat všechno, co v době optimalizace může. A překvapivě nefunguje ani tohle:
Exekuční plán pro "nesmyslný" join..

Exekuční plán pro "nesmyslný" join..

Úplně optimální by bylo přečíst index inx_tbl_test_ab a z něj hodnoty pro A,B a sloupec tbl_dva.C doplnit hodnotou null – jinou mít ani nemůže – join (1=2) sice nikdy nebude uspokojen, ale optimalizátor tohle bohužel neumí – join prostě fyzicky provede a tabulku TBL_TEST si olízne, ač má dostatek informací pro to, aby to dokázal vrátit z indexu.
Suma sumárum – neodkazované věci kydlí, odkazované nekydlí aní když je na ně odkazováno pouze v nesplnitelné podmínce, kterou zaručeně zná v době optimalizace.
 Posted by at 18:11