Odhad cardinality – hodnota mimo histogram

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:

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ž:

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):

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:

Spočet histogramu:

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.

Nelíbí, nelíbí a nelíbí..