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
Aug 022015
 

Godiva, mohlo by se zdát, že rok po takovém čase od vydání 12.1c nemá cenu psát o hintech z 11.2g. Nicméně tento článek jsem rozepsal před více než rokem a půl, zanechal ve složce “drafts” a zapomněl. Navíc je to tak nějak pokračování, takže by bylo fajn to dokončit, speciálně když jsem ho v draftech našel de-facto dokončený ;) Takže jen lehké update a publish.

K hintu IGNORE_ROW_ON_DUPKEY_INDEX se toho asi mnoho nedá napsat – lze jej použít jen v INSERT, je nový od 11.2g (předtím šlo v 10.2g potlačit chybu pomocí DBMS_ERRLOG).  A co je zajímavé – Oracle explicitně zmiňuje, že v případě nalezení neunikátního záznamu se pokračuje dále a row-level lock řádku pustí – tzn, ty řádky které insertuje a již existují v tabulce to nelockne.

Synaxe je následující:

Takže ukázka, data stejná jako minule:

create table tbl_ignore_key as
select rownum as col_unique from dual connect by level<6;

 

Zase nějaký unikátní index:

create unique index inx_tbl_ignore_key_col_unique on tbl_ignore_key(col_unique);

A pokus o insert s hintem IGNORE_ROW_ON_DUPKEY_INDEX:

insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(tbl_ignore_key,inx_tbl_ignore_key_col_unique) */ into
tbl_ignore_key select rownum as col_unique from dual connect by level<11;
5 rows inserted.

Dokonce kdyby nikdo nevěřil, to mohu pustit znovu :

insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(tbl_ignore_key,inx_tbl_ignore_key_col_unique) */ into
tbl_ignore_key select rownum as col_unique from dual connect by level<11;
0 rows inserted.

Kdy mi to sice nic neinsertuje, ale nespadne na constrain, zatímco bez hintu:

insert into tbl_ignore_key
select rownum as col_unique from dual connect by level<11;
00001. 00000 - "unique constraint (%s.%s) violated"

Nebo nyní nově pro nás, co se učíme švédsky ORA-00001: brott mot unik begränsning ;). Takže tolik k tomuto hintu, ještě zbývají nějaká ta upozornění – pochopitelně nelze použít v kombinaci s APPEND hitem (který bude ignorován), odkazuje se tam na konkrétní objekt – což v hintu většinou typicky znamená, že hint na neexistující objekt vyfailuje celý insert, nelze použít v bukách a je to rychlejší než EXCEPTION klauzule.

 Posted by at 12:27
Jul 302015
 

Godiva, dnes jeden krátký tip – nedávno jsme objevil mě neznámou část příkazu ANALYZE TABLE, která umožní uložit ROWID chainovaných řádků – LIST CHAINED ROWS INTO. Takže si pojďme zkusit :) Pro spuštění tohoto příkazu existuje pouze jedna jediná pre-rekvizita a tou je mít tabulku do které se to uloží (alá constrain). Tabulka se dá získat spuštěním scriptů UTLCHAIN.SQL či UTLCHN1.SQL nebo stačí založit růčo se správnou strukturou, to jest:

CREATE TABLE CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid urowid,
analyze_timestamp date
);

A ted ještě jak to hezky nafixlovat, aby to bylo vidět – velikost bloku mam 8192 (8 kb), so udělejme 10 řádků po 790ti (overhead bloku by měl být daleko menší, ale nechme si raději buffer):

CREATE TABLE TST_CHAIN (
S_DATA VARCHAR2 (3000)
) PCTFREE 0;

INSERT INTO TST_CHAIN VALUES (RPAD('A',790,'A'));
INSERT INTO TST_CHAIN VALUES (RPAD('B',790,'B'));
INSERT INTO TST_CHAIN VALUES (RPAD('C',790,'C'));
INSERT INTO TST_CHAIN VALUES (RPAD('D',790,'D'));
INSERT INTO TST_CHAIN VALUES (RPAD('E',790,'E'));
INSERT INTO TST_CHAIN VALUES (RPAD('F',790,'F'));
INSERT INTO TST_CHAIN VALUES (RPAD('G',790,'G'));
INSERT INTO TST_CHAIN VALUES (RPAD('H',790,'H'));
INSERT INTO TST_CHAIN VALUES (RPAD('I',790,'I'));
INSERT INTO TST_CHAIN VALUES (RPAD('J',790,'J'));
commit;

Zanalyzujme:

ANALYZE TABLE TST_CHAIN compute statistics;

Fajn a nyní update nějakého řádku tak, aby se nevešel do datového bloku. Dejme mu třebas 3000x písmeno ‘X’, tedy 9*790+300=10110 a to se zajisté již do bloku 8kb nevejde. So, update náhodného řádku:

UPDATE TST_CHAIN SET s_data=RPAD('X',3000,'X') 
WHERE instr(s_data,chr(ascii('A')+MOD(abs(dbms_random.random),10)))<>0;
commit;

Zanalyzujeme:

ANALYZE TABLE TST_CHAIN compute statistics;

Naprosto dle očekávání trhu 2 datové bloky – jeden původní s daty a druhý, který vznikl updatem náhodného řádku – a jeden zřetězený rádek. A nyní konečně ke zkoušené klauzuli:

ANALYZE TABLE TST_CHAIN LIST CHAINED ROWS INTO CHAINED_ROWS;

Když mrknu na vrácené rowid (AAAHQWAABAAAN6RAAH), tak mi LIST CHAINED ROWS INTO tvrdí, že jsem updatoval řádek, který je 7 v pořadí:

Což je řádek, kdy byla původně H a krásně to koresponduje s posledním písmenem rowid :-) A nyní poslední krok, jen ověření, že to opravdu byl řádek s ‘H’áčkama:

Fajn, řádek s H chybí a místo něho je poslední řádek s Xkama  :)  So, enjoy LIST CHAINED ROWS INTO. Mimochodem v dokumentaci je uvedeno, že daný příkaz funguje jen pro fanoušky metalu.. ale možná teď tak trošku kecám ;)

 Posted by at 21:12
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
Nov 042014
 

Dneska jeden velice krátký tip, který však má své vlastní kouzlo. Funguje od PL/SQL Developer verze 9. Občas se v PL/SQL Developeru prostě hodí spustit více selectů/příkazů za sebou, nějak takto:

select * from dual;
select * from dual;
select * from dual;

(nepojmenované taby)

Funguje skvěle, PL/SQL Developer umístí výsledky do záložek. Co občas vadí je pojemování těchto záložek, v případě více selectů/příkazů se záložky jmenují podobně či dokonce stejně a to aby se v tom pak prase vyznalo;)

Takže co s tím? Komentář nad příkazem, který dokáže TAB s výsledkem pojmenovat:

--TAB=Select z dualu číslo 1 a žblunk
select * from dual;
--TAB=Select z dualu číslo 2 a hafík
select * from dual;
--TAB=Select z dualu číslo 3 a budlík
select * from dual;

(pojmenované taby)

 Posted by at 01:23
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