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

Potyčky s dlouhotravajícími selecty zná asi každý. Nejdůležitější je rychle a především přesně identifikovat  tu největší pain v daném selectu a začít u ní. Protože až po nalezení problémového místa může teprve přijít nějaká ta optimalizace -  přepis selectu, založení indexu, ohintování atd. Takže dnes pár tipů jak zjistit, co databáze dělá na  právě běžícím selectu a tedy jak identifikovat problémové místo či problémová místa.

Především je třeba správně  umět chápat “cost” v exekučním plánu, řádek s největším costem totiž rozhodně nemusí být problém a typicky ani není. Proč okamžitě neobvinit řádek exekučního plánu s největším costem, když nás trápí doba běhu selectu je doufám jasné a plyne to z  toho, co cost představuje. Spíš si myslím, že je třeba vysvětlit větu, proč si myslím, že tenhle to zrovna nebude. Záleží hodně na systému, v dávkových systémech, které jsem doposud potkával já tomu bylo tak, že estimátor spíše počet vrácených řádků do dalšího stupně exekučního plánu podhodnocuje než nadhodnocuje. Konkrétní  číslo, které se dá vyjet jednoduchým selectem je 2,58x. Tedy je 2,58x pravděpodobnější, že Oracle počet vrácených  řádků v exekučním plánu podhodnotí – pochopitelně v závislosti na systému, tohle je číslo jednoho konkrétního

Důvodů proč se asi člověk bude na batch systémech bude setkávát spíše s podhonocením  se asi najde několik. Obecně je tomu tak, že máme nějakou představu o datech a sloupce jsou nějakým způsobem téměř vždy korelovány,  což o proti náhodným datům a operátoru AND, kdy estimátor selectivity podmínek pronásobí opravdu spíše vede k podhodnocení. Podobně rozdělení do 4 threadu ORA_HASH(id,3) je funkce se selektivitou 25%, ale  defalutní selektivita pro funkce je 1%. Jinými slovy – záleží na systému a pokud tam programátor nesedí prvně, měl by mít nějakou představu -já by default očekávám, že další stupně exekučního plánu budou mít jako vstup počet řádků podhodnocen a tedy i cost bude menší než ve skutečnosti – a to nejspíše budou ty řádky, které budou trápit ;) Podhodnocení počtu očekávaných řádků vede logicky k použití indexu, tam kde to není úplně nejlepšejší..

A nyní tipy jak sledovat, co se tam děje:

1) Pohled v$session_longops

select * from v$session_longops where sid=&mysid order by start_time desc;

Velice známé view, která použije semtam nějaké IDE na zobrazení progressu či programátor. Zde je důležité správně hodnoty intepretovat. Především do view nejdou všechny operace, ale jen některé (dynamic sampling, spočet statistik, přístupy k objektům (Index/Table (Fast/Skip/)(Range/Full) Scany), Sorty, Hash join a pár dalších) Další věc – do view jdou operace trvající déle než 6 sekund – to znamená, že pokud Index Range Scan operace trvá v Nested loopě několik hodin, ovšem pro každou interaci se to vejde pod 6s tak v$session_longops nezobrazí nic ačkoliv dlouhotrvající Index Range Scan do tohoto view jde. Pozor na špatnou intepretaci těch nejzajímavějších sloupců sofar a totalwork (a jejich časové analogie). Protože výraz to_char(round((sofar/totalwork)*100)) ||’ %’ vypadá, že by mohl ukazovat kolik % již je hotovo. Ukazuje, ale ne však vždy – například pokud je tabulka partišnovaná a select je jen z jedné partition,  pak totalwork ukazuje počet datových bloků pro selectovanou partition v případě, že je známá v době optimalizace (static partition prunning, where x=’X’). Pokud je konkrétní partiton známá až za běhu selectu (dynamic partition pruning, where x=(select dummy from dual)) pak totalwork je průměrný počet datových bloků všech partition tabulky). Jinými slovy do totalwork mohou vstupovat statistická data, průměry i odhady, na což je třebas myslet, když je v v$session_longops řádek hotový na 178% a stále to pokračuje ;)

