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

 Leave a Reply

(required)

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>