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

Hintování se stává národním sportem! No, dobře, přeháním – semtam sem přijde někdo s dotazem na hinty. Přiznejme si, že hinty INDEX, FIRST_ROWS, USE_HASH, DRIVING_SITE, FULL.. jsou ty nejpoužívanější, ale špatně se o nich něco píše – nejsou prostě zajímavé (nebo alespoň mi tak nepřípadají) a dokumentace je vcelku dobře popisuje, včetně příkladů. Nicméně pár chvil bych hintům věnovat mohl – ovšem jen těm nejlepším a nejzajímavějším kouskům ;)

Takže hint číslo jedna: CARDINALITY

Důvodů, proč jsem si vybral tento hint jsou tři: Jednak se moc dobře hodí na předchozí článek :Naděje na stejný exekuční plán po paralelizaci , není dokumentovný a především je slibováno, že od Oracle 12c, bude oficiálním hintem. A zrovna tento hint je tak něja považován za “bezepčný” i když není pro 10g ani 11g oficiálně posvěcený Oraclem.

Hint slouží vnucení estimátoru správnou cardinalitu, kterou z nějakého důvodu správně neurčí (z jakéhokoliv důvodu – statistiky, nevhodně nastavený dynamic_sampling parametr nebo prostě pricnipálně nemůže kombinaci daných predikátů podchytit)

Synaxe: /*+ cardinality(<tabulka> <cardinalita> ) */

Takže s minulého postu, podstatně jednodušeji, nedokumentovaným hintem:

Nedokumentovaný hint cardinality

Nedokumentovaný hint cardinality

 Šikovná věcička, minimálně se hodí na odlaďování – nápověda Oracle, kolik řádků má očekávat a po té nejspíše jinak sestaví exekuční plán – a po té jeho prozkoumání, podobně jako s DYNAMIC_SAMPLING hintem, ale o něm třeba příště ;)

 Posted by at 16:50
Nov 052012
 

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

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

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í

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

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í :D

 Posted by at 00:52
Nov 012012
 

O tom, že se Oracle rozhodl správně s opuštěním RBO (Rule Based Optimalizer) a s prosazením CBO (Cost Based Optimalizer) asi není pochyb.  CBO má na svou spustu výhod bohužel i nějaká ta omezení a nějaké ty nevýhody. Jednou z větších nevýhod je “by default” nemožnost správně odhadovat selektivitu/cardinalitu u závislých sloupců.  Místo dlouhého textu raději nějaký příklad, takže go hébičky go!

CREATE TABLE TABULKA_ENERGY_DRINKU AS
select
rownum as ID,
case
when mod(rownum,3)=2 then 'Red Bull'
else to_char(rownum)
end red_bull,
case
when mod(rownum,3)=2 then 'Monster Energy'
else to_char(rownum)
end monster_energy
from dual connect by level<10000;

Pointa je taková, že pokaždé když je zbytek po dělení třemi dva, tak je ve sloupci red_bull hodnota Red Bull a ve sloupci monster_energy hodnota Monster Energy. Hodnoty ve sloupcich red_bull a monster_energy jsou na sobě tedy závislé. V datech takto:

Závislé sloupce

Závislé sloupce

Sesbírání statistik nad tabulkou:
begin
dbms_stats.gather_table_stats('AZOR','TABULKA_ENERGY_DRINKU',method_opt=>'FOR ALL COLUMNS SIZE 254');
end;

A nyní exekuční plán pro WHERE red_bull=’Red Bull’ (pro druhý energetický nápoj je pochopitelně stejný)

Cardinalita pro where red_bull='Red Bull'

Cardinalita pro where red_bull='Red Bull'

Očekáváme 3333, takže odhad cardinality 3366 je ok, řádově 33% tabulky. (mimochodem čekal bych, že Oracle 12c tam se slibovanými hybritními histogramy odhadne přesně 3333, ale uvidíme ;)

Problém nastává až uvedením druhého závislého sloupce ve where podmínce (ačkoliv výsledek zůstane stejný):