A těch příkladů by se našlo více, dalším jsou například dvě a více analytické funkce v projection ukazující se v exeukčním plánu stále jako jeden řádek. Sice to vypadá jako, že to trvá jeden krok (sort operace), ale v totalwork je vidět, že počet řazených bloku s každou analytickou funkcí, může růst (až dvojnásobek, podle sloupců order by) a tedy ne, neni to zadarmo ;)

Suma sumárum: jednoduché view ve kterém toho moc není a kde je třeba umět správně intepretovat  hodnoty. Co je fajn, je možnost vlastního záznamu v v$session_longops pomocí API v dbms_application_info.  Další fajn věcička je, že se tam propisují í některé dlouhotrvající tooly Oracle (backupy, přepočet statistik)

2) Pohled v$active_session_history

Tohle view je mým favoritem. View jako takové neslouží ke sledování vytížení ani běhu sql, ale obsahuje snpashoty v$session po 1 vteřině včetně toho, co zrovna daná session dělá. Stačí tedy řádky zgrupovat podle sql_plan_line_id a count(*) řádků ukazuje kolik sekund na daném řádku session trávila či ještě tráví:

select count(*),his.sql_plan_line_id, his.sql_plan_operation,his.sql_plan_options from v$active_session_history his where his.SQL_ID='sql id' and sample_time>sysdate-1 group by his.sql_plan_line_id,his.sql_plan_operation,his.sql_plan_options;

Suma sumárum: Jednoduché, krásné, efektivní. Ve většině případů dostačující. Ne moc vhodné pro sledování paralelních selectů. V$active_session_history rocks!

3) Pohled v$sql_plan_monitor

V případě, že v$active_session_history je nedostatečné, je tu ještě vyšší kalibr : v$sql_plan_monitor. View do kterého jdou SQL trvající déle než 5 sekund (neni to překlep, sem po 5s do longops 6s, dle Oracle), paralelní selecty a selecty s hintem /*+ MONITOR */. A nejdou všechny ostatní – neparalelní kratší než 5s a případně s inverzním hintem /*+ NO_MONITOR */.

select * from v$sql_plan_monitor where sql_id=‘sql_id’;

V tomhle view je vidět téměř vše co je potřeba - doba potřebná pro danou operaci, jak jdou operace za sebou, jak velké byly zprávy které si mezi sebou posílaly paralelní části, počet řádků vystupujících z daného kroku, potřebná paměť, velikost tempu pro danou operaci, počet iterací nested loopy atd.

Suma sumárum: V některých případech hůře čitelné než v$active_session_history, ale lépe se tam odhalují vnořené nested loopy a je to podstatně vhodnější pro sledování paralelních selectů. Velká nevýhoda je doba po kterou v tomto view selecty zůstávají (velice krátce – desítky minut). Nejlepšejší vychytávka: Možnost zjisti v jaké nested loop iteraci zrovna Oracle je.

4) Pohled v$sesstat

Úžasné view se spoustou statistik (pro všechny session). Dokonce tolik statistik, že pro sledování běžícího selectu je vhodné dělat pomocí CTAS:

create table actual_stats as select sid,s.statistic#,value,name,class from v$sesstat s join v$statname sp
on (s.statistic#=sp.statistic#)
where s.sid=143;

A po nějaké chvilce to odečíst, aby bylo vidět co se mění:

select sid,s.statistic#,value,name,class from v$sesstat s join v$statname sp
on (s.statistic#=sp.statistic#)
where s.sid=143;
minus select * from actual_stats;

Tohle je spíše doplňková informace pro odhalení případných podezření na operace, které nejsou na první pohled vidět a nebo na detailní rozpad.

Suma sumárum: O proti ostatním pohledum není samo o sobě vhodné pro identifikaci problému v selectu (neukazuje na kterém objektu, jaký exekuční plán apod.), nicméně pomůže v případech, které nejsou patrné v ostatních view. Typickým příkladem by mohl být chybějící index v cizím klíči – full table scan, který je nutný k dodžení referenční integrity v exekučním plánu vidět není,
ve statistikách se však objeví.

 

Tak to by bylo pro dnešek vše ;) Tipy se snad hodí.. alespoň pro některé systémy – pro OLTP se to moc nehodí ;)

 Posted by at 22:16

 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>