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

 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>