Naděje na stejný exekuční plán po ruční paralelizaci

Mimo featur Oracle se občas snažíme o paralelizaci sami – pomocí rozdělení tabulky do více threadu pomocí MOD(x,2)=1 či ORA_HASH(x,3)=1 atd. Rozdělení do několika threadu je určitě pro dlouhotrvající selecty výhodné a není důvod se tomu vyhýbat. Dnes bych chtěl ovšem mluvit o samotném přidání podmínky pro paralelizaci, která má pochopitelně dopad na selectivitu a exekuční plán, což si ovšem často nepřejeme – když se rozhodneme “ručně” něco zparalelizovat, pak často nestojíme o to, aby paralelizační podmínka nakopla náš exekuční plán (čekal bych ho již vyladěný, když již se člověk rozhodne pro paralelizaci):

Takže go, hébičky, go na hraní si selectivitou, založme nějakou tabulku, klasika:

create table tbl_test_paralelizace as
select rownum as id, -- nejake id
mod(rownum,40) pod, -- nějaké id na normální podmínku
rownum as paralelizacni_sloupec
from dual connect by level<100000;


Spočítáme statistiky:

begin
dbms_stats.gather_table_stats('AZOR','TBL_TEST_PARALELIZACE',method_opt=>'FOR ALL COLUMNS SIZE 254');
end;

A nyní select, který budeme paralelizovat:

Select pro paralelizaci

Selectivita je 75% a odhadnutá cardinalita 77354 (ze 100000 řádků) je tedy ok, reálně select vrací 74999 záznamů.

Nyní přidáme mod(paralelizacni_sloupec,2)=1, což by měla být příprava na paralelizaci ve dvou threadech:

Nástřel paralelizace

Select vrací 37500 řádků (druhé vlákno by vrátilo druhou půlku a po té by chtělo výsledky spojit – čistě pro ukázku – pro takto jednoduchý select to smysl nemá). Cardinalita 774 :/. Dneska si ani tak nechci stěžovat na to, že je Oracle v tomto případě úplně mimo – funkce MOD – a uživatelské funkce obecně mají selectivitu 1% by default, a tedy 774 Oracle sebral z (77354*0,01= cca 774).  O čem chci mluvit je, jak mu “vnutit” zpět selectivitu původního selectu, tak aby po paralelizaci byla větší šance na stejný exekuční plán – na tabulce, na které budeme paralelizovat vnutíme totiž Oracle stejnou selectivitu.

Založme následující funkci:

create or replace function fake_selectivity return number
as
begin
return -1;
end;


Funkce je opravdu dummy – prostě vrací mínus jedničku.  V selectu ji správně použijeme, ale srovnáme ji však na plus jedna, čímž nezměníme žádným způsobem výsledek, ale trošku zahýbeme s odhadnem selectivity :

Selectivita s přidanou funkcí

Nyní je odhad selectivity Oracle následující:
0,75 x (0,01 + 0,01) = 0,75 x 0,02 = 0,015 slectivita a z toho cardinalita 1500. Výpočet se od odhadu liší nepřesností statistik a mou zjednodušeným vzorcem, ve skutečnosti OR sice znamená sečtení selectivit, ale také odečtení toho, co maji pravděpodobně společného, takže něco takového (0,01+0,01-(0,01*0,01)).

A celé cvičení bylo jen proto, abychom mohli Oracle podstrčit pro funkci fake_selectivity selectivitu, která vrátí zpět odhad, který jsme nabořili paralelizační podmínkou:

ASSOCIATE STATISTICS WITH FUNCTIONS fake_selectivity DEFAULT SELECTIVITY 100;


A nyní vytoužený paralelizovaný select se stejným odhadem cardinality jako původní:

Paralelizace bez nakopnutí exekučního plánu

Výpočet je cca následující:
0,75 x (0,01 + 1) = 0,75 selectivita a odtud cca 77354 cardinalita.

Cíl splněn – select je paralelizovaný a zároveň tato změna pravděpodobně nezmění předtím ověřený exekuční plán (neudělá to nějakou nově nečekanou neastedloopu). Řešit by se to dalo mnoha způsoby (tenhle je jeden z nejrychlejší), ale tenhle má ještě jednu výhodu – lze jednoduchým výpočtem dopočítat správnou selectivitu, tak, aby Oracle bral třeba i vpotaz tu paralelizaci (a lépe než pouze z MOD):

DISASSOCIATE STATISTICS FROM FUNCTIONS fake_selectivity;
ASSOCIATE STATISTICS WITH FUNCTIONS fake_selectivity DEFAULT SELECTIVITY 50;

V tomto případě Oracle odhadne, že chceme cca 50% dat z tabulky, tedy pro dva thready. Fake_selectivity, je to, co to řídí – MOD a jeho defaulutní selectivita 1% je to to co to kazí