Godiva, dnes jedna z věcí, která se moc nepouživá, což je myslím škoda – SQL Plan Baseline a s několika málo featurama, které k tomu patří – automatické tuning sql tasky, akceptování sql plánů a jejich envolvování a podobně. SQL Plan Management měl již dávno nahradit outlines a jako obvykle do toho přináší více nových vlastností a všechno to zesložiťuje 
Nadruhou stranu přináší spoustu výhod, jednou z takových více diskutovaných je možnost více akceptovatelných plánů pro jedno sql, ale můžeme si poručit i jeden fixní jako tomu bylo u outlines. Outlines které je pochopitelně možné namigrovat na SPM.
První věc – chtělo by downgradovat Oracle 12.1c, protože v rámci zkoušení DBMS_SPM bude třeba mít více plánů a několik z nich určitě neoptimálních. Na 12.1c by se nám klidně mohlo stát, že v průběhu běhu sql se změní exekuční plán, což se na testování zrovna moc nehodí, a zároveň, už rovnou downgradujeme CBO a nastavíme použivání baselines:
ALTER SYSTEM SET optimizer_features_enable='11.2.0.1' SCOPE=BOTH;
ALTER SYSTEM SET optimizer_adaptive_reporting_only=TRUE SCOPE=BOTH;
ALTER SYSTEM SET optimizer_use_sql_plan_baselines=TRUE SCOPE=BOTH;
Databázový parametr optimizer_features_enable (taky session parametr a hint) asi není třebas představovat, ale optimizer_adaptive_reporting_only by se hodilo připomenout, je nový, – nově jsou adaptivní exekuční plány. Inu parametr mění chování adaptivních sql plánů – hodnota FALSE (default), znamená povolení adaptivních plánů, hodnota TRUE znamená, že jsou změny pouze reportovány.
Otázka je, jak vymyslet testovací scénář, kde postupně od od Oracle dostanu více exekučních plánů – protože ta úplně nejjednoduší technologie použití hintu – nepomůže, hint má před SQL Plan Baseline přednost, exactly speaking – takové sql má pak jinou signaturu a nematchnout si. Nicméně i tak je dost možností, jak Oracle donutit počítat jiné sql plány – bind peeky, filtrování funkcí, statistiky, parametry optimalizátoru, hacking a nebo prostě zakládání optimalizačních struktur (a schválně nepíši jen indexu) až po naloadování sql plánu do sel setu.
CREATE TABLE A_BASET AS SELECT ROWNUM+1000 ID FROM DUAL CONNECT BY LEVEL<54201; CREATE TABLE B_BASET AS SELECT ROWNUM+1000 ID FROM DUAL CONNECT BY LEVEL<51201; CREATE TABLE C_BASET AS SELECT ROWNUM+1000 ID FROM DUAL CONNECT BY LEVEL<51201; CREATE TABLE D_BASET AS SELECT ROWNUM+1000 ID FROM DUAL CONNECT BY LEVEL<51201;
A ted nějaké sql u kterého budeme evolvovat plány, ale ještě předtím funkce, index a statistiky. Funkce nedělá nic jiného než, že pro určité procento řádků vrací stejné ID, jinak -1. Takové obalení MOD funkce, kdyby náhodou bylo potřeba ještě nějak CBO potrápit. MOD funkci nemůžu přiřadit statistiky, F_MOD ano.
CREATE OR REPLACE FUNCTION F_MOD(P_PERCENT NUMBER,P_ID NUMBER) RETURN NUMBER
IS
BEGIN
RETURN (case when MOD(p_id,100)<P_PERCENT then p_id else -1 end);
END;
A indexy:
CREATE INDEX INX_A_BASET ON A_BASET(ID); CREATE INDEX INX_B_BASET ON B_BASET(ID);
A ještě statistiky, normálně bych na 12.1c nemusel po CTASu, nicméně po změně optimizer_features=’11.2.0.1′ enable již musím, porotože na 11g to ještě dostupné nebylo:
BEGIN DBMS_STATS.GATHER_TABLE_STATS('AZOR','A_BASET'); DBMS_STATS.GATHER_TABLE_STATS('AZOR','B_BASET'); DBMS_STATS.GATHER_TABLE_STATS('AZOR','C_BASET'); DBMS_STATS.GATHER_TABLE_STATS('AZOR','D_BASET'); END;
A nyní konečně nějaké neoptimální SQL na kterém bude možné vyšívat:
select * from A_BASET a
join B_BASET b on (a.id=b.id)
join C_BASET c on (b.id=c.id)
join D_BASET d on (c.id=d.id)
where
c.ID<2000 and
d.ID<2000 and
a.ID=F_MOD(100,a.ID) and
b.ID=F_MOD(100,b.ID);
Uf, na zlepšťování je tam toho myslím dost – přístupové cesty jsou přesně než by bylo optimální – na C,D jede Oracle full table scan, nicméně lepšejší by byl index, na A,B je to přesně opačně – měl by se jet ideálně full table scan, Oracle používá index.
V rámci zkoušení to pojďme naloadovat z SQL Setu. Vytvoříme SQL Set a do něj z sql area naloadujeme naše sql:
BEGIN DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name=>'SQL_SET_WINTERSUN',description=>'Budlíčky a šmudlíčky'); END;
A ještě naloadovat dané sql do SQL Setu:
DECLARE
c_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN c_sqlarea_cursor FOR SELECT VALUE(p)
FROM TABLE(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
' sql_id=''fuzzb02x0mu39'' ')
) p;
DBMS_SQLTUNE.LOAD_SQLSET (
sqlset_name => 'SQL_SET_WINTERSUN'
, populate_cursor => c_sqlarea_cursor
);
END;
A ověření, že se povedlo naloadovat (statement_count=1):
Další krok a jeden z posledních v rámci příprav je naloadovat sqlset do SQL Plan baselines:
DECLARE
godiva pls_integer;
BEGIN
godiva:= DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'SQL_SET_WINTERSUN');
END;
A ještě si změníme jméno:
DECLARE i NUMBER; BEGIN i:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(plan_name=>'SQL_PLAN_3w14kphm9j83a71488e3f', attribute_name=>'plan_name', attribute_value=>'ORIGOŠ_PLÁN'); END;
Tohle je lehce nepříjemné, v SPM, Sql taskach atd. je většinou vystavená funkce, která vrací nějakou hodnotu, ale zárovň to nejde vytáhnout z dualu – nelze v selectu modifikovat objekt, ačkoliv některé z nich zafungují (otázka je jak) i přes tuto chybovou hlášku, alespoň na 12.1c. Při alter sql baseline stačí zadat jen plan_name nebo sql_handle, paradoxně pro změnu názvu je vyžadováno staré jméno, s hadnle to nefunguje (patrně kvůli mapování 1:N).
A pojďme envolvovat! V některých případech by mělo stačit spustit testovací sql ještě jednou – od té doby co má Oracle Cardinality Feedback a hlidá si, rozdíl mezi svým očekáváním selectivity a skutečným početem vrácených řádků. Ale to se mě aktuálně netýká (nemam histogramy například), takže založme indexu nad ID tabulky C_BASET, kde určitě chybí:
CREATE INDEX INX_C_BASET ON C_BASET(ID);
No, a pak už stačí spustit testovací sql a máme nový plán, za pozornost stojí ještě ACCEPTED=NO, narozdíl od manuálního loadu AUTO-CAPTURE přidává by deafult neacceptovatelné plány:
A zase změna jména:
DECLARE i NUMBER; BEGIN i:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(plan_name=>'SQL_PLAN_3w14kphm9j83aeb69e7cd', attribute_name=>'plan_name', attribute_value=>'INDEX_C'); END;
A jedeme dále, index na D:
CREATE INDEX INX_D_BASET ON D_BASET(ID);
A zase spustit testovací sql a přejmenovat task:
DECLARE i NUMBER; BEGIN i:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(plan_name=>'SQL_PLAN_3w14kphm9j83afb7b557d', attribute_name=>'plan_name', attribute_value=>'INDEX_C_D'); END;
A SQL plány:
S tím, že aktuální plán je:
Takže vygenerujme report a necháme si schválit plán, zatím jen výkonově ověřený:
DECLARE
r clob;
BEGIN
r:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => 'SQL_3e0492ac2698a06a');
dbms_output.put_line(r);
END;
Výsledky k nahlédnutí. A především sql plán byl potvrzen a akceptován:
Plán byl akceptován jen jeden a ten nejlepší, by default (a taková je i filozofie) funkce ENVOLVE_SQL_PLAN_BASELINE potvrzuje zlešení spuštěním, ale v případě nouze se to dá přeskočít a acceptovat plán bez ověření pomocí prametru verify=>’NO’.
Jeden důležitý parametr, ale nechce vynechat – FIXED. Oracle k tomu píše výraz “preferované” – slovo FIXED vypadá, že jinudy cesta nepovede, ale je to trošku složitější – plánů s FIXED může být více (a pak Oracle vybere na základě odhadnutého costu) a FIXED plán nemusí být replikovatelný (drop index) a v tom případě bude CBO dát propadávat rozhodovacím stromem (ACCEPTED vs NOVÝ S MENŠÍM COSTEM). Inu ještě fixed:
declare
i number;
begin
i:=dbms_spm.alter_sql_plan_baseline(plan_name=>'INDEX_C',
attribute_name=>'fixed',attribute_value=>'YES');
end;
A pak už stačí jen vesele používat. Nebo ještě lépe tracovat si optimalizáror, což tak není úplně reklama na muj nedávný post, ale trace ukazuje velmi zajímavé věci – ve kterých se částečně mluví o dokumentaci – že optimalizace je stále provedena, pak je nalezen fixní plán, a následuje ověření proveditelnosti a přpočitání costu podle plánu Mimochodem je třebas myslet na to, že prvně je vygenerován “normální” plán ještě než je akceptován fixed (či nějaký jiný) a tudíš na EXPLAIN_PLAN pozor, nicméně v V$SQL.SQL_BASELINE je to vidět, podobně jako v AUTOTRACE či v 10053 EVENTu, a odměnou je pak tento řádek:
- SQL plan baseline “SQL_PLAN_bxyyd7kvru30g9f449af4″ used for this statement
Užívejme s radostí a při Wintersunu !
Je to už hoooodně dlouho co jsem na podobné téma něco napsal – vlastně pokud se nepletu, to byl jen tip na hint NO_STMT_QUEUING. A z nějakého důvodu jsem tam tvrdil, že je to nedokumentovaný hint – je na čase si to opravit 
– či vnést do toho více světla a méně chyb. Dokumentovaný je, ale neni mezi hintama v SQL Reference, ostatně takových je více.
Paradoxně mi v tomhle pomohla švédština kdy jsem přes brutal force vypisoval chybové hlášky z SQLERRM a vypisoval si švédskou chybu a anglický překlad. V rámci brutal force jsem objevil spoustu zajímavých chybových hlášek, které jsem nikdy neviděl a asi ani nikdy neuvidím. Ale narazil jsem taky na list toho co je možné tracovat – konkrétně jsou to hodnoty mezi 55 a xxx. Z takových těch zajímavých věcí je to deadlock, hashjoin (to je mazec, možná někdy napíšu nějaký článek) a pak trace 
