Oct 292014
 

Dnešní post bude o pořadí a vyhodnocování predikátů ve WHERE podmínce.  Původně to mělo být spíše o hintu ORDERED_PREDICATES, ale pojďme to trošku rozšířit ;) Je asi známá věc, že pokud píši do podmínky něco ve smyslu:

IF podmínka1 AND podmínka2 THEN

Že Oracle pokud nebude splněna podmínka1 již nebude spouštět podmínka2, protože k tomu není důvod – ifem již to nemůže projít a nic to na té skutečnosti již nezmění. Což se samozřejmě netýká jen Oracle - většina jazyků pracuje přesně takto. Co je méně známý fakt je, že podobně pracuje Oracle i ve vyhodnocování WHERE podmínky. Respektive pravida jsou trošku složitější. Toto je pořadí dle dokumentace:

  • Predikáty bez uživatelských funkcí, typových metod, subselectů jsou vyhodnocovány jako první.
  • Predikáty s uživatelskou funkčí či typových metod, které mají přidělenu cost jsou vyhodnocovány podle costu v zestupném pořadí.
  • Predikáty kde je uživatelská funkce či typová metoda a nemají přiřazeny costy.
  • Pak jsou vyhodnocovány podmínky, které se do WHERE dostaly tranzitivně přes jinou podmínku (propasoval je ta optimalizátor – push predicate apod.)
  • A po té jsou vyhodnocovány podmínky, které obsahují subselecty

Začněme příkladem, který je možné vidět prakticky na každém blogu:

create table t_predicates as
select 'Suicide silence' as kapela, 1 as id from dual union all
select '2' as kapela, 2 as id from dual union all
select 'Wintersun' as kapela, 3 as id from dual

(tabulka)

A příkladový select:
select * from t_predicates where kapela=2 and id=2;

Idea je myslím jasná – pokud Oracle začne vyhodnocovat nejprve kapela=2, pak jsme v pytli a skončíme na ORA-01722, vlastně již jsem na to kdysy nějaký post psal: Nebezpečná chyba a exekuční plán. Pokud Oracle začne nejprve s id=2, pak řádky id=1 a id=3 vyloučí automaticky a nebude nikdy testovat Suicide Silence=2 a Wintersun=2 a tedy ani zkoušet konvertovat oblíbené kapely na čísla a select doběhne ;)

A nyní jaký je rozdíl mezi:
select * from t_predicates where kapela=2 and id=2;
select * from t_predicates where id=2 and kapela=2;

Popravdě – nevím. V dokumentaci, se píše že predikáty, které jsou na stejné úrovni jsou vyhodnocovány odzadu, má to ovšem nějaké “ale” a tím je třeba i selectivita predikátů – ty nejvíce selektivní jdou pochopitelně jako první. V tomto případě selecty doběhnout v obou verzích  (12.1c) – což znamená, že mi Oracle kašle na “odzadu“, ale prioritně řadí id=2 z nějakého důvodu. Napadají mi dva - selectivita, ačkoliv oba predikáty jsou naprosto stejně selectivní (podle explain plánu), tak možná až po za okrouhlení a Oracle je interně seřadí před zaokrouhlením. Druhá varianta (a podle mě pravděpodobnější) je ta, že Oracle interně musí zkusit zkonvertovat sloupec kapela na number (alespoň to zkusit), což je volání funkce, což je drahé a proto až druhé.

Dobře, takže to zkusme s hintem ORDERED_PREDICATES:

Prvně jde Oracle do kapela=2, čímž okamžitě končí na ORA-01722, ale opačně je to lepšejší:

Tímhle většina blogu končí, nicméně pojďme dál - do funkcí. Ovšem nejprve založme nějakou zajímavějšejší tabulku:

create table t_pred_func as
select rownum id, case when rownum<=5 then 1 else 2 end haf from dual
connect by level<11;

A nyní tam začněme navěšovat funkce a hrát si s tim. Většinou se do funkcí dává dbms_output apod, aby bylo vidět kolikrát je daná funkce volaná. Nicméně dává mi smysl tam nyní nacpat dbms_lock.sleep.  Už jen proto si osvojit, že na tomhle pořadí záleží, a že to má výkonové dopady. Takže založme dvě funkce – jedna, která Oracle zdrží o 100 sekund a druhá, která zdrží Oracle o 5 sekund. Obě vrací hodnotu 2. Takhle o řád+ od sebe, aby to bylo krásně vidět a daly se počty volání dopočítat a krásně od sebe oddělit.

