Mar 272016
 

Godiva,

tak pod dlouhé době zase nějaký post.  Ne, že bych snad něměl nápady, co sem spát, ale prostě není čas -ale i takto tu tak nějak hodlám tento rok udržovat :) Inu, dnešní téma je DEFAULT na 12c a nová featura/optimalizace Oracle, která nás tak trošku potrápila, ale ve skutečnosti je neskutečně sexy.

Kolega nedávno objevil záhadný hidden sloupec v tabulce (a tudíš jemu patří credit) a ptal se jak se tam sakra dostal. Popravdě Oracle 12c si vlastní systémové sloupce přidává docela rád a ne vždy to člověka potěší. Při takovém SWITCH PARTITIONS to nepotěší nikdy ;) Nicméně, na otázku kolegy jsem mu nabídl vysvětlení extended statistic, Oracle 12c (v defaulutu zapnuto) si pro korelované sloupce v predikátech založí virtuální sloupec a nad nim spočitá statistiky. Což se nepotvrdilo. Určit exaktně o co přesně jde z následující tabulky:

<code>  property      number not null,           /* column properties (bit flags): */
/* 0×0001 =       1 = ADT attribute column                   */
/* 0×0002 =       2 = OID column                             */
/* 0×0004 =       4 = nested table column                    */
/* 0×0008 =       8 = virtual column                         */
/* 0×0010 =      16 = nested table’s SETID$ column           */
/* 0×0020 =      32 = hidden column                          */
/* 0×0040 =      64 = primary-key based OID column           */
/* 0×0080 =     128 = column is stored in a lob              */
/* 0×0100 =     256 = system-generated column                */
/* 0×0200 =     512 = rowinfo column of typed table/view     */
/* 0×0400 =    1024 = nested table columns setid             */
/* 0×0800 =    2048 = column not insertable                  */
/* 0×1000 =    4096 = column not updatable                   */
/* 0×2000 =    8192 = column not deletable                   */
/* 0×4000 =   16384 = dropped column                         */
/* 0×8000 =   32768 = unused column – data still in row      */
/* 0×00010000 =   65536 = virtual column                         */
/* 0×00020000 =  131072 = place DESCEND operator on top          */
/* 0×00040000 =  262144 = virtual column is NLS dependent        */
/* 0×00080000 =  524288 = ref column (present as oid col)        */
/* 0×00100000 = 1048576 = hidden snapshot base table column      */
/* 0×00200000 = 2097152 = attribute column of a user-defined ref */
/* 0×00400000 = 4194304 = export hidden column,RLS on hidden col */
/* 0×00800000 = 8388608 = string column measured in characters   */
/* 0×01000000 = 16777216 = virtual column expression specified    */
/* 0×02000000 = 33554432 = typeid column                          */
/* 0×04000000 = 67108864 = Column is encrypted                    */
/* 0×20000000 = 536870912 = Column is encrypted without salt       */

/* 0×000800000000 = 34359738368 = default with sequence                */
/* 0×001000000000 = 68719476736 = default on null                      */
/* 0×002000000000 = 137438953472 = generated always identity column    */
/* 0×004000000000 = 274877906944 = generated by default identity col   */
/* 0×080000000000 = 8796093022208 = Column is sensitive                */
</code>

