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 0
Sep 082015
 

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 !

 Posted by at 05:12
Aug 302015
 

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.

Možná bych měl začít tím, co je to Parallel Statement Queuning – někdy na Oracle 10g2 to funtovalo tak, že pokud si někdo poručil DOP (degree of parallel) třeba 32 a odčerpal tím tak nějaké množství ze zdrojů tak u následujícího SQL mohlo dojít k downgrade stupně parallelizmu. Dobře? Těžko říct, v závislosti na business požadavcích, konkrétním SQL, konkrétní degradaci. Každopádně jak usoudil Oracle bychom rádi i jiné chování – takové, že když si poručím parallel 32 tak ho prostě dostanu – ovšem zdroje jsou omezené, takže sice dostanu, ale musím si daný zdroj počkat – což právě dělá Parallel Statement Queuing.

No, pojďme to rovnou zkusit, první prerekvizita je nastavení parametru PARALLEL_DEGREE_POLICY na AUTO, defaulut je totiž MANUAL – tedy že parallel řídí programátor či jak to nazvat – na úrovni session, hinty, objekty, statementy které to mají nastavitelné atd. Parametr je možné nastavit na úrovni session nebo system:

ALTER SYSTEM SET PARALLEL_DEGREE_POLICY=AUTO SCOPE=MEMORY;

Tak, další věc kterou je třeba nastavit je maximum paralelních serverů, které je možné použít než půjde SQL do fronty. By default je to 4*CPU*PARALLEL THREADS PER CPU (alternativně krát ještě počet instancí na RACu). Hodnota by určo měla být pod PARALLEL_MAX_SERVERS, aby se system nepřetížil jen na parallelch:

ALTER SYSTEM SET PARALLEL_SERVERS_TARGET=4 SCOPE=MEMORY;

Tak ještě bych měl správně nastavit PARALLL_MAX_SERVERS, ale vzhledem k tomu, že jsem PARALLL_SERVERS_TARGET snížil, tak to není potřeba – hodnota je dostatečná (odvozeno od CPU, threads na cpu, pga). No a tohle nastavení by mohlo stačit. Takže to pojďme zkusit, já s tim docela bojoval ;) Založil jsem si totiž tabulku na zkoušku - 15 bloků což je přesně cca 112KB, při defaulutu 200MB/S to prostě nemá cenu paralelizovat, to chápu, speciálně když je tu ještě databázový parametr PARALLEL_MIN_ TIME_THRESHOLD, který by default řiká že nic s odhadem pod 10s nemá cenu optimalizovat parallelně. Sundal jsem to na minimum, to jest na 1s a pořád jsem se potýkal s nepěknou hláškou automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold. Nicméně ač neúspěšné pro mě hezké cvičení při snaze ukecávat Oracle na statistikách – inserty do dba_rsrc_io_calibrate, mreadtim, sreadtim, ioseektime, mbrs čímž jsem vyhnal cost někam k 7 x 10E23, ale pořád nic, občas Oracle změnil hlášku na ještě nepříjemnější: “no expensive parallel operation“.

Ještě než ukáži jak jsem nakonec “zvítězil”, pár věcí na které jsem při tom narazil, tak prvně při nastavování parametrů a snaha přesvědčit Oracle, že čtení 112KB mu bude trvat minuty jsem začal přepočítávat costy – vzorce si nepamatuji, nicméně když už jsem v tom byl, tak jsem si vyjel tenhle select:

SELECT (BLOCKS * (IOSEEKTIM + MBRC * BLKSIZ / IOTFRSPEED) / (MBRC * (IOSEEKTIM + BLKSIZ / IOTFRSPEED )) FROM DUAL;

Což je cost full table scanů, na 12c mi tedy nějak nevychází – ale liší se to jen v řádu jednotek procet, inu proč ne – takže pro dosazení pro všechny tabulky:

SELECT BLOCKS * (1 + 8 * 8192 / 4096) / (1 * round(8 + 8192 / 4096 )) const, owner,table_name FROM dba_tables ORDER BY BLOCKS DESC NULLS LAST;

Další věc, co jsem zjistil

  • Oracle mi nechá nastavit db_file_multiblock_read_count pod 1 – a obecně je to integer, tak nějak podle očekávání trhu. Ovšem přes begin dbms_stats.set_system_stats( ‘MBRC’, 0.0001 ); end; si mužu poručit i opravdu malé číslo.
  • Největší tabulka v prázdné databázi na počet bloků je SYS.IDL_UB1$ (zdrojové kody)
  • S hitem parallel queuing funguje, ovšem ne vždy to píše hlášku because of hint, což by mohlo být nepříjemné

No, nic zpět k ukázce queuing. Možností jak ven z toho, že mi Oracle nechce paralelně číst tabulku s 15ti bloky a zároveň chci ukázat čekání SQL na parallení zdroje je víc. To nejhorší možné – co jsem viděl na webu je založit si nějakou obrovskou tablku – děkuji, nechci ve virtual boxu. Přehintovat SQL (vnořené neasted loopy), aby běželo déle, ale to už bych rovnou mohl dát parallel :) Takže pojďme na to funkcí, které přiřadím ošklivé statistiky, navíc krásně můžu řídit za jak dlouho mi doběhne SQL.

CREATE TABLE TEST_PARALLEL_2 AS SELECT ROWNUM ID FROM DUAL CONNECT BY LEVEL<11;

No a ještě tu funkci:

CREATE OR REPLACE FUNCTION f_sleep_me(p_id NUMBER) RETURN NUMBER PARALLEL_ENABLE IS
BEGIN
  dbms_lock.sleep(10);
  RETURN p_id;
END;

A k ní ty statistiky:

ASSOCIATE STATISTICS WITH FUNCTIONS f_sleep_me DEFAULT COST (10000000,10000000,10000000);

A nyní již pro mě konečně Oracle pouští SQL parallelně:

Parallelně

Parallelně

