SQL Plan Baselines a evolving sql plánů

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 !