Problém v určení cardinality závislých sloupců

Problém v určení cardinality závislých sloupců

Oracle v tomto případě neuvažoval závislost sloupců a proto AND v podmínce vyhodnotí jako vynásobení selectivit z obou podmínek, konkrétně : (3366/9999)*(3366/9999)=0,1133. Tzn. selectivita 11,33% a z toho plyne i odhanutá cardinalita: 0,1133*9999=1133

Nicméně protože tam kde je red_bull=’Red Bull’ je i monster_energy=’Monster Energy’ je správná cardinalita 3333 (či 3366) a optimalizátor Oracle je hodně vedle i při takto jednoduché podmínce.

Možná to vypadá jako umělý příklad, ale ze života – zákazník má end_date vyplněný, když již není zákazníkem a zároveň status=N. A v tomto případě optimalizátor podobně nakopne podmínka where status=’N’ and end_date is not null.

Jak z toho? Například hint DYNAMIC_SAMPLING s parametrem 4 a více:

Dynamic sampling a závislé sloupce

Jedn z důvodů, proč by hint DYNAMIC_SAMPLING měl být náš oblíbený ;) Cardinalita je v tomto případě dokonce přesně 3333. Awesome.

Jenže hint zrovna systémový není, že? Od toho jsou “rozšířené statistiky”, které jsou právě určeny pro silně závislé sloupce, konkrétně CREATE_EXTENDED_STATS:

DECLARE
ls_name VARCHAR2(30);
BEGIN
ls_name:=DBMS_STATS.CREATE_EXTENDED_STATS (
ownname =>'AZOR',
tabname =>'TABULKA_ENERGY_DRINKU',
extension =>'(RED_BULL,MONSTER_ENERGY)'); -- výraz/závislé sloupce
dbms_output.put_line(ls_name);
END;

Funkce CREATE_EXTENDED_STATS vrací název extension, který je možné si pak prohlédnout v *_STAT_EXTENSION:

Objekt EXTENSION STATS

Objekt EXTENSION STATS

Po té je nutné opět sesbírat statistiky nad tabulkou či danými sloupci:
begin
dbms_stats.gather_table_stats('AZOR','TABULKA_ENERGY_DRINKU',method_opt=>'FOR ALL COLUMNS SIZE 254');
end;

A nyní pohled na odhad cardinality:

Vylepšený odhad CARDINALITY přes CREATE_EXTENDED_STATS

Vylepšený odhad CARDINALITY přes CREATE_EXTENDED_STATS

3267 – To neni špatné, ne? Podstatně blíže realitě než kolik poskytují statistiky, které neřeší závislosti sloupců. Mimochodem mimo závislých sloupců pomůže CREATE_EXTENDED_STATS i s cardinalitou/seletivitou výrazů.

 Posted by at 00:43
Mar 312012
 

Jaký bude mít prováděcí plán následující select ?

SELECT count(*) FROM tabulka?

Myslíte si, že je třeba projít celou tabulku a spočítat všechny řádky? Tedy full table scan? Správná odpověď je ovšem trošku složitější, záleží jestli je nad tabulkou vhodný index, který má méně datových bloků (ve smyslu je možné ho rychleji přečíst – menší počet bloků ještě neznamená, že je jej možné rychleji přečíst, ačkoliv je to velice pravděpodobné), ze kterého lze count(*) zjistit. Velice vhodný index je bitmapový –  je malý a především ukládá hodnoty null do své struktury. Pokud neexistuje žádný bitmapový index nad touto tabulkou, pak je možné použít k výpočtu count(*) klasický  b*-tree index, ale to pouze v případě, že sloupec zaručeně neobsahuje hodnotu null, což jde zajistit NOT NULL constrainem, alternativně podle indexu svázaného z primárním klíčem tabulku (což je technicky UNIQUE+NOT NULL).