Mám nastaveno PARALLEL_SERVERS_TARGET=4 v tomhle případě si vezme 2, takže stačí spustit ve dvou sessions a třetí si již počká:

Čekání ;)

Čekání ;)

A když už jsme u toho, tak ještě zkusme parametr, který udává minimální množství dostupným parallel serverů, které jsou potřeba dostupné, aby vůbec Oracle sql spustil. Možná to vypadá jako zvláštní přístup, ale když si pes představí SQL, které s parallelelm 20 běží 2 hodiny, tak rozhodně nemá chuť si nechat si automaticky zkrhouhnout si DOP na 2 ;). Tedy ještě vyzkoušení PARALLEL_MIN_SERVERS, prvně je však nutné vrátit parallel na manual:

ALTER SYSTEM SET parallel_degree_policy=MANUAL SCOPE=BOTH;

A pak tedy nastavit ještě další potřebné parametry:

ALTER SYSTEM SET PARALLEL_MIN_PERCENT=80 SCOPE=SPFILE;
ALTER SYSTEM SET PARALLEL_MIN_SERVERS=10 SCOPE=SPFILE;
ALTER SYSTEM SET PARALLEL_MAX_SERVERS=20 SCOPE=SPFILE;

Důvodem proč SPFILE je parametr PARALLEL_MIN_PRECENT, který neni modifiable a tímpádem asi i všechno, ať to neni na půlku konfigurované v paměti a půlku na spfile. A ještě dva parametry upravme:

ALTER SYSTEM SET parallel_threads_per_cpu=10 SCOPE=SPFILE;
ALTER SYSTEM SET parallel_servers_target=30  SCOPE=SPFILE;

Fajn a po otočení Oracle:

/*SESSION 1*/  select /*+ parallel(a 10) */ * from test_parallel_2 a where id=f_sleep_me(id);
/*SESSION 2*/  select /*+ parallel(a 10) */ * from test_parallel_2 a where id=f_sleep_me(id);

Čímž si vezmu všechno 2×10=20 a tudíš ve třetí session již mi to Oracle nedovolí spustit a skončím na:

ORA-12827: otillräckliga parallella frågeslavar (begärda 10, tillgängliga 0, parallel_min_percent 80)

Což je švédská hláška řikající, že chci 10, dostupných je 0 a tudíš mam smůlu, tohle je hodně extrémní případ, kdyt jsem všechno vyčerpal na nulu, takže ještě jednou :

/*SESSION 1*/  select /*+ parallel(a 3) */ * from test_parallel_2 a where id=f_sleep_me(id);
/*SESSION 2*/  select /*+ parallel(a 3) */ * from test_parallel_2 a where id=f_sleep_me(id);

Tedy si zkusim požádat o 19 další session:

ORA-12827: otillräckliga parallella frågeslavar (begärda 19, tillgängliga 11, parallel_min_percent 80)

Jestli mi zbývá 11 pak (1/0,8)*11=13,75, kolik múžu použít, to jest:

 select /*+ parallel(a 14) */ * from test_parallel_2 a where id=f_sleep_me(id);
 ORA-12827: otillräckliga parallella frågeslavar (begärda 14, tillgängliga 11, parallel_min_percent 80)
 /* ale s 13 to je ok */
 select /*+ parallel(a 13) */ * from test_parallel_2 a where id=f_sleep_me(id); -- normálně běží

A to je asi pro dnešek vše.

 Posted by at 21:47
Mar 182015
 

Možná to opět o delší odmlce vypadá, že mi přejelo auto nebo jsem snad dokonce přišel o lásku k Oracle ;) Ne, nic takového se neděje. Akorád mezi vztah já a Oracle se dostala švédština. A všichni víme, že k tomu aby byl den dokonalý mu chybí dobrejch 5-6 hodin..

Měl jsem poustu tipů na články, spoustu věcí na zkoušení - za dobu nepsaní jsem se spoustu věcí o Oracle dozvěděl, spoustu zapomnil.. atd. A dokonce mam na blogu spoustu nedokončených věcí a pokusím se s tim něco udělat. Nicméně v Oracle jsem narazil na něco, co je neuvěřitelně SEXY a musím to dostat z hlavy a mé srdce bije zrychleným tepem ;) Takže here we go!

Nevím jaké procent lidí, co čtou tenhle blog si něco vybaví při čísle 10046. Pokud nic, tak za domací úkol doplnit ;) Co znají si automaticky vybaví následující:

ALTER SESSION SET EVENTS '10046 trace name context forever,level 12'

Když už to programátor viděl xkrát za svuj život a podobný výstup si umí vytáhnout z různých view, tak to prostě použivá a nepřemýšlí nad tím, jak moc cool to je. Ovšem trace eventu 10046 neni zdaleka jedniné, co jde tracovat a ovlivňovat. Dlouho jsem hledal nějaký list a nenašel :( 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 event 10053 ze kterého jsem si sednul na prdel.

A nyní co to dělá:

Tracuje to CBO optimalizátor. A to do takové úrovně, že to píše všechny transformace které to zkouší, všechny možné rewrites, takové ty věci jako tranzitivní uzávěry, vykostění predikátu, následuje spočitání nákladu na jednotlivé přístupové cesty – s tím, že to vypíše estimate řádku, pamět, costy (a to ještě před zaokouhlením !), všechny parametry optimalizátoru, hinty, a pak postupně přijde něco co je uplně nejvíce cool – permutace v optimalizačním plánu a výpisy costu pro jednotlivé vypermutované plány + případně důvod zamítnutí, včetně takových věcí jako, že to Oracle killne uprostředka protože už to přelezlo přes cost prozatím nejlepšího nalezeného plánu, je tam vidět jak si při optimalizaci CBO spouští sql na dynamic sampling.  Prostě kurva mazec !

Asi si dovedete představit jak mi bylo - ještě líp než když se mi v pondělí povedlo na třetí hodině švédštiny vykoktat švédsky učitelce kompliment, že má nádherné modré oči a hezký úsměv ;)  Tohle asi bude trošku rozsáhlé, protože ty logy a výstupy z trace jsou velké, takže tenhle článek je první a hodně high level co to vlastně je:

Nejprve zjistit kde jsou trace logy:

select value,name from v$parameter2 where name like 'user%dump%des%';

U mě je to C:\app4\AZOR\diag\rdbms\orcl12\orcl12\trace. Dobře a ted

ALTER SESSION SET EVENTS10053 trace name context forever,level 1′;

A nyní nějaké SQL a začněme tím úplně nejjednodušším:

select * from dual;

A pak ideálně nic již nedělat, exit ze session nebo vypnout. A nyní co ten soubor obsabuje :

  • Hlavičku (verze Oracle, OS, umístění, počet CPU, velikost paměti..)
Trace file C:\APP4\AZOR\diag\rdbms\orcl12\orcl12\trace\orcl12_ora_4932.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Windows NT Version V6.1 Service Pack 1 
CPU                 : 8 - type 8664, 4 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:3546M/8043M, Ph+PgF:2830M/8042M 
Instance name: orcl12
Redo thread mounted by this instance: 1
Oracle process number: 9
Windows thread id: 4932, image: ORACLE.EXE (SHAD)
  • Informace o SESSION – id, modul, action..
*** 2015-03-18 22:16:18.321
*** SESSION ID:(60.7) 2015-03-18 22:16:18.321
*** CLIENT ID:() 2015-03-18 22:16:18.321
*** SERVICE NAME:(SYS$USERS) 2015-03-18 22:16:18.321
*** MODULE NAME:(sqlplus.exe) 2015-03-18 22:16:18.321
*** ACTION NAME:() 2015-03-18 22:16:18.321
  • Query blocky a jejich pojmenování
Registered qb: SEL$1 0x170b24b8 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$1 nbfros=1 flg=0
    fro(0): flg=4 objn=142 hint_alias="DUAL"@"SEL$1"

SPM: statement not found in SMB
SPM: capture of plan baseline is OFF

V tomhle případě je tam jeden query blok (SEL$1). A dole hláška SPM Zkratka neni dole vysvětlena, ale podle kontextu to bude SQL Plan Management (dbm_spm).

  • Check na ADOP (automaic degree of parallelism)
**************************
Automatic degree of parallelism (AUTODOP)
**************************
Automatic degree of parallelism is disabled: Parameter.

Jen informace o tom, že je to vypnuto.

  • Seznam a vysvětlení zkratek, které jsou použity (bohužel né všechny)
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
OST - old style star transformation
ST - new (cbqt) star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
JF - join factorization
SLP - select list pruning
DP - distinct placement
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUSPEED - CPU Speed 
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
  1: no partitioning required
  2: value partitioned
  4: right is random (round-robin)
  128: left is random (round-robin)
  8: broadcast right and partition left
  16: broadcast left and partition right
  32: partition left using partitioning of right
  64: partition right using partitioning of left
  256: run the join in serial
  0: invalid distribution method
sel - selectivity
ptn - partition
AP - adaptive plans
***************************************
  • Parametry optimalizátory – všechny, včetně nastavení + fixy a jejich stav

