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
Jul 142013
 
Již nějaký ten týden je venku dvanáctka s dokumentací. Když pominu celý koncept “pluggable database” a všechny změny, které se toho týkají, tak je tu spousta nových fíčur, které budeme milovat (i nenávidět), ale jejich chování a implementace není nějak technicky zajímavá. Ovšem pak jsou tu větší fíčury, které stojí za podstatně více pozornost a minimálně tři již mám vybrány “hybridní histogramy“, “adaptivní exekuční plány” a “automatická akceptace akceptovatelného exekučního plánu v sql plan managementu nalezeného autotunnig taskem“. A zajisté najdu další . Na některé bych se rád podíval blíže a pochopitelně sem o nich napsal nějaký ten post – a jako první jsem zkoumat hybridní histogramy ;)

Nicméně článek se postupně rozrůstá a tak jsem se rozhodl vyhodit ven do jiného postu jednu věc, která mi na Oracle 11gr2 vadila a která bohužel nadále zůstává. Jednoho času mi trápil sloupec se zkosenými daty – říkejme mu třeba MDS_LAST_UPD – ne, protože bych si to vymyslel, ale protože se tak opravdu jmenoval. MDS_LAST_UPD měl jednu velmi nehezkou dvojvlastnost – zkosená data a zároveň velice často aktualizovaná a ještě navíc byl pekelně důležitý. Což bohužel s neaktuálními statistikami vedlo na vcelku špatně odhadovanou cardinalitu, pokud histogram nebyl aktuální (desítky hodin max.). Výpočet/odhad počtu řádků, které má predikát vrátit je bohužel pro hodnoty, které jsou mimo interval histogramu tzn. <LOW_VALUE nebo >HIGH_VALUE stejný. A to bohužel nezávisle na tom, jak distribuce dat vypadá v histogramu.

Na obrázku (ano, obrázku – graf to není ;) to pak vypadá nějak takto. Červená křivka jsou hodnoty v histogramu:
Hodnoty mimo LOW a HIGH histogramu

Hodnoty X1 a X2 - mimo LOW a HIGH histogramu

Oracle pak pro WHERE ID=X1 odhadne stejný počet řádků jako pro WHERE ID=X2. Protože X1 je od LOW_VALUE hodnoty v histogramu stejně daleko – 50 – jako X2 od hodnoty HIGH_VALUE. Ale pouhým pohledem na histogram si od oka asi tipnem, že pro X1 by bylo lepšejší odhadovat větší cardinalitu než pro X2. Neřešme o kolik – ale určitě by se pro to hodil přesnější výpočet
než:

Cardinalita= num_rows /num_distinct * (1 - (Distance - num_distinct) / (high_value - low_value))

Vzorec jsem splašil, kdys na netu – je to pro operátor “=” a hodnoty mimo histogram. Přesný vzorce to, imho, úplně není – nikdy není odhadováno méně než 1 řádek a chtělo by to asi ještě obalit nějakým max(1,ten výraz). Ale je vidět, že pokles je lineární, žádné normální rozdělení, jak by si asi člověk tipnul a hodnotě rozhoduje nejen spread mezi hodnotou a LOW_VALUE případně HIGH_VALUE, ale i samotný spread mezi LOW_VALUE a HIGH_VALUE. Kdo chce počítat přesně, je k tomu kniha Cost-Based Oracle Fundamentals od Jonathana Lewise - bohužel, jak píše na začátku, jen co to vyzkoumá a vydá knihu – Oracle už má novou verzi databáze, což je bohužel fakt – nejaktuálnější verze knihy sotva pokrývá “desítku”.

Nicméně zpět k odhadování cardinality pro hodnoty mimo histogram a nějakou zkoušku (na verzi 11gR2):

CREATE TABLE TEST_11G_HISTOGRAM AS
SELECT
CASE
WHEN ROWNUM<300000 THEN MOD(ROWNUM,21)
ELSE MOD(ROWNUM,32001)
END AS ID
FROM DUAL CONNECT BY LEVEL<=475000;