create or replace function f_sleep_100s return number
is
begin
dbms_lock.sleep(100);
return 2;
end;
/
create or replace function f_sleep_5s return number
is
begin
dbms_lock.sleep(5);
return 2;
end;

Fajn, a nyní pojďme začít hébičkovat:

select * from t_pred_func where id=f_sleep_5s and haf=f_sleep_100s;

Oracle má na predikátech navěšeny dvě uživatelské funkce. Nemá naprosto žádnou představu o costu ani selectivitě – respektive obě funkce mají defalutní cost a defalutní selectivitu.

Trvá 150 sekund. Konkrétně 10x Oracle testuje id=f_sleep_5s a v jednom případě je ta podmínka splněna (id=2) a v té chvíli musí jít i do testu  haf=f_sleep_100s tedy 10 x 5 sekund + 1x 100 sekund. Daleko horší je, když si to vynutíme opačně:

select /*+ ordered_predicates */ * from t_pred_func where haf=f_sleep_100s and id=f_sleep_5s;

Tentokrát to však trvá 1025s . Konkrétně 10x Oracle testuje haf=f_sleep_100s s tím, že haf=2 ja splněno v 5ti případech a Oracle tedy musí jít ještě do testu id=f_sleep_5s.  Což dělá celkem 10x 100 sekund + 5x 5 sekund. No dobře, pojďme ještě do OR operátoru:

select /*+ ordered_predicates */ * from t_pred_func where haf=f_sleep_100s OR id=f_sleep_5s;

V tomhle případě opět Oracle začne funkcí f_sleep100s, ovšem jeho možnosti logického vykydlení jsou naprosto jiné. Nejprve 10x ověří haf=f_sleep_100s a všude tam, kde je to nesplněno (5 případů, kde je haf=1) musí zkusit jestli se náhodou to nesplněné nepodaří splnit v druhé podmínce. Tedy opět 10x 100 sekund + 5x 5 sekund  = 1025s, ovšem tentokrát se id=f_sleep_5s volala u jiných řádků. Takže si to vynuťme opačně:

select /*+ ordered_predicates */ * from t_pred_func where haf=f_sleep_5s OR id=f_sleep_100s;

Pro tento případ Oracle musí otestovat haf=f_sleep_5s pro každý řádek a tam kde to není splněno (5x) ještě provolat id=f_sleep_100s. Což tentokrát dělá 10x 5sekund + 5×100 sekund. Inu, trváme 550 sekund. Ovšem slíbil jsem, že tenhle post nebude jen o hintu ordered_predicates, takže si pojďme vynutit pořadí provádění funkcí pomocí přiřazení jim nedefalutního costu:

ASSOCIATE STATISTICS WITH FUNCTIONS f_sleep_100s DEFAULT COST (10,40,0);
ASSOCIATE STATISTICS WITH FUNCTIONS f_sleep_5s DEFAULT COST (10,10,0);
select * from t_pred_func where haf=f_sleep_5s OR id=f_sleep_100s;

Přiřazená COST samozřejmě zdaleka neodpovídá reálu, funkce která čeká 100 sekund by měla obrovský cost. Nicméně mým cílem není to přesně spočítat (pokud chcete, pak je třebas mrknout do tabulek sys.aux_stats$ jako či dbms_stats.get_system_stats/dbms_stats.gather_system_stats  a spočítat si to.). Po spuštění trvá tehle select 550 sekund.  Což znamená, že Oracle jde prvně do funkce f_sleep_5s, takže prohoďme costy:

DISASSOCIATE STATISTICS FROM FUNCTIONS f_sleep_100s,f_sleep_5s;
ASSOCIATE STATISTICS WITH FUNCTIONS f_sleep_100s DEFAULT COST (10,10,0);
ASSOCIATE STATISTICS WITH FUNCTIONS f_sleep_5s DEFAULT COST (10,40,0);
select * from t_pred_func where haf=f_sleep_5s OR id=f_sleep_100s;