To jako úplně vypisovat nebudu, je toho fakt hodně. Co je fajn, je že jsou tam vypsány i nedokumentované parametry. Což dělá tenhe trace sámo o sobě zajímavým ;)

  • Pak následují pokusy o transformace query blocků
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: No partitioned table in query block.
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for a5ks9fhw2v9s1.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE:     CSE not performed on query block SEL$1 (#0).
OBYE:   Considering Order-by Elimination from view SEL$1 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE:     OBYE bypassed: no order by to eliminate.
CVM: Considering view merge in query block SEL$1 (#0)
OJE: Begin: find best directive for query block SEL$1 (#0)
OJE: End: finding best directive for query block SEL$1 (#0)
query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: No partitioned table in query block.
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for a5ks9fhw2v9s1.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE:     CSE not performed on query block SEL$1 (#0).
SU: Considering subquery unnesting in query block SEL$1 (#0)
********************
Subquery Unnest (SU)
********************
SJC: Considering set-join conversion in query block SEL$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
PM:     PM bypassed: Outer query contains no views.
PM:     PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
 ?? 
apadrv-start sqlid=11730480049179961089
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE:     CSE not performed on query block SEL$1 (#0).
  :
    call(in-use=1136, alloc=16344), compile(in-use=54968, alloc=58616), execution(in-use=2632, alloc=4032)

Upřímně nevím co to dělá tady. Že od 10g Oracle permutuje bloky nikoliv celé sql jsem již někde postřehl. Oracle vzdal permutace na celém sql, protože pro takové SQL jako generuje například Siebel (150+ tabulek a joinu) jsou často permutace nemožné (ostatně ani faktoriál 10ti neni malé číslo – 362880, pokud si pamatuji tak před timhle přístupem bylo maximum 60000 a nazdar bazar). Takže raději rozsekání na části, ty permutovat a pak případně permutovat proti sobě. Proč ale přepisy nedělá na urovní celého sql netuším :(, ač si umím představit, že většina z nich nepůjde udělat přes hranice query bloku, tak něco by určitě šlo. Na mém select  from dual nemá co vymyslet  – zkusil a neuspěl.

  • Kontrola peek variables na cursoru

Tam jen zeje hláška, že nic. Nic být ani nemůže, nemam žádnou bid variable :D

  • Výpis SQL po transformaci, tzn ten který pujde na permutování
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "DUAL"."DUMMY" "DUMMY" FROM "SYS"."DUAL" "DUAL"
kkoqbc: optimizing query block SEL$1 (#0)
        
call(in-use=1136, alloc=16344), compile(in-use=64416, alloc=66952), execution(in-use=2632, alloc=4032)

kkoqbc-subheap (create addr=0x00000000170BF760)

Žádné transformace se nepovedly, takže stejné sql jako originál.

  • Výpis systémových statistik
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats
  CPUSPEEDNW: 3264 millions instructions/sec (default is 100)
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM:  10 milliseconds (default is 10)
  MBRC:       NO VALUE blocks (default is 8)
  • Statistiky o tabulce
Table Stats::
  Table: DUAL  Alias: DUAL
  #Rows: 1  #Blks:  1  AvgRowLen:  2.00  ChainCnt:  0.00

Na tabulce dual není nic zajímavého ;) Jeden řádek, délka 2, žádné chainy.

  • Analýza přístupu do tabulky
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for DUAL[DUAL] 
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Table: DUAL  Alias: DUAL
    Card: Original: 1.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  Access Path: TableScan
    Cost:  2.00  Resp: 2.00  Degree: 0
      Cost_io: 2.00  Cost_cpu: 7271
      Resp_io: 2.00  Resp_cpu: 7271
  Best:: AccessPath: TableScan
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.00  Bytes: 0

Na tabulce dual není nic zajímavého. Stejně má Oracle jen možnost full table scan, žádný index tam neni.

  • Permutace
OPTIMIZER STATISTICS AND COMPUTATIONS
PJE:  Bypassed; QB has a single table SEL$1 (#0)

Oracle dokonce ani nezkoušel permutovat, nemá s čím, máme jen jednu tabulku, takže taky nic.

  • Finální plán
kkeCostToTime: using io calibrate stats maxpmbps=200(MB/s) 
 block_size=8192 mb_io_count=1 mb_io_size=8192 (bytes) 
 tot_io_size=0(MB) time=0(ms)
kkeCostToTime: using io calibrate stats maxpmbps=200(MB/s) 
 block_size=8192 mb_io_count=1 mb_io_size=8192 (bytes) 
 tot_io_size=0(MB) time=0(ms)
Starting SQL statement dump

user_id=0 user_name=SYS module=sqlplus.exe action=
sql_id=a5ks9fhw2v9s1 plan_hash_value=272002086 problem_type=3
----- Current SQL Statement for this session (sql_id=a5ks9fhw2v9s1) -----
select * from dual
sql_text_length=19
sql=select * from dual
----- Explain Plan Dump -----
----- Plan Table -----
 
============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |     2 |           |
| 1   |  TABLE ACCESS FULL | DUAL    |     1 |     2 |     2 |  00:00:01 |
-------------------------------------+-----------------------------------+

Uf, pak už print other_xml a tedy print outline hintu a to je vše.  Takže to byl spíše takový rychlý popis tak tento trace file vypadá. Oracle neměl na takhle jednoduchém sql žádnout možnost optimalizace, transformace, permutace a předem byl předurčen jít full table scanem. Nicméně doufám, že jsem na to ukázal pár zajímavých věcí, ačkoliv to zajímavější ještě přijde, v nějaké v příštích postů bych rád prověřil vždycky nějakou zajímavou část.

A nyní bez zkladu co mi praštilo do oka:

  • Pokud se nejede hard-parse trace Oracle nevybleje
  • Když Oracle dělá dynamic sampling (měl jsem nastavo 2) tak používá klauzli SAMPLE BLOCK, ale to jen v případě, že tabulka má více datových bloku než dynamic samling chce. Pokud má méně datových bloků než chce dynamic sampling CBO nahulí sql na celou tabulku.

S tímhle číslem 10053 si tu doufám, ještě užijeme spoustu legrace na blogu, je to fakt vymazlená věcička. Hébičkám zdar!

 Posted by at 22:31
Oct 292014
 

Dnešní post bude o pořadí a vyhodnocování predikátů ve WHERE podmínce.  Původně to mělo být spíše o hintu ORDERED_PREDICATES, ale pojďme to trošku rozšířit ;) Je asi známá věc, že pokud píši do podmínky něco ve smyslu:

IF podmínka1 AND podmínka2 THEN

Že Oracle pokud nebude splněna podmínka1 již nebude spouštět podmínka2, protože k tomu není důvod – ifem již to nemůže projít a nic to na té skutečnosti již nezmění. Což se samozřejmě netýká jen Oracle - většina jazyků pracuje přesně takto. Co je méně známý fakt je, že podobně pracuje Oracle i ve vyhodnocování WHERE podmínky. Respektive pravida jsou trošku složitější. Toto je pořadí dle dokumentace:

  • Predikáty bez uživatelských funkcí, typových metod, subselectů jsou vyhodnocovány jako první.
  • Predikáty s uživatelskou funkčí či typových metod, které mají přidělenu cost jsou vyhodnocovány podle costu v zestupném pořadí.
  • Predikáty kde je uživatelská funkce či typová metoda a nemají přiřazeny costy.
  • Pak jsou vyhodnocovány podmínky, které se do WHERE dostaly tranzitivně přes jinou podmínku (propasoval je ta optimalizátor – push predicate apod.)
  • A po té jsou vyhodnocovány podmínky, které obsahují subselecty

Začněme příkladem, který je možné vidět prakticky na každém blogu:

create table t_predicates as
select 'Suicide silence' as kapela, 1 as id from dual union all
select '2' as kapela, 2 as id from dual union all
select 'Wintersun' as kapela, 3 as id from dual

(tabulka)

A příkladový select:
select * from t_predicates where kapela=2 and id=2;

Idea je myslím jasná – pokud Oracle začne vyhodnocovat nejprve kapela=2, pak jsme v pytli a skončíme na ORA-01722, vlastně již jsem na to kdysy nějaký post psal: Nebezpečná chyba a exekuční plán. Pokud Oracle začne nejprve s id=2, pak řádky id=1 a id=3 vyloučí automaticky a nebude nikdy testovat Suicide Silence=2 a Wintersun=2 a tedy ani zkoušet konvertovat oblíbené kapely na čísla a select doběhne ;)

A nyní jaký je rozdíl mezi:
select * from t_predicates where kapela=2 and id=2;
select * from t_predicates where id=2 and kapela=2;

Popravdě – nevím. V dokumentaci, se píše že predikáty, které jsou na stejné úrovni jsou vyhodnocovány odzadu, má to ovšem nějaké “ale” a tím je třeba i selectivita predikátů – ty nejvíce selektivní jdou pochopitelně jako první. V tomto případě selecty doběhnout v obou verzích  (12.1c) – což znamená, že mi Oracle kašle na “odzadu“, ale prioritně řadí id=2 z nějakého důvodu. Napadají mi dva - selectivita, ačkoliv oba predikáty jsou naprosto stejně selectivní (podle explain plánu), tak možná až po za okrouhlení a Oracle je interně seřadí před zaokrouhlením. Druhá varianta (a podle mě pravděpodobnější) je ta, že Oracle interně musí zkusit zkonvertovat sloupec kapela na number (alespoň to zkusit), což je volání funkce, což je drahé a proto až druhé.

Dobře, takže to zkusme s hintem ORDERED_PREDICATES:

Prvně jde Oracle do kapela=2, čímž okamžitě končí na ORA-01722, ale opačně je to lepšejší:

Tímhle většina blogu končí, nicméně pojďme dál - do funkcí. Ovšem nejprve založme nějakou zajímavějšejší tabulku:

create table t_pred_func as
select rownum id, case when rownum<=5 then 1 else 2 end haf from dual
connect by level<11;

A nyní tam začněme navěšovat funkce a hrát si s tim. Většinou se do funkcí dává dbms_output apod, aby bylo vidět kolikrát je daná funkce volaná. Nicméně dává mi smysl tam nyní nacpat dbms_lock.sleep.  Už jen proto si osvojit, že na tomhle pořadí záleží, a že to má výkonové dopady. Takže založme dvě funkce – jedna, která Oracle zdrží o 100 sekund a druhá, která zdrží Oracle o 5 sekund. Obě vrací hodnotu 2. Takhle o řád+ od sebe, aby to bylo krásně vidět a daly se počty volání dopočítat a krásně od sebe oddělit.

create or replace function f_sleep_100s return number
is
begin
dbms_lock.sleep(100);
return 2;
end;
/
create or replace function f_sleep_5s return number
is
begin
dbms_lock.sleep(5);
return 2;
end;

Fajn, a nyní pojďme začít hébičkovat:

select * from t_pred_func where id=f_sleep_5s and haf=f_sleep_100s;

Oracle má na predikátech navěšeny dvě uživatelské funkce. Nemá naprosto žádnou představu o costu ani selectivitě – respektive obě funkce mají defalutní cost a defalutní selectivitu.

Trvá 150 sekund. Konkrétně 10x Oracle testuje id=f_sleep_5s a v jednom případě je ta podmínka splněna (id=2) a v té chvíli musí jít i do testu  haf=f_sleep_100s tedy 10 x 5 sekund + 1x 100 sekund. Daleko horší je, když si to vynutíme opačně:

select /*+ ordered_predicates */ * from t_pred_func where haf=f_sleep_100s and id=f_sleep_5s;

Tentokrát to však trvá 1025s . Konkrétně 10x Oracle testuje haf=f_sleep_100s s tím, že haf=2 ja splněno v 5ti případech a Oracle tedy musí jít ještě do testu id=f_sleep_5s.  Což dělá celkem 10x 100 sekund + 5x 5 sekund. No dobře, pojďme ještě do OR operátoru:

select /*+ ordered_predicates */ * from t_pred_func where haf=f_sleep_100s OR id=f_sleep_5s;

V tomhle případě opět Oracle začne funkcí f_sleep100s, ovšem jeho možnosti logického vykydlení jsou naprosto jiné. Nejprve 10x ověří haf=f_sleep_100s a všude tam, kde je to nesplněno (5 případů, kde je haf=1) musí zkusit jestli se náhodou to nesplněné nepodaří splnit v druhé podmínce. Tedy opět 10x 100 sekund + 5x 5 sekund  = 1025s, ovšem tentokrát se id=f_sleep_5s volala u jiných řádků. Takže si to vynuťme opačně:

select /*+ ordered_predicates */ * from t_pred_func where haf=f_sleep_5s OR id=f_sleep_100s;

Pro tento případ Oracle musí otestovat haf=f_sleep_5s pro každý řádek a tam kde to není splněno (5x) ještě provolat id=f_sleep_100s. Což tentokrát dělá 10x 5sekund + 5×100 sekund. Inu, trváme 550 sekund. Ovšem slíbil jsem, že tenhle post nebude jen o hintu ordered_predicates, takže si pojďme vynutit pořadí provádění funkcí pomocí přiřazení jim nedefalutního costu:

ASSOCIATE STATISTICS WITH FUNCTIONS f_sleep_100s DEFAULT COST (10,40,0);
ASSOCIATE STATISTICS WITH FUNCTIONS f_sleep_5s DEFAULT COST (10,10,0);
select * from t_pred_func where haf=f_sleep_5s OR id=f_sleep_100s;

Přiřazená COST samozřejmě zdaleka neodpovídá reálu, funkce která čeká 100 sekund by měla obrovský cost. Nicméně mým cílem není to přesně spočítat (pokud chcete, pak je třebas mrknout do tabulek sys.aux_stats$ jako či dbms_stats.get_system_stats/dbms_stats.gather_system_stats  a spočítat si to.). Po spuštění trvá tehle select 550 sekund.  Což znamená, že Oracle jde prvně do funkce f_sleep_5s, takže prohoďme costy:

DISASSOCIATE STATISTICS FROM FUNCTIONS f_sleep_100s,f_sleep_5s;
ASSOCIATE STATISTICS WITH FUNCTIONS f_sleep_100s DEFAULT COST (10,10,0);
ASSOCIATE STATISTICS WITH FUNCTIONS f_sleep_5s DEFAULT COST (10,40,0);
select * from t_pred_func where haf=f_sleep_5s OR id=f_sleep_100s;

Kupodivu nezafungovalo a select trval opět 550 sekund. Tedy nejprve jsem zkusil exekuční plány zda se opravdu COST funkcím přiřadil a on přiřadil. Takže wtf? Popravdě jsem na to nepřišel – nastavení cursor_sharing=exact, byly parasovány dvě sql, ale z nějakého důvodu Oracle nebral vpotaz nově přiřazené COSTy a nevyhodnocoval podle toho predikáty, nicméně úspěch jsem slavil když jsem ho donutil vyprázdnit shared_pool:

ALTER SYSTEM FLUSH SHARED_POOL;

Je to teda s vakuovou pumou na vrabce, správně bych měl flushnout jen to sql nebo objekty pod ním. A teprve po té to trvalo 1050 sekund. Oracle opravdu začal nejprve funkcí f_sleep_100s na základě mnou špatně přirazeného costu ;)  A zkusme ještě najaký příklad na select:

select /*+ ordered_predicates */ * from t_pred_func where (id>(select 5 from dual)) and (id=f_sleep_5s+6 or id=f_sleep_100s+7) and (id=f_sleep_100s);

Celé SQL mi trvalo 625 sekund takže pojdme do nějakého toho vysvětlení, pro připomenutí data vypadají takto:

(data)

Hintem jsem vynutil provádění zleva, takže takhle to vypadá:

ID=1 – netrvá prakticky nic, vyfiltruje to select 5 from dual
ID=2 – netrvá prakticky nic, vyfiltruje to select 5 from dual
ID=3 – netrvá prakticky nic, vyfiltruje to select 5 from dual
ID=4 – netrvá prakticky nic, vyfiltruje to select 5 from dual
ID=5 – netrvá prakticky nic, vyfiltruje to select 5 from dual
ID=6 -  select 5 from dual splněno Oracle musí vyhodnocovat dál. f_sleep_5s+6 – nesplněno v závorce musí Oracle ještě otestovat f_sleep_100s+7 kvuli OR. To je nesplněno, takže konec. Celkem 105 sekund.
ID=7 – select 5 from dual splněno Oracle musí vyhodnocovat dál. f_sleep_5s+6 – nesplněno v závorce musí Oracle ještě otestovat f_sleep_100s+7 kvuli OR. To je nesplněno, takže konec. Celkem 105 sekund.
ID=8 – select 5 from dual splněno Oracle musí vyhodnocovat dál. f_sleep_5s+6 – tentokrát nesplěno v závorce v závorce již Oracle nemusí testovat f_sleep_100s+7. To padá. Musí však ještě otestovat id=f_sleep_100s To je nesplněno, nicméně přidalo to 100s do exekuce.. Celkem 105 sekund.
ID=9 – select 5 from dualsplněno Oracle musí vyhodnocovat dál. f_sleep_5s+6 – nesplněno v závorce musí Oracle ještě otestovat f_sleep_100s+7 kvuli OR. To je splněno takže ještě test na,  id=f_sleep_100s. Celkem 205 sekund.
ID=10 – select 5 from dual splněno Oracle musí vyhodnocovat dál. f_sleep_5s+6 – nesplněno v závorce musí Oracle ještě otestovat f_sleep_100s+7 kvuli OR. To je nesplněno, takže konec. Celkem 105 sekund.

Což je 105+105+105+205+105+(select z dualu, který s tím nezahýbe)=625 sekund, přesně podle očekávání trhu ;) Dobře? No, umíme to lépe:

select /*+ ordered_predicates */ * from t_pred_func where (id>(select 5 from dual)) and (id=f_sleep_100s) and (id=f_sleep_5s+6 or id=f_sleep_100s+7)

500 sekund. Vcelku krásně spočítatelné. Pro ID<=5 vypadnou řádky díky select  select 5 from dual Pro ID>5 se však musí spouštět f_sleep_100s,  což je 5x 100 sekund.Nikdy nedojde ke spuštění dalších predikátů, takže 500 sekund.

Tohle byl hodně úmělý příklad – podle definice jde na vyhodnocení filtrování podle subselectu jako poslední. Nicméně zde  je však daleko rychlejší select konstanty z dualu než spouštění funkce (5 a 100 sekund). Nicméně pro tento případ je tu ORDERED_PREDICATES. Pokud to nechám na Oracle záleží pak na tom jak jsem nastavi costy funkcím f_sleep_100s a f_sleep_5s+6 se kterou začne – v horším případě 1105 sekund.

Tolik k pořadí vyhodnocování predikátů. A všem, co si chtějí zaletět do USA na pár hodin zabruslit chci říct, že i když to není třebas možné, tak dokud není konec, máme ve skříni brusle a latenci se kterou reagujem na změnu držíme proklatě nízko.

 Posted by at 21:19
Oct 042014
 

Včera (a dneska) mi v práci potrápil jeden select. Respektive jeho zobrazení v V$SQL_PLAN_MONITOR – spustil jsem select a ta mrška běžela dlouho, takže jsem se začal starat, co právě dělá a především za jako dlouho asi doběhne a jestli vůbec.  Bylo tam pod sebou pár hash joinů – úplně optimálně to nevypadalo, ale nic drastického také ne. Takže jsem si vpohodě čekal až to doběhne – přesně do chvíle, než Oracle začal full scannovat tabulku, říkejme jí třebas ACC_SOURCE, která měla 972 partitions. Dokud se ve sloupci STARTS objevovala čísla 1..972 bylo to fajn, jakmile Oracle začal ukazovat 1008 a pokračoval dál, bylo to jasné WTF (whats the fuck = to mi poser kozy). Inu, cílem tohodle článků je podívat se na hodnoty ve sloupci STATS v pohledu V$SQL_PLAN_MONITOR s tím, že to rozdělím do třech dílů:

  • Nepartitiované tabulky (tenhle díl)
  • Pro partitiované tabulky (a tam bude odpověď proč tam může být více startů než kolik je partitions)
  • Pro paralelní selecty

Takže tolik na úvod a nyní pojďme na nějaké to zkoušení:

create table tbl_just_one_part_100rows as select rownum as id,mod(rownum,2) id_mod_2 from dual connect by level<101;

Tabulka se dvěma sloupci (id a id_mod_2), 100 řádky a bez partitioningu. Takže jíz zkusme proscannovat:

select * from tbl_just_one_part_100rows;

Zjistím si sql_id (ckqzr8t38gug7) a podívám se do v$sql_plan_monitor do sloucpce starts, kolik uvidím? Neuvidím nic, protože jsem nesplnil žádnou s podmínek, aby oracle online monitoroval sql – neběželo to déle než 5s, nebylo to paralelní a ani jsem tam neměl hint. Takže přidávám hint:

select /*+ MONITOR */ * from tbl_just_one_part_100rows

A nyní už je to lepší (nyní sql_id 36ztar2wkfvww):

select status,plan_line_id as id,plan_operation,plan_object_owner as owner
,plan_object_name,starts,output_rows as o_rows from v$sql_plan_monitor
where sql_id='36ztar2wkfvww'

 (pozn. řádky 1 a 2 jsou jedno spuštění a řádky 3 a 4 jsou druhé)

Vcelku jednoduché, ne? Jedno čtení = hodnota ve sloupci STARTS = 1. Trošku matoucí je možná output rows, které řiká 50 ačkoliv má tabulka 100 řádků – jednoduché vysvětlení, SQL Developer prvně fetchnul 50 řádků a mě výsledek nezajímal, takže jsem nefetchoval dalších 50.  Zůstaňme u této tabulky a zkusme nějaký join:

select /*+ MONITOR */ * from tbl_just_one_part_100rows a join tbl_just_one_part_100rows b on (a.id=b.id)

Kolik bude startů? Tentokrát to ovšem již závisí na exekučním plánu. Takže jaký bude plán? Inu, joinuju na sebe dvakrát stejnou tabulku a u obou jejich instancí je to bez predikátu. Takže pro instanci tabulky A i instanci B odhadne Oracle stejnou cardinalidu. Ve skutečnosti to zkouším na Oracle 12C, které při CTASu už počitá statistiky. Takže vcelku přesně “ví”, že je to 100 řádků na 100 řádků:

Takže po spuštění:

select /*+ MONITOR */ * from tbl_just_one_part_100rows a join tbl_just_one_part_100rows b on (a.id=b.id);

První test joinu

Je vidět, že to každou instanci tabulky (A i B) vezme jednou a počet STARTů je tedy jedna, dle očekávání trhu. Za zmínku možná stojí ještě output_rows, které je 100 přestože jsem nefetchnul všechno v SQL Developeru. Důvodem je, že Oracle nemůže vracet průběžně výsledek pro operaci hashjoin (všechno nebo nic).  Pustil bych se dál do zkoušení, ale raději založme jiné tabulky, ať je to lépe vidět:

create table tbl_A_100 as select rownum as ID,mod(rownum,2) ID_MOD_2 from dual connect by level<101;
create table tbl_B_200 as select rownum as ID,mod(rownum,2) ID_MOD_2 from dual connect by level<201;

Dobře, dobře a nyní join a vynuceně neasted loopu:

select /*+ MONITOR use_nl(a b)*/ a.id from tbl_A_100 a join tbl_B_200 b on (a.id=b.id);

(neasted loopa a počet starts)

U tabulky tbl_b je 100 startů u tabulky tbl_a je 1 start. Což je logické – tabulku tbl_a si slízne oracle jednou a pak dle definice nested loopy si pro každý řádek líže tbl_b. Takže je pojďme přehodit:

select /*+ MONITOR use_nl(b a) leading(b a)*/ a.id from tbl_A_100 a join tbl_B_200 b on (a.id=b.id);

(počet startu neasted loopy)

Pokud v nested loopě obrátíme pořadí tak je to přesně opačně – slízne si to jednou tabulku tbl_b_200 a pak to iteruje pro každý řádek tabulku tbl_a_100 tentokrát však 200x.  No dobře, pokračujme dále. Merge join:

select /*+ MONITOR use_merge(a b)*/ * from tbl_A_100 a join tbl_B_200 b on (a.id=b.id);

(merge join

Merge join je již lehce složitější na čtení ale i tak je vidět, že obě tabulky se čtou pouze jednou, a počet startů u sortu je pouze u jedné (v mém případě TBL_B_200) a je datově závislý na datech z druhé tabulky.

Tohle samozřejmě nejsou všechny případy a zdaleka nemám v plánu zkoušet je všechny, ale pro ukázku jak s takovým sloupcem zacházet to myslím stačí ;)

A nyní to, co mělo být na začátku – proč se tomu věnuji. Ačkoliv hint byl již v úvodním odstavci. Pole STARTS (a nejen tohle pole) sleduji poměrně často pokud ladím nějaké dlouhoběžící sql. Taková ta jedna z nejrychlejších cest jak najít úzké hrdlo. Typickým příkladem je, když tohle pole obsahuje nějaké velké číslo (bžilion a více). Vcelku se člověk může vsadit, že Oracle dělá místo hashjoin nested loopu. Další krása tohodle pole je ta, že dokáže odhadnout jak dlouho daná operace bude trvat:

  • Během operace NESTED join poznáte kolik % již je hotovo (pokud je znám očekávaný počet startů – známe z dat, z předchozího stupně exekučního plánu – output rows v V$SQL_PLAN_MONITOR atd.)
  • Během operace HASH JOIN (neparallelně a bez partitions) je počet startů 1, takže tam koukáme na počet datových bloků kolik ještě zbývá (highmark watermark či z předchozího exekučního stupně – V$SQL_LONGOPS apod.)
  • Během full scannů (index i table) je opět počet startů 1 nebo počet sub(partitions) tedy alespoň se dá odhadnout kolik z kolika partitions již to vzalo (s lehkou rezervou, ne vždy je to přesný počet partitions a vlastně proto vzniknul i tenhle příspěvek na blogu, respektive další díl)

Zkrátka je to hodně sexy sloupec. Kdo neznal může si klidně vyselectit where starts>100000 a má dobrý tip na sql, které nejspíše nebudou optimální. Nutno podotknout, že v tomhle view nezůstávají data moc dlouho.

 Posted by at 00:14
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
Jul 122013
 
Nedávno jsem byl svědkem diskuze dvou kolegů, kde řeč byla o tom jestli by šlo udělat to, že jednou by v selectu byla analytická klauzule projection, tzn select count(*) over (partition by..) a jednou by byl select spouštěn bez ní. Existovaly dva názory – jeden že to nejde bez toho aniž bychom měli dva podobné selecty v proceduře a druhý obecný, že to lze. Na meetingu jsem sprostě mlčel neb jsem na to neměl úplně přesný názor, neztotožňoval jsem se ani s větou, že to lze. Ale ani s větou, že bychom na to potřebovali dva selecty, když nebudeme uvažovat podmínečnou kompilaci. Nemyslel jsem si, že bychom potřebovali dva různé selecty – jeden s analytickou klauzulí a jeden bez. Spíše než dva selecty jsem si říkal, že budou nutné dva výstupy řádkového generování, což nezbytně nutně neznamená dva různé selecty. Mířeno na otázku jestli, nedokážeme napsat schizofrenní sql select, kde můžeme optimalizátor řídit a buď mu analytickou klauzuli povolit vykydlit nebo naopak mu tuhle optimalizaci nedovolit? Konkrétně jsem myslel na něco takového:

select
a,
b,
case when plsql_promena=1 then 0 else C end
from (select
a,
b,
count(*) over (partition by C) as C
from TBL_TEST)

Myšlenka byla taková, že pokud z PL/SQL enginu propasuji proměnnou (a tedy v sql enginu to bude už jako konstanta) plsql_promena s hodnotou 1, pak zcela jistě Oracle nemusí provádět sort, neb může vykydlit “count(*) over (partition by d) as C”, protože to k výsledku nepotřebuje. Naopak pro plsql_promena<>1 to prostě spočítat musí – ten výsledek chceme. Nicméně takhle to nefunguje, Oracle optimalizátor to takhle nevykydlí.
Napadlo mi to, protože Oracle má tuhle úžasnou optimalizaci:

select
a,
b
from (select
a,
b,
c from TBL_TEST)

Oracle nebude číst sloupec C – sice je požadován v subselectu, ale ve výsledku se na něj vůbec neodkazujeme, není tedy potřeba.
Dá se na to udělat krásný test – viz starší příspěvek na blogu o covering indexech. Tedy ten test.

Založení tabulky:
create table tbl_test
as
select rownum as a,
rownum as b,
rownum as c
from dual connect by level<=100

Constrainy jsou potřeba (nebo je nutné to vypsat v selectu IS NOT NULL):
ALTER TABLE TBL_TEST MODIFY (A NUMBER CONSTRAINT cons_a NOT NULL);
ALTER TABLE TBL_TEST MODIFY (B NUMBER CONSTRAINT cons_b NOT NULL);
ALTER TABLE TBL_TEST MODIFY (C NUMBER CONSTRAINT cons_c NOT NULL);

A nyní index, ale pouze na sloupcích A a B:
CREATE INDEX inx_tbl_test_ab ON TBL_TEST(a,b);

A nyní exekuční plán pro select všech tří sloupců:
Exekuční plán pro SELECT A,B,C

Exekuční plán pro SELECT A,B,C

Sloupec C má jen v tabulce, musí do ní. Exekuční plán pro select sloupců a,b:
select sloupců A a B

Exekuční plán pro SELECT A,B

V tomhle případě zafungovar covering index a Oracle čte pouze index ve kterém má oba sloupce – a i b. A constrainy zaručují, že tam není žádná hodnota NULL, jinak by to nebylo možné, protože v b-tree indexu NULLy nejsou. A ted exekuční plán pro select uvedený dříve – v subselectu chci sloupce A, B, C ale v hlavním selectu se odkazuji jen na sloupce A a B:
Exekuční plán pro select a,b from (select a,b,c from tbl_test)

Exekuční plán pro select a,b from (select a,b,c from tbl_test)

Hébičkozní, ne? Optimalizátor rozpoznal, že C pak vůbec nepotřebujeme a proto fyzicky jde jen do indexu ve kterém může najít pouze A a B sloupec, C tam vůbec není. Dokonce funguje i když se na to navěsí funkce:
Exekuční plán pro subselect s funkcí

Exekuční plán pro subselect s funkcí

Oracle dokonce vykydlí v optimalizaci i spouštění funkce, když její výsledek v hlavním selectu vůbec nepoužiji. Tím to však končí, jakmile se na něj začne odkazovat bude to reálně spuštěno – a to bohužel dokonce i když logicky ten výsledek nemůže být použit a je to odvozeno od konstant, tedy:
Exekuční plán pro select a,b,case..

Exekuční plán pro select a,b,case..

A nezafunguje na to bohužel ani hint DYNAMIC_SAMPLING, který by měl umožnit optimalizátoru vypočítat všechno, co v době optimalizace může. A překvapivě nefunguje ani tohle:
Exekuční plán pro "nesmyslný" join..

Exekuční plán pro "nesmyslný" join..

Úplně optimální by bylo přečíst index inx_tbl_test_ab a z něj hodnoty pro A,B a sloupec tbl_dva.C doplnit hodnotou null – jinou mít ani nemůže – join (1=2) sice nikdy nebude uspokojen, ale optimalizátor tohle bohužel neumí – join prostě fyzicky provede a tabulku TBL_TEST si olízne, ač má dostatek informací pro to, aby to dokázal vrátit z indexu.
Suma sumárum – neodkazované věci kydlí, odkazované nekydlí aní když je na ně odkazováno pouze v nesplnitelné podmínce, kterou zaručeně zná v době optimalizace.
 Posted by at 18:11