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

 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>