No dobře, neni to přesně tabulka, ale jsou to vykopírované komentáře z rdbms/admin/dcore.bsq. A obecně jsou tyto typy souboru (admin/*.bsq) vhodné pro zkoumání, co která hodnota PROPERTY asi tak znamená (pro mně novinka).

Zkrátka jsme po chvíli přišli na to, že se jednalo o přidání sloupce s DEFALUTEM na NULLABLE sloupec – zatímco v 11g si Oracle musí naupdatovat všechny řádky s novou DEFULAT hodnotou, na 12c se jedná pouze o změnu definici tabulky a běh v řádu častí sekundy – ovšem za cenu nového hidden sloupce, který nyní udržuje informaci, zda-li je v řádku reálně NULL nebo DEFAULT hodnota. Krásný článek je o tom přímo u Oracle: http://www.oracle.com/technetwork/articles/database/ddl-optimizaton-in-odb12c-2331068.html. Který navíc krásně popisuje jak to pak funguje, když se na sloupec dotazuji (použití NVL na určení, co je je opravdová hodnota).

Takže zkouška:
CREATE TABLE T_TEST(a VARCHAR2(1), b VARCHAR2(2));
INSERT INTO T_TEST VALUES ('a', 'b');
INSERT INTO T_TEST VALUES ('a', 'b');
INSERT INTO T_TEST VALUES ('a', 'b');
ALTER TABLE T_TEST ADD (c VARCHAR2(1) DEFAULT 'z');
INSERT INTO T_TEST(a,b,c) VALUES ('a', 'b',null);
INSERT INTO T_TEST(a,b,c) VALUES ('a', 'b',null);
INSERT INTO T_TEST(a,b,c) VALUES ('a', 'b',null);

A nyní pohled na data (s jedním řádkem, před přidáním constrainu):

V tabulce je nově sloupce SYS_N00003$, který určuje, jestli se jedná reálně o NULL, nebo o sloupec ve kterém byl NULL před přidáním constrainu a tudíš by nyní měl obsahovat “z”. Oracle na to musí aplikovat funkce a rozhodnout, která hodnota je tedyy platná – ve sloupci C/default či SYS_N00003$. Jak to dělá ukazuje linknutý článek na Oracle.com – co je nutné podotknout, není to vidět před spuštěním ale až v reálném exekučním plánu. Článek to ukazuje na predikátu, ale stačí kouknout do PROJECTION : DECODE(TO_CHAR(SYS_OP_VECBIT(“SYS_NC00003$”,0)),NULL,NVL(“C”,’z’),’0′,NVL(“C”,’z’),’1′,”C”). A to samé si pak mohu použít v SQL:

Jak se toho zbavit?

Expost, popravdě nevím – sloupec nelze droponout (ORA-14148) a ani s pár pokusama změnit pod system PROPERTY jsem neuspěl :(. Nicméně jedná se o novinku a obyčejně Oracle “somehow” umožňuje nové chování potlačit. Obyčejně se jedná o zkryté parametry. Což se hledá velice špatně – většinou řeším LIKE + kombinace s hodnotou o které si myslím, že je právě nastavena. Tedy zkrytý parametr obsahující slovo NULL, který je nastaven na TRUE:

select * from (
SELECT
a.ksppinm Param ,
b.ksppstvl SessionVal ,
c.ksppstvl InstanceVal,
a.ksppdesc Descr
FROM
x$ksppi a ,
x$ksppcv b ,
x$ksppsv c
WHERE
a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '/_%' escape '/'
AND b.ksppstvl='TRUE'
ORDER BY
1)
where upper(descr) like '%NULL%'
;

S tím, že mi to našlo celkem 5 parametrů. Většina se týká joinu a pak _add_nullable_column_with_default_optim. Tento zkrytý parametr je možné nastavit i na úrovni session i na úrovni instance a neni statistký. Sice nepodporovaný, ale když si před přidáním DEFAULT hodnoty na NULLABLE sloupce vylatrujete session :

alter system set "_add_nullable_column_with_default_optim"=false;

So, nepříjemnost při SWITCHOVÁNÍ PARTITIONS či použití ALL_TAB_COLS místo ALL_TAB_COLUMNS nebo sexy věc při přidání DEFAULTu na sloupec (nyní nově na nullable sloupec) v tabulce s 100mil záznamy a během v řádu desetin sekundy ;) Podle toho, kdo jste ;)

 Posted by at 15:31
Dec 242015
 

Godiva trpaslici,  krátký post do nového roku. Všem kdo sem občas zabloudí přeji krásný další rok strávený v SQLplusu nebo někde jinde poblíž databáze. Vím, že jsem toho tady moc za ten rok nenapsal a příští rok to asi nebude lepší. Mým cílem je šlapat příští rok po certifikacích Oracle takže určitě mohu slíbit nějaký článek z nějakých témat kolem toho, až se budu připravovat. Mimo dvou na které již mam voucher a asi se na ně nebudu nějak výrazně připravovat mam vyhlídnuté následující:

  • Oracle Database Performance and Tuning 2015 Certified Implementation Specialist
  • Oracle Database as a Service (DBaaS) Cloud Certified Expert
  • Oracle Application Express Developer Certified Expert
  • + (něco neoracle – Java I nebo Anroid 401)

Je to seřazeno podle toho jak to aktuálně vidím, ale třebas to proházím. Technicky vzato je pro mě jednoznačně ta první nejzajímavější co se týče témat a obsahu – mimo takových těch známých ASH, AWR, SQL Tuning, Advisorové apod. jsou tam takové Real Application Testing, Performance Hub, SQL Performance Analyzer a Database Replay.. a i tak to vypadá více ukecaně než technicky. Druhá cloudová je jasná – všichni teď blbnou s těma mrakama, tak at mam taky nějakou mrakovou certifikaci, ale upřímně mi příprava určitě poleze krkem, kecy kecy kecy.. zkouška je aktuálně ve fázi BETA, což je příjemné – ještě se k tomu dokopávat nemusím ;) A poslední APEX – tam nevím, rád bych, minimální effort ovšem minule jsem to nechal být páč zkouška byla v BETA a lámala se verze APEXu, takže jsem usilování o certifikaci pozastavil. “plusko” je pak nějaké spíše zbožné přání o rozšíření mých znalostí mimo Oracle, heh.

Mimochodem certifikace je jedno z moha mých novoročních předsevzetí, obecně s tim mám problém – ne vždy je splním, ale typicky o to alespoň hodně usiluji.

Takže mimo článku na témta certifikací ještě budu chtít dokončit nějaké rozepsané vícepostovéposty a v neposlední řadě nějaké novinky z 12.1.  Speciálně ZoneMapy na které bych se strašně rád podíval, ale prostě není kurva čas!

Z Oracle nás v následujícím roce 2016 čeká nejspíše vydání Oracle 12.2.

Inu, všechno nejlepší do nového roku všem milovníkům databáze Oracle. Uživatelé MSSQL, MySQL a ostatní ať si trhnou nohou ;)

P.S.  V rámci přesunu do Švédska/Norska jsem si zabavil doménu http://plsql.se , protože pevně věřím že v roce 2016 přijde muj první post na blog ve jazyku švédském ;)

 Posted by at 21:57
Dec 052015
 

Godiva, minulý týden jsem měl možnost účasnit se školení, které poskytoval sir. Jonathan Lewis. Jestli u mě na blogu nejste poprvé zajisté jméno znáte. Nebudu hledat ty všechny články, kde jsem ho zmínil, ale minimálně jsem vychvaloval dvě jeho knihy CBO (přesný název Cost-Based Oracle Fundamentals) a Essential Internals for DBAs and Developers. Bohužel obě knihy ač jsou výborné trpí atributem “zastaralý”. Ostatně i sám Lewis to v úvodu CBO knihy píše, že vždy když si nastuduje nějakou verzi Oracle napíše k tomu knihu, že už v době releasu knihy je 2 verze Oracle pozadu.  A bohužel tim, že je často řeč na pomezí interních věcí Oracle, které nejsou standardizovány, se vše mění dost podstatně (v porovnáním s knížkama jinýma, kde nás před velkou změnou mezi verzemi chrání SQL standard či snaha o backwards compatibilty).  Ono vypadá, že některé věci by mohly zůstat přes všechny verze, protože třeba matematický výpočet selectivity na intervalu zůstane navždy stejný. Ovšem jakmile se přidají do toho histogramy, tak se to od verze od verze dost mění (ve 12c třebas nově větší histogramy >256 bucketu, Top N a hybridní histogram, interni vypocet je trošku jiný…). Tak či tak je to dobrá knížka a jedna z nejlepších o Oracle.

Přednáška byla rozdělat na tři části Designing Optimal SQL, Troubleshooting and Tuning a diskuze. Nejsem způsobilý k tomu, abych mohl recenzovat ani snad komentovat  jak někdo vykládá. Prostě vpořádku, vypadá jak typický britský účitel a vykládat dost dobře. Pouze jedna poznámka, co se mi tom hodně líbilo a což jsem od něj tak čakal: Velmi často řiká “možná”, “když budete mít štěstí” atd. Do výkladu vkládá krásnou nestabilitu, nikoliv protože by nevěděl, ale protože ví, že jsou za tim další komplikovaná rozhodnutí CBO, které třebas nemá čas popisovat a které se třeba objevují jen zřídka. Alá: “na pořadí v jakém napíšu tabulky do joinu ve skutečnosti záleží” – nicméně to je na úrovni internal Oracle a je strašně nepravděpodobné, že to někdy někdo z nás uvidí či to bude problém. Lewis sice píše pořadí tabulek ve FROM klauzuli v nějakém specifickém pořadí nicméně důvodem neni performance ale čitelnost.

Fakt luxusní povidání, třebas se mi hodně líbilo jak si dal pozor na to, aby uvedl na pravou míru, že na reverzním indexu je opravdu možné použít operaci “range scan”, ovšem pouze a jenom na equality operátoru, jinak bohužel. Většina knížek se s tim nesere a napíše, že prostě “range scan” neni na možný na reverse indexu. Někde na svém blogu se o tom zminoval. Ostatně většinu věcí co vykládal jsem poznával, že je relativní ke konkrétnímu postu na jeho blogu.

Technologie, co popisoval byly známé. Nicméně jsem si vědom, že je možné někdo nezná, tedy dohledejte si případně – budu jen vyjmenovávat. A na konci, co bylo nového pro mou psa a chci si zapamatovat:

Designing Optimal SQL:

Zmíněné featury: index_join ,index_combine, index rebuild, index coalescence, clusters, data analysis, qb_name, readibility, leading, index, use_nl, use_hash, swap:_joins_inputs, full, no_push_subq, materialize, sql baselines, or explain gather_plan_statistic, adaptive curshor sharing, index_ffs.

A taky ukazoval zjednodušené vzorce pro NL/HASH/MERGE joiny. Bohužel to strašně zjednodušil a zrovna tam by mi zajímalo jaký benefit má nová implementace NL v 11g kdy se lehce prohodilo pořadí v exekučním plánu a přidalo bufferovani, aby se zvednula pravděpodobnost brani bloku z cache.

Pokud je query bloku větší množství tabulek, tak Oracle vezme jednu, přijoinuje druhou, třetí, čvrtou. Občas ale chceme join mezi 1 a 4 a join mezi 2 a 3 a teprve pak najoinovat na sebe výsledky. De-facto rozpadnout jeden query block na dva. Jonathan to nazval dvěma pojmy, které jsem předtím nikdy neslyšel: Left-deep (to co dělá Oracle 1,2,3,4) a Bushy tree (to co se mu snažíme vnutit my pomocí with as, zavorek, redesingu, unnest a no_unnest (1,4)@(2,3)). Další pozoruhodná poznámka byla, že Oracle 12c už je v tomhle chytřejší.

A co si odnáším páč jsem nevěděl:

  • table_cached_blocks v dbms_stats u preferencích tabulky lze setnout i tenhle attribut
  • index rebuild online – nejde na indexech enforsujícíh PK/UK, dobré vědět. Nadruhou stranu na 11+ Oracle je rebuild potřeba jen vyjmečně a zrovna u PK, kde by se sloupec neměl updatovat vubec, to vubec nevadi ;)
  • index rebuild online – vyžaduje stejně lock při applikování journalu (stejně jako při switch dbms_redefinition)
  • sys_op_lbid – při mém štěstí jsem tuhle funkce už použil (jednou a nedávno :D), takže jsem věděl, nicméně stojí za připomenutí interní funkce vracící leaf block id z indexu na základě daného rowid
  • materialize hint – nemá to ověřené ale temp tabulka je vždy když tabulku z with as odkazujeme více než jednou, což by krásně odpovídalo tomu co dělá star_transformation u které je to tak nějak naznačeno přímo v dokumentaci
  • _optimalizer_adaptive_cursor_sharing – ha, zkrytý databázový parametr jak vypnout tuhle featuru ;) Na úrovni statementu by mělo stačit normálně NO_BIND_AWARE hint, který teda Oracle zdokumentoval až ve 12c, pokud se nepletu.

Troubleshooting and Tuning

Zmíněné featury: ASH, session trace,  sqlmonitoring, dbms_sqltune, v$sess_time_model , index reverse, cardinality hint, step by step slow processing, hprofiler, pragma udf, no segment indexy, invisible indexy, fixed views, waiting metriky, sql plan statistic, awr, obarvování sql, deklarace funkce with as v 12.1c

Jinak to bylo zajímavé povidání o konci tabulek a hot blokách, že trapných 600 session potkávajících se v library cache na jedno sql může udělat problém. A hodně řešil fixed views a data dictionary pohledy a ukazoval jak jsou napsány obecně a ne vždy se hodí z peformance důvodu – aneb view je query blok (pokud to oracle neztransformuje) optimalizovaný zvlášt a maximálně tam může pushnout predikát, což ne vždy stačí.

A co si odnáším páč jsem nevěděl:

  • index reverse – nikdy jsem nad tím nepřemýšlel, ale index reverse pochopitelně ovlivní clustering_factor (česky: nejspíše ho totálně rozmrdá)
  • temporary tabulky sice mají na 12c vlastní statistiky pro usery, ale ne by default, navíc jdou setnout do temporary tablespace
  • dbms_stats.num_array – musim si zkusit
  • mohu sám setnout clustering_factor na indexu
  • virtual column + check constraint (+hidden ideálně na 12c) může pomoci exekučnímu plánu. Check je pochopitelně vyčerveněno schválně, protože fakt to Oracle umí i s expression (COOL !)
  • pragma udf – uplně jsem na ní zapomněl. PL/SQL funkce volaná z SQL – pomůže to eliminovat overhead na přepinání contextu nez pl/sql a sql engines (ač tomu Lewis řikal lehce jinak). Nicméně na 12.1 je tohle rychlejšejší.
  • v$sql_workarea_histogram – luxusni view, neznal jsem

Diskuze

To bylo dost volné řešily se MVIEW Logy, ukazoval parallení exekuci a nějaká menší zmínka o DBMS_REDEFINITION,  SQL Baselines a pár nelichotivých poznámek na 12c ;) Na mých slovíčkách ze švédštiny se třebas objevil na 12c nový sloupec neb Oracle přišel na to, že je tam nějaká kolerace, takže si chlapec založil virtuální sloupec a hodil na něj extented statistiky, nicméně lze vypnout a jen nechat Oracle to reportovat.

A co si odnáším páč jsem nevěděl

  • treedump indexu bloku jsem nikdy nezkoušel, vypadá zajímavě (eventu nepamatuji)
  • Pokud jsem to dobře pochopil, tak rozpartišnování tabulky pujde na 12.2 ONLINE ;) wohowo
  • DBMS_REDEFINITION a aplikace journalu – Jonathan o tom mluvil, jako že aplikace journalu muže nějaký čas trvat a vyžaduje LOCK, musim zjistit – do ted jsem si myslel, že po synchronizaci hlavni tabulky, mužu/bude journal ještě pořád sychrnonizován online a obecně mužu dosynchrnizovovát libovolně dokud to neni skoro fresh a pak teprve LOCK.
  • 40x ? Jestli jsem slyšel dobře tak overhead na MATERIALIZED VIEW LOG je 40 rekurzivnich selectu – budu muset najit, přijde mi to podezřele vysoké

Bloom filtr – tohle je hroznej chyták, funguje to od Oracle 10g a zdokumentováno to bylo až v 12.1c. Lewis mluvil o tom, že v 11g se to neukazuje v exekučním plánu. Což je možná dobré upřesnit, protože záleží na tom který.  Bloom fitr na úrovni filtrování rows při paralelní exekuci je v plánu jen v OTHER/XML na 11.2. Ovšem i na 11.2 je vidět v exekučním plánu pokud je to na úrovni partitions – prostě eliminace partitions na bloom filtru.

Suma sumárum hodně děkuji svému zaměstanavateli za luxusní školení, nevim koho bych na školení raději než Lewise určitě zajímavější než Kyte. Možná ještě zajímavej by byl Julian Dyke (+ a pochopitelně jakákoliv prsatá samička, která třebas ani Oracle nezná ale nosí těsné džíny) ;)

 Posted by at 14:19
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