Tabulka s 475 000 řádky. Hodnoty <0-20> jsou v tabulce cca 15005x a hodnoty <21-32000> pouze 5x, na obrázku nějak takto:
Distribuce dat v jedniném sloupci ID

Distribuce dat v jedniném sloupci ID

Spočet histogramu:
begin
dbms_stats.gather_table_stats(USER,'TEST_11G_HISTOGRAM',method_opt=>'FOR ALL COLUMNS SIZE 254');
end;
Histogram

Histogram

Odhadovaná cardinalita a predikáty:
Predikát Odhadovaná cardinalita Skutečná cardinalita
where ID=0 1309 1504
where ID=-1 5 0
where ID=-10 5 0
where ID=-5000 4 0
where ID=-15000 3 0
where ID=32000 5 5
where ID=32000+1 5 0
where ID=32000+10 5 0
where ID=32000+5000 4 0
where ID=32000+15000 3 0

Je vidět, že odhad cardinality pro zkosená data pro hodnoty mimo histogram Oracle řeší nezávisle na tom, co má v histogramu - pro zkosená data odhaduje stejnou cardinalitu (modře/vlevo) pod LOW_VALUE a stejným způsobem i pro hodnoty nad HIGH_VALUE (červeně/vpravo) nezávisle na distribuci dat – smutné, smutné a ještě jednou smutné. Tenhle příklad je dost umělý, reálně však nuly nebudou mimo interval histogramu a tipneme si, že cardinalita pro hodnoty pod LOW_VALUE by měla být prostě odhadována většejší.

Trošku tam mate ten přechod na záporná čísla, ale to je jen příkladem – obecně jsou tam prostě čísla, či datum – jako tomu bylo v mém nenáviděném sloupci MDS_LST_UPD.
A nyní hrubou silou, kdy dochází ke změně odhady cardinality (tedy klesá to 5,4,3,2,1):

DECLARE
P INT:=-1;
A INT:=-1;
BEGIN
FOR I IN 1..50000 LOOP
EXECUTE IMMEDIATE 'EXPLAIN PLAN FOR SELECT * FROM TEST_11G_HISTOGRAM WHERE ID=-'||i;
SELECT MAX(CARDINALITY) INTO A FROM PLAN_TABLE WHERE
OPERATION='TABLE ACCESS' AND OBJECT_NAME='TEST_11G_HISTOGRAM' AND FILTER_PREDICATES='"ID"=(-'||to_char(I)||')';
IF P<>A THEN
dbms_output.put_line('Změna v -'||I||', CARDINALITY='||A);
P:=A;
END IF;
END LOOP;
END;
Asi nepotřebuje komentář – jede predikáty od -1 do -50000, nechává pro ně vygenerovat (EXPLAIN PLAN FOR..) exekuční plán, fetchne cardinalitu (SELECT CARDINALITY..) a pokud je rozdílná než minule, vyhodí to na output. Výsledek je pak něco takového:
Změna v -1, CARDINALITY=5
Změna v -3776, CARDINALITY=4
Změna v -10048, CARDINALITY=3
Změna v -16320, CARDINALITY=2
Změna v -22591, CARDINALITY=1
Fajn, to byly hodnoty pod LOW_VALUE, a nyní po menší úpravě skriptu ještě jednou pro hodnoty nad HIGH_VALUE:
Změna v 32001, CARDINALITY=5
Změna v 35774, CARDINALITY=4
Změna v 42046, CARDINALITY=3
Změna v 48318, CARDINALITY=2
Změna v 54589, CARDINALITY=1
Obě strany intervalu tedy padají, naprosto stejně. Například pro změnu na odhad cardinality 3 je to hodnota -10048, tzn vzdálenost 10048 vlevo od LOW_VALUE a hodnota 42046, tzn vzdálenost 42046-32000=10046 vpravo od HIGH_VALUE. Rozdíl 10048 vs 10046 je prostě zaokrouhlení – v histogramech i v odhadech.
Pokled odhadu cardinality mimo histogram

Pokled odhadu cardinality mimo histogram

Nelíbí, nelíbí a nelíbí..
 Posted by at 12:36

 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>