Kupodivu nezafungovalo a select trval opět 550 sekund. Tedy nejprve jsem zkusil exekuční plány zda se opravdu COST funkcím přiřadil a on přiřadil. Takže wtf? Popravdě jsem na to nepřišel – nastavení cursor_sharing=exact, byly parasovány dvě sql, ale z nějakého důvodu Oracle nebral vpotaz nově přiřazené COSTy a nevyhodnocoval podle toho predikáty, nicméně úspěch jsem slavil když jsem ho donutil vyprázdnit shared_pool:

ALTER SYSTEM FLUSH SHARED_POOL;

Je to teda s vakuovou pumou na vrabce, správně bych měl flushnout jen to sql nebo objekty pod ním. A teprve po té to trvalo 1050 sekund. Oracle opravdu začal nejprve funkcí f_sleep_100s na základě mnou špatně přirazeného costu ;)  A zkusme ještě najaký příklad na select:

select /*+ ordered_predicates */ * from t_pred_func where (id>(select 5 from dual)) and (id=f_sleep_5s+6 or id=f_sleep_100s+7) and (id=f_sleep_100s);

Celé SQL mi trvalo 625 sekund takže pojdme do nějakého toho vysvětlení, pro připomenutí data vypadají takto:

(data)

Hintem jsem vynutil provádění zleva, takže takhle to vypadá:

ID=1 – netrvá prakticky nic, vyfiltruje to select 5 from dual
ID=2 – netrvá prakticky nic, vyfiltruje to select 5 from dual
ID=3 – netrvá prakticky nic, vyfiltruje to select 5 from dual
ID=4 – netrvá prakticky nic, vyfiltruje to select 5 from dual
ID=5 – netrvá prakticky nic, vyfiltruje to select 5 from dual
ID=6 -  select 5 from dual splněno Oracle musí vyhodnocovat dál. f_sleep_5s+6 – nesplněno v závorce musí Oracle ještě otestovat f_sleep_100s+7 kvuli OR. To je nesplněno, takže konec. Celkem 105 sekund.
ID=7 – select 5 from dual splněno Oracle musí vyhodnocovat dál. f_sleep_5s+6 – nesplněno v závorce musí Oracle ještě otestovat f_sleep_100s+7 kvuli OR. To je nesplněno, takže konec. Celkem 105 sekund.
ID=8 – select 5 from dual splněno Oracle musí vyhodnocovat dál. f_sleep_5s+6 – tentokrát nesplěno v závorce v závorce již Oracle nemusí testovat f_sleep_100s+7. To padá. Musí však ještě otestovat id=f_sleep_100s To je nesplněno, nicméně přidalo to 100s do exekuce.. Celkem 105 sekund.
ID=9 – select 5 from dualsplněno Oracle musí vyhodnocovat dál. f_sleep_5s+6 – nesplněno v závorce musí Oracle ještě otestovat f_sleep_100s+7 kvuli OR. To je splněno takže ještě test na,  id=f_sleep_100s. Celkem 205 sekund.
ID=10 – select 5 from dual splněno Oracle musí vyhodnocovat dál. f_sleep_5s+6 – nesplněno v závorce musí Oracle ještě otestovat f_sleep_100s+7 kvuli OR. To je nesplněno, takže konec. Celkem 105 sekund.

Což je 105+105+105+205+105+(select z dualu, který s tím nezahýbe)=625 sekund, přesně podle očekávání trhu ;) Dobře? No, umíme to lépe:

select /*+ ordered_predicates */ * from t_pred_func where (id>(select 5 from dual)) and (id=f_sleep_100s) and (id=f_sleep_5s+6 or id=f_sleep_100s+7)

500 sekund. Vcelku krásně spočítatelné. Pro ID<=5 vypadnou řádky díky select  select 5 from dual Pro ID>5 se však musí spouštět f_sleep_100s,  což je 5x 100 sekund.Nikdy nedojde ke spuštění dalších predikátů, takže 500 sekund.

Tohle byl hodně úmělý příklad – podle definice jde na vyhodnocení filtrování podle subselectu jako poslední. Nicméně zde  je však daleko rychlejší select konstanty z dualu než spouštění funkce (5 a 100 sekund). Nicméně pro tento případ je tu ORDERED_PREDICATES. Pokud to nechám na Oracle záleží pak na tom jak jsem nastavi costy funkcím f_sleep_100s a f_sleep_5s+6 se kterou začne – v horším případě 1105 sekund.