Ideálně tedy pojede tento select přes fast full index scan. Pokud však nemáte bitmapový index nebo žádný b*tree index v kombinaci s NOT NULL constrainem či ještě něco jiného (Query Rewrite například),  pak vás teprve čeká drahý full table scan. Alternativně pokud nemáte NOT NULL constraint, ale máte index můžete za Oracle převzít zodpovědnost a napsat a tento select :

SELECT count(*) FROM tabulka WHERE col_co_nema_null_a_ma_index IS NOT NULL;

Pak stačí když je na sloupci u kterého jsme dali v podmínce NOT NULL obyčejný b*tree index a jede se přes fast full index scan.

Oracle se podobně však chová nejen pro count(*), ale chová se tak také pokud máte v projection pouze sloupec či sloupce z indexu , takzvaný covering index. Textu již je tu dost, takže malinký příklad.

1) Založíme tabulku
CREATE TABLE TBL_SCAN_TEXT AS SELECT ROWNUM AS PK, TO_CHAR(chr(MOD(rownum,22)+50)) test_char,'POOR_INDEX' poor_index,'A'||ROWNUM unk FROM DUAL CONNECT BY LEVEL<10000;

2) Test exekučního plánu
EXPLAIN PLAN FOR SELECT COUNT(*) FROM TBL_SCAN_TEXT;
Dle očekávání trhu je v PLAN_TABLE full table scan. Není zde žádný vhodný index (či jiná struktura), která by optimalizátoru umožnila rychlejší přístup k výsledku tohoto selectu.

3) Fajn, založme tedy index, například nad sloupcem PK, který je odvozen od ROWNUM (číslo řádku) původního selectu
CREATE INDEX inx_tbl_scan_text_pk ON TBL_SCAN_TEXT(PK);

4) Test exeukčního plánu
EXPLAIN PLAN FOR SELECT COUNT(*) FROM TBL_SCAN_TEXT;
Smolík, do b*tree indexu se neukládají NULL hodnoty a Oracle nemá jistotu ani informaci, že ve fyzické struktuře indexu jsou všechny řádky, tedy full table scan, jinak to nejde.

5) Převezmem zodpovědnost za Oracle, že ve sloupci PK nejsou žádné hodnoty null a pomůžeme mu v selectu
EXPLAIN PLAN FOR SELECT COUNT(*) FROM TBL_SCAN_TEXT WHERE PK IS NOT NULL;
A je to tu, fast full index scan. Chceme počet všech řádků v tabulce a nepotřebujeme (alternativně přejímáme odpovědnost za to, že tam nejsou) řádky, kde PK má hodnotu null.

6) Takže přidejme constraint NOT NULL
ALTER TABLE TBL_SCAN_TEXT MODIFY PK CONSTRAINT con_nn_tbl_scan_text NOT NULL;

7) Test exeukčního plánu
EXPLAIN PLAN FOR SELECT COUNT(*) FROM TBL_SCAN_TEXT;
Ano, je to fast full index scan, krásná ukázka toho, proč je důležité používat constrainty i z pohledu výkonu, že?

8) Nad naší tabulkou máme NOT NULL constraint a index, co zkusit založit bitmapový index?
CREATE BITMAP INDEX inx_tbl_scan_text_test_char ON TBL_SCAN_TEXT(test_char);

9) Test exeukčního plánu
EXPLAIN PLAN FOR SELECT COUNT(*) FROM TBL_SCAN_TEXT;
Dle očekávání trhu, v bitmap indexu se ukládají i nully hodnoty a index je podstatně menší (u mne v tomto případě 21 vs 3 datové bloky) než b*-tree na sloupci PK, tedy optimalizér vybírá cestu fast full index scan, nyní však přes bitmapový index inx_tbl_scan_text_test_char.

10) A co exekuční plán tohoto selectu?
EXPLAIN PLAN FOR SELECT PK FROM TBL_SCAN_TEXT;
Všechnu infromaci má Oracle v indexu, tedy také fast full index scan, k čemuž se váže zmíněný název covering index, tedy index, ze kterého lze vytáhnout všechny informace, typicky je však covering index index nad více než jedním sloupcem.

Suma sumárum,  NOT NULL constraint je nezbytný pro zachování kvality dat, ale může být také velice důležitý z pohledu výkonu. Každá rozumná tabulka by měla obsahovat primární klíč a tedy by se mělo jet při count(*) na fast full index scanu téměř vždy. Ovšem odhalení chybějícího NOT NULL constriantu nad indexovaným sloupcem stále může urychlit tento select, pokud bude tento index menší než ten nad primárním klíčem.

 Posted by at 22:14
Dec 162011
 

 

Můj šéf nedávno řekl jednu moc pěknou větu “Business si o každém IT systému defalutně myslí, že umí všechno” , o systému na kterém pracuji si navíc myslím, že slovo ASAP je také defalutní. Některé ASAP požadavky od přijmu do produkčního běhu dělí 40 minut (včetně deployment managementu, pracovních příkazů a o testech si nechte zdát..). Nemám problém sloužit na “ASAP” prostředí, ale měl bych mín alespoň jednou za čas nějaký volný prostor  na maintenence, jinak se to prodraží i našemu businessu.

Především budu narážet na výkon – znáte někoho z businessu, kdo by vám sdělil “já už to nepotřebuji”? Já nikoho – přidávat, přidávat a zase přidávat. V Sieblu například co se týče manzání (Organizace, Sloupce, Objekty, Projekty, Seed responsibility…) tato funkcionalita často chybí a ještě jsem neviděl někoho, kdo by si na to ztěžoval ;)

Pokud chce někdo něco přidat  do tabulky, uděláte ALTER TABLE ADD () a jede se dál.

Co už ale nikdo neudálá je seřazení sloupců. Sloupce, které mají často hodnotu null mají být na konci. Na netu je spousta “radilů”, kteří tvrdí, že na pořadí sloupců nezáleží. Na pořadí záleží a hodně. Oracle vynechává ukládání všech posledních sloupců s null hodnotama. Což znamená menší počet datových bloků, rychlejší full scany , menší počet zřetězených řádků, menší potřebu paměti v SGA, menší práci s LRU datových bloků.. a mohl bych pokračovat.

Dnes mi totiž praštila do oka jedna z našich větších tabulek: 13 334 565 řádků krát 498 sloupců a děsivý počet null mezi sloupci 230 a 495 zatímco poslední 3 sloupce hodnotu null nemaují prakticky nikdy tzn. nejhorší případ. (Mimochodem podobně trpí Siebel, kde je to ještě horší pak srovnat)

Takže hurá do analýzy:
1) Pomocí CTAS jsem vytvořil reprezentativní vzorek 150 000 záznamů:

CREATE TABLE sample_rowid (order_id VARCHAR2(30));
INSERT INTO sample_rowid SELECT ORDER_ID FROM MON.ANALYZOVANA_TABULKA WHERE ROWNUM<=150000;

2) Vytvořil jsem sub-tabulku jako originál:

Tedy tabulku s vzorkem 150 000 záznamů, kde jsou sloupce seřazeny přesně tak jak je máme na produkci.

CREATE TABLE analyzovana_normal as SELECT (sloupce_v_normalnim_poradi) FROM MON.ANALYZOVANA_TABULKA WHERE ORDER_ID IN (SELECT order_id FROM sample_rowid);

3) Vytvořil jsem tabulku, kde ke kazdému sloupci vypíšu počet null hodnot:

Prázdná tabulka pro jméno sloupce a počet hodnot null.

CREATE TABLE tbl_ordered (COL_NAME VARCHAR2(30), N_COUNT NUMBER);

4) Script, který jí naplní

Vzorek 150 000 objednávek je více než reprezantativní, statistici by měli radost ;)

Mimochodem tabulku bych mohl stejně tak naplnit (i vytvořit pomoci CTASu) ze statistik. Nicméně rozhodl jsem se pro PL/SQL script a přesné počty z mého vzorku.