Tolik k pořadí vyhodnocování predikátů. A všem, co si chtějí zaletět do USA na pár hodin zabruslit chci říct, že i když to není třebas možné, tak dokud není konec, máme ve skříni brusle a latenci se kterou reagujem na změnu držíme proklatě nízko.

 Posted by at 21:19
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 052014
 

Pokračování krátkého zkoumání jaké hodnoty se objevují ve sloupci STARTS v pohledu V$SQL_PLAN_MONITOR. Předchozí díl se věnoval počtu startů u tabulky, která není partišnovaná a dnešní díl se tedy bude věnovat tomuto sloupci u partitiovaných tabulek. Další díl budu věnovat paralelním selectům a pak se uvidí ;) Protože je to dost zajímavý sloupec a těch kombinací a zajímavých operací je spousty, ale muj čas je omezen. Takže hurá na to!

Takže pojďme udělat rovnou tabulku s nějakýma partitions:

create table tbl_range (a_column)
PARTITION BY RANGE(a_column)
(
PARTITION p1 VALUES LESS THAN(1),
PARTITION p2 VALUES LESS THAN(2),
PARTITION p3 VALUES LESS THAN(3),
PARTITION p4 VALUES LESS THAN(4),
PARTITION p5 VALUES LESS THAN(5),
PARTITION p6 VALUES LESS THAN(6)
) as
select mod(rownum,6) from dual connect by level<1001;

A nyní select:

select /*+ MONITOR */ * from tbl_range;

Asi by člověka napadlo, že bude mít tolik startů, kolik má partitions. A přesně tak tomu je, ovšem s menším ale – protože první SQL Developer fetchne pouze 50 řádků (defalutní hodnota), takže je třeba proscrollovat a dostáváme počet startů rovných 6ti:

(počet startů – range)

Nebylo zajímavé, že? Prozradím, že s INLIST a HASH partitions to funguje naprosto stejně, daleko zajímavější je INVERVAL partitions. A vlastně důvodu proč tenhle článek vzniknul:

CREATE TABLE tbl_rang_i (a_column NUMBER(10))
PARTITION BY RANGE(a_column)
INTERVAL(13)
(PARTITION base VALUES LESS THAN (0)
);

A nyní insertneme nějaká data:

insert into tbl_rang_i
select rownum from dual connect by level<=400 union all
select rownum+800 from dual connect by level<=400;

Konkrétně je to 800 řádků. Čísla od 1..400 a 800..1200. Takže se podívejme, kolik má tabulka partitions:

select owner,table_name,partition_count from all_part_tables where lower(table_name)='tbl_rang_i';

Počet partitions v tomhle view je 1048575, překvani? Taky jsem nedávno byl ;) Ale Oracle v tomhle view pro tabulky na kterých je interval partitioning udává Oracle vždy číslo 1048575. Číslo není nějak moc magické, udává maximální počet partitions, který tabulka může být.  Takže zkusime jiné view:

select count(*) from all_tab_partitions where lower(table_name)='tbl_rang_i';

Je jich 64. Lehce podezřele binárně kulaté číslo ;) Ale to je jen náhoda – začínání od 0 a dva intervaly v rozmezí 1..400 a 800..1200 (takže něco jako select 2*(ceil(400/13))+1+1 from dual).  A nyní konečně počet startů:

select /*+ MONITOR */ * from tbl_rang_i;

(počet startů – range-interval)

94. Což je lehce podezřelé číslo. Speciálně, když čekáte až Vám doběhne select a full table scan nad tabulkou co má 64 partitions přesáhne 65 a vesele si pokračuje. Nicméně po několika mých testech jsem doše k tomu, že Oracle pro interval partitions připočítává do STARTS i “mezery”. Tedy výpočet pro tento případ je select ceil(1200/13)+1 from dual tzn. 94 (jedna je pro BASE partition).

Inu počty startů:

  • Pro range, list, hash – vždy počet partitions
  • Pro range-interval – (spread mezi LOW a HIGH hodnotou v partiton sloupci) / intervalem partišnování (případně +1 pokud base partition je mimo)
  • Precedenci berou “nižší karta” tedy full scanu nad supartišnovanou tabulkou je ve sloupcu STARTS počet čtených subpartitions.

Bohužel vzhledem k mému omezenému času tento článek končím, ale napadají mi daleko zajímavější scénáře – systém a referenční partitioning, partition wise-join a počet startů..

 Posted by at 11:52
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
Aug 302014
 

Vcelku častý scénář – potřebujem zvalidovat constrain a zároveň jsme si vědomi, že data jsou špinavá a validace constrainu nám hnedtak neprojde. Takže nezbývá než nějaké řádky promazat či jinak vyřešit duplikace. Dnešní tip je věnován klauzuli EXCEPTIONS INTO, která je spoučástí příkazu ALTER TABLE a umožní uložit rowid duplicitních řádků při validaci constrainu. Takže hurá do toho!

CREATE TABLE TEST_CONS_EX AS SELECT ROWNUM AS ID FROM DUAL CONNECT BY LEVEL<=666;

A ještě nějaké náhodné duplicity (29 řádků)

INSERT INTO TEST_CONS_EX SELECT A FROM (SELECT ROWNUM A FROM DUAL CONNECT BY LEVEL<=666) WHERE ORA_HASH(A,21)=7;

Nic zajimavého, tabulka s jedním sloupcem ID s čísly od 1 do 666 s tím, že duplikovaných hodnot je tam 29, vždycky po dvou kouskách. Následně je potřeba vytvořit tabulku do které se porušení constrainu bude reportovat. Oracle odkazuje na scripty UTLEXCPT.SQL nebo UTLEXPT1.SQL. Ovšem stačí mít jen tabulku ve správné struktuře a není potřeba používat tyhle scripty.

CREATE TABLE dej_to_sem(row_id ROWID,
owner VARCHAR2(30),
table_name VARCHAR2(30),
constraint VARCHAR2(30));

Dokonce se to i hezky pamatuje, rowid, owner, jméno tabulky, jméno constrainu. Velikost přestně tolik, koli to může maximálně mít. Nyní vytvořme nějaký PK klíč. Neměl by být enabled.

ALTER TABLE TEST_CONS_EX ADD CONSTRAINT test_pk PRIMARY KEY (id) DISABLE;

A nyní enable společně s exceptions into klauzulí:

ALTER TABLE TEST_CONS_EX ENABLE CONSTRAINT test_pk EXCEPTIONS INTO DEJ_TO_SEM;¨
SQL Error: ORA-00001: nesplněna podmínka jedinečnosti (AZOR3.TEST_PK)

Sice nás to vyfuckovalo, ale díky použití klauzule máme k dispozici rowid řádků, které jsou duplicitní:

Data v exceptions tabulce..

Důležité je pamatovat si, že každý řádek, který je tam duplicitní je tam tolikrát kolikrát je duplicitní (nikoliv n-1) tedy odmazat z master tabulky všechny záznamy, které jsou uvedeny v tabulce duplicit rozhodně není dobrý nápad, šéf ani Marta Jandová by za to nepochváli. Exspeciálně, když další logický krok po odmazání je zapnutí constrainu, což je příkaz ALTER TABLE, což je změna struktury tabulky a tedy nemožnost napsat nějakou verzi flashaback selectu (ORA-01466: nelze číst data – definice tabulky byla změněna) Jinak žádné záludnosti, tabulka pro uložení duplikací musí být pochopitelně přístupná majiteli constrainu.

 Posted by at 15:31
Jun 212014
 

Ahoj, nedávno se mi stala taková nepříjemná věc, kterou bylo upgrade php verze od mého hostingu, což vedlo na rozhození v IE a na čínské znaky v Chromech, Firefoxech a jiných náhrážkách.  Když už jsem musel řešit upgrade PHP, rovnou jsem se rozhodl upgradovat i verzi WordPressu a tudíš přecházím i na nové theme. Vybral jsem něco trošku jiného, tak se snad bude líbit ;)  Což berte jako omluvu za předchozí dny a možná i budoucí, kdy to budu trošku upravovat a ohýbat podle svého gusta.  Jsem ted trošku busy, ale napíšu jsem teda i v pruběhu toho nějaký ten článek – i když jsou měsíce, kdy blog vypadá mrtvě, tak normálně žiju, nic mi nepřejelo.

Dále jsem zavřel komentáře ke všem článkům. Se změnou template by to mohlo vypadat na nedemokratický režim. Pravdou však je, že počet spamových viagra komentářů (i tisíce denně) již přerostl rozumnou úroveň a já jsem se svou prostatou vcelku spokojen.. pokusím se implementovat nějěký human-check a komentáře zase otevřít.

 Posted by at 11:20