declare
s_statement VARCHAR2(3000);
n_count NUMBER;
begin
for i in (SELECT * FROM ALL_TAB_COLS WHERE table_name='ANALYZOVANA_TABULKA') loop
s_statement:='SELECT COUNT(*) FROM
analyzovana_normal WHERE '|| I.COLUMN_NAME || ' is null';
EXECUTE IMMEDIATE s_statement INTO n_count;
INSERT INTO tbl_ordered VALUES (i.column_name,n_count);
commit;
end loop;
end;

5) Vyvořil jsem tabulku podle pořadí sloupcu a jejich počty s hodnotama null:

Doporučuju si vyselectovat i ten select – protože vypisovat 498 sloupcu se asi nikomu nechce ;)

CREATE TABLE analyzovana_ordered AS SELECT (sloupce_v_null_like_poradi) WHERE
ORDER_ID IN (SELECT order_id FROM sample_rowid);

6) Následuje spocitani statistik nad tabulkama:

ANALYZE TABLE
analyzovana_normal COMPUTE STATISTICS;
ANALYZE TABLE
analyzovana_ordered COMPUTE STATISTICS;

7) A zobrazení výsledku:

SELECT table_name,blocks,avg_space,chain_cnt FROM USER_TABLES WHERE TABLE_name in (UPPER('
analyzovana_ordered'),UPPER('analyzovana_normal'));

Table name Počet bloku Počet zřetězených bloku
analyzovana_normal 31194 168
analyzovana_ordered 23898 4

 

O 26% procent může být tabulka menší pouhým seřazením sloupců! A co je možná ještě důležitější – počet chained rows je nyní na 1/40! Je pekelnej rozdíl jestli má tabulka 26GB nebo 19 GB. Zvlášt když je to téměř 1/3 SGA.

Asi neni co řešit, je na čase přemýšlet jak to elegantně za provozu překopat. Ve skutečnosti si takhle asi projedu všechny tabulky – nemusim to dělat takto,  bude stacit projet informace o sloupcich. Pak je to rekurzivní select do systémových tabulek/view.

Mimochodem, když se nad tím člověk zamyslí, tak přijdete na to, že to není nejlepší řešení, z zhlediska optimality by se sloupce daly seřadit ještě lépe. Což vede na nějakou  optimalizační úlohu a implementaci nějaké heuristiky. Očekávaný benefit o proti tomuto nastřenelnému řešeni vs. cena nákladu na programátora – to asi zaměstanavateli neudělám ;) Jsem tam proto, abych odváděl práci, co se vyplatí – což snad dělám, narozdíl od “produkťáků” .. ;)

 Posted by at 18:52
Dec 142011
 

Dneska pár poznámek o některých optimalizacích PL/SQL a SQL, které mi zaujaly – ne tím, že by přinášely o hodně větší výkon ale tím, že vůbec existují a (dle mého názoru překvapivě) mohou poskytnout větší výkon i když některé v omezeném množství :

  • Oracle doporučuje aliasovat tabulky, protože alis (většinou kratší než název tabulky) může zmenšit velikost potřebné paměti.
  • Oracle doporučuje prefixovat v projekci názvy sloupců aliasem tabulky, ůdajně to redukujuje počet nutých rekurzivních selektů do datového slovníku (wow)
  • Oracle nedoporučuje kopírovat temporary loby, místo toho doporučuje kopírovat jen lokátor.
  • Oracle nedoporučuje používat DBMS_SESSION balík pokud to není nezbytně nutné, SET SESSION je rychlejší.
  • V PL/SQL se pro FORALL dá použít hint /*+APPEND_VALUES */, což patrně přináší opravdu vyšší výkon, nicméně pro mne novinka.
  • DBMS_UTLITY.EXEC_DDL_STATEMENT je pomalejší než EXECUTE IMMEDIATE
  • NOLOGGING známe, redo logy jsou generovány jen pro DDL datového slovníku – ale velikost tohoto minimálního logování může ještě ovlivnit supplemental logging na úrovni databáze.
  • Datové bloky jsou komprimovány i v buffer cache
  • Nespuštěná user funkce má cost 10 a selectivitu 0.01

 

 

 Posted by